Proyecto: análisis de cohortes y retención de clientes

1-- Resultado final: Matriz de retencion
2-- cohorte    | mes_0 | mes_1 | mes_2 | mes_3 | mes_4 | mes_5
3-- 2023-01    | 100%  |  42%  |  31%  |  28%  |  25%  |  22%
4-- 2023-02    | 100%  |  45%  |  33%  |  29%  |  26%  |
5-- 2023-03    | 100%  |  38%  |  29%  |  25%  |       |
6-- 2023-04    | 100%  |  41%  |  30%  |       |       |
7-- ...
8
9-- Lectura: "De los clientes que compraron por primera vez en enero 2023,
10-- el 42% volvio a comprar en el mes 1, el 31% en el mes 2, etc."

La matriz de cohortes: cada fila es un grupo de clientes, cada columna un mes de vida.

1-- Paso 1: Fecha de primera compra de cada cliente (su cohorte)
2CREATE OR REPLACE TABLE cohortes_base AS
3SELECT
4    cliente_id,
5    DATE_TRUNC('month', MIN(fecha)) AS cohorte,
6    MIN(fecha) AS primera_compra
7FROM pedidos
8WHERE estado = 'completado'
9GROUP BY cliente_id;
10
11-- Verificar
12SELECT cohorte, COUNT(*) AS clientes
13FROM cohortes_base
14GROUP BY cohorte
15ORDER BY cohorte;

Cada cliente pertenece a una sola cohorte: el mes de su primera compra.

1-- Paso 2: Actividad mensual + distancia desde la cohorte
2CREATE OR REPLACE TABLE actividad_mensual AS
3SELECT DISTINCT
4    cb.cliente_id,
5    cb.cohorte,
6    DATE_TRUNC('month', p.fecha) AS mes_actividad,
7    DATEDIFF('month', cb.cohorte, DATE_TRUNC('month', p.fecha)) AS meses_desde_inicio
8FROM cohortes_base cb
9JOIN pedidos p ON p.cliente_id = cb.cliente_id
10WHERE p.estado = 'completado'
11  AND DATEDIFF('month', cb.cohorte, DATE_TRUNC('month', p.fecha)) >= 0;
12
13-- meses_desde_inicio = 0 significa "el mes de su primera compra"
14-- meses_desde_inicio = 1 significa "el mes siguiente", etc.

DATEDIFF calcula cuántos meses han pasado entre la cohorte y cada compra.

1-- Paso 3: Matriz de retencion completa
2WITH tamano_cohorte AS (
3    SELECT cohorte, COUNT(DISTINCT cliente_id) AS clientes_iniciales
4    FROM cohortes_base
5    GROUP BY cohorte
6),
7retencion AS (
8    SELECT
9        am.cohorte,
10        am.meses_desde_inicio,
11        COUNT(DISTINCT am.cliente_id) AS clientes_activos
12    FROM actividad_mensual am
13    GROUP BY am.cohorte, am.meses_desde_inicio
14)
15SELECT
16    r.cohorte,
17    tc.clientes_iniciales,
18    r.meses_desde_inicio,
19    r.clientes_activos,
20    ROUND(100.0 * r.clientes_activos / tc.clientes_iniciales, 1) AS pct_retencion
21FROM retencion r
22JOIN tamano_cohorte tc ON tc.cohorte = r.cohorte
23WHERE r.meses_desde_inicio <= 6  -- Primeros 6 meses
24ORDER BY r.cohorte, r.meses_desde_inicio;

La query final: cohorte — meses — porcentaje de retención.

1-- Enriquecer: tendencia de retencion con LAG
2WITH tamano_cohorte AS (
3    SELECT cohorte, COUNT(DISTINCT cliente_id) AS n
4    FROM cohortes_base GROUP BY cohorte
5),
6retencion AS (
7    SELECT
8        am.cohorte, am.meses_desde_inicio,
9        COUNT(DISTINCT am.cliente_id) AS activos,
10        ROUND(100.0 * COUNT(DISTINCT am.cliente_id) / MAX(tc.n), 1) AS pct
11    FROM actividad_mensual am
12    JOIN tamano_cohorte tc ON tc.cohorte = am.cohorte
13    GROUP BY am.cohorte, am.meses_desde_inicio
14)
15SELECT
16    cohorte,
17    meses_desde_inicio,
18    pct AS retencion_pct,
19    LAG(pct) OVER (PARTITION BY cohorte ORDER BY meses_desde_inicio) AS pct_mes_anterior,
20    pct - LAG(pct) OVER (PARTITION BY cohorte ORDER BY meses_desde_inicio) AS caida_vs_anterior,
21    -- Media de retencion de TODAS las cohortes en este mismo mes de vida
22    ROUND(AVG(pct) OVER (PARTITION BY meses_desde_inicio), 1) AS media_todas_cohortes
23FROM retencion
24WHERE meses_desde_inicio <= 6
25ORDER BY cohorte, meses_desde_inicio;

LAG para ver caída mes a mes. AVG OVER para comparar cada cohorte con la media histórica.

1-- Metrica 1: Retencion promedio al mes 1 (la mas critica)
2SELECT
3    ROUND(AVG(pct_mes_1), 1) AS retencion_promedio_mes_1
4FROM (
5    SELECT cohorte, 
6        ROUND(100.0 * COUNT(DISTINCT am.cliente_id) / MAX(tc.n), 1) AS pct_mes_1
7    FROM actividad_mensual am
8    JOIN (SELECT cohorte, COUNT(DISTINCT cliente_id) AS n FROM cohortes_base GROUP BY cohorte) tc
9        ON tc.cohorte = am.cohorte
10    WHERE am.meses_desde_inicio = 1
11    GROUP BY am.cohorte
12);
13
14-- Metrica 2: Mejor y peor cohorte
15WITH retention_m1 AS (
16    SELECT
17        am.cohorte,
18        ROUND(100.0 * COUNT(DISTINCT am.cliente_id) / MAX(tc.n), 1) AS pct
19    FROM actividad_mensual am
20    JOIN (SELECT cohorte, COUNT(DISTINCT cliente_id) AS n FROM cohortes_base GROUP BY cohorte) tc
21        ON tc.cohorte = am.cohorte
22    WHERE am.meses_desde_inicio = 1
23    GROUP BY am.cohorte
24)
25SELECT
26    cohorte, pct,
27    RANK() OVER (ORDER BY pct DESC) AS rank_mejor,
28    RANK() OVER (ORDER BY pct ASC) AS rank_peor
29FROM retention_m1
30ORDER BY pct DESC;

Las métricas ejecutivas que salen de la matriz: retención media, mejor cohorte, peor cohorte.