GA4 + BigQuery para eCommerce: margen y LTV por SEO

1. Por qué GA4 no basta: revenue ≠ beneficio (casos reales)

GA4 es excelente para medir comportamiento y conversiones, pero en eCommerce suele fallar justo donde se toman decisiones serias: rentabilidad. En GA4 ves revenue, pero normalmente no ves (o está incompleto) el coste de producto (COGS), el coste de envío real, el coste de pasarela, los descuentos netos, las devoluciones o el impacto de cupones de atención al cliente. Resultado: optimizas “ingresos” cuando deberías optimizar margen.

Casos típicos que vemos en consultoría:

• SEO trae ventas, pero de productos con margen bajo: una categoría con mucho volumen orgánico puede ser “top” en GA4 y, sin embargo, destruir el margen por devoluciones o por COGS alto.
• Landing con alta conversión y baja contribución: páginas informativas que empujan a productos en promoción, con descuentos agresivos, elevan el revenue pero reducen el beneficio neto.
• Mix de catálogo sesgado: SEO posiciona fichas antiguas con stock residual y peor margen, mientras Ads empuja best-sellers con mejor margen. Sin BigQuery, no lo detectas.

Además, en 2026 el problema de atribución se agrava: restricciones de cookies, tráfico desde entornos con tracking limitado y el auge de resultados con IA (AI Overviews, asistentes y respuestas directas) hacen que el “canal” en GA4 sea menos fiable si no lo refuerzas con datos propios (CRM/ERP y server-side).

La solución práctica: usar GA4 como capa de evento y adquisición, y usar BigQuery como capa de unificación y cálculo. Si quieres acelerar esto, en servicios de analítica y consultoría SEO lo montamos como framework replicable para eCommerce, con dataset listo y dashboards que apuntan a margen y LTV, no a métricas vanidosas.

2. Arquitectura recomendada: GA4 → BigQuery → CRM/ERP

La arquitectura que mejor escala (y más “a prueba de atribución”) es una tubería simple y mantenible:

1. GA4: recoge eventos, sesiones, fuentes/medios, landing pages, items y transacciones (purchase).
2. BigQuery: recibe exportación diaria (y opcionalmente intradía), donde haces limpieza, normalización y modelos (vistas/tablones).
3. CRM/ERP: aporta la verdad económica: COGS, estado del pedido, devoluciones, reembolsos, costes operativos por pedido, y el identificador de cliente.

Patrón recomendado de datos (mínimo viable):

• GA4 events (export nativa) → dataset analytics_XXXX.
• Tabla de pedidos (ERP) con un ID unívoco: order_id, order_date, gross_revenue, discount, shipping_revenue, tax, refund_amount, cogs, payment_fees, shipping_cost, customer_id.
• Tabla de clientes (CRM) con customer_id, first_order_date, country, segment, consent_flags (si aplica).

¿Cómo se conectan? Idealmente por order_id (transacción) y por customer_id (vida del cliente). GA4 tiene transaction_id y puede tener user_id. La clave es decidir un estándar y aplicarlo siempre: el mismo order_id que vive en tu ERP debe viajar a GA4 en el evento purchase. Y el mismo customer_id del CRM debe mapearse a user_id (cuando haya login o identificación).

Si tu web necesita mejoras para tracking, dataLayer, server-side o rendimiento (que impacta en SEO y conversión), revisa optimización web y medición. Un modelo de margen/LTV falla si los IDs no están bien instrumentados.

3. Setup paso a paso (GA4 export + tablas necesarias)

En abril de 2026, la forma estándar sigue siendo: GA4 → BigQuery vía integración nativa, y luego cargas tus datos propios (ERP/CRM) con conectores o jobs programados. Pasos mínimos:

1. Activa la exportación de GA4 a BigQuery (daily export). Si necesitas casi tiempo real para operaciones, habilita también intradía.
2. Verifica el esquema: en BigQuery tendrás tablas tipo events_YYYYMMDD con campos event_params, user_properties, traffic_source, y eCommerce en items.
3. Normaliza IDs:
   • transaction_id (GA4) = order_id (ERP).
   • user_id (GA4) = customer_id (CRM), cuando sea posible.
