Ecommerce Performance Insights

1. El problema

La Google Merchandise Store (https://shop.merch.google/) vende merchandising de Google, Android y YouTube.

Las preguntas que llevan a mirar estos datos son tres: - ¿Cuál es la conversión del ecommerce? - ¿Los productos que más facturan en volumen son los que mejor convierten? - ¿Qué categoría convierte mejor? ¿Es la que más factura?

Para poder responder estas preguntas se hace dos cosas. Primero, se audita la tabla de eventos para saber qué se puede medir y qué no. Después, con lo que sobrevive a esa auditoría, se intenta responder las preguntas.

Sin embargo, para poder responder a las preguntas de negocio, no podemos ir con la premisa que los datos vienen limpios, tenemos que responder antes una pregunta importante para poder analizar los datos:

2. Los datos

3. Diagnóstico

Antes de armar un reporte debemos tener un contexto de los datos y del negocio. Esto debido a que no siempre los datos vienen limpios y además es necesario saber cómo están estructurados sus datos.

La documentación del esquema de la tabla que vamos a analizar lo encontramos en el siguiente artículo: https://support.google.com/analytics/answer/ . Sin embargo, esto solo nos dice como está estructurado es esquema. Lo que debemos revisar son los datos específicos del negocio.

Los eventos

3.1 ¿Qué está midiendo la tienda?

Para saber si la tienda puede responder la pregunta de negocio principal, primero hay que ver qué mide. Y conviene no llegar buscando eventos concretos: en GA4 los nombres de evento los decide quien implementó la medición, así que no hay garantía de que existan los estándar, ni de que los que existan hagan lo que su nombre sugiere.

Lo que se pide es el mapa completo, sin filtrar.

SELECT
  event_name,
  COUNT(*) AS eventos
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
GROUP BY event_name
ORDER BY eventos DESC

Diecisiete tipos de evento, con los nombres estándar de ecommerce de GA4. Y entre ellos está el embudo entero: view_item → add_to_cart → begin_checkout → purchase.

Eso es más de lo que suele haber. Sobre el papel, la tienda tiene lo que hace falta para responder la pregunta.

3.2 ¿Qué eventos traen productos?

La pregunta es sobre productos, así que hay que construir el universo de productos antes de medir nada. Y para eso hay que saber de dónde salen: en GA4 los productos no son columnas, viajan anidados en un ARRAY llamado items que no todos los eventos llevan.

Hasta el momento podemos utilizaritem_id como la clave del producto.

SELECT
  event_name,
  COUNT(*)                  AS filas,
  COUNT(DISTINCT i.item_id) AS productos_unicos
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND i.item_id IS NOT NULL AND i.item_id NOT IN ('(not set)', '')
GROUP BY event_name
ORDER BY filas DESC

Ocho eventos traen productos, no los cuatro del embudo. select_item aporta 640 y hasta view_promotion trae uno. Si el universo se sacara solo del embudo se dejarían fuera eventos que sí identifican productos.

Los productos

3.3 ¿Cuántos productos hay en total?

Aun con la contradicción anterior sin resolver, el conteo global es el que fija la base sobre la que se trabaja.

SELECT COUNT(DISTINCT i.item_id) AS productos_en_total
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND i.item_id IS NOT NULL AND i.item_id NOT IN ('(not set)', '')

Mil trescientos noventa y tres productos con actividad en tres meses. Es una cifra alta para una tienda de merchandising, pero no imposible — y de momento no hay con qué contrastarla.

Conviene dejar dicho qué es y qué no es este número. No es el catálogo de la tienda: solo aparece lo que tuvo algún evento en la ventana analizada. El stock es dinámico, así que un producto que existía y nadie miró no está aquí, y no hay forma de saberlo desde estos datos. Como no hay tabla de dimensión de productos, esto es lo más cerca que se puede estar del catálogo.

3.4 ¿Están completos esos productos?

Un producto necesita como mínimo un identificador y un nombre. Toca comprobar que los 1,393 tengan las dos cosas.

No basta con preguntar si tienen nombre alguna vez: un identificador puede traerlo en una fila y no traerlo en las otras diez mil, y eso ya contaría como "tiene nombre". Hay que separar los tres casos.

WITH por_id AS (
  SELECT
    i.item_id AS id,
    COUNTIF(i.item_name IS NULL OR i.item_name IN ('(not set)',''))          AS sin_nombre,
    COUNTIF(i.item_name IS NOT NULL AND i.item_name NOT IN ('(not set)','')) AS con_nombre
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND i.item_id IS NOT NULL AND i.item_id NOT IN ('(not set)','')
  GROUP BY id
)
SELECT
  COUNT(*)                                   AS ids_totales,
  COUNTIF(sin_nombre = 0)                    AS siempre_con_nombre,
  COUNTIF(sin_nombre > 0 AND con_nombre > 0) AS a_veces_sin_nombre,
  COUNTIF(con_nombre = 0)                    AS nunca_con_nombre
FROM por_id

La comprobación inversa —agrupar por item_name y contar los que no traen identificador— no encuentra ninguno: todos los nombres vienen siempre con su id.

Ninguno se queda sin nombre del todo, así que el filtro no descarta ningún producto. Lo que descarta la comprobación es otra cosa: una entidad que nunca debió estar en la base.

Entró porque en la pregunta 2 se admitió cualquier evento con items, y los de promoción no traen productos sino campañas. Es la decisión de alcance que faltaba declarar: una promoción no es un producto. Fuera view_promotion y select_promotion, la base queda en 1,392.

3.5 ¿item_id e item_name cuentan lo mismo?

Con los eventos de promoción fuera, la base baja a 1,392 identificadores. Sobre esa base ya limpia toca la comprobación siguiente: hay un segundo campo que también parece identificar productos, y si los dos identifican lo mismo tienen que dar el mismo número.

SELECT
  COUNT(DISTINCT i.item_id)   AS identificadores,
  COUNT(DISTINCT i.item_name) AS nombres
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name NOT IN ('view_promotion', 'select_promotion')
  AND i.item_id   IS NOT NULL AND i.item_id   NOT IN ('(not set)', '')
  AND i.item_name IS NOT NULL AND i.item_name NOT IN ('(not set)', '')

No cuentan lo mismo. Como cada fila es una pareja de identificador y nombre, comparar los dos totales ya dice de qué lado se rompe la relación:

Así que item_id no identifica productos: identifica alguna otra cosa, más fina, que se repite dentro del mismo producto.

Se ve mejor con un producto cualquiera de los 416:

SELECT DISTINCT i.item_name, i.item_id
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND i.item_name = 'Google Land & Sea French Terry Sweatshirt'
ORDER BY i.item_id

Una sudadera, catorce identificadores. Agrupando por item_id serían catorce productos en el análisis; agrupando por nombre, uno.

3.6 ¿Están los 429 productos en los eventos que vamos a analizar?

La pregunta de negocio necesita, para cada producto, cuánta gente lo vio y cuánta lo compró. De poco sirve tener 429 productos si no aparecen en las dos puntas del embudo.

WITH por_producto AS (
  SELECT
    i.item_name AS producto,
    MAX(IF(event_name = 'view_item',      1, 0)) AS vista,
    MAX(IF(event_name = 'add_to_cart',    1, 0)) AS carrito,
    MAX(IF(event_name = 'begin_checkout', 1, 0)) AS checkout,
    MAX(IF(event_name = 'purchase',       1, 0)) AS compra
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
    AND i.item_name IS NOT NULL AND i.item_name NOT IN ('(not set)','')
  GROUP BY producto
)
SELECT vista, carrito, checkout, compra, COUNT(*) AS productos
FROM por_producto
GROUP BY vista, carrito, checkout, compra
ORDER BY productos DESC

379 de 429 recorren el embudo entero. Otros 24 se quedan a mitad —se ven, se añaden al carrito y nunca se compran—, y eso es normal en cualquier tienda.

Pero hay una fila que no puede existir: 8 productos con compra y sin vista. Nadie compra algo que no vio.

3.7 ¿Con qué productos nos quedamos?

Los 8 imposibles no se pueden analizar: sin vistas no hay denominador, así que no tienen conversión. Antes de excluirlos conviene verlos, porque un producto que se vende sin registrar visitas es un problema de medición que alguien debería arreglar.

WITH por_producto AS (
  SELECT
    i.item_name AS producto,
    COUNTIF(event_name = 'view_item') AS vistas,
    COUNTIF(event_name = 'purchase')  AS compras
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
    AND i.item_name IS NOT NULL AND i.item_name NOT IN ('(not set)','')
  GROUP BY producto
)
SELECT producto, vistas, compras
FROM por_producto
WHERE vistas = 0 AND compras > 0
ORDER BY compras DESC

164 compras sin una sola visita registrada, y las ocho son ropa.

La hipótesis más razonable es que en la mitad alta del embudo estos productos se llamen de otra forma —Womens Google Striped LS frente a algún Google Women's Striped L/S— y que el nombre, igual que antes el identificador, tampoco los una. Con estos datos no se puede confirmar: haría falta el catálogo para saber si dos nombres son el mismo artículo.

Aun así el nombre sigue siendo la clave. Con item_id el problema no sería de ocho productos sino de 416, y además inventaría productos que no existen. Cambiar de clave no arregla esto: lo empeora.

Para el análisis se quitan, y la base queda así:

Esas 645 compras —un 4% del total— quedan fuera de cualquier cálculo por producto. No es un error del análisis, es una limitación de los datos, y se declara.

Las categorías

3.8 ¿Cada producto tiene una sola categoría?

Las preguntas de negocio no son solo por producto, también por categoría. Lo ideal es que cada producto tenga una y solo una.

Lo natural sería empezar descartando los productos sin categoría, pero todavía no se puede: eso daría por hecho que existe una categoría de catálogo y que sabemos dónde está. En este punto item_category es un campo del que no sabemos nada.

Primero hay que comprobar si es fiable; el descarte viene después y depende de lo que salga.

WITH base AS (
  SELECT DISTINCT i.item_name AS producto
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'view_item'
    AND i.item_name IS NOT NULL AND i.item_name NOT IN ('(not set)','')
),
cat AS (
  SELECT i.item_name AS producto, COUNT(DISTINCT i.item_category) AS categorias
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
    AND i.item_name     IS NOT NULL AND i.item_name     NOT IN ('(not set)','')
    AND i.item_category IS NOT NULL AND i.item_category NOT IN ('(not set)','')
  GROUP BY producto
)
SELECT categorias, COUNT(*) AS productos
FROM cat JOIN base USING (producto)
GROUP BY categorias
ORDER BY categorias

3.9 ¿Ocurre lo mismo en todos los eventos?

Un campo puede comportarse distinto según quién lo escribe. Antes de descartarlo conviene mirar evento por evento.

SELECT
  event_name,
  COUNT(*)                AS productos,
  COUNTIF(categorias = 1) AS con_una_sola,
  MAX(categorias)         AS maximo
FROM (
  SELECT event_name, i.item_name AS producto, COUNT(DISTINCT i.item_category) AS categorias
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
    AND i.item_name     IS NOT NULL AND i.item_name     NOT IN ('(not set)','')
    AND i.item_category IS NOT NULL AND i.item_category NOT IN ('(not set)','')
  GROUP BY event_name, producto
)
GROUP BY event_name
ORDER BY productos DESC

En purchase y begin_checkout la categoría sí es única por producto. En view_item y add_to_cart casi nunca.

Para saber por qué hay que mirar qué guarda de manera más frecuente cada uno:

SELECT
  event_name,
  i.item_category,
  COUNT(*) AS filas
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
  AND i.item_category IS NOT NULL AND i.item_category NOT IN ('(not set)','')
GROUP BY event_name, i.item_category
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_name ORDER BY filas DESC) <= 2
ORDER BY filas DESC

