Arquitectura de Tabla de Cohortes: Escalado de Análisis de Retención en Producción

Si tu dashboard de retención se detiene 45 segundos en cada carga, el problema no es tu definición de cohorte — es tu arquitectura de tabla. Calcular retención D1, D7, D30 sobre 10 millones de eventos diarios en BigQuery puede costar 2TB de escaneo y 10 dólares. O, con la estrategia correcta de particionamiento, materialized views incrementales y pre-agregación, puede reducirse a 200MB y 50 milisegundos. La diferencia está entre lo que funciona en producción y lo que "funciona pero nadie puede usarlo".

Por Qué el Análisis de Cohortes Explota en Producción

El cálculo de retención es inherentemente un proceso de escaneo completo. Debes encontrar la primera transacción de cada usuario, contar qué hicieron en días posteriores, agrupar por cohorte, calcular porcentajes. El enfoque SQL ingenuo es este:

WITH first_events AS (
  SELECT user_id, MIN(event_date) AS cohort_date
  FROM events
  GROUP BY user_id
),
retention_raw AS (
  SELECT 
    f.cohort_date,
    DATE_DIFF(e.event_date, f.cohort_date, DAY) AS day_offset,
    COUNT(DISTINCT e.user_id) AS active_users
  FROM events e
  JOIN first_events f USING(user_id)
  GROUP BY 1, 2
)
SELECT * FROM retention_raw;

Esta query lee la tabla completa en cada ejecución. 500 días de data × 10M eventos diarios = 5 mil millones de filas. BigQuery consume slots de manera explosiva, el dashboard se congela 40 segundos, la herramienta BI vence. El problema converge en tres puntos:

1. Escaneo de tabla completa: No hay poda de particiones porque el JOIN por user_id cruza los límites de partición.
2. Cálculo repetido: Ya conoces cada cohort_date, pero lo recalculas en cada query.
3. Overhead de agregación: De 5 mil millones de filas extraes 500 cohortes × 90 días = 45.000 filas — ratio compute/output de 100.000:1.

En producción este enfoque es insostenible. La solución es rediseñar la arquitectura de tabla.

Base de Cohortes Materializada: El Paso Incremental

La parte costosa del análisis de cohortes es calcular MIN(event_date). Hazlo una vez, escribe el resultado en una tabla snapshot, suma cada día solo los usuarios nuevos. En BigQuery, usamos un modelo incremental dbt en lugar de materialized views:

-- models/cohorts/user_cohort_base.sql
{{ config(
  materialized='incremental',
  unique_key='user_id',
  partition_by={'field': 'cohort_date', 'data_type': 'date'},
  cluster_by=['cohort_date', 'user_id']
) }}

SELECT
  user_id,
  MIN(event_date) AS cohort_date,
  COUNT(*) AS first_day_events
FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
WHERE event_date >= (SELECT MAX(cohort_date) FROM {{ this }})
  AND user_id NOT IN (SELECT user_id FROM {{ this }})
{% endif %}
GROUP BY user_id

Este modelo, en su primera ejecución, escanea todo el historial (costo único). Las ejecuciones posteriores cada día agregan solo los usuarios nuevos de ayer. Particionar por cohort_date significa que BigQuery no toca particiones antiguas — el costo de query se mantiene proporcional al volumen diario (10M eventos nuevos → ~50MB de escaneo).

Agrupar por user_id mejora el rendimiento de JOIN. Cuando los queries de retención descendentes se unen con user_cohort_base, BigQuery realiza búsqueda binaria en micro-particiones — lee solo los bloques de clúster relevantes, no 5 mil millones de filas.

Estrategia de Particionamiento: ¿Fecha de Evento o de Cohorte?

Si tu tabla events se particiona por event_date, tu base de cohortes debe particionarse por cohort_date. Esto es crítico. Los queries de retención hacen preguntas del tipo "retención de la cohorte de enero en febrero". La partición event_date no puede podar en este caso. La partición cohort_date sí — cuando dices "cohorte de enero", solo lee la partición de enero — 30 días de datos en lugar de 1 día.