4. Crea dataset “mart”: un dataset separado (por ejemplo mart_ecommerce) con vistas o tablas materializadas: fact_orders, dim_product, dim_landing, dim_channel, fact_customer_ltv.
5. Carga ERP/CRM: al menos una vez al día. Puedes hacerlo con jobs SQL, Cloud Functions o automatización. Si trabajas con n8n, el patrón típico es: extraer del ERP (API), normalizar, upsert en BigQuery.

Tablas mínimas recomendadas (nombres orientativos):

• raw_ga4.events_*: export GA4 sin tocar.
• raw_erp.orders: pedidos y economía por pedido.
• raw_erp.order_items: detalle por SKU (cantidad, revenue por ítem, cogs por ítem si existe).
• raw_crm.customers: atributos de cliente y fechas clave.
• mart.orders_enriched: unión final para análisis (margen, canal, landing, categoría).

Consejo operativo: evita recalcular todo cada vez. Materializa por partición de fecha (por order_date) y usa cargas incrementales. Esto reduce coste y acelera dashboards.

4. Modelo de datos: pedidos, devoluciones, coste y cliente

Para medir margen por canal y LTV por SEO, tu modelo debe resolver dos fricciones: (1) la economía real está en ERP/finanzas, y (2) la adquisición está en GA4. El modelo recomendado se basa en una tabla de hechos de pedidos enriquecida con dimensiones de adquisición y contenido.

Hecho principal: pedido (order-level). Campos clave:

• Identidad: order_id, customer_id, order_timestamp.
• Ingresos: gross_revenue, discount_amount, net_revenue (defínelo), impuestos si los separas.
• Costes: cogs, shipping_cost, payment_fees, packaging_cost (si lo tienes).
• Devoluciones/reembolsos: refund_amount, return_status, return_date. Si puedes, separa “devuelto” de “reembolsado”.
• Métricas derivadas: gross_margin y net_margin (según tu definición).

Dimensiones de adquisición y contenido (desde GA4):

• Primer canal / primer touch (para LTV): first_user_source, first_user_medium, first_user_campaign.
• Canal de la compra (para margen por canal): session_source, session_medium, default_channel_group o tu agrupación custom.
• Landing page (para SEO): landing_page o page_location del inicio de sesión.

Importante: define una sola fórmula de margen y documenta qué incluye. Ejemplo pragmático:

• Margen bruto = net_revenue - cogs.
• Margen contribución = net_revenue - cogs - payment_fees - shipping_cost.

Si tu negocio usa devoluciones masivas, considera un modelo “as-of” (margen ajustado a 30/60 días) para no premiar canales que venden mucho y devuelven mucho. En el método de SEOAGIL solemos separar “margen inmediato” y “margen confirmado” para que SEO no se optimice a ciegas.

5. SQL listo: margen por landing, categoría y producto

A continuación tienes SQL orientativo para BigQuery. Asume:

• ERP en raw_erp.orders y raw_erp.order_items.
• GA4 export en raw_ga4.events_*.
• Que transaction_id en GA4 coincide con order_id en ERP.

Objetivo: calcular margen por landing SEO, y desglosar por categoría/producto (si tu ERP o catálogo lo permite).

-- 1) Extrae información de sesión/landing asociada a la compra (GA4)
WITH purchases AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS order_id,
    event_date,
    user_pseudo_id,
    user_id,
    traffic_source.source AS user_source,
    traffic_source.medium AS user_medium,
    (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 ga_session_id
  FROM `raw_ga4.events_*`
  WHERE event_name = 'purchase'
),

-- 2) Landing de la sesión: primera page_view de esa sesión
session_landings AS (
  SELECT
    e.user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    MIN(e.event_timestamp) AS first_ts,
    ANY_VALUE((SELECT value.string_value FROM UNNEST(e.event_params) WHERE key = 'page_location')) AS landing_page
  FROM `raw_ga4.events_*` e
  WHERE e.event_name = 'page_view'
  GROUP BY 1,2
),

-- 3) Une compra con landing
purchase_enriched AS (
  SELECT
    p.order_id,
    p.event_date,
    COALESCE(sl.landing_page, p.page_location) AS landing_page,
    p.user_source,
    p.user_medium
  FROM purchases p
  LEFT JOIN session_landings sl
    ON sl.user_pseudo_id = p.user_pseudo_id
   AND sl.ga_session_id = p.ga_session_id
),

