lección 4

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

Calculas las métricas que Marta necesita para inversores: DAU/MAU con tendencia, retención por cohorte D1/D7/D30 y engagement score por usuario.

⏱ 55 min

## Miércoles, 8:00 AM -- Los datos están listos

Hoy se consulta, no se carga. Las consultas de esta lección leen las vistas que dejó montadas el pipeline de ayer: `silver_playbacks` (una fila por reproducción, con `event_date`, `watch_seconds`, `is_meaningful`, `completion_ratio` y `session_category`), `dim_users`, `dim_content`, y las tres que se añadieron al final del montaje: `suscripciones`, `pagos` y `marketing`. Si lo reproduces en tu máquina y no tienes S3, el atajo es abrir DuckDB en local y crear las mismas vistas sobre los ficheros descargados: `reproducciones_eventos.parquet`, `usuarios.csv`, `catalogo.csv`, `suscripciones.csv`, `pagos.csv` y `gasto_marketing_semanal.csv`. Dos avisos antes de empezar. El extracto de reproducciones cubre 21 días (del 23 de septiembre al 14 de octubre de 2024) y eso va a condicionar la mitad de lo que se puede calcular hoy. Y la duración del catálogo está en `duration_minutes`, en MINUTOS, mientras el tiempo visto está en segundos: cualquier cociente entre los dos necesita un por 60 antes, o sale 60 veces mal.

El pipeline corrió anoche a las 03:00. Los 3 millones de reproducciones históricas están en S3 como Parquet particionado por fecha. DuckDB tiene vistas configuradas. Es hora de transformar bytes en narrativa. Porque eso es lo que Marta necesita para el viernes: no un dashboard con números, sino una HISTORIA que un inversor pueda seguir y que le haga sacar la chequera.

La historia que los inversores de streaming quieren escuchar tiene tres actos: "La gente viene" (crecimiento DAU/MAU), "La gente se queda" (retención por cohorte) y "La gente se engancha" (engagement creciente). Si las tres curvas van hacía arriba, la conclusión es inevitable: este producto tiene product-market fit y merece inversión para escalar.

## Acto 1: "La gente viene" -- DAU, WAU, MAU

Las métricas de actividad son el primer indicador de salud. Un inversor mira DAU/MAU como un médico mira el pulso: si hay pulso constante y creciente, el paciente está vivo y mejorando. El ratio DAU/MAU (stickiness) indica si el producto es un hábito diario o una visita esporádica.

1-- DAU y MAU movil. Se ejecuta en DuckDB contra las vistas que dejo
2-- configurada la carga de ayer: silver_playbacks, dim_users, dim_content.
3
4-- DAU: usuarios distintos que vieron algo cada dia.
5CREATE OR REPLACE VIEW metric_dau AS
6SELECT
7    event_date,
8    COUNT(DISTINCT user_id) AS dau,
9    -- Media movil de 7 dias, para que el sabado y el martes no parezcan
10    -- tendencias distintas.
11    AVG(COUNT(DISTINCT user_id)) OVER (
12        ORDER BY event_date
13        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
14    ) AS dau_7d,
15    -- Las seis primeras filas son una media de MENOS de siete dias: la ventana
16    -- todavia no esta llena. Se marcan para poder dejarlas fuera del grafico.
17    ROW_NUMBER() OVER (ORDER BY event_date) >= 7 AS media_completa
18FROM silver_playbacks
19WHERE is_meaningful          -- el umbral vive en el dia 2, no aqui
20GROUP BY event_date
21ORDER BY event_date;
22
23-- MAU movil: usuarios distintos en los ultimos 30 dias, recalculado cada dia.
24--
25-- OJO CON EL GROUP BY. La version obvia -- meter la ventana y agrupar por fecha
26-- en la misma consulta -- no compila:
27--     Binder Error: column "user_id" must appear in the GROUP BY clause
28-- Despues de agrupar por fecha, user_id ya no existe como columna, asi que la
29-- ventana no lo puede contar. La ventana va DENTRO y el GROUP BY FUERA, sobre
30-- su resultado, para dejar una sola fila por fecha.
31CREATE OR REPLACE VIEW metric_mau AS
32SELECT
33    event_date,
34    MAX(mau_movil) AS mau_movil,
35    -- Los 29 primeros dias no tienen la ventana completa: su MAU sale bajo por
36    -- construccion, y como el stickiness es DAU/MAU, el stickiness de esos dias
37    -- sale INFLADO. Esta columna dice cuando se puede empezar a mirar.
38    event_date >= (SELECT MIN(event_date) + INTERVAL '29 days' FROM silver_playbacks)
39        AS ventana_completa
40FROM (
41    SELECT
42        event_date,
43        COUNT(DISTINCT user_id) OVER (
44            ORDER BY event_date
45            RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
46        ) AS mau_movil
47    FROM (
48        SELECT DISTINCT event_date, user_id
49        FROM silver_playbacks
50        WHERE is_meaningful
51    )
52)
53GROUP BY event_date
54ORDER BY event_date;
55
56-- Stickiness: que fraccion del mes aparece cada dia.
57SELECT
58    d.event_date,
59    d.dau,
60    ROUND(d.dau_7d, 1)  AS dau_7d,
61    m.mau_movil,
62    ROUND(d.dau * 100.0 / NULLIF(m.mau_movil, 0), 1) AS stickiness_pct,
63    d.media_completa,
64    m.ventana_completa
65FROM metric_dau d JOIN metric_mau m USING (event_date)
66ORDER BY d.event_date;

