GA4 en BigQuery: las consultas que contestan algo

La interfaz de GA4 contesta las preguntas que Google anticipó. BigQuery contesta el resto, y la entrada cuesta entender una sola cosa rara del esquema. Cuando eso hace clic, casi todo el análisis que querés en serio son cuarenta líneas de SQL.

Esto asume que la exportación ya está prendida. Si la prendiste hace poco y necesitás los meses anteriores, el histórico no se puede recuperar desde GA4 — conviene saberlo antes de planificar alrededor.

Lo único raro del esquema

En BigQuery, una fila es un evento. No una sesión, no un usuario. Y los parámetros que ese evento llevaba no viven en columnas: viven en event_params, un array de structs clave/valor:

event_date      = '20260929'
event_timestamp = 1759104000000000      -- microsegundos, no milisegundos
event_name      = 'purchase'
user_pseudo_id  = '1234567.8901234'
event_params    = [
  { key: 'ga_session_id',   value: { int_value: 1759103000 } },
  { key: 'page_location',   value: { string_value: 'https://...' } },
  { key: 'session_engaged', value: { string_value: '1' } }
]

Así que no podés escribir WHERE page_location = .... Entrás al array con una subconsulta escalar, y este patrón es cerca del 80% de todo el SQL de GA4:

SELECT
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'ga_session_id')  AS session_id
FROM `mi-proyecto.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930'
  AND event_name = 'purchase'

Dos detalles ahí adentro que importan más de lo que parecen:

events_* con _TABLE_SUFFIX es cómo se consultan tablas particionadas por día. El filtro no es opcional: si lo dejás afuera, cada consulta escanea todo tu histórico. Esa es la única forma de que esto salga caro.

event_timestamp está en microsegundos. Dividí por 1.000.000 para segundos, o usá TIMESTAMP_MICROS(). Tratarlo como milisegundos te manda los datos a 1970 y el error no siempre es evidente.

Sesiones y usuarios, bien contados

ga_session_id no es único por sí solo — es un entero derivado de un timestamp con alcance por usuario, así que dos usuarios pueden compartir uno. La clave de sesión es el par:

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS fecha,
  COUNT(DISTINCT user_pseudo_id) AS usuarios,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS sesiones,
  COUNTIF(event_name = 'purchase') AS compras,
  ROUND(SUM(ecommerce.purchase_revenue), 2) AS facturacion
FROM `mi-proyecto.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930'
GROUP BY fecha
ORDER BY fecha

Esto no va a coincidir exactamente con la interfaz de GA4, y eso es esperable, no un bug. La interfaz modela conversiones que no observó y reconcilia identidades; la exportación te da lo que se recolectó. Diferencias por debajo del 10% son normales. Por encima, revisá el rango de fechas y la clave de sesión antes de sospechar de la exportación.

Un funnel que te dice dónde se va la gente

La exploración de funnel de la interfaz alcanza hasta que necesitás un paso definido por algo que no te deja expresar. En SQL el paso es un COUNTIF y nada más:

WITH sesiones AS (
  SELECT
    CONCAT(user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ) AS clave_sesion,
    COUNTIF(event_name = 'view_item')      > 0 AS vio_producto,
    COUNTIF(event_name = 'add_to_cart')    > 0 AS agrego,
    COUNTIF(event_name = 'begin_checkout') > 0 AS fue_a_checkout,
    COUNTIF(event_name = 'purchase')       > 0 AS compro
  FROM `mi-proyecto.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260930'
  GROUP BY clave_sesion
)
SELECT
  COUNTIF(vio_producto) AS paso_1_producto,
  COUNTIF(agrego) AS paso_2_carrito,
  COUNTIF(fue_a_checkout) AS paso_3_checkout,
  COUNTIF(compro) AS paso_4_compra,
  ROUND(100 * COUNTIF(agrego)         / NULLIF(COUNTIF(vio_producto), 0), 1)   AS pct_producto_a_carrito,
  ROUND(100 * COUNTIF(fue_a_checkout) / NULLIF(COUNTIF(agrego), 0), 1)         AS pct_carrito_a_checkout,
  ROUND(100 * COUNTIF(compro)         / NULLIF(COUNTIF(fue_a_checkout), 0), 1) AS pct_checkout_a_compra
FROM sesiones

Cambiá cualquier paso por una condición propia — una página puntual, el valor de un parámetro, un tipo de dispositivo — y la forma de la consulta no cambia. Esa sustitución es toda la razón para estar acá adentro.

First-touch vs last-touch, sobre tus propios datos

Esta es la consulta que justifica la exportación. Los reportes de GA4 ya no te dejan comparar modelos — first-click, lineal y time-decay se eliminaron en 2023 —, pero los eventos crudos no traen ninguna atribución, lo que significa que podés asignar el crédito como quieras:

WITH contactos AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    COALESCE(collected_traffic_source.manual_source, 'direct') AS fuente,
    COALESCE(collected_traffic_source.manual_medium, 'none')   AS medio,
    event_name,
    ecommerce.purchase_revenue AS facturacion
  FROM `mi-proyecto.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260701' AND '20260930'
),
convertidores AS (
  SELECT user_pseudo_id, MIN(event_timestamp) AS convirtio_en, SUM(facturacion) AS facturacion
  FROM contactos
  WHERE event_name = 'purchase'
  GROUP BY user_pseudo_id
),
ordenados AS (
  SELECT
    c.user_pseudo_id,
    v.facturacion,
    CONCAT(c.fuente, ' / ', c.medio) AS canal,
    ROW_NUMBER() OVER (PARTITION BY c.user_pseudo_id ORDER BY c.event_timestamp ASC)  AS rank_primero,
    ROW_NUMBER() OVER (PARTITION BY c.user_pseudo_id ORDER BY c.event_timestamp DESC) AS rank_ultimo
  FROM contactos c
  JOIN convertidores v USING (user_pseudo_id)
  WHERE c.event_timestamp <= v.convirtio_en
)
SELECT
  canal,
  ROUND(SUM(IF(rank_primero = 1, facturacion, 0)), 2) AS facturacion_first_touch,
  ROUND(SUM(IF(rank_ultimo  = 1, facturacion, 0)), 2) AS facturacion_last_touch
