Optimización de consultas programadas de GA4 BigQuery

Desde que Google presentó GA4 en octubre de 2020, todos han estado discutiendo UA versus GA4 en lugar de centrarse en el premio real: ¡extraer valor comercial de los datos ! Suponiendo que haya progresado más allá de esta etapa y ahora haya logrado la migración a GA4, haya vinculado sus propiedades a BigQuery y haya comenzado a recopilar sus impresionantes datos de GA4. Es posible que esté considerando replicar sus informes de UA en BigQuery con GA4 o generar nuevos informes automatizados de GA4.

Además del nuevo esquema de datos basado en eventos, Google actualiza la exportación diaria hasta 72 horas después de la fecha de la tabla. ¿Porque preguntas? Para dar cabida a cualquier evento fallido o retrasado que pueda surgir del Protocolo de medición.

Esto plantea una pregunta esencial: ¿ cuál es la forma más eficiente de programar una consulta diaria sin duplicar datos o perder las últimas actualizaciones? Hay cuatro opciones aparentes:

• La primera es consultar el intervalo de fechas completo todos los días y sobrescribir los resultados... ¡pero ese enfoque es demasiado CARO!
• La segunda es programar una consulta separada para cada uno de los tres días más recientes... pero luego tendría que actualizar tres consultas cada vez que hiciera cambios.
• La opción tres es consultar los últimos tres días y luego desduplicar... pero esa no es la solución más limpia.
• La cuarta opción es configurar una función en la nube que invoque una consulta programada cuando los datos se actualicen o lleguen a través de Cloud Logging. Una solución sólida, sin duda, pero que requiere algo serio de Python/Node.js, etc. know-how y requiere tiempo para configurar dicha canalización.

#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;

Esta consulta redujo el costo diario de consultar todo el rango de fechas en un increíble ~200%+ (los costos y el porcentaje variarán según el volumen de datos históricos, por supuesto). Por lo tanto, no solo está obteniendo un gran valor de sus datos, sino que también está ahorrando mucho dinero y manteniendo sus informes de datos en el punto.

Mientras me aventuro por el nuevo universo GA4 dentro de BigQuery, compartiré mis aprendizajes en una serie de blogs.

Disfrute de bucles For en BigQuery y no solo extraiga valor de sus datos, ¡hágalo con estilo!