¿Trabajas con Google Analytics (Universal Analytics)? Te habrás dado cuenta de que es fácil llegar a los límites de esta herramienta. Hay preguntas que sencillamente no se pueden responder a través de GA. ¿Cuál es la secuencia completa de páginas visitadas por cada uno de tus usuarios? ¿Se repite en cada una de sus sesiones? ¿Qué pasa con tus usuarios cuando han enviado un formulario de contacto y sus leads están alojados en un CRM?
Google Analytics es una herramienta de medición muy potente y muy customizable. Funciona muy bien para medir la captación de tráfico (fuente/medio) y su rendimiento posterior (¿cumple -o no- los objetivos de tu ‘site’?) También es una plataforma muy buena para medir el rendimiento de los productos de tu comercio electrónico (si has implementado Enhanced Ecommerce) en tu ‘site’ o ‘app’. Pero llega un momento en el que necesitas ir un paso más allá
Exportando tus datos de GA a BigQuery consigues una granularidad total, almacenas tus datos de analítica en bruto. Esto te permite hacer todas las operaciones de análisis que puedas imaginar. Ten en cuenta que BigQuery no es gratis: pagas por el almacenamiento de datos y por su extracción. Eso sí, hay una versión gratuita con la que puedes hacer pruebas
En este post te voy a explicar cómo puedes configurar una query para responder a la siguiente pregunta analizando tus datos de Universal Analytics en BigQuery:
“Para las sesiones que arrancan en mi site a través de una determinada página, ¿cuál es la secuencia de páginas que visitan hasta convertir?»
Universal Analytics (UA) vs. Google Analytics 4 (GA4) en BigQuery
Es importante que sepas que en este post voy a trabajar con datos de Universal Analytics, no de GA4, que es el nuevo estándar de GA. Merece la pena hacer la siguiente aclaración:
-El volcado de datos de Universal Analytics a BigQuery está reservado para versiones ‘enterprise’ de Google Analytics (GA360), aunque hay conectores (casi siempre de pago) que puedes usar para tu versión estándar de Universal Analytics.
-El ‘export’ de datos de Google Analytics 4 a BigQuery es gratis, viene integrado por defecto en todas las propiedades de GA4
-El modelo de datos de UA y GA4 es diferente, y esto se refleja en las tablas que se crean en BigQuery: en el caso de UA, cada fila es una sesión, en el caso de GA4 cada fila es un evento.
El dataset público de Google Analytics (Universal Analytics) en BigQuery
He creado las queries que te detallo a continuación usando el dataset público que Google Analytics pone a disposición de todo el mundo en el proyecto bigquery-public-data. El nombre del dataset es google_analytics_sample, la tabla se llama ga_sessions. Los datos que se vuelcan aquí son del site shop.googlemerchandisestore.com
Primer paso: crea tu tabla de origen
Para poder extraer datos de Bigquery tienes que trabajar con SQL. Este lenguaje de programación te permite trabajar con bases de datos de muchas formas diferentes. Yo te propongo una estructura de queries WITH AS: queries que generan tablas sobre las que se lanzan otras queries. Pienso que es una forma de trabajar organizada, pero puedes hacerlo de otras muchas formas.
Bien, para responder a la pregunta “para las sesiones que arrancan en mi site a través de una determinada página, ¿cuál es la secuencia de páginas que visitan hasta convertir? voy a crear la siguiente tabla de origen:
select
fullVisitorId,
visitId,
isEntrance,
type,
hitNumber,
page.hostname,
page.pagePath
from
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
unnest(hits) as hits
where
_table_suffix between '20170801' and '20170801'
and type = 'PAGE' Como ves, la anterior query proporciona todas las dimensiones que se necesitan para lograr el análisis. No entraré en significado de cada uno de ellas, ya que este post ser haría interminable. Si tienes dudas lo mejor es que consultes el ‘Esquema BigQuery de Universal Analytics’ (yo lo consulto con frecuencia).
A continuación te muestro una foto de una muestra de los resultados que devuelve la anterior query:
Segundo paso: encontrar la primera página de cada sesión
Si quieres hacer tu análisis en base a la página a través de la cual arrancan las visitas de tu site, vas a necesitar conocer la primera página de cada una de tus sesiones, es decir: la página de destino. Esto se puede hacer a través de la función analítica first_value(). Fíjate:
with raw_data as(
select
fullVisitorId,
visitId,
isEntrance,
type,
hitNumber,
page.hostname,
page.pagePath
from
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
unnest(hits) as hits
where
_table_suffix between '20170801' and '20170801'
and type = 'PAGE')
--Se crea tabla landing_page_data en la que se obtiene la página de destino.
select
fullVisitorId,
visitId,
isEntrance,
type,
first_value(pagePath) over (partition by fullVisitorId,visitId order by hitNumber asc) as paginaDestino,
hitNumber,
hostname,
pagePath
from
raw_data¿Lo ves? Acabas de obtener la primera página que ha visitado cada una de tus sesiones en la columna paginaDestino. Para ello has envuelto tu query original en un WITH AS statement y la has llamado raw_data. La query final (la que devuelve los datos que ves en la foto de debajo), se hace sobre la tabla raw_data.
Ahora puedes actualizar la anterior query para ver los datos de las visitas que arrancan a través de la página de destino que quieras.
Tercer paso: ¿qué conversión te interesa medir?
En BigQuery no vas a encontrar las conversiones que puedas tener creadas en Universal Analytics (ni ninguna otra métrica en realidad). Tienes que crearlas de cero. Para este ejercicio voy a suponer que la conversión se produce cuando un usuario llega a la siguiente url: https://shop.googlemerchandisestore.com/basket.html
Voy a usar de nuevo la tabla de origen que creé al principio para generar una columna en la que se recoja esta url siempre. El ejercicio es el mismo que en la anterior query: envuelvo la tabla original dentro de un WITH AS statement y la llamo raw_data. Lanzo la query que devuelve los datos que busco sobre esta tabla raw_data.
with raw_data as(
select
fullVisitorId,
visitId,
isEntrance,
type,
hitNumber,
page.hostname,
page.pagePath
from
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
unnest(hits) as hits
where
_table_suffix between '20170801' and '20170801'
and type = 'PAGE')
--Se crea tabla conversion_data en la que se obtiene visitas a la página /basket.html
select
fullVisitorId,
visitId,
isEntrance,
type,
hitNumber,
pagePath as paginaCesta
from
raw_data
where
pagePath like '%basket%'
Último paso: toca hacer el join de las dos tablas
Ya tienes las dos partes que necesitas para tu análisis. Por un lado una tabla con la secuencia de páginas visitadas por las sesiones que han arrancado a través de una url determinada. Por otro, otra con las sesiones que han visitado -y por lo tanto han convertido- la página https://shop.googlemerchandisestore.com/basket.html Ahora tienes que juntar ambas.
Para ello voy a hacer un left join, ya que voy a fusionar la segunda tabla (la de las conversiones) con la primera (la de la secuencia de páginas visitadas). Hace falta una clave en común entre ambas tablas. En este caso usaré la columna visitId. Pero ojo, lo correcto sería concatenar los campos fullVisitorId y visitId, ya que ese es el único identificador de sesión real para Universal Analytics en BigQuery. Por algún motivo mi CMS no me permite usar la función de SQL para concatenar, por lo que no me queda más remedio que usar el visitId. Yo creo que para la muestra datos con la que estoy trabajando será suficiente.
Fíjate en que ahora estoy creando dos nuevas tablas partiendo de raw_data: landing_page_data y conversion_data. Como ves, esto trabajando con dos subqueries WITH AS que llaman a la misma tabla inicial. Fíjate, además, que estoy filtrando la query final para ver sólo sesiones que han arrancado a través de páginas que contengan ‘office’ en su url.
--Se crea tabla raw_data que actúa como fuente de datos. Aquí se aplican filtros de fechas
with raw_data as(
select
fullVisitorId,
visitId,
isEntrance,
type,
hitNumber,
page.hostname,
page.pagePath
from
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
unnest(hits) as hits
where
_table_suffix between '20170801' and '20170801'
and type = 'PAGE'),
--Se crea tabla landing_page_data en la que se obtiene la página de destino.
landing_page_data as(
select
fullVisitorId,
visitId,
isEntrance,
type,
first_value(pagePath) over (partition by fullVisitorId,visitId order by hitNumber asc) as paginaDestino,
hitNumber,
hostname,
pagePath
from
raw_data),
--Se crea tabla conversion_data en la que se obtiene visitas a la página /basket.html
conversion_data as(
select
fullVisitorId,
visitId,
isEntrance,
type,
hitNumber,
pagePath as paginaCesta
from
raw_data
where
pagePath like '%basket%')
--Se hace join (left) entre las dos tablas (landing_page_data y conversion_data) para obtener secuencia de páginas visitadas por sesiones que arrancan a través de una url determianda y convierten
select
landing_page_data.fullVisitorId,
landing_page_data.visitId,
landing_page_data.isEntrance,
landing_page_data.type,
landing_page_data.paginaDestino,
landing_page_data.hitNumber,
landing_page_data.hostname,
landing_page_data.pagePath,
conversion_data.paginaCesta
from
landing_page_data
left join conversion_data
on landing_page_data.visitId = conversion_data.visitId
where
landing_page_data.paginaDestino like '%office%'
and conversion_data.paginaCesta != 'null'
Reflexiones finales
Me dedico a la definición e implementación de planes de medición, al desarrollo de analítica. Ese es mi foco y mi especialidad. No obstante, hay muchas veces que trabajo en BigQuery haciendo extracciones de datos o creando nuevos datasets. A veces porque el cliente lo solicita y no hay nadie más que pueda hacerlo. Otras por necesidad, para auditar los datos de Google Analytics (por ejemplo) en bruto.
“Quién mucho abarca, poco aprieta” dice el dicho. Y cuando se trata de la analítica digital creo que es cierto. Implementar analítica y extraer datos alojados en bases de datos (como las tablas de BigQuery) supone trabajar en dos polos opuestos de este oficio: en la recolección de datos (JavaScript) y en su posterior extracción (SQL).
Dicho lo cual, el caso es que me gusta mucho trabajar en BigQuery. Además, creo que visualizar la estructura de un dataset y de una tabla en BigQuery (o cualquier otro data warehouse) es muy bueno de cara a definir un plan de medición y una estrategia de implementación.