FROM ordenados
GROUP BY canal
ORDER BY facturacion_last_touch DESC

Correla y las dos columnas van a estar en desacuerdo, a veces en serio. La búsqueda de marca y el directo se inflan del lado del last-touch; el paid social y el display se inflan del lado del first-touch. El tamaño de esa brecha es la medida práctica de cuánto depende tu reporte de canales de una decisión contable.

Un límite honesto: esto está armado sobre user_pseudo_id, que es una cookie en un navegador. Los recorridos cross-device aparecen como personas distintas, así que el camino real es más largo que cualquier cosa que esta consulta pueda ver. Si eso importa para tu negocio, la solución es user_id en el tráfico logueado más tracking server-side, no una función de ventana más ingeniosa.

Cómo mantener la factura en cero

El primer terabyte de consultas de cada mes es gratis y el almacenamiento son un par de centavos por gigabyte, así que la mayoría de las propiedades no paga nada. Tres hábitos lo mantienen así:

• Filtrá siempre _TABLE_SUFFIX. Este es el importante. Sin él escaneás todos los días que recolectaste en tu vida, cada vez.
• Nunca SELECT *. BigQuery cobra por columnas escaneadas. Nombrar cinco columnas en lugar de traer todas suele ser una diferencia de 10× sobre las mismas filas.
• Materializá lo que reusás. Si una transformación alimenta un dashboard, escribila a una tabla de forma programada en vez de recalcularla en cada refresh. Eso también es lo que evita que un dashboard que tu equipo abra sea tan lento que dejen de abrirlo.

Usá la estimación de bytes en seco de la consola antes de correr algo nuevo. Es gratis y te dice al instante si te olvidaste del filtro de fechas.

Cuatro trampas que conviene conocer antes

events_intraday_* es otra tabla. Si la exportación incluye streaming, los datos de hoy están en una tabla intradiaria aparte con una forma un poco distinta. events_* matchea las dos sin avisar, así que una consulta con wildcard puede mezclar días cerrados con uno parcial. Sé explícito cuando la distinción importe.

user_pseudo_id viene nulo cuando se deniega el consentimiento. Según tu configuración de consentimiento, algunos eventos llegan sin identificador. Son eventos reales que no se pueden agrupar en un usuario, e ignorarlo en silencio subestima tus totales.

(not set) es un valor real. Significa que esa dimensión no se recolectó para ese evento, no que no pasó nada. Filtralo a propósito en vez de dejarlo viajar dentro de un GROUP BY.

La facturación de ecommerce vive en dos lugares. ecommerce.purchase_revenue a nivel evento y items como registro repetido para el detalle por línea. Sumar los dos sin pensarlo duplica.

La versión corta

Una fila es un evento, los parámetros se esconden en event_params detrás de UNNEST, los timestamps son microsegundos, y _TABLE_SUFFIX es lo único que te separa de una factura sorprendente. Con esos cuatro hechos podés contar sesiones bien, armar un funnel con cualquier definición de paso que quieras y comparar modelos de atribución sobre tus propios caminos — que es el análisis que la interfaz no te va a dar a ningún precio.

Si tus reportes y tu facturación no coinciden y querés saber cuál de los dos miente, ahí es donde suelo empezar.