Ottimizzazione delle query pianificate BigQuery GA4

Da quando Google ha introdotto GA4 nell'ottobre 2020, tutti hanno discusso di UA contro GA4 invece di concentrarsi sul vero premio: estrarre valore aziendale dai dati ! Supponendo che tu abbia superato questa fase e ora hai completato la migrazione a GA4, collegato le tue proprietà a BigQuery e iniziato a raccogliere i tuoi impressionanti dati GA4. Potresti considerare di replicare i tuoi rapporti UA in BigQuery utilizzando GA4 o produrre nuovi rapporti automatizzati GA4.

Oltre al nuovissimo schema di dati basato sugli eventi, Google aggiorna l'esportazione giornaliera fino a 72 ore oltre la data della tabella. Perchè lo chiedi? Per accogliere eventuali eventi falliti o ritardati che potrebbero emergere dal protocollo di misurazione.

Ciò solleva una domanda essenziale: qual è il modo più efficiente per pianificare una query giornaliera senza duplicare i dati o perdere gli ultimi aggiornamenti? Ci sono quattro opzioni apparenti:

• Il primo è interrogare l'intero intervallo di date ogni giorno e sovrascrivere i risultati... ma questo approccio è troppo COSTOSO!
• Il secondo è pianificare una query separata per ciascuno dei tre giorni più recenti... ma poi dovresti aggiornare tre query ogni volta che apporti modifiche.
• L'opzione tre è interrogare gli ultimi tre giorni e quindi deduplicare... ma questa non è la soluzione più pulita.
• La quarta opzione consiste nell'impostare una funzione cloud che richiami una query pianificata quando i dati vengono aggiornati o arrivano tramite Cloud Logging. Una soluzione solida, certo, ma che richiede un serio Python/Node.js, ecc. know-how e richiede tempo per creare una tale pipeline.

#DECLARE VARIABLES AND TIMEFRAME
DECLARE 
  set_date date;
DECLARE
  final_date DEFAULT DATE_ADD(CURRENT_DATE(),INTERVAL -1 day); 
DECLARE
  initial_date date DEFAULT DATE_ADD(CURRENT_DATE(),INTERVAL -3 day); 
DECLARE
  formatted_date STRING;
SET set_date = initial_date;

# START FOR LOOP 
LOOP
SET
  set_date = DATE_ADD(set_date,INTERVAL 1 day);
SET
  formatted_date = FORMAT_DATE("%Y%m%d", set_date);
IF
  set_date > final_date THEN
LEAVE; 
END IF;
EXECUTE IMMEDIATE
#FORMAT QUERY 
  FORMAT("""
  CREATE OR REPLACE TABLE
  `{PROJECT_ID}.{DATASET_ID}.ga4_revenue_%s` AS
   ############ REVENUE FOR EACH USER ###############
      (
        SELECT
          user_pseudo_id,
          SUM(ecommerce.purchase_revenue) as revenue
        FROM
          `{PROJECT_ID}.{DATASET_ID}.events_*`
        WHERE
          (_TABLE_SUFFIX = '%s') AND user_pseudo_id IS NOT NULL
        GROUP BY
          1
        ORDER BY
          user_pseudo_id)
  
  """
  , formatted_date, formatted_date);
END LOOP;

Questa query ha ridotto il costo giornaliero dell'interrogazione dell'intero intervallo di date di un folle ~ 200% + (costi e percentuale varieranno in base al volume di dati storici, ovviamente). Quindi non solo stai ottenendo un grande valore dai tuoi dati, ma stai anche risparmiando un bel po' di denaro e mantenendo i tuoi rapporti sui dati puntuali.

Mentre mi avventuro nel nuovo universo GA4 all'interno di BigQuery, condividerò le mie conoscenze in una serie di blog.

Divertiti a eseguire il looping in BigQuery e non solo estrarre valore dai tuoi dati: fallo con stile!