Otimização de consultas programadas do BigQuery do GA4

Desde que o Google introduziu o GA4 em outubro de 2020, todos estão discutindo UA versus GA4 em vez de focar no prêmio real - extrair valor comercial dos dados ! Supondo que você tenha progredido além desse estágio e agora tenha concluído a migração para o GA4, vinculou suas propriedades ao BigQuery e começou a coletar seus impressionantes dados do GA4. Você pode estar pensando em replicar seus relatórios do UA no BigQuery usando o GA4 ou produzir novos relatórios automatizados do GA4.

Além do novo esquema de dados baseado em eventos, o Google atualiza a exportação diária por até 72 horas após a data da tabela. Porque você pergunta? Para acomodar quaisquer eventos com falha ou atrasados ​​que possam surgir do Protocolo de Medição.

Isso levanta uma questão essencial: qual é a maneira mais eficiente de agendar uma consulta diária sem duplicar dados ou perder as atualizações mais recentes? Existem quatro opções aparentes:

• A primeira é consultar todo o intervalo de datas todos os dias e sobrescrever os resultados... mas essa abordagem é muito CARA!
• A segunda é agendar uma consulta separada para cada um dos três dias mais recentes... mas você teria que atualizar três consultas sempre que fizesse alterações.
• A terceira opção é consultar os últimos três dias e depois desduplicar... mas essa não é a solução mais limpa.
• A quarta opção é configurar uma Cloud Function que invoca uma consulta programada quando os dados são atualizados ou chegam por meio do Cloud Logging. Uma solução sólida, com certeza, mas que requer alguns Python/Node.js sérios, etc. know-how e leva tempo para configurar esse 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;

Essa consulta cortou o custo diário de consultar todo o intervalo de datas em aproximadamente 200% + (os custos e a porcentagem variam de acordo com o volume de dados históricos, é claro). Portanto, você não está apenas obtendo valor sério de seus dados, mas também economizando muito dinheiro e mantendo seus relatórios de dados atualizados.

À medida que me aventuro no novo universo do GA4 no BigQuery, compartilharei meus aprendizados em uma série de blogs.

Aproveite o loop For no BigQuery e não apenas extraia valor de seus dados — faça isso com estilo!