¡Aprende las Funciones Básicas de BigQuery y Consigue tus Primeros Resultados!

En el post anterior vimos cómo sincronizar una propiedad de GA4 con BigQuery, la herramienta de Google Cloud Platform diseñada para almacenar y consultar grandes volúmenes de datos. En él se anima a Marketers y Analistas Digitales a dar el primer paso y aprender el lenguaje de consulta más popular y extendido en el sector, el SQL (en inglés sequel). Hoy queremos avanzar un pasito más explicando las funciones básicas de BigQuery.

Recuerda que en julio de 2023 UA dejará de recopilar información, forzando a las empresas a migrar definitivamente hacia GA4, donde la conexión con BigQuery es totalmente nativa. Más pronto que tarde empezaremos a escuchar demandas de clientes para combinar distintas fuentes de datos como el CRM, herramientas de email marketing, Google Analytics, campañas publicitarias o archivos CSV. Te recomiendo leer este artículo: «Tres en uno. Unificar datos de distintas fuentes, 2021, Víctor Corral».

En este segundo post explicamos cómo empezar a hacer funcionar la herramienta. Aprenderás qué estructura debe tener una query, cuáles son las funciones básicas de BigQuery más utilizadas para analizar una tabla y entenderás como ejecutar la función UNNEST, básica si trabajas con Google Analytics. Además, veremos un ejemplo real que podrás poner en práctica utilizando los proyectos públicos de BigQuery. En concreto, utilizaremos la tabla del Ecommerce Merchandise Store de Google. Pero si te interesan otros temas como los censos electorales, datos sobre el Covid-19, demografía global, inversiones, etc también los encontrarás en este enlace oficial. 

¡Adelante Vídeo! Funciones Básicas de BigQuery 

Si prefieres ver el contenido de este artículo en vídeo, aquí debajo lo tienes: 

La Herramienta desde Dentro

Una vez conseguida la sincronización de GA4 con BigQuery y haber creado el dataset de tu proyecto verás algo así “analytics_123456789” en el desplegable de datasets. Esta es tu tabla de Google Analytics. 

Al hacer click sobre ella lo primero que debemos hacer para validar que los datos se están recopilando correctamente es utilizar la funcionalidad Preview. Esta permite previsualizar la tabla. Recuerda que la herramienta es de pago cuando se superan los 10 GB de almacenaje o 1TB al mes de análisis. La previsualización está exenta de costes. 

• Recomendación: No ejecutes consultas para explorar los datos de las tablas ni obtener una vista previa de ellos. Si exploras tus datos o experimentas con ellos, puedes usar las opciones de vista previa de tablas de forma gratuita sin afectar las cuotas. Aquí puedes ver la documentación oficial de Google

Te dispones a ver la tabla y…. ¡Sorpresa!

Enseguida reconoces que la tabla no tiene un aspecto al que estamos acostumbrados como podría ser una hoja de Excel, si no que para un mismo valor hay distintas filas, algunas mostrando información y otras con null. Esto es porque los datos de Google Analytics están anidados. En programación, NEST (anidado) describe un código que realiza una función particular y que se encuentra dentro de otro código que realiza una función más amplia. 

• Definición aplicada a la tabla que ves en BigQuery: Para cada evento que se recoge en GA4 se le asocian unos parámetros y cada uno de estos parámetros tiene un valor que puede ser un string, integer o float. 

Así se ve una tabla anidada:

La Estructura del Lenguaje SQL

Una query se estructura a partir de comandos y funciones. Probablemente habrás oído acerca de la función SELECT(*), pero cuidado, no vayas a seleccionar everything de primeras. Si te acostumbras a ejecutar esta función para consultar bases de datos, podrías tener alguna sorpresa si la aplicas con grandes volúmenes. Recuerda, Previsualiza la tabla primero. Lo más recomendable es realizar queries con la siguiente estructura:  

SELECT 
FROM
WHERE
GROUP BY
ORDER BY

Definición de los Comandos

SELECT → Se utiliza para especificar los nombres de los campos que contienen los datos en una consulta. Es decir, el nombre que recibe cada columna de la tabla. Los campos que indiquemos son los que se mostrarán en el output de la consulta. Por ejemplo: event_date, event_name, event_params.key

