Combinar tablas de GA4 intraday y diarias en BigQuery

Una pregunta muy común al extraer datos de GA4, es ¿cómo puedo combinar datos de las tablas intraday y diarias de Google Analytics4? Esto suele ser una práctica necesaria para obtener datos de periodos recientes, por ejemplo, número de usuarios por horas de la última semana, incluido el día de hoy. Pues en este artículo verás como hacerlo.

Diferencia entre tabla intraday y diaria

Las exportaciones de GA4 a BigQuery, siempre que tengas la función streaming activada, crearán tablas con dos formatos de sufijos diferentes:

• events_intraday_yyyymmdd: Para las tablas del día en curso o que aún no terminaron de procesarse por completo.
• events_yyyymmdd: Una vez el día fue procesado por completo, se elimina la tabla intraday y los datos son cargados en esta tabla.

Así se vería un ejemplo de tabla intraday:

bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_intraday_20231005

Así se vería un ejemplo de tabla diaria:

bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20231005

¿Cómo realizar una consulta SQL de un período sin importar si es intraday o no?

Esto te resultará muy útil en caso de necesitar llevarte las consultas a Looker Studio, ya que no sabrás que está consultando el usuario final. A continuación te explicamos el paso a paso de una consulta para obtener los _table_suffix de un período junto con el total de usuarios en cada tabla. Puedes aplicarla a cualquier otra consulta.

1. En el apartado SELECT incluimos los _table_suffix seleccionados y la suma de usuarios.
2. En el FROM seleccionamos el proyecto y conjunto de tablas a consultar.
3. En la cláusula WHERE filtramos por _table_suffix que empiece por events y se encuentre entre el 20231001 y la fecha actual. Con la expresión regular hacemos que la intraday o fechas concretas queden incluidas. Esta es la clave para combinar intraday con tablas diarias.
4. El GROUP BY es simplemente para que el conteo de usuarios sea por table_suffix.
5. ORDER BY no indica como queremos ordenar los datos.

SELECT
  _table_suffix AS table_suffix,
 COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  REGEXP_EXTRACT(_table_suffix,'[0-9]+') BETWEEN '20231001'
  AND FORMAT_DATE('%Y%m%d', current_date())
GROUP BY
  table_suffix
ORDER BY  table_suffix DESC;

Sustituye `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` por tu proyecto y tablas en BigQuery.

Si quieres hacerlo con fechas fijas también puedes. Ejemplo a continuación:

SELECT
  _table_suffix AS table_suffix,
 COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  REGEXP_EXTRACT(_table_suffix,'[0-9]+') BETWEEN '20231001'
  AND '20231002'
GROUP BY
  table_suffix
ORDER BY  table_suffix DESC;

Como siempre espero que este artículo te sirva de ayuda y si tienes alguna duda puedes dejármela en los comentarios o en el formulario de contacto.