Día 3 -- Métricas de producto: DAU, retention, engagement

1-- Cálculo de DAU, WAU, MAU con tendencia
2-- Ejecutar en DuckDB contra las vistas del lake
3
4-- DAU: usuarios únicos que reprodujeron algo cada día
5CREATE OR REPLACE VIEW metric_dau AS
6SELECT
7    event_date,
8    COUNT(DISTINCT user_id) AS dau,
9    -- Media móvil 7 días para suavizar variación diaria
10    AVG(COUNT(DISTINCT user_id)) OVER (
11        ORDER BY event_date
12        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
13    ) AS dau_7d_avg
14FROM v_playbacks
15WHERE watch_seconds > 30  -- Solo sesiones significativas (> 30s)
16GROUP BY event_date
17ORDER BY event_date;
18
19-- MAU: usuarios únicos en ventana de 30 días rolling
20CREATE OR REPLACE VIEW metric_mau AS
21SELECT
22    event_date,
23    COUNT(DISTINCT user_id) OVER (
24        ORDER BY event_date
25        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
26    ) AS mau_rolling
27FROM (
28    SELECT DISTINCT event_date, user_id
29    FROM v_playbacks
30    WHERE watch_seconds > 30
31)
32GROUP BY event_date
33ORDER BY event_date;
34
35-- Stickiness: DAU / MAU
36SELECT
37    d.event_date,
38    d.dau,
39    m.mau_rolling AS mau,
40    ROUND(d.dau * 100.0 / NULLIF(m.mau_rolling, 0), 1) AS stickiness_pct
41FROM metric_dau d
42JOIN metric_mau m ON d.event_date = m.event_date
43WHERE d.event_date >= '2024-01-01'
44ORDER BY d.event_date;

DAU con media móvil 7 días + stickiness. Solo sesiones > 30s para no inflar con bounces.

1-- Retención por cohorte semanal: D1, D7, D14, D30
2-- Cohorte = semana de primer playback del usuario
3
4WITH user_first_play AS (
5    -- Fecha del primer playback significativo de cada usuario
6    SELECT
7        user_id,
8        MIN(event_date) AS cohort_date,
9        DATE_TRUNC('week', MIN(event_date))::DATE AS cohort_week
10    FROM v_playbacks
11    WHERE watch_seconds > 30
12    GROUP BY user_id
13),
14user_activity AS (
15    -- Todas las fechas en que cada usuario fue activo
16    SELECT DISTINCT user_id, event_date
17    FROM v_playbacks
18    WHERE watch_seconds > 30
19),
20retention_calc AS (
21    SELECT
22        ufp.cohort_week,
23        COUNT(DISTINCT ufp.user_id) AS cohort_size,
24        COUNT(DISTINCT CASE
25            WHEN ua.event_date = ufp.cohort_date + INTERVAL '1 day'
26            THEN ufp.user_id END) AS retained_d1,
27        COUNT(DISTINCT CASE
28            WHEN ua.event_date BETWEEN ufp.cohort_date + INTERVAL '6 days'
29                                    AND ufp.cohort_date + INTERVAL '8 days'
30            THEN ufp.user_id END) AS retained_d7,
31        COUNT(DISTINCT CASE
32            WHEN ua.event_date BETWEEN ufp.cohort_date + INTERVAL '13 days'
33                                    AND ufp.cohort_date + INTERVAL '15 days'
34            THEN ufp.user_id END) AS retained_d14,
35        COUNT(DISTINCT CASE
36            WHEN ua.event_date BETWEEN ufp.cohort_date + INTERVAL '28 days'
37                                    AND ufp.cohort_date + INTERVAL '32 days'
38            THEN ufp.user_id END) AS retained_d30
39    FROM user_first_play ufp
40    LEFT JOIN user_activity ua ON ufp.user_id = ua.user_id
41    GROUP BY ufp.cohort_week
42)
43SELECT
44    cohort_week,
45    cohort_size,
46    ROUND(retained_d1 * 100.0 / cohort_size, 1) AS retention_d1_pct,
47    ROUND(retained_d7 * 100.0 / cohort_size, 1) AS retention_d7_pct,
48    ROUND(retained_d14 * 100.0 / cohort_size, 1) AS retention_d14_pct,
49    ROUND(retained_d30 * 100.0 / cohort_size, 1) AS retention_d30_pct
50FROM retention_calc
51WHERE cohort_week >= '2023-10-01'  -- Últimos 5 meses (cohortes con D30 completo)
52ORDER BY cohort_week;

Retención por cohorte semanal con ventanas flexibles (+-1 día). El dato que cierra rondas.

1-- Engagement Score por usuario (últimos 30 días)
2-- Composición: frecuencia (40%) + profundidad (30%) + intensidad (30%)
3
4WITH user_metrics_30d AS (
5    SELECT
6        user_id,
7        COUNT(DISTINCT event_date) AS active_days,
8        COUNT(*) AS total_plays,
9        SUM(watch_seconds) / 3600.0 AS total_hours,
10        AVG(watch_seconds) / 60.0 AS avg_session_minutes,
11        AVG(CASE WHEN completion_ratio > 0 THEN completion_ratio END) AS avg_completion,
12        COUNT(CASE WHEN completed THEN 1 END) AS titles_completed
13    FROM v_playbacks
14    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
15      AND watch_seconds > 30
16    GROUP BY user_id
17),
18scored AS (
19    SELECT
20        user_id,
21        active_days,
22        total_plays,
23        total_hours,
24        avg_session_minutes,
25        avg_completion,
26        titles_completed,
27        -- Frecuencia: días activos / 30 (max 1.0)
28        LEAST(active_days / 30.0, 1.0) AS freq_score,
29        -- Profundidad: completion rate promedio (ya está entre 0 y 1)
30        COALESCE(avg_completion, 0) AS depth_score,
31        -- Intensidad: minutos por sesión normalizado (30 min = 1.0)
32        LEAST(avg_session_minutes / 30.0, 1.0) AS intensity_score
33    FROM user_metrics_30d
34)
35SELECT
36    user_id,
37    active_days,
38    total_hours,
39    avg_completion,
40    -- Engagement Score: ponderado
41    ROUND(
42        (freq_score * 0.4 + depth_score * 0.3 + intensity_score * 0.3) * 100,
43        1
44    ) AS engagement_score,
45    -- Segmento basado en score
46    CASE
47        WHEN (freq_score * 0.4 + depth_score * 0.3 + intensity_score * 0.3) >= 0.7
48            THEN 'power_user'
49        WHEN (freq_score * 0.4 + depth_score * 0.3 + intensity_score * 0.3) >= 0.4
50            THEN 'regular'
51        WHEN (freq_score * 0.4 + depth_score * 0.3 + intensity_score * 0.3) >= 0.15
52            THEN 'casual'
53        ELSE 'at_risk'
54    END AS user_segment
55FROM scored
56ORDER BY engagement_score DESC;

Engagement score compuesto: frecuencia (40%) + profundidad (30%) + intensidad (30%).

1-- Resumen ejecutivo de segmentos para el dashboard
2SELECT
3    segment,
4    COUNT(*) AS users,
5    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct,
6    ROUND(AVG(engagement_score), 1) AS avg_score,
7    ROUND(AVG(total_hours), 1) AS avg_hours_monthly,
8    ROUND(AVG(active_days), 1) AS avg_days_active
9FROM (
10    -- (subquery del engagement score anterior)
11    SELECT user_id, engagement_score, total_hours, active_days,
12           CASE
13               WHEN engagement_score >= 70 THEN 'Power User'
14               WHEN engagement_score >= 40 THEN 'Regular'
15               WHEN engagement_score >= 15 THEN 'Casual'
16               ELSE 'At Risk'
17           END AS segment
18    FROM user_engagement_scores
19)
20GROUP BY segment
21ORDER BY avg_score DESC;
22
23-- Resultado esperado:
24-- Power User  | 2.940  | 19.6% | 81.2 | 12.4h | 21.3 días
25-- Regular     | 5.250  | 35.0% | 53.7 |  5.8h | 10.1 días
26-- Casual      | 4.560  | 30.4% | 27.3 |  2.1h |  3.8 días
27-- At Risk     | 2.250  | 15.0% |  8.4 |  0.4h |  1.2 días

Distribución de segmentos: 55% engaged (power+regular) es una señal fuerte para inversores.

1-- Crecimiento Month-over-Month de MAU
2WITH monthly_mau AS (
3    SELECT
4        DATE_TRUNC('month', event_date)::DATE AS month,
5        COUNT(DISTINCT user_id) AS mau
6    FROM v_playbacks
7    WHERE watch_seconds > 30
8    GROUP BY DATE_TRUNC('month', event_date)
9)
10SELECT
11    month,
12    mau,
13    LAG(mau) OVER (ORDER BY month) AS prev_month_mau,
14    ROUND(
15        (mau - LAG(mau) OVER (ORDER BY month)) * 100.0
16        / NULLIF(LAG(mau) OVER (ORDER BY month), 0),
17        1
18    ) AS growth_mom_pct
19FROM monthly_mau
20WHERE month >= '2023-06-01'
21ORDER BY month;
22
23-- Resultado esperado (últimos meses):
24-- 2024-01: 12.400 MAU (+6.8% MoM)
25-- 2024-02: 13.600 MAU (+9.7% MoM)
26-- 2024-03: 15.100 MAU (+11.0% MoM) <- tendencia acelerando

Crecimiento MoM acelerando: la señal que todo VC quiere ver.