BigQuery es el data warehouse de Google. Forma parte de la suite de herramientas de Google Cloud Platform y te permite alojar -y explotar- bases de datos de gran tamaño a muy buen precio y con muy buen rendimiento (en realidad BigQuery se puede usar para otras muchas cosas, como por ejemplo para elaborar modelos de Machine Learning).
Google Analytics (tanto GA360 como GA4) tiene una integración nativa con BigQuery para exportar tus datos de analítica en bruto a este warehouse. Puedes hacer lo mismo con otras fuentes como por ejemplo un CRM como Salesforce o una plataforma como Google Ads. En todos estos casos puedes trabajar estos datos en bruto. Pero ojo, no sólo se trata de explotar cada base de datos por separado, sino de establecer relaciones entre todas ellas. Este es, bajo mi punto de vista, uno de los potenciales de trabajar con un data warehouse como BigQuery: establecer relaciones entre todas tus fuentes de información, acabar con los silos de datos. En el argot del SQL (el lenguaje de programación para explotar BBDD) esto es lo que se conoce como joins (uniones en castellano).
Pues bien, en este post te voy a enseñar a hacer un join entre dos datasets públicos que puedes encontrar en el proyecto bigquery-public-data: el dataset de Google Analytics 4 (GA4) (recoge datos de GA4 del ‘site’ https://shop.googlemerchandisestore.com/) y el dataset con datos del clima de todo el planeta de la organización National Oceanic and Atmospheric Administration (NOAA)
Para que veas el nivel de análisis que puedes lograr con BigQuery, voy a intentar averiguar si la tendencia de visitas a https://shop.googlemerchandisestore.com/ cambió en función de si llovió o no en enero de 2021.
Cómo se organizan los datos en BigQuery: proyectos, datasets y tablas
Es importante entender cómo se organizan los datos en BigQuery, tanto a la hora de alojarlos como de explotarlos. La jerarquía que se sigue es la siguiente:
Proyecto > Dataset > Tabla
Un proyecto puede englobar muchos datasets, y un dataset puede contener muchas tablas. Si navegas a BigQuery y despliegas el proyecto bigquery-public-data verás todos los datasets que ‘cuelgan’ de él. Así mismo, si despliegas un dataset (el de GA4, por ejemplo), verás todas las tablas que cuelgan de dicho dataset.
Es muy importante que tengas en cuenta en qué ubicación geográfica están alojados tus datos. Sólo podrás hacer joins entre tablas que estén alojadas en la misma región, de lo contrario tendrás que transferir los datos a la región correcta. Y recuerda que BigQuery es un producto de pago, pagas por almacenar los datos y por explotarlos. El rango de precios es atractivo, pero te aconsejo que lo tengas en cuenta.
El dataset público de Google Analytics 4 (GA4) en BigQuery
Para el ejercicio que voy a hacer en este post voy a usar el dataset público de GA4 en BigQuery: ga4_obfuscated_sample_ecommerce. Como te decía un poco antes, este dataset recoge datos del ‘site’ https://shop.googlemerchandisestore.com/
La estructura de los datos de GA4 en BigQuery es compleja, y es además diferente a la estructura de los datos de Universal Analytics (GA360) en BigQuery. Fíjate en los siguientes pantallazos:
Cada fila de la tabla hace referencia a un único evento (session_start, page_view, first_visit, etc.), y cada una de las columnas hace referencia a un parámetro de ese evento. La complejidad de la tabla de GA4 en BigQuery viene dada porque es una tabla anidada, es decir, cada celda puede contener varios valores. Parámetros como el parámetro geo son anidados. Parámetros como event_params son anidados pero además repetidos. Conocer esto es muy importante de cara a poder explotar los datos de GA4 en BigQuery.
Si recuerdas de lo que has leído al principio, me he propuesto averiguar si la tendencia de sesiones en enero de 2021 cambió en función de si llovió o no. Bien, para eso voy a necesitar los siguientes datos del dataset de GA4 en BigQuery:
- event_date → La fecha del evento.
- user_pseudo_id → Identificador de usuario (navegador) de GA4.
- event_name → Nombre de evento
- event_param de valor ga_session_number → Parámetro necesario para hallar si una visita es la primera de un usuario, o no.
- geo.country → País desde el que tiene lugar el evento de GA4.
No tengo sitio en este post para entrar a explicar en detalle el resto de campos que están disponibles en BigQuery para GA4. Si quieres informarte con más detalle, te recomiendo que consultes la documentación oficial del Google al respecto.
Listo, esta es la consulta que he preparado para obtener los anteriores datos:
with ga4_data as (
select
--Converting event_date string into date format
parse_date("%Y%m%d", event_date) as fecha,
user_pseudo_id,
event_name,
case
when (select value.int_value from unnest(event_params) where key = 'ga_session_number' and event_name = 'session_start') = 1 then 'visita nuevo usuario'
else 'visita usuario recurrente'
end as tipo_de_visita,
geo.country
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
where
_table_suffix between '20210101' and '20210131'
and event_name = 'session_start'
and geo.country = 'Spain'
group by
fecha,
user_pseudo_id,
event_name,
tipo_de_visita,
country
),Ten en cuenta que la anterior es una subquery, es decir, es una query que genera una tabla de datos que usaré en otra query más adelante. Esto es algo que ya hice en el post ‘BigQuery y Google Analytics (Universal): hallar la secuencia de páginas visitadas en una sesión’.
El resultado de la consulta es el siguiente:
Como ves, he generado una tabla en la que puedo ver a nivel de usuario (user_pseudo_id) cuántas sesiones han tenido lugar en España durante enero de 2021, tanto las acometidas por usuarios nuevos como por usuarios recurrentes. He filtrado los resultados para ver sólo datos de España con el objetivo de economizar la consulta. Recuerda que ejecutar consultas en BigQuery tiene un coste asociado.
El dataset del tiempo en BigQuery
De todos los campos que hay disponibles, me interesan tres:
- stn → El id de la estación climática
- date → La fecha
- rain_drizzle → Si llovió, o no
Voy a usar el campo stn para filtrar los datos y ver sólo los correspondientes a las estaciones de Madrid, así consigo economizar aún más la consulta. Pero en Madrid hay varias estaciones, y no todas han tomado mediciones en 2021, por lo que también hay que tomar en consideración este factor. Usaré los datos de la estación 082210, situada en Barajas, cuyo último es de febrero de 2022 (tienes toda esta información en la tabla stations del dataset noaa_gsod de BigQuery, aunque también puedes consultarla en el informe ‘Station History Reports’ de NOAA).
Bien, esta es la query que ejecuto para conseguir esta información (date cuenta de que de nuevo es una subquery, es decir, una query que uso para generar una tabla sobre la que lanzaré una consulta más adelante):
madrid_weather_data as (
select
date,
rain_drizzle
from
`bigquery-public-data.noaa_gsod.gsod2021`
where
stn = '082210'
),Y estos son los resultados:
El join entre las dos tablas: GA4 y los datos del tiempo
Ya tengo los datos de visitas de GA4 y los datos del tiempo de Madrid, ahora toca fusionarlos para poder explotarlos en su conjunto. Para ello voy a hacer un join, es decir, voy a fusionar ambas tablas en una sola. Aquí hay varias cosas que tienes que tener en cuenta:
- Cuando haces un join entre tablas debe haber un elemento en común entre ellas. Puede ser un id, un valor booleano, un número, etc. En este caso usaré el campo date, es decir, la fecha. De esta forma podré ver el dato de visitas (sacado del dataset de GA4) en la misma fila que los datos del tiempo (sacado del dataset de NOAA)
- Hay varios tipos de joins, y en este caso voy a hacer un left join. En el ejercicio que hago en este post esto quiere decir que uniré los datos del tiempo a los de GA4, y no al revés. Sólo se añadirán a los datos de GA4 los datos del tiempo que cumplan la condición del join, que en este caso es el de la fecha. El resto se descartan.
- Cuando haces un join estás seleccionando columnas de ambas tablas. Es importante que en tu query referencies la tabla en la que están cada una de estas columnas. Como verás en la siguiente query, yo uso los pseudónimos a. y b.
Venga, fíjate en la query que he configurado para hacer el join. Fíjate también en las dos subqueries que preceden a la consulta del join, son justo las que he creado en los anteriores apartados de este post. Ahora las puedes ver en acción. ¿Ves cómo simplifican la consulta? En lugar de configurar una única query, creas varias y simplificas el trabajo.
with ga4_data as (
select
parse_date("%Y%m%d", event_date) as fecha,
user_pseudo_id,
event_name,
case
when (select value.int_value from unnest(event_params) where key = 'ga_session_number' and event_name = 'session_start') = 1 then 'visita nuevo usuario'
else 'visita usuario recurrente'
end as tipo_de_visita,
geo.country
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
where
_table_suffix between '20210101' and '20210131'
and event_name = 'session_start'
and geo.country = 'Spain'
group by
fecha,
user_pseudo_id,
event_name,
tipo_de_visita,
country
),
madrid_weather_data as (
select
date,
rain_drizzle
from
`bigquery-public-data.noaa_gsod.gsod2021`
where
stn = '082210'
)
select
a.fecha,
case
when (a.tipo_de_visita = 'visita usuario recurrente' and b.rain_drizzle = '1') then count(user_pseudo_id)
else 0
end as visitas_usuarios_recurrentes_con_lluvia,
case
when (a.tipo_de_visita = 'visita nuevo usuario' and b.rain_drizzle = '1') then count(user_pseudo_id)
else 0
end as visitas_nuevos_usuarios_con_lluvia,
case
when (a.tipo_de_visita = 'visita usuario recurrente' and b.rain_drizzle = '0') then count(user_pseudo_id)
else 0
end as visitas_usuarios_recurrentes_sin_lluvia,
case
when (a.tipo_de_visita = 'visita nuevo usuario' and b.rain_drizzle = '0') then count(user_pseudo_id)
else 0
end as visitas_nuevos_usuarios_recurrentes_sin_lluvia,
from
ga4_data as a
left join madrid_weather_data as b
on a.fecha = b.date
group by
fecha,
tipo_de_visita,
rain_drizzle 
with ga4_data as (
select
parse_date("%Y%m%d", event_date) as fecha,
user_pseudo_id,
event_name,
case
when (select value.int_value from unnest(event_params) where key = 'ga_session_number' and event_name = 'session_start') = 1 then 'visita nuevo usuario'
else 'visita usuario recurrente'
end as tipo_de_visita,
geo.country
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
where
_table_suffix between '20210101' and '20210131'
and event_name = 'session_start'
and geo.country = 'Spain'
group by
fecha,
user_pseudo_id,
event_name,
tipo_de_visita,
country
),
madrid_weather_data as (
select
date,
rain_drizzle
from
`bigquery-public-data.noaa_gsod.gsod2021`
where
stn = '082210'
),
final_data as(
select
a.fecha,
case
when (a.tipo_de_visita = 'visita usuario recurrente' and b.rain_drizzle = '1') then count(user_pseudo_id)
else 0
end as visitas_usuarios_recurrentes_con_lluvia,
case
when (a.tipo_de_visita = 'visita nuevo usuario' and b.rain_drizzle = '1') then count(user_pseudo_id)
else 0
end as visitas_nuevos_usuarios_con_lluvia,
case
when (a.tipo_de_visita = 'visita usuario recurrente' and b.rain_drizzle = '0') then count(user_pseudo_id)
else 0
end as visitas_usuarios_recurrentes_sin_lluvia,
case
when (a.tipo_de_visita = 'visita nuevo usuario' and b.rain_drizzle = '0') then count(user_pseudo_id)
else 0
end as visitas_nuevos_usuarios_recurrentes_sin_lluvia,
from
ga4_data as a
left join madrid_weather_data as b
on a.fecha = b.date
group by
fecha,
tipo_de_visita,
rain_drizzle
)
select
fecha,
sum(visitas_usuarios_recurrentes_con_lluvia) as visitas_usuarios_recurrentes_con_lluvia,
sum(visitas_nuevos_usuarios_con_lluvia) as visitas_nuevos_usuarios_con_lluvia,
sum(visitas_usuarios_recurrentes_sin_lluvia) as visitas_usuarios_recurrentes_sin_lluvia,
sum(visitas_nuevos_usuarios_recurrentes_sin_lluvia) as visitas_nuevos_usuarios_recurrentes_sin_lluvia
from
final_data
group by
fecha 
Reflexiones finales
Cada vez me gusta más trabajar con BigQuery. Me doy cuenta de que explotar los datos de Google Analytics (ya sean los de GA360 o GA4) a través de este warehouse permite hacer análisis muy elaborados. Explotar tus datos de analítica en bruto, calcular tus propias métricas, objetivos, agrupaciones por dimensiones… BigQuery posibilita hacer análisis adhoc con los datos de tu negocio.
Pero donde realmente veo el potencial de esta herramienta es en la posibilidad de establecer relaciones entre bases de datos. Ya no analizas tus datos de analítica por un lado y los de tu CRM por otro, los relacionas en origen y generas tablas de datos conjuntas.