-- 4) ERP: margen por pedido
orders_margin AS (
  SELECT
    o.order_id,
    o.order_timestamp,
    o.customer_id,
    o.net_revenue,
    o.cogs,
    o.shipping_cost,
    o.payment_fees,
    o.refund_amount,
    (o.net_revenue - o.cogs) AS gross_margin,
    (o.net_revenue - o.cogs - o.shipping_cost - o.payment_fees) AS contribution_margin
  FROM `raw_erp.orders` o
)

SELECT
  pe.landing_page,
  COUNT(DISTINCT om.order_id) AS orders,
  SUM(om.net_revenue) AS net_revenue,
  SUM(om.contribution_margin) AS contribution_margin,
  SAFE_DIVIDE(SUM(om.contribution_margin), SUM(om.net_revenue)) AS contribution_margin_rate
FROM purchase_enriched pe
JOIN orders_margin om
  ON om.order_id = pe.order_id
WHERE
  -- Filtra SEO (ajusta a tu convención)
  (LOWER(pe.user_medium) = 'organic' OR LOWER(pe.user_source) = 'google')
GROUP BY 1
ORDER BY contribution_margin DESC;

Para categoría y producto, normalmente lo más fiable es el ERP (porque GA4 items no siempre cuadran con devoluciones/cambios). Si tienes order_items con product_id y category:

SELECT
  oi.category,
  oi.product_id,
  COUNT(DISTINCT oi.order_id) AS orders,
  SUM(oi.net_item_revenue) AS net_item_revenue,
  SUM(oi.item_cogs) AS item_cogs,
  SUM(oi.net_item_revenue - oi.item_cogs) AS item_gross_margin
FROM `raw_erp.order_items` oi
GROUP BY 1,2
ORDER BY item_gross_margin DESC;

Con esto puedes responder preguntas accionables: “¿Qué landings SEO generan más margen?” y “¿Qué categorías SEO generan margen pero también devoluciones?”. Si quieres que esta capa esté lista en semanas (no meses), en SEOAGIL lo empaquetamos como data mart + Looker para dirección.

6. SQL listo: LTV por cohortes (primer canal = SEO)

El LTV útil para SEO no es “ingresos acumulados” sin contexto, sino margen acumulado por cohortes de adquisición. La lógica: agrupa clientes por su primer canal (first touch) y mide su valor a 30/60/90/180 días desde su primera compra.

Necesitas dos piezas:

• Primera compra por cliente (desde ERP, fuente de verdad).
• Primer canal (desde GA4 o CRM si lo guardas al alta).

Ejemplo: obtener el primer canal GA4 asociado al primer pedido (aproximación pragmática cuando no tienes canal guardado en CRM):

WITH orders AS (
  SELECT
    order_id,
    customer_id,
    DATE(order_timestamp) AS order_date,
    contribution_margin
  FROM `raw_erp.orders`
),

first_order AS (
  SELECT
    customer_id,
    MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY 1
),

ga_purchase_channels AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS order_id,
    LOWER(traffic_source.source) AS source,
    LOWER(traffic_source.medium) AS medium
  FROM `raw_ga4.events_*`
  WHERE event_name = 'purchase'
),

first_order_channel AS (
  SELECT
    fo.customer_id,
    fo.first_order_date,
    ANY_VALUE(CASE
      WHEN g.medium = 'organic' THEN 'seo'
      WHEN g.source = 'google' AND g.medium = 'organic' THEN 'seo'
      ELSE 'other'
    END) AS first_channel
  FROM first_order fo
  JOIN orders o
    ON o.customer_id = fo.customer_id
   AND o.order_date = fo.first_order_date
  LEFT JOIN ga_purchase_channels g
    ON g.order_id = o.order_id
  GROUP BY 1,2
),

orders_with_age AS (
  SELECT
    o.customer_id,
    o.order_date,
    o.contribution_margin,
    f.first_order_date,
    DATE_DIFF(o.order_date, f.first_order_date, DAY) AS days_since_first
  FROM orders o
  JOIN first_order_channel f
    ON f.customer_id = o.customer_id
)

SELECT
  f.first_order_date AS cohort_date,
  f.first_channel,
  COUNT(DISTINCT f.customer_id) AS customers,
  SUM(CASE WHEN o.days_since_first <= 30 THEN o.contribution_margin ELSE 0 END) AS margin_30d,
  SUM(CASE WHEN o.days_since_first <= 90 THEN o.contribution_margin ELSE 0 END) AS margin_90d,
  SUM(CASE WHEN o.days_since_first <= 180 THEN o.contribution_margin ELSE 0 END) AS margin_180d,
  SAFE_DIVIDE(SUM(CASE WHEN o.days_since_first <= 180 THEN o.contribution_margin ELSE 0 END),
              COUNT(DISTINCT f.customer_id)) AS ltv_margin_180d