FROM → Hace referencia a la tabla donde se realiza la selección de los datos. En la sección Details del proyecto encontrarás el campo Table ID que muestra el nombre exacto que debes utilizar en el comando. En el caso de BigQuery se debe añadir el símbolo (`) al principio y al final de la especificación de la tabla. Por ejemplo: `nombre-proyecto.analytics_123456789.events_20220917`

WHERE → Especifica los criterios que tienen que cumplir los valores de campo para que los registros que contienen los valores se incluyan en los resultados de la consulta. Sirve para aplicar filtros que incluyan los valores que nos interesen o para excluir aquellos que no queremos mostrar en el output.  Por ejemplo: un rango de fechas o el nombre de un evento concreto.

GROUP BY → Identifica una columna seleccionada para agrupar los resultados repetidos y los devuelve en una fila de resultados para cada grupo. Por ejemplo: Imagina una tabla con 100 filas. En cada una de ellas se muestra la  dimensión device_category de evento concreto. Con este comando se agruparán todas las filas solamente en tres categorías: mobile, desktop y tablet. Combinado con un conteo los 100 hits del evento se disglosarán en las 3 filas. Por ejemplo: mobile 60, desktop 30 y tablet 10.

ORDER BY → Como su nombre indica, ordena los registros resultantes de una consulta por un campo o campos especificados en orden ascendente o descendente.

Funciones Principales

Existen centenares de funciones en SQL que puedes encontrar en este enlace de W3 Schools. A continuación mostramos las funciones básicas de BigQuery más comunes.

CONTEOS, SUMAS, RESTAS, AVG, MAX, MIN
Operaciones matemáticas y estadísticas

• COUNT() → Contabiliza el total de registros
• COUNT(DISTINCT) → Contabiliza el total de registros únicos
• SUM() → Realiza la suma
• AVG() → Calcula el promedio
• MAX() → Devuelve el valor máximo
• MIN() → Devuelve el valor mínimo

CONDICIONES WHERE
Uso de filtors para eventos y parámetros que contengan un valor concreto.

• != “2022-06-29” (is not that date)
• > “2022-06-29” (bigger than)
• < “2022-06-29” (minor than)
• >= “2022-06-29” (equal or bigger)
• <= “2022-06-29” (equal or minor)
• IS NOT NULL (elimina los nulls)
• BETWEEN (valor 1) AND (valor 2) 
• LIKE "%abc%" (coincide con la expresión)

FECHAS
Podemos transformar event_date o event_timestamp en diferentes formatos. 

• PARSE_DATE("%Y%m%d",event_date) AS date_1,
• FORMAT_DATE('%d-%m-%Y', PARSE_DATE('%Y%m%d', event_date)) AS date_2,
• FORMAT_DATE('%m-%d-%Y', PARSE_DATE('%Y%m%d', event_date)) AS date_3
  

CASE WHEN 
La sentencia utiliza expresiones de igualdad dentro de una lista de selección (when) para alterar la salida (then), indicando que al detectar un valor concreto, se encasille en una categoría. Un ejemplo común son los rangos.

• WHEN budget >= 99999 THEN "more than 100.000€"
• WHEN budget BETWEEN 50000 AND 99999 THEN "50.000€ - 100.000€"
• WHEN budget BETWEEN 25000 AND 49999 THEN "25.000€ - 50.000€"
• WHEN budget BETWEEN 10000 AND 24999 THEN "10.000€ - 25.000€"
• WHEN budget BETWEEN 5000 AND 9999 THEN "5.000€ - 10.000€"
• WHEN budget BETWEEN 1000 AND 4999 THEN "1.000€ - 5.000€"
• WHEN budget BETWEEN 100 AND 999 THEN "less than 1.000€"
  

Ahora que ya conoces las principales funciones, ¡Pasamos a la parte práctica!

UNNEST Function

Cada vez que se consigue un hit en Google Analytics se registra un log en la tabla con todos los parámetros asociados a ese evento en la misma fila. Por eso, para consultar una tabla de GA4 no queda otro remedio que entender la función UNNEST, una de las funciones básicas de BigQuery más imprescindibles. Cuando la ejecutamos, estamos modificando la tabla para tener acceso a los valores anidados. Y, para tener acceso a la información de cada parámetro, es necesario indicar a qué valores en concreto queremos acceder.

Para entenderlo mejor, veamos un ejemplo con la tabla de Ecommerce de la tienda de Merchandising de Google: Por cada evento queremos saber todos los parámetros que se le asocian, el Id del producto y el nombre. Como hemos podido detectar en la previsualización de la tabla, tanto los parámetros de eventos como de ítems se encuentran anidados. Por lo tanto, hay que utilizar la función para ambos. 

SELECT event_name, event_params.key, event_params.value.string_value,
event_params.value.int_value,items.item_id, items.item_name

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`,
UNNEST (event_params) AS event_params,
UNNEST (items) AS items

¡Conseguido! Hemos desanidado la tabla. Ahora tiene el aspecto al que estamos acostumbrados: filas únicas y columnas.
Aunque si te fijas bien, el resultado muestra 190.856 filas  😅😅

Algo debemos hacer aquí, ¿verdad?

En Busca del Dato

A continuación vamos a ejecutar una query que nos ayude a entender exactamente qué eventos forman esta tabla de Ecommerce. A partir de aquí, podremos valorar cuáles son interesantes para el análisis. Para ello, seleccionaremos solamente los eventos y el número de veces que se cumple. 

SELECT event_name AS event_name, COUNT (event_name) AS counting
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`

GROUP BY event_name
ORDER BY counting DESC

El resultado de la consulta es una tabla con el nombre de todos los eventos existentes en la tabla y el conteo de veces que se han registrado.
Ahora la tabla es mucho más entendible y accionable. Si además añadimos la función SUM(COUNT(event_name)) OVER () obtendremos la suma total de la columna counting. El resultado es de 26.489. Y para saber el porcentaje que representa cada evento respecto al total, podemos añadir la función COUNT (event_name) / SUM(COUNT(event_name)) OVER () * 100

Como has podido comprobar, ejecutar una Query significa transformar una pregunta en una secuencia de código SQL. En este caso ¿Cuántos eventos únicos tiene la tabla? Si quieres verificarlo solo tienes que ejecutar la siguiente función SELECT COUNT (DISTINCT event_name).

Veámos un par de ejemplos más.

Ejemplo 1: page_view + params

¿En qué páginas del sitio web se han generado más visitas?

Para responder a esta pregunta necesitamos filtrar la tabla para que devuelva solamente los resultados del evento page_view y de su parámetro page_title. Como hemos podido observar en el Schema de la tabla, el valor del parámetro se recoge con un valor string. Por lo tanto, dentro de los parámetros anidados nos interesa solamente acceder a event_params.value.string_value. Cómo además queremos contar el número de veces que se ha visitado dichas páginas, utilizaremos la función COUNT y un GROUP BY para que las agrupe. 

SELECT event_params.value.string_value, COUNT(event_name) AS counting, COUNT(event_name) / SUM(COUNT(event_name)) OVER () * 100  AS percentage
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`,
UNNEST (event_params) AS event_params

WHERE event_name = 'page_view' AND event_params.key = 'page_title'
GROUP BY event_params.value.string_value
ORDER BY counting DESC

Ejemplo 2: add_to_cart + items

¿Qué productos se añaden más al carrito de la compra?

Para conseguir este dato necesitamos una query que devuelva el nombre de los productos y el conteo de veces que se ha añadido en el carrito. Una vez más, utilizaremos un filtro para que solo se incluyan los resultados del evento add_to_cart y utilizaremos un COUNT y un GROUP BY para que cada producto ocupe solamente una fila.

SELECT items.item_name, items.item_id, COUNT(event_name) AS counting
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`,UNNEST (items) AS items
WHERE event_name = 'add_to_cart'
GROUP BY items.item_id, items.item_name
ORDER BY counting DESC

Accionables

Ahora que ya conoces las funciones básicas de BigQuery y sabes como acceder a los eventos principales de un Ecommerce y a los parámetros que se le asocian, puedes empezar a hacerte preguntas que se transformen en acciones concretas para mejorar los resultados del negocio. Te dejo aquí algunos ejemplos para que practiques: 

• ¿Qué eventos forman el funnel de ventas de este Ecommerce?
• ¿Cuál es la tasa de añadir al carrito de este Ecommerce?
• ¿Qué acciones podrías realizar en la página de producto para mejorar esta tasa?
• ¿Coinciden el top 5 productos añadidos al carrito con el top 5 más vendidos?
• ¿Cuál es la tasa de conversión de este Ecommerce?
• ¿Qué fuente de tráfico tiene la conversión más elevada?

En el siguiente artículo desvelaremos las respuestas. Para entonces, te dejamos unas semanas para que vayas experimentando con la herramienta.