view_item y add_to_cart guardan la ruta de navegación por la que llegó el usuario. Un producto alcanzable desde ofertas, desde hombre y desde Apparel acumula tres rutas, y ocho de media. begin_checkout y purchase guardan una etiqueta plana.

Queda una duda razonable antes de fiarse de la etiqueta plana: en la página de compra no hay ruta de navegación, así que quizá ese valor no describa el producto sino la página. Si fuera así, todos los productos de una misma compra tendrían la misma categoría.

WITH productos_comprados AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST(event_timestamp AS STRING)) AS compra,
    i.item_category                                              AS categoria
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'purchase'
    AND i.item_category IS NOT NULL AND i.item_category NOT IN ('(not set)','')
),
por_compra AS (
  SELECT
    compra,
    COUNT(*)                  AS productos,
    COUNT(DISTINCT categoria) AS categorias
  FROM productos_comprados
  GROUP BY compra
)
SELECT
  COUNT(*)                AS compras_con_2_o_mas_productos,
  COUNTIF(categorias = 1) AS todos_la_misma_categoria,
  COUNTIF(categorias > 1) AS con_categorias_distintas,
  MAX(categorias)         AS maximo_en_una_compra
FROM por_compra
WHERE productos >= 2

No es de la página: 2,768 compras llevan categorías distintas dentro del mismo evento, y una llega a doce. Si el valor viniera de la página, las 3,399 tendrían una sola.

