Cohort analysis ecommerce: cómo medir LTV real con BigQuery + dbt

Resumen

Cohort analysis es la única forma de medir LTV real en ecommerce DTC. Setup mínimo: Shopify orders → BigQuery (free tier hasta 10GB) → dbt para transformations → cohort tables por mes/canal/SKU. Permite responder: ¿qué canales captan high-LTV?, ¿qué cohort tiene mejor retention 12m?, ¿LTV proyectado a 24m vs CAC actual?, ¿qué SKU es ‘gateway product’ a repeat?. ROI: identificar cohort high-LTV mejora ROAS Meta +40% al excluir cold de bajo LTV.

---

Por qué los promedios mienten

Tu reporte muestra: “LTV medio: €180, CAC blended: €47, ratio 3.8×”. Suena bien. Pero la realidad:

• 60% de tus customers tienen LTV €40 (compraron 1 vez con descuento agresivo)
• 30% tienen LTV €180 (cohort medio)
• 10% tienen LTV €750 (high-LTV, repeat 5+ veces)

Si optimizas paid media basado en el promedio €180, escalas para captar más del 60% bajo. Si optimizas por excluir bajo y replicar high-LTV, mismo spend produce 40% más margen.

Esto requiere cohort analysis. Y cohort analysis requiere stack mínimo de data.

Stack técnico mínimo · €0/mes para <50K orders

[Shopify orders + customers]
       ↓ Webhooks daily
[Cloud Functions / serverless]
       ↓ ETL básico
[BigQuery]
       ├─ raw_orders
       ├─ raw_customers
       └─ raw_events
       ↓ dbt transformations
[Models]
       ├─ dim_customer (cohort attributes)
       ├─ fact_order
       ├─ cohort_retention_monthly
       └─ ltv_projected_24m
       ↓ Looker / Superset / Sellencia OS
[Dashboards interactivos]

Free tier:

• BigQuery: 1TB queries/mes + 10GB storage = €0
• Cloud Functions: 2M invocations/mes = €0
• dbt Cloud: 1 developer seat = €0
• Looker Studio: gratis, conectado a BigQuery

Total: €0/mes hasta 50K orders.

4 cohorts críticos que debes construir

Cohort 1 · Por canal de adquisición

Pregunta: ¿Meta Ads realmente capta high-LTV o solo bargain hunters?

Setup:

SELECT
  acquisition_channel,
  cohort_month,
  COUNT(DISTINCT customer_id) as customers,
  SUM(revenue) / COUNT(DISTINCT customer_id) as ltv_acumulado,
  AVG(orders_count) as repeat_rate
FROM dim_customer
GROUP BY 1, 2

Lo que descubres: Meta Ads reporta ROAS 4.5× con LTV avg €120. Pero Meta cohort tiene 35% one-time-buyers. Google Ads reporta ROAS 3.1× con LTV avg €240, repeat rate 2.4. Decisión: Google Ads es más rentable largo plazo aunque el ROAS reportado sea menor.

Cohort 2 · Por mes de adquisición

Pregunta: ¿se está degradando el LTV con el tiempo?

Lo que descubres: Cohort octubre 2024 retiene 34% a 12 meses. Cohort octubre 2025 retiene 22%. Caída de 12 puntos = saturación del producto, fatiga audience, o creative quemada. Acción: investigar diferencia y corregir.

Cohort 3 · Por geography

Pregunta: ¿qué países convierten en LTV vs solo orders impulsivas?

Lo que descubres: Italia tiene CAC €38 (bajo, parece bueno) pero LTV €52 (devoluciones 32%). Real LTV/CAC = 1.4×. Action: excluir Italia de cold targeting Meta + Google Ads o subir AOV mínimo en checkout.

Cohort 4 · Por primer SKU comprado

Pregunta: ¿qué productos son “gateway” a repeat customer?

Lo que descubres: Customers que primero compran SKU “Bestseller-XYZ” tienen repeat rate 48% vs 18% promedio. Es el “gateway product”. Action: optimizar paid ads para empujar este SKU específico.

dbt models recomendados

Estructura de modelos dbt mínima para cohort analysis:

models/
├── staging/
│   ├── stg_orders.sql
│   ├── stg_customers.sql
│   └── stg_products.sql
├── intermediate/
│   ├── int_first_order_per_customer.sql
│   ├── int_orders_with_cohort.sql
│   └── int_customer_attributes.sql
└── marts/
    ├── dim_customer.sql        ← attributes + cohort flags
    ├── fact_order.sql           ← orders con dimensiones
    ├── cohort_retention.sql     ← retention curve por cohort
    ├── ltv_projection.sql       ← LTV 12m, 24m proyectado
    └── high_ltv_predictors.sql  ← señales early warning

Templates dbt Sellencia OS™ — vienen pre-construidos para clients Growth Partner.

LTV projection · cómo calcular 24 meses futuros

Modelo simple con BigQuery:

WITH retention_by_month AS (
  SELECT
    cohort_month,
    months_since_first_order,
    COUNT(DISTINCT customer_id) / FIRST_VALUE(COUNT(DISTINCT customer_id)) OVER (PARTITION BY cohort_month ORDER BY months_since_first_order) as retention_rate,
    AVG(monthly_revenue) as avg_revenue_per_active
  FROM customer_monthly_activity
  GROUP BY 1, 2
)
SELECT
  cohort_month,
  SUM(retention_rate * avg_revenue_per_active) as ltv_projected_24m
FROM retention_by_month
WHERE months_since_first_order <= 24
GROUP BY 1

Permite tomar decisiones: “Si LTV proyectado 24m de cohort enero 2026 es €280 y CAC actual es €47, puedo escalar +50% spend porque LTV/CAC = 6×”.

Cómo conectar cohort insights a paid media

Una vez tienes los cohorts, los datos vuelven a Meta + Google Ads como audiences:

Customer List Lookalike Meta:

• Subir CSV con high-LTV customers (top 20% LTV)
• Crear LAL 1%, 2%, 3%
• Resultado: ROAS 2-3× mejor que LAL genérica

Google Customer Match:

• Subir lista de high-LTV
• Crear similar audiences
• Boost Smart Bidding signal

Excluir bajo LTV:

• Subir lista one-time-buyers cohort
• Excluir de cold targeting
• Mismo spend, mejor margen

Errores típicos en cohort analysis

1. Promedios sin segmentar — esconden la verdad
2. Cohorts demasiado grandes — necesitas <12 meses para detectar degradación
3. No incluir devoluciones — LTV bruto vs neto puede diferir 30%
4. Olvidar fees pasarela — €100 revenue ≠ €100 margen
5. Sin data warehouse — Excel imposible con >5K customers
6. No conectar insights de vuelta a paid — saber sin actuar = inútil

Roadmap implementación 14 días

Días 1-3: Setup BigQuery + Cloud Functions + webhooks Shopify

Días 4-7: dbt staging + intermediate + dim/fact models básicos

Días 8-11: Cohort retention model + LTV projection

Días 12-14: Dashboard Looker Studio + connection a Meta CAPI + Google Customer Match

A los 14 días: tienes cohort analysis funcional. A los 30 días: paid media optimizado con audiences cohort-driven.

Conclusión: cohort analysis es la diferencia entre ecommerce que escala y el que se estanca

Sin cohorts, optimizas con ruido. Con cohorts, identificas señal y escalas margen.

Tu próximo paso:

1. Audita si tu data está en un warehouse o solo en Shopify admin
2. Si en Shopify: setup BigQuery + dbt es la primera prioridad data
3. Si quieres setup llave en mano: Sellencia OS™ viene con cohort templates pre-construidos
4. Si dudas si hay margen para optimizar: auditoría 7 días

---

Posts relacionados

• Sellencia OS arquitectura técnica
• Contribution Margin Bidding
• IA para startups ecommerce

Preguntas frecuentes

¿Qué es cohort analysis y por qué importa en ecommerce?

Cohort analysis agrupa customers por características comunes (mes de adquisición, canal de origen, primer SKU comprado, geography) y mide su comportamiento a lo largo del tiempo. Permite distinguir entre 'tenemos buena retention general' y 'el cohort de Meta Ads enero 2026 retiene 35% mejor que el de Google Ads enero 2026'. Sin cohorts, optimizas con promedios que esconden la verdad.

¿Cuánto cuesta el stack BigQuery + dbt para ecommerce?

Free tier BigQuery: 1TB queries/mes + 10GB storage. Para ecommerce con &lt;50K orders/mes está dentro del free tier. dbt Cloud free tier: 1 developer seat. Total: 0€/mes para ecommerce típico. Con >100K orders/mes: 30-100€/mes BigQuery + 100€/mes dbt. Comparado con Triple Whale ($300-1500/mes) o Polar Analytics ($200-800/mes), BigQuery+dbt self-host es 5-30× más barato.

¿Necesito un data analyst para hacer cohort analysis?

Setup inicial: 1-2 días con alguien que sepa SQL y dbt básico. Mantenimiento: 2-4h/semana para iterar models. Si no tienes data analyst, opciones: (1) freelance €40-60/h una vez por mes, (2) Sellencia OS™ que ya viene con cohort templates pre-construidos, (3) Triple Whale plug-and-play (caro pero zero-config).

¿Cómo identificar high-LTV customers temprano (semana 1-2)?

Indicadores predictivos: (1) primer SKU comprado >€60 (proxy de intent serio), (2) email engagement en welcome series (open >50%), (3) entrega correcta sin issue (proxy CX), (4) follow social/Instagram tras compra. Combinarlos en score predictivo en BigQuery con regresión logística. Score top 20% tienen LTV 3-5× promedio. Excluirlos de cold paid + activarlos VIP flow.

¿Qué cohorts son más útiles para optimizar paid media?

Top 4 cohorts a construir en BigQuery: (1) por canal de adquisición (Meta vs Google vs orgánico) — informa si Meta ROAS reportado es real; (2) por mes de cohort — detecta degradación creative o saturación audience; (3) por geography — Italia/Francia suelen tener LTV bajo en moda por devoluciones; (4) por primer SKU — gateway products que llevan a repeat. Cada cohort permite excluir/incluir audiences en Meta CAPI + Google Customer Match.