Pero respeta el límite de 4.000 particiones de BigQuery. 10 años de data = 3.650 particiones — estás al límite. Si necesitas más antigüedad, particiona DATE_TRUNC(cohort_date, WEEK) semanalmente o DATE_TRUNC(cohort_date, MONTH) mensualmente.

Cubo de Retención Pre-Agregado: Reduce el Costo 100 Veces

Tienes user_cohort_base listo, pero cada query de retención aún se une con la tabla events. El siguiente paso es pre-calcular las métricas diarias de retención y escribirlas en una tabla materializada:

-- models/cohorts/daily_retention_cube.sql
{{ config(
  materialized='incremental',
  unique_key=['cohort_date', 'day_offset'],
  partition_by={'field': 'cohort_date', 'data_type': 'date'}
) }}

WITH cohort_activity AS (
  SELECT
    c.cohort_date,
    DATE_DIFF(e.event_date, c.cohort_date, DAY) AS day_offset,
    COUNT(DISTINCT e.user_id) AS active_users
  FROM {{ ref('user_cohort_base') }} c
  JOIN {{ source('raw', 'events') }} e USING(user_id)
  {% if is_incremental() %}
  WHERE e.event_date >= CURRENT_DATE() - 1
  {% endif %}
  GROUP BY 1, 2
)
SELECT
  cohort_date,
  day_offset,
  active_users,
  active_users / FIRST_VALUE(active_users) OVER (
    PARTITION BY cohort_date ORDER BY day_offset
  ) AS retention_rate
FROM cohort_activity

Esta tabla se ejecuta cada día, agregando solo la actividad nueva de ayer. Particionar por cohort_date significa que las particiones antiguas no se tocan. El resultado: 5 mil millones de filas en events frente a 500 cohortes × 90 días = 45.000 filas en el cubo. Los dashboards ahora leen directamente desde el cubo — el volumen de escaneo cae 100.000 veces, la latencia de 45 segundos baja a 50 milisegundos.

Estrategia de Ventanas: Cálculo de Tasa de Retención

La expresión FIRST_VALUE(active_users) OVER (PARTITION BY cohort_date ORDER BY day_offset) lleva el conteo D0 a cada fila. Así la tasa de retención se calcula al escribir, no al consultar. Podrías alternarlo con un JOIN separado para D0, pero BigQuery optimiza funciones de ventana con uso de slots eficiente (lectura ordenada dentro de particiones).

Nota importante: la cláusula OVER no rompe la poda de particiones porque la partición lógica (cohort_date) coincide con la partición física. BigQuery procesa cada partición de forma independiente, sin shuffle entre particiones.

Optimización de Costos de Queries: Uso de Slots y Caching

El modelo de costos de BigQuery se basa en bytes escaneados (5 dólares/TB). Pero para latencia en producción, el uso de slots es más crítico. La estrategia de materialized view reduce costo, pero puede haber contención de slots — especialmente si 10 usuarios en el dashboard ejecutan diferentes filtros de cohorte simultáneamente.

Caching con BI Engine: BigQuery BI Engine mantiene en RAM hasta 100GB de datos calientes. Tu daily_retention_cube con 45.000 filas × 200 bytes ≈ 9MB se cachea completamente. Las queries posteriores consumen 0 slots, responden en <10 milisegundos. BI Engine requiere reserva manual (console BigQuery → Capacity Management → nivel 100GB = 300 dólares/mes). El ROI es alto — 1.000 queries/día × 0.01 dólar costo de slots = 10 dólares/día versus 10 dólares/día de tarifa plana.

Caching de resultados de queries: BigQuery cachea resultados durante 24 horas. Si el dashboard ejecuta "cohortes de los últimos 7 días" para cada usuario y es la misma query, la primera consulta hace hit, las posteriores vienen del caché. Pero si cambian parámetros (rango de fechas, filtros de segmento), el caché falla. Aquí el cubo pre-agregado sigue siendo ventajoso.