FROM first_order_channel f
JOIN orders_with_age o
  ON o.customer_id = f.customer_id
GROUP BY 1,2
ORDER BY cohort_date DESC;

Este enfoque te permite comparar cohortes SEO vs otros canales en margen, no solo en revenue. Si tu negocio tiene recompras largas (p. ej., 9–12 meses), amplía ventanas.

Nota: esto es “primer canal por primer pedido” y puede no reflejar el primer contacto real. La versión robusta es guardar el primer touch en CRM (ver sección 7).

7. Cómo atribuir “SEO” sin cookies (server-side + CRM)

En 2026, apoyarte solo en cookies del navegador para atribución es frágil. Lo que funciona de forma consistente es construir identidad y fuente en tus sistemas propios:

• Server-side tracking: envía eventos críticos (lead, add_to_cart, purchase) desde servidor, reduciendo pérdida por bloqueos y mejorando consistencia de IDs.
• Captura de UTM y referrer al alta: cuando el usuario crea cuenta, pide newsletter o inicia checkout, guarda en tu backend: first_source, first_medium, first_landing, first_referrer, timestamp.
• Customer ID estable: un customer_id que viva en CRM/ERP y se replique en analítica (GA4 user_id cuando sea posible).

Patrón recomendado:

1. En la primera visita, generas un visit_id (first-party) y guardas landing + referrer + gclid/gbraid si existen en tu backend (con consentimiento cuando aplique).
2. Cuando el usuario se identifica (email/login), haces el merge: asocias ese historial a customer_id.
3. Cuando hay compra, el pedido lleva: order_id, customer_id, y first_channel persistido (p. ej. “seo”).

Así, el LTV por SEO deja de depender de la exactitud de GA4 y pasa a depender de tu CRM/ERP. GA4 queda como capa de exploración y segmentación, pero el “dato final” vive en tu base.

Si quieres implementar este sistema (server-side + CRM + BigQuery) con automatización, pide ayuda desde el formulario de contacto. Es uno de los puntos donde más rápido se desbloquea ROI: menos pérdidas de atribución, más claridad de inversión.

8. Dashboard en Looker: KPIs accionables para eCommerce

Un dashboard útil no es el que enseña “sesiones orgánicas” en grande. Es el que responde: ¿qué escalo y qué recorto?. Recomendación de páginas y KPIs (Looker / Looker Studio conectado a BigQuery):

• Vista ejecutiva:
  • Margen contribución total (semana/mes)
  • Margen contribución por canal (SEO vs resto)
  • LTV margen 180d por primer canal
  • Ratio devolución y su impacto en margen
• SEO → Landings:
  • Top landings por margen (no por revenue)
  • Margen por sesión orgánica (proxy) y por pedido
  • Conversión orgánica vs margen rate
• SEO → Categorías/Productos:
  • Categorías con margen alto pero poca visibilidad (oportunidad de contenido/enlazado)
  • Productos con margen alto y demanda orgánica (prioridad SEO)
  • Productos con alto revenue y margen bajo (revisar pricing, bundles, CRO)
• Cohortes y recurrencia:
  • LTV por cohortes (mes de primera compra)
  • Tiempo a segunda compra por canal
  • Distribución de margen por cliente (whales vs long tail)

Buenas prácticas de implementación:

• Define el “SEO” con una regla única (agrupación de canal) y úsala en todo el modelo.
• Incluye controles de calidad: pedidos sin order_id en GA4, discrepancias revenue ERP vs GA4, etc.
• Documenta métricas y fórmulas en el propio dashboard.

Si ya tienes Looker pero no “cierra” con finanzas, el problema no es Looker: es el modelo. En SEOAGIL solemos empezar por el data mart y luego pintamos. Es el orden que evita rehacer.

9. Errores comunes y checklist de validación

Cuando cruzas GA4 + BigQuery + ERP, los fallos típicos son repetibles. Aquí van los más comunes y una checklist para validar rápido.

Errores comunes:

• Transaction_id inconsistente: GA4 guarda un ID distinto al del ERP (prefijos, ceros, formatos). Sin match, no hay margen por canal.
• Revenue distinto: GA4 usa revenue “de evento” (a veces antes de devoluciones), ERP usa neto real (descuentos, reembolsos). Tienes que decidir cuál es “oficial”.
• Devoluciones fuera de ventana: atribuyes margen en el día de compra pero la devolución llega 20 días después. Tu dashboard “celebra” antes de tiempo.
• Canal mal clasificado: “google / organic” ok, pero tráfico de apps, pagos, referral internos o pasarelas contaminan el canal si no filtras.
• Identidad fragmentada: mismo cliente con múltiples dispositivos, sin user_id ni CRM-first-touch guardado.

Checklist práctica de validación (rápida):

• Match rate: % de pedidos ERP que encuentran purchase en GA4 por order_id.
• Reconciliación: compara SUM(net_revenue) ERP vs GA4 revenue (mismo rango de fechas) y documenta diferencias esperadas.
• Duplicados: detecta compras duplicadas en GA4 (mismo order_id repetido) y deduplica.
• Devoluciones: valida que refund_amount aparece y se descuenta del margen según regla definida.
• Canales: revisa top 20 de source/medium y corrige “referral” internos (pasarelas, subdominios, etc.).
• Landing SEO: valida que la landing de sesión se captura (page_view inicial) y no una página posterior.
• Clientes: comprueba % de pedidos con customer_id (si hay guest checkout, define estrategia).

Si quieres, podemos convertir esta checklist en validaciones automáticas (alertas) dentro de tu pipeline, para que el modelo no se degrade con cambios de tracking. Es un caso típico de automatización con datos propio que encaja con el enfoque de implementación por método.

10. Conclusión: framework para decidir qué SEO escalar

Cuando mides “SEO” solo con GA4, sueles acabar en decisiones incompletas: priorizas páginas por tráfico, keywords por volumen o landings por revenue. En eCommerce competitivo (2026), eso es insuficiente. El framework recomendado es:

1. Unifica adquisición + economía: GA4 para canal y comportamiento, ERP/CRM para margen y cliente.
2. Define métricas decisionales: margen por landing/categoría/producto y LTV margen por primer canal (SEO).
3. Modela y automatiza: BigQuery como “capa de verdad analítica” y dashboard con KPIs accionables.
4. Optimiza SEO a beneficio: contenido, enlazado interno, arquitectura y CRO orientados a margen y recurrencia, no solo a visitas.

La gran ventaja es que el SEO deja de ser una disciplina “de tráfico” y pasa a ser un canal gestionado como finanzas: puedes decir con confianza qué clusters, categorías y landings merecen inversión, y cuáles generan volumen pero no beneficio.

Si tu eCommerce ya tiene GA4 pero no tiene margen y LTV por SEO en el mismo panel, el siguiente paso no es “otro informe”, sino el modelo y los IDs. Y eso se construye una vez y se reutiliza para todo el crecimiento.

Preguntas frecuentes

¿Puedo calcular margen por SEO si no tengo COGS por producto?

Sí, pero será una aproximación. Puedes empezar con COGS medio por categoría o por marca, y luego refinar. Lo importante es que el modelo soporte reemplazar estimaciones por datos reales cuando el ERP los exponga.

¿GA4 revenue y ERP revenue no coinciden: cuál uso?

Para decisiones de rentabilidad, usa ERP (o sistema financiero) como fuente de verdad. GA4 es útil para atribución y análisis de comportamiento, pero el revenue “oficial” debe ser contable/operativo.

¿Cómo defino “SEO” de forma consistente en BigQuery?

Define una regla única (por ejemplo: medium = organic y source en lista de buscadores) y aplícala en una vista dim_channel. Evita mezclar “default channel group” con reglas ad-hoc en cada consulta.

¿Qué ventana de LTV debo usar (30/90/180 días)?

Depende del ciclo de recompra. Como base, 90 y 180 días suelen capturar repetición sin esperar un año. Si vendes reposición frecuente, 30–60 días puede ser suficiente.

¿Esto funciona si gran parte del tráfico llega desde resultados con IA?

Funciona mejor cuando refuerzas identidad y primer touch en CRM/server-side. Si parte del tráfico llega con referrer limitado, el CRM-first-touch y el customer_id estable reducen la pérdida de atribución.