3.10 ¿Todos los productos tienen categoría de catálogo?

La categoría buena solo aparece en begin_checkout y purchase. Un producto que se ve pero nunca llega a esas etapas no la tiene en ninguna parte.

WITH analizables AS (
  SELECT DISTINCT i.item_name AS producto
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'view_item'
    AND i.item_name IS NOT NULL AND i.item_name NOT IN ('(not set)','')
),
con_categoria AS (
  SELECT DISTINCT i.item_name AS producto
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('begin_checkout','purchase')
    AND i.item_name     IS NOT NULL AND i.item_name     NOT IN ('(not set)','')
    AND i.item_category IS NOT NULL AND i.item_category NOT IN ('(not set)','')
)
SELECT
  (SELECT COUNT(*) FROM analizables)                                     AS productos_analizables,
  (SELECT COUNT(*) FROM analizables JOIN con_categoria USING (producto)) AS con_categoria_de_catalogo,
  (SELECT COUNT(*) FROM analizables
   WHERE producto NOT IN (SELECT producto FROM con_categoria))           AS sin_categoria_de_catalogo

No se pueden recuperar desde estos datos. La mayoría sí tiene valores de categoría en item_category dentro de view_item, pero esos son rutas de navegación —la sección anterior lo demostró— y no describen al producto. No es una categoría que se esté desperdiciando: es otro campo que no sirve para esto.

Las vistas de producto

3.11 ¿Qué significa una vista?

Ya sabemos qué productos analizar. Falta comprobar qué mide el evento que va a hacer de denominador en todas las preguntas de conversión.

El supuesto es simple: view_item es alguien entrando a ver un producto. Si eso es cierto, cada evento debería traer exactamente un producto.

SELECT
  ARRAY_LENGTH(items) AS productos_en_el_evento,
  COUNT(*)            AS eventos
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name = 'view_item'
GROUP BY productos_en_el_evento
ORDER BY productos_en_el_evento

Dos cosas rompen el supuesto por separado.

Solo 8,726 eventos traen un producto, un 2% del total. En el 98% restante, o no hay ninguno o hay doce.

El tope está en 12 y ahí se acumula el 57%. Un número redondo repetido en 219,633 eventos no es comportamiento de usuario: es el tamaño de algo.

4. Las respuestas

El diagnóstico deja una base clara y un límite duro. Estas son las tres preguntas del principio, con lo que se puede afirmar de cada una.

4.1 ¿Cuál es la conversión del negocio?

Sí se puede. Es la única de las tres que no necesita saber qué producto se vio, así que el defecto de view_item no la afecta.

Antes de calcularla hay que comprobar que el evento de compra no se repita, porque si se repite el numerador queda inflado.

WITH e AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params)
           WHERE key = 'ga_session_id') AS STRING)) AS sesion,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaccion
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'purchase'
)
SELECT
  COUNT(*)                                          AS eventos_de_compra,
  COUNT(DISTINCT transaccion)                       AS transacciones_distintas,
  COUNTIF(transaccion IS NULL)                      AS sin_transaction_id
FROM e

Los 906 eventos sin transaction_id no se pueden deduplicar y se conservan tal cual. Con esa regla quedan 5,372 transacciones.

Ya se puede calcular. Hay dos formas de expresarla y no dicen lo mismo:

WITH transacciones AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params)
           WHERE key = 'ga_session_id') AS STRING)) AS sesion,
    COALESCE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id'),
      CONCAT(user_pseudo_id, '-', CAST(event_timestamp AS STRING))
    ) AS transaccion,
    event_timestamp
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'purchase'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaccion ORDER BY event_timestamp) = 1
),
sesiones AS (
  SELECT CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params)
         WHERE key = 'ga_session_id') AS STRING)) AS sesion
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  GROUP BY sesion
)
SELECT
  (SELECT COUNT(*) FROM sesiones)                    AS sesiones,
  (SELECT COUNT(*) FROM transacciones)               AS transacciones,
  (SELECT COUNT(DISTINCT sesion) FROM transacciones) AS sesiones_con_compra,
  ROUND((SELECT COUNT(*) FROM transacciones) /
        (SELECT COUNT(*) FROM sesiones) * 100, 2)    AS a_transacciones_por_sesion,
  ROUND((SELECT COUNT(DISTINCT sesion) FROM transacciones) /
        (SELECT COUNT(*) FROM sesiones) * 100, 2)    AS b_sesiones_que_compran
FROM sesiones LIMIT 1

La A mide volumen: cuántas compras genera cada visita. Puede pasar de 100% si una sesión compra varias veces.

La B mide probabilidad: qué parte de las visitas acaba comprando. Nunca pasa de 100%, y es la que reporta GA4 como conversión de sesión y contra la que se comparan referencias del sector.

La diferencia entre las dos —1.49 frente a 1.35— son las 524 sesiones que hicieron más de una compra.

4.2 ¿Los productos que más facturan son los que mejor convierten?

Solo la mitad. La facturación por producto sale de purchase, que trae el ingreso y el producto, y no necesita vistas.

WITH compras AS (
  SELECT
    COALESCE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id'),
      CONCAT(user_pseudo_id, '-', CAST(event_timestamp AS STRING))
    ) AS transaccion,
    event_timestamp, items
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'purchase'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaccion ORDER BY event_timestamp) = 1
)
SELECT
  i.item_name                           AS producto,
  COUNT(*)                              AS unidades,
  ROUND(SUM(IFNULL(i.item_revenue, 0))) AS ingresos
FROM compras, UNNEST(items) AS i
WHERE i.item_name IS NOT NULL AND i.item_name NOT IN ('(not set)','')
GROUP BY producto
ORDER BY ingresos DESC
LIMIT 8

Ya asoma algo: los joggers venden más unidades que nadie —273— y quedan terceros en ingresos. El Tech Fleece vende 107, cuatro veces menos, y se queda a 465 dólares de ellos. Unidades e ingresos no ordenan igual.

Lo que no se puede es la otra mitad. La conversión por producto necesita saber cuánta gente vio cada uno, y la sección 11 demostró que view_item no mide eso.

4.3 ¿Qué categoría convierte mejor? ¿Es la que más factura?

Mismo reparto. La facturación por categoría se puede, sobre los 390 productos que tienen categoría de catálogo.

WITH compras AS (
  SELECT
    COALESCE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id'),
      CONCAT(user_pseudo_id, '-', CAST(event_timestamp AS STRING))
    ) AS transaccion,
    event_timestamp, items
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'purchase'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaccion ORDER BY event_timestamp) = 1
),
categoria_de_catalogo AS (
  SELECT producto, categoria FROM (
    SELECT
      i.item_name     AS producto,
      i.item_category AS categoria,
      ROW_NUMBER() OVER (PARTITION BY i.item_name ORDER BY COUNT(*) DESC) AS rn
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items) AS i
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
      AND event_name IN ('begin_checkout','purchase')
      AND i.item_name     IS NOT NULL AND i.item_name     NOT IN ('(not set)','')
      AND i.item_category IS NOT NULL AND i.item_category NOT IN ('(not set)','')
    GROUP BY producto, categoria
  ) WHERE rn = 1
),
ventas AS (
  SELECT i.item_name AS producto, IFNULL(i.item_revenue, 0) AS ingreso
  FROM compras, UNNEST(items) AS i
  WHERE i.item_name IS NOT NULL AND i.item_name NOT IN ('(not set)','')
)
SELECT
  c.categoria,
  COUNT(DISTINCT v.producto) AS productos,
  COUNT(*)                   AS unidades,
  ROUND(SUM(v.ingreso))      AS ingresos
FROM ventas v
JOIN categoria_de_catalogo c USING (producto)
GROUP BY c.categoria
ORDER BY ingresos DESC
LIMIT 8

Apparel factura seis veces más que la siguiente. Y aparece una categoría llamada Uncategorized Items que mueve 21,139 — el propio catálogo tiene productos sin clasificar, además de los 31 que no traen categoría en ningún sitio.

5. Los hallazgos

Once comprobaciones, y cada una cambió algo. Se separan en dos grupos, porque el destinatario es distinto.

5.1 Lo que se resuelve analizando

Defectos del dato que se sortean eligiendo bien. Son decisiones del análisis y no requieren tocar nada del sitio.

5.2 Lo que no se arregla desde SQL

Aquí el análisis no puede hacer nada. Son fallos de medición y quien los corrige es quien implementó el sitio.

5.3 Qué habría que arreglar, en orden

1. Disparar view_item en la ficha de producto, con ese producto en items. Es lo único que desbloquea la conversión por producto, que es la pregunta original.
2. Usar el mismo identificador de producto en las cuatro etapas. Hoy la mitad alta manda SKU y la baja un identificador por talla.
3. Separar la categoría del catálogo de la ruta de navegación en dos campos distintos.