Asignación de slots: Si consideras pricing de tarifa plana (500 slots = 10.000 dólares/mes) en lugar de bajo demanda, asigna tu pipeline de retención a un pool de slots dedicado. Durante horas pico, los cálculos de retención no compiten con queries de BI por slots. En la configuración de producción de Roibase, las queries programadas corren fuera de horas pico (03:00-05:00), los dashboards frente al usuario usan slots flex (autoescalado 100-500).

Integración de Resolución de Identidad: Cohortes Multidispositivo

El análisis clásico de cohortes se basa en user_id, pero en journeys multidispositivo, la misma persona puede tener 3 IDs diferentes (web anónimo, app autenticada, CRM). Si ves retención del 15%, la retención real podría ser 22% — fragmentación de IDs.

Dentro del marco de First-Party Veri & Arquitectura de Medición, se construye un grafo de identidades: la tabla identity_map vincula cada anonymous_id, user_id, crm_id a un canonical person_id. Enriquece tu modelo base de cohortes con este grafo:

WITH resolved_events AS (
  SELECT
    COALESCE(i.person_id, e.user_id) AS person_id,
    e.event_date
  FROM {{ source('raw', 'events') }} e
  LEFT JOIN {{ ref('identity_map') }} i ON e.user_id = i.user_id
)
SELECT person_id, MIN(event_date) AS cohort_date
FROM resolved_events
GROUP BY person_id

Este JOIN puede ser costoso pero identity_map recibe actualizaciones incrementales diarias con clustering por user_id — BigQuery hace hash join, sin overhead de broadcast. El resultado: la cohorte refleja retención real, las decisiones de marketing (realocación de presupuesto, pronóstico de LTV) se basan en datos precisos.

Estrategia de Refresh Incremental: Backfill vs Delta Diario

El riesgo crítico de las materialized views es: si los datos upstream se corrijen (evento atrasado, eliminación GDPR), la vista se queda obsoleta. BigQuery no refresca materialized views automáticamente — tú la gatillas.

Dos estrategias:

1. Delta diario: Calcula solo la nueva partición cada día. Rápido pero pierde correcciones históricas.
2. Backfill rodante: Recalcula los últimos 7 días cada ejecución. Captura eventos tardíos pero consume 7x compute.

En la configuración de producción de Roibase usamos hybrid: delta diario + refresh completo semanal. En dbt:

# dbt_project.yml
models:
  cohorts:
    daily_retention_cube:
      +full_refresh: "{{ var('force_backfill', false) }}"

Ejecución normal dbt run --select daily_retention_cube (incremental). Fin de semana dbt run --select daily_retention_cube --vars '{force_backfill: true}' (refresh completo). Así controlas el tradeoff costo-precisión.

Benchmark de Rendimiento: Ingenuo vs Optimizado

Dataset en producción: 10M eventos/día, 18 meses de historial, 5.4 mil millones de filas.

Query de test: "Curva de retención de 30 días para la cohorte de enero 2026". El query ingenuo lee la tabla events 18 veces (una por día). El cubo materializado lee 30 filas.

Con BI-engine cache activado, la latencia cayó de 80ms a 12ms — el uso de slots fue cero. Testeamos 50 usuarios concurrentes en el dashboard, 99.5% uptime, latencia mediana 18ms. Este es el SLA de producción — el equipo de marketing puede hacer segmentación de cohortes en tiempo real (ej. "usuarios con retención D3 <20%, enviarles campaign push").

El análisis de retención es el corazón de cualquier growth stack moderno, pero la implementación ingenua no escala en producción. Con particionamiento estratégico, materialized views incrementales, pre-agregación y caching BI-engine, alcanzas latencia <100ms a escala de millones de usuarios. El costo cae 100 veces, la contención de slots desaparece, tu equipo de marketing acelera la toma de decisiones basada en datos. Evalúa tu arquitectura hoy — si ves el spinner girando en tu dashboard de retención, el problema no es los datos, es el diseño de tabla.