DAU con media móvil, MAU móvil y stickiness. Las dos columnas de "completa" son la parte que importa.

Ejecutas la consulta y salen 21 filas. El DAU sube limpiamente de 51 a 405. Pero mira la columna del stickiness: la primera fila dice 100%, la segunda 88,2%, la tercera 75,9%, y desde ahí baja sin parar hasta el 27,0%. No es que el producto se esté deteriorando: es que el primer día el MAU móvil vale exactamente lo mismo que el DAU, porque no hay nada antes. La columna `ventana_completa` te lo dice de una forma más incómoda: está en `false` en las veintiuna filas. Con veintiún días de datos, una ventana de treinta nunca llega a llenarse.

Así que aquí tomas la primera decisión desagradable de la semana: el stickiness no se puede presentar el viernes. No porque el número sea malo, sino porque no es el número -- es un artefacto de que el histórico son tres semanas. Y esto lo sabías desde el lunes; lo que pasa es que hasta que no lo calculas no ves cuánto duele. Lo que se hace con esto NO es enseñarlo con una nota al pie en letra pequeña: se saca de la presentación, se dice que se tendrá en cuatro semanas, y se presenta lo que sí se sostiene. Un inversor que descubre que una de tus cinco métricas era un artefacto deja de creerse las otras cuatro.

Sobre el umbral: la lección de ayer dejó `is_meaningful` en 120 segundos y estas consultas lo usan tal cual, en vez de repetir un número. Antes de dar ese umbral por bueno conviene mirar cuánta gente cae justo en la frontera, y es una comprobación de dos minutos: de las 17.423 sesiones del extracto, 901 duran menos de 30 segundos y 151 caen entre los 30 y los 120. O sea que la elección entre 30 y 120 mueve 151 sesiones, un 0,9%. Poco, pero no nada: son las que alguien vio entre medio minuto y dos minutos, y decidir si eso cuenta como ver algo es una decisión de producto, no de código. Aquí da igual cuál elijas, y saber que da igual vale más que elegir bien por suerte: el día que la diferencia sea del 15% querrás haber hecho esta comprobación antes y no después.

Lo que NO se puede apoyar en esa comprobación es la justificación. "Un usuario que abre un vídeo y lo cierra en cinco segundos no es activo" -- que es verdad y es la frase que Pablo, el product manager de TaskFlow, te dejó grabada -- vale igual para 30 que para 120 segundos: los dos lo excluyen. La frase explica por qué hace falta UN umbral, no cuál. Para elegir el número hay que mirar la distribución, que es lo que acabas de hacer.

## Acto 2: "La gente se queda" -- Retención por cohorte

La retención es LA métrica que separa startups con product-market fit de las que no lo tienen. Un inversor sofisticado como Meridian Capital sabe que el crecimiento de MAU puede ser artificial (publicidad pagada). Pero la retención no miente: si los usuarios vuelven después de 7 días sin que les pagues por hacerlo, el producto tiene algo que funciona.

1-- Retencion por cohorte: D1, D3, D7, D14.
2--
3-- La cohorte es el PRIMER DIA en que el usuario vio algo, no su fecha de alta.
4-- Con 21 dias de reproducciones, agrupar por semana deja tres cohortes y no se
5-- ve nada; por dia salen 21 y la curva se puede leer.
6--
7-- Y no hay D30. Para saber si alguien volvio el dia 30 hacen falta 30 dias de
8-- historico DESPUES de su primer dia, y aqui hay 21 en total. Una columna D30
9-- sobre estos datos saldria a cero y no significaria nada.
10
11WITH corte AS (
12    -- "Hoy" es el ultimo dia con datos, nunca CURRENT_DATE. Si se ancla al reloj
13    -- del ordenador, en cuanto el extracto envejezca un mes las consultas
14    -- devuelven cero y parece que las escribiste mal.
15    SELECT MAX(event_date) AS ultimo_dia FROM silver_playbacks
16),
17dia_cero AS (
18    SELECT user_id, MIN(event_date) AS cohorte
19    FROM silver_playbacks WHERE is_meaningful
20    GROUP BY user_id
21),
22actividad AS (
23    -- Un usuario por dia, con la distancia a su dia cero.
24    SELECT DISTINCT c.cohorte, s.user_id,
25           DATE_DIFF('day', c.cohorte, s.event_date) AS dn
26    FROM silver_playbacks s JOIN dia_cero c USING (user_id)
27    WHERE s.is_meaningful
28),
29r AS (
30    SELECT
31        cohorte,
32        COUNT(DISTINCT user_id) AS usuarios,
33        -- Las ventanas son de la MISMA anchura en las tres columnas que la
34        -- tienen: +-1 dia. Si el D7 contara 3 dias y el D14 contara 5, el D14
35        -- tendria mas oportunidades de contar a alguien y saldria alto por
36        -- construccion. La curva se aplanaria al final sin que nadie se fuera.
37        COUNT(DISTINCT CASE WHEN dn = 1               THEN user_id END) AS vuelven_d1,
38        COUNT(DISTINCT CASE WHEN dn BETWEEN 2  AND 4  THEN user_id END) AS vuelven_d3,
39        COUNT(DISTINCT CASE WHEN dn BETWEEN 6  AND 8  THEN user_id END) AS vuelven_d7,
40        COUNT(DISTINCT CASE WHEN dn BETWEEN 13 AND 15 THEN user_id END) AS vuelven_d14
41    FROM actividad
42    GROUP BY cohorte
43)
44SELECT
45    r.cohorte,
46    r.usuarios,
47    ROUND(r.vuelven_d1  * 100.0 / r.usuarios, 1) AS d1_pct,
48    ROUND(r.vuelven_d3  * 100.0 / r.usuarios, 1) AS d3_pct,
49    ROUND(r.vuelven_d7  * 100.0 / r.usuarios, 1) AS d7_pct,
50    ROUND(r.vuelven_d14 * 100.0 / r.usuarios, 1) AS d14_pct,
51    -- CADA COLUMNA TIENE SU PROPIA FECHA DE CORTE, y el plazo es el ANCHO DE LA
52    -- VENTANA, no el numero del nombre: si el D7 cuenta hasta el dia 8, el plazo
53    -- son 8 dias. Una cohorte que no ha cumplido el plazo sale a cero, y un cero
54    -- se lee igual que una fuga de usuarios.
55    r.cohorte <= (SELECT ultimo_dia FROM corte) - 1  AS d1_maduro,
56    r.cohorte <= (SELECT ultimo_dia FROM corte) - 4  AS d3_maduro,
57    r.cohorte <= (SELECT ultimo_dia FROM corte) - 8  AS d7_maduro,
58    r.cohorte <= (SELECT ultimo_dia FROM corte) - 15 AS d14_maduro
59FROM r
60ORDER BY r.cohorte;

Retención por cohorte diaria, con una marca de madurez por columna. Las marcas son la lección.

Ejecutas y salen 21 filas. Las primeras tienen las cuatro columnas con números; las últimas tienen ceros que van subiendo hacia la izquierda como una escalera. La cohorte del 11 de octubre tiene D1 88,6%, D3 71,4% y D7 a cero. La del 12 tiene D1 89,9% y el resto a cero. Y la del 13 de octubre, el último día con datos, tiene las cuatro columnas a cero: para saber si alguien volvió al día siguiente hace falta que exista el día siguiente. Sesenta y ocho usuarios estrenándose ese día, y la tabla los cuenta como si no hubieran vuelto nunca.

Si metes esas filas en el mismo gráfico que las maduras, la curva de retención se cae a plomo en las últimas semanas y parece exactamente una fuga de usuarios. Es el falso hallazgo más repetido del oficio y se ha presentado en muchas reuniones de inversores como una alarma que no existía. Filtra cada columna por su marca de madurez, y pon la fecha de corte en el pie del gráfico: "D7 sobre cohortes hasta el 5 de octubre; las posteriores no han cumplido su plazo" es una línea y evita que alguien saque la conclusión contraria a la verdad.

Con sólo las cohortes maduras de cada columna, esto es lo que hay: D1 (exacto) 73,0% sobre 20 cohortes, D3 (±1) 70,9% sobre 17, D7 (±1) 29,4% sobre 13 y D14 (±1) 5,9% sobre 6. Tres de cada cuatro vuelven al día siguiente y siete de cada diez siguen ahí a los tres días: eso es un arranque muy bueno. A la semana queda el 29%, que para contenido bajo demanda es defendible. El D14 hay que darlo con cuidado y diciéndolo: sale de seis cohortes de unos setenta usuarios cada una, así que un puñado de personas mueve el número varios puntos. Una cifra con esa base se presenta con la base al lado, o no se presenta.

Con los datos cortados el 13 de octubre: qué casillas de la tabla de retención se pueden leer y cuáles están vacías sólo porque el día todavía no ha llegado.

## Cuándo una cohorte está madura

Una cohorte es un grupo de usuarios que empezaron a la vez, y su curva de retención se va rellenando con el tiempo: el D1 se sabe al día siguiente, el D7 a la semana, el D30 al mes. Hasta que no pasa ese tiempo, la casilla no está vacía por falta de retención: está vacía porque todavía no ha ocurrido. Y la casilla no sabe distinguirlo. Devuelve un cero en los dos casos.

Así que cada columna tiene su propia fecha de corte, y el plazo es el ancho de la ventana, no el número del nombre: si tu D7 cuenta hasta el día 8, el plazo son ocho días, no siete. Y si la cohorte fuera semanal en vez de diaria, habría que sumarle los seis días que van del lunes al domingo, porque el último usuario del grupo se dio de alta seis días más tarde que el primero. Con cohortes diarias eso no hace falta, y es una razón más para usarlas.

Una cohorte entra en la columna D-n cuando han pasado n días desde que empezó, contando hasta el borde de la ventana. Ni uno menos.
La fecha de corte va escrita en el pie del gráfico, no en la cabeza de quien lo hizo.
Y el otro extremo también avisa: las cohortes muy antiguas tampoco son comparables sin más, porque el producto de hace un año no era éste. Si la serie cubre mucho tiempo, di qué cambió y cuándo.

## Acto 3: "La gente se engancha" -- Engagement Score

El engagement es más difícil de capturar en un solo número. No es solo "cuánto tiempo ven" -- es una combinación de frecuencia (cuántos días a la semana), profundidad (cuánto contenido completan) e intensidad (cuánto tiempo por sesión). Diseñas un engagement score compuesto que pondera estos tres factores:

1-- Puntuacion de engagement por usuario.
2-- Tres factores: frecuencia (40%), profundidad (30%), intensidad (30%).
3
4WITH ventana AS (
5    -- La ventana se ancla al ULTIMO DIA CON DATOS, no a CURRENT_DATE. Con el
6    -- reloj del ordenador, en cuanto el extracto envejezca un mes "los ultimos
7    -- 30 dias" no contienen nada y la tabla sale vacia. Y el numero de dias se
8    -- calcula, porque es el denominador de la frecuencia: si se pone 30 a mano y
9    -- los datos son 21 dias, nadie puede llegar al 1,0 y la frecuencia de todo
10    -- el mundo sale hundida.
11    SELECT MAX(event_date) AS ultimo_dia,
12           DATE_DIFF('day', MIN(event_date), MAX(event_date)) + 1 AS dias
13    FROM silver_playbacks
14),
15por_usuario AS (
16    SELECT
17        user_id,
18        COUNT(DISTINCT event_date)   AS dias_activos,
19        COUNT(*)                     AS reproducciones,
20        SUM(watch_seconds) / 3600.0  AS horas,
21        AVG(watch_seconds) / 60.0    AS min_por_sesion,
22        -- SIN la condicion "> 0". Con el filtro de sesion significativa ya
23        -- aplicado, un ratio pequeno es una vision corta de verdad y tiene que
24        -- contar. Lo unico que dejaria fuera ese "> 0" son los NULOS, y un nulo
25        -- aqui no es un abandono: es un titulo sin duracion en el catalogo, o
26        -- sea un cruce que falla. AVG ignora los nulos solo.
27        AVG(completion_ratio)        AS pct_visto,
28        -- Y el problema de datos se CUENTA, no se tapa.
29        COUNT(*) FILTER (WHERE completion_ratio IS NULL) AS sin_duracion
30    FROM silver_playbacks
31    WHERE is_meaningful
32      AND event_date >= (SELECT ultimo_dia FROM ventana) - INTERVAL '20 days'
33    GROUP BY user_id
34),
35puntuada AS (
36    SELECT
37        p.*,
38        LEAST(p.dias_activos * 1.0 / (SELECT dias FROM ventana), 1.0) AS f_frecuencia,
39        p.pct_visto                                                   AS f_profundidad,
40        LEAST(p.min_por_sesion / 30.0, 1.0)                            AS f_intensidad,
41        ROUND(100 * (
42              0.40 * LEAST(p.dias_activos * 1.0 / (SELECT dias FROM ventana), 1.0)
43            + 0.30 * p.pct_visto
44            + 0.30 * LEAST(p.min_por_sesion / 30.0, 1.0)
45        ), 1) AS puntuacion
46    FROM por_usuario p
47    -- Un usuario del que no sabemos la profundidad NO es un usuario con
48    -- profundidad cero. Se queda fuera de la clasificacion y se cuenta aparte.
49    -- El COALESCE(..., 0) de la version ingenua lo habria etiquetado "en riesgo"
50    -- y nadie sabria que lo que falta es un dato del catalogo.
51    WHERE p.pct_visto IS NOT NULL
52)
53SELECT user_id, dias_activos, horas, ROUND(pct_visto * 100, 1) AS pct_visto,
54       ROUND(min_por_sesion, 1) AS min_por_sesion, puntuacion
55FROM puntuada
56ORDER BY puntuacion DESC;

La puntuación, anclada al último día con datos y sin rellenar huecos con ceros.

Ahora los segmentos. La receta de manual dice: por encima de 70 son usuarios intensos, entre 40 y 70 habituales, entre 15 y 40 ocasionales, y por debajo de 15 gente en riesgo. La ejecutas sobre los datos de StreamVibe y sale esto: 1.398 usuarios habituales, 103 intensos, y ni un solo ocasional ni nadie en riesgo. El 93,1% en un grupo y los otros dos segmentos vacíos.

Cuando el 93% de tu gente cae en un segmento y dos de los cuatro grupos se quedan vacíos, los que están mal puestos son los cortes, no la gente. Los 70, 40 y 15 son tres números elegidos a ojo por quien escribió la receta, mirando otro producto. Antes de fijar un corte hay que mirar la distribución real: aquí la puntuación va de 48,3 a 93,7, con la mitad de los usuarios entre 58,2 y 65,1. Ningún corte en 15 ni en 40 va a separar a nadie, porque no hay nadie ahí abajo.

Así que los cortes salen de los cuartiles de la propia distribución, que además tiene una ventaja para el panel: los grupos quedan del mismo tamaño y comparar sus medias es comparar cuartos iguales. Con los cortes en 65,1, 61,3 y 58,2 la tabla queda así, y ahora sí se puede describir cada grupo en una frase.

1-- Resumen de segmentos para el panel.
2-- Los cortes salen de los cuartiles de la propia distribucion, no de tres
3-- numeros redondos elegidos a ojo.
4WITH cortes AS (
5    SELECT QUANTILE_CONT(puntuacion, 0.75) AS q3,
6           QUANTILE_CONT(puntuacion, 0.50) AS q2,
7           QUANTILE_CONT(puntuacion, 0.25) AS q1
8    FROM puntuada
9)
10SELECT
11    CASE WHEN puntuacion >= (SELECT q3 FROM cortes) THEN '1 intensos'
12         WHEN puntuacion >= (SELECT q2 FROM cortes) THEN '2 habituales'
13         WHEN puntuacion >= (SELECT q1 FROM cortes) THEN '3 ocasionales'
14         ELSE '4 de paso' END                          AS segmento,
15    COUNT(*)                                          AS usuarios,
16    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct,
17    ROUND(AVG(puntuacion), 1)                         AS puntuacion,
18    ROUND(AVG(dias_activos), 1)                       AS dias_activos,
19    ROUND(AVG(horas), 1)                              AS horas,
20    -- Los numeros CRUDOS van al lado de la puntuacion, siempre. Una puntuacion
21    -- compuesta no se ensena sola: quien la mire tiene que poder comprobarla.
22    ROUND(AVG(pct_visto) * 100, 1)                    AS pct_visto,
23    ROUND(AVG(min_por_sesion), 1)                     AS min_sesion
24FROM puntuada
25GROUP BY segmento
26ORDER BY segmento;
27
28-- Lo que sale con los datos del caso (21 dias, 1.500 usuarios):
29--
30-- segmento      | usuarios | pct   | punt | dias | horas | visto | min/ses
31-- 1 intensos    |      380 | 25,3% | 68,7 |  6,9 |  10,8 | 85,0% |    54,9
32-- 2 habituales  |      377 | 25,1% | 63,0 |  4,3 |  10,0 | 83,0% |    54,9
33-- 3 ocasionales |      374 | 24,9% | 59,7 |  2,6 |   9,6 | 82,2% |    55,7
34-- 4 de paso     |      370 | 24,6% | 55,9 |  1,5 |   9,8 | 76,6% |    56,3

Los cuatro segmentos con sus números crudos al lado. Mira las tres últimas columnas.

Ahora las frases para Marta, que es el trabajo de verdad. La segmentación sirve si dice qué hacer con cada grupo: los intensos son los evangelistas, y a ésos se les pide que traigan gente. Los habituales son el núcleo, y a ésos lo mejor que se les puede hacer es no molestarlos y mejorar el catálogo. Los ocasionales son la oportunidad: vienen dos o tres días de cada veintiuno y, cuando vienen, ven tanto como los demás, así que lo que falta no es interés, es motivo para volver. Y los de paso son la alerta temprana: si ese cuarto crece, hay un problema de producto.

Cuidado con la frase que se dice en voz alta. "Uno de cada cuatro de nuestros usuarios es un usuario intenso" es cierta sobre los 1.501 que aparecen en la ventana; sobre los 20.000 registrados son el 1,9%. Las dos cifras son verdad y dicen cosas muy distintas, y un inversor que haya leído la ficha de la empresa va a preguntar cuál es. Di siempre la base: "de los usuarios que entran cada mes". Y ojo con otra: "ve el 85,0% de lo que empieza" NO es "termina dos de cada tres títulos". El ratio mide la fracción media vista, no cuántos títulos se acaban. Son dos cifras distintas, las dos interesan, y hay que decir cuál se está dando.

## Crecimiento MoM -- la tendencia que importa

1-- Crecimiento mes a mes del MAU DEL MES.
2--
3-- Ojo con el nombre, porque hay dos MAU distintos en esta leccion:
4--   MAU MOVIL  = usuarios distintos en los ultimos 30 dias, recalculado cada
5--                dia. Es el que va en el stickiness, porque cambia a diario.
6--   MAU DEL MES = usuarios distintos dentro de un mes natural. Es el que va en
7--                la serie mensual.
8-- Casi nunca coinciden (solo el ultimo dia de un mes de 30), asi que si los dos
9-- aparecen en el mismo panel llamados igual, alguien va a preguntar por que hay
10-- dos numeros distintos con el mismo nombre.
11WITH mau_mes AS (
12    SELECT
13        DATE_TRUNC('month', event_date)::DATE AS mes,
14        COUNT(DISTINCT user_id)              AS mau_mes,
15        MIN(event_date)                      AS primer_dia,
16        MAX(event_date)                      AS ultimo_dia
17    FROM silver_playbacks
18    WHERE is_meaningful
19    GROUP BY 1
20),
21completos AS (
22    -- Un mes entra en la serie solo si esta COMPLETO por los dos lados.
23    -- Un mes a medias tiene menos dias para que un usuario aparezca, asi que su
24    -- recuento de usuarios distintos sale mas BAJO -- y eso se lee como una
25    -- caida que no existe.
26    SELECT *,
27        primer_dia = mes
28        AND ultimo_dia = LAST_DAY(mes) AS mes_completo
29    FROM mau_mes
30)
31SELECT
32    mes, mau_mes, primer_dia, ultimo_dia, mes_completo,
33    LAG(mau_mes) OVER (ORDER BY mes) AS mes_anterior,
34    ROUND((mau_mes - LAG(mau_mes) OVER (ORDER BY mes)) * 100.0
35          / NULLIF(LAG(mau_mes) OVER (ORDER BY mes), 0), 1) AS crecimiento_pct
36FROM completos
37ORDER BY mes;
38
39-- Lo que sale con los datos del caso:
40--
41-- mes      | mau_mes | primer_dia | ultimo_dia | completo
42-- 2024-09  |     558 | 2024-09-23 | 2024-09-30 | false
43-- 2024-10  |   1.191 | 2024-10-01 | 2024-10-13 | false
44--
45-- Dos meses y ninguno completo. Septiembre tiene 8 dias de datos y octubre 14.
46-- El "+113% mes a mes" que sale de dividir 1.191 entre 558 no mide crecimiento:
47-- mide que octubre tiene 13 dias de datos y septiembre 8.

La serie mensual, con la columna que dice si cada mes se puede usar. Ninguno se puede.

## La afirmación que no habías comprobado

A media mañana Marta te suelta de pasada la frase más valiosa de la semana: "el 80% de nuestro crecimiento viene de referidos y búsqueda orgánica". Crecer sin pagar por los usuarios es lo que separa una empresa que escala de una que compra tracción, y si eso es verdad es la mejor diapositiva del viernes. Así que no lo das por bueno: está en los datos. El fichero de usuarios trae el origen de cada alta.

1-- De donde vienen las altas de verdad.
2SELECT
3    signup_source,
4    COUNT(*)                                          AS altas,
5    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
6FROM dim_users
7GROUP BY 1 ORDER BY 2 DESC;
8
9-- organic    7.047  35,2%
10-- instagram  4.926  24,6%
11-- google     4.076  20,4%
12-- referral   2.383  11,9%
13-- tiktok       974   4,9%
14-- other        594   3,0%
15
16-- Y ahora la pregunta que decide la cifra: los referidos, ¿se pagan?
17SELECT channel, ROUND(SUM(spend_eur), 2) AS gasto
18FROM marketing GROUP BY 1 ORDER BY 2 DESC;
19
20-- tiktok             64.680,32
21-- referral_program   60.872,66   <-- aqui esta la respuesta
22-- google             59.341,40
23-- partnerships       51.593,75
24-- instagram          49.004,31
25-- organic                 0,00

Dos consultas de tres líneas que cambian la diapositiva principal del viernes.

1-- Lo que cuesta traer a un usuario comprado.
2--
3-- El gasto se divide entre las altas de CANALES DE PAGO, no entre todas las
4-- altas. Si se divide entre todas, el resultado baja cuanto mas crezcas
5-- organicamente -- o sea que mejora sin que hayas hecho nada mejor. Ese numero
6-- tambien se usa, pero es otro y se llama coste mezclado.
7SELECT
8    ROUND(SUM(spend_eur), 2)                                       AS gasto,
9    SUM(signups_attributed)                                        AS altas_de_pago,
10    ROUND(SUM(spend_eur) / NULLIF(SUM(signups_attributed), 0), 2)  AS coste_por_alta
11FROM marketing
12WHERE channel <> 'organic';
13
14-- gasto 285.492,44 | altas_de_pago 21.478 | coste_por_alta 13,29
15
16-- PARA. Antes de llevar ese 13,29 a ninguna reunion, cuadra el denominador.
17-- Las 21.478 "altas atribuidas" salen del fichero de marketing, o sea de lo que
18-- cada plataforma DICE haber conseguido. StreamVibe tiene 20.000 usuarios EN
19-- TOTAL, organicos incluidos. No pueden venir 21.478 de canales de pago.
20SELECT
21    (SELECT SUM(signups_attributed) FROM marketing WHERE channel <> 'organic')
22        AS altas_que_se_atribuyen,
23    COUNT(*) AS altas_reales_de_esos_canales,
24    ROUND(
25        (SELECT SUM(signups_attributed) FROM marketing WHERE channel <> 'organic')
26        * 1.0 / COUNT(*), 2
27    ) AS veces_infladas
28FROM users
29WHERE signup_source IN ('google', 'instagram', 'tiktok', 'referral', 'partnerships');
30-- altas_que_se_atribuyen 21.478 | altas_reales 9.976 | veces_infladas 2,15
31
32-- Con las altas REALES el coste por alta es otro:
33--   285.492,44 / 9.976 = 28,62 EUR
34-- El 13,29 no esta mal calculado: esta calculado sobre un denominador que las
35-- plataformas se han inflado. Es lo de siempre en publicidad: si tres canales
36-- tocan al mismo usuario, los tres se lo apuntan. A Marta se le llevan los DOS
37-- numeros y de donde sale cada uno.

Coste por alta: 13,29 EUR con las altas que se atribuyen las plataformas, 28,62 con las reales.

## Una métrica compuesta es una opinión con decimales

Antes de cerrar el día conviene mirar lo que acabas de construir con algo de desconfianza. La puntuación de engagement parece una medida y no lo es: es un juicio de valor escrito en SQL. Tiene cinco decisiones dentro y ninguna viene de los datos.

Qué entra. Frecuencia, profundidad e intensidad. Todo lo que no entra -- si vuelve después de una ausencia, si comparte, si valora -- no cuenta, y eso ya es una postura sobre qué significa estar enganchado.
Cuánto pesa cada cosa. El 40/30/30 no sale de ningún sitio: lo has elegido tú. Está bien siempre que puedas defenderlo (la frecuencia pesa más porque volver cuesta más que quedarse un rato) y siempre que lo digas.
Dónde está el tope de cada factor. "Treinta minutos por sesión es un 1,0" es otra decisión: con ella, quien ve cuarenta minutos y quien ve dos horas puntúan igual. A veces es lo que quieres; hay que saber que lo estás haciendo.
Dónde cortas los segmentos. Los 70, 40 y 15 del manual metían al 93% de la gente en un grupo y dejaban dos vacíos. Los cortes se miran contra la distribución, siempre.
Qué haces con los huecos. Un usuario sin datos en un factor no es un usuario con cero en ese factor. Rellenar con cero es una decisión, y de las que más mueven el resultado.

La prueba de que una puntuación compuesta está bien hecha es ésta: coge tres usuarios de cada segmento, mira lo que han hecho de verdad y pregúntate si los llamarías así. Si el que sale "usuario intenso" es alguien que dejó una pestaña abierta, la fórmula está mal por muy bonita que sea la tabla. Y para presentarla: nunca sola. Al lado van los dos o tres números crudos de los que sale -- días activos, horas, porcentaje visto -- para que quien la mire pueda comprobarla. Es lo que ha permitido ver, hace dos párrafos, que dos de los tres factores no distinguían a nadie.

## Los tres sitios donde el calendario te ha engañado hoy

Repasa el día y hay un mismo error con tres caras distintas. Merece la pena verlas juntas, porque es el error más frecuente de este oficio y no se parece a un error: se parece a un hallazgo.

El periodo en curso. El mes de octubre tiene trece días de datos y septiembre ocho, y aun así octubre sale con más del doble de usuarios. Un periodo incompleto tiene menos oportunidades de contar a alguien, así que sale más bajo -- y se lee como una caída.
La cohorte joven. La cohorte del 13 de octubre tiene las cuatro columnas a cero, incluido el D1, y son 68 usuarios. No se han ido: el día siguiente no existe todavía.
El principio de una ventana móvil. Una media de siete días sobre los tres primeros días es una media de tres. Un recuento de treinta sobre los tres primeros es un recuento de tres. Y como el stickiness divide por ese recuento, el stickiness de los primeros días sale inflado: el primero da el 100% y el segundo el 88,2%.

Los tres tienen la misma forma: un periodo incompleto parece un periodo malo -- o bueno, si está en el denominador. Y los tres se arreglan igual: cortando la serie donde deja de estar completa, marcando en la propia consulta qué filas no se pueden leer, y escribiendo la fecha de corte en el pie. La parte incómoda es que a veces, como hoy con el stickiness y con la tendencia mensual, el resultado de hacerlo bien es que no hay número que dar.

## Resumen del Día 3 (mañana)

Calculaste DAU diario con media móvil de 7 días: sube de 51 a 405 en tres semanas
Arreglaste la vista del MAU móvil, que no compilaba, y descubriste que con 21 días de histórico la ventana de 30 nunca se llena: el stickiness no se puede presentar
Retención por cohorte diaria con ventanas de la misma anchura: D1 73,0%, D3 70,9%, D7 29,4% sobre cohortes maduras, y D14 con la base al lado porque son sólo seis cohortes
Marcaste la madurez columna por columna, que es lo que evita presentar una fuga de usuarios que no existe
Puntuación de engagement anclada a los datos, sin rellenar huecos con ceros, y con los cortes sacados de la distribución real en vez de los 70/40/15 del manual
Viste que de los tres factores de la puntuación sólo uno distingue a la gente en esta población, y eso hay que saberlo antes de defenderla
Comprobaste la afirmación de Marta: el crecimiento no pagado es el 35,2%, no el 80%, porque el programa de referidos cuesta 60.872 euros
Tienes dos de los tres actos sólidos -- "la gente viene" y "la gente se queda" -- y una lista corta de lo que no se puede decir todavía

Regístrate para guardar tu progreso.