Día 3 - Cohortes de retención y la paradoja de Simpson

1#data-team | Jueves 9:10 AM
2
3Pablo Herrera  9:10 AM
4?? Standup jueves - sprint final
5- Sandra: listos los modelos de activacion? Los necesita el nuevo
6- Marcos: pipeline billing mergeado! ??
7- Nuevo: HOY cohortes. Si necesitas algo de nosotros avisa
8  temprano. No quiero sorpresas mañana.
9
10Deadline recordatorio: demo viernes 16:00. Lucia estará.
11
12Sandra Vidal  9:12 AM
13Mi modelo int_user_first_activity está listo ??
14Lo puedes usar como base para las cohortes: tiene user_id,
15signup_date, first_core_event_date, y first_event_type.
16Esta en el schema analytics_int.
17
18Tu  9:14 AM
19Perfecto Sandra! Lo uso como base. Objetivo de hoy:
201. Query de cohortes semanales con retención W1-W12
212. Validar números con Sandra
223. Preparar visualización para la demo
23
24Pablo Herrera  9:15 AM
25?? Me gusta ese plan. Avisame cuando tengas los primeros
26números -- quiero verlos antes de que se los ensenes a Lucia.
27
28    ? 2

1-- Cohortes de retención semanal
2-- Cada fila = cohorte (semana de signup)
3-- Cada columna = semanas_desde_signup (0, 1, 2, 4, 8, 12)
4-- Valor = % de usuarios de esa cohorte que fueron core_active esa semana
5
6WITH user_cohorts AS (
7    -- Asignar cada usuario a su cohorte (semana de signup)
8    SELECT
9        user_id,
10        DATE_TRUNC('week', signup_date) AS cohort_week,
11        signup_date
12    FROM analytics_int.int_user_first_activity
13),
14
15user_weekly_activity AS (
16    -- Para cada usuario, en que semanas fue core_active?
17    SELECT
18        e.user_id,
19        DATE_TRUNC('week', e.event_timestamp) AS activity_week
20    FROM staging.stg_product_events e
21    WHERE e.event_category = 'core'
22    GROUP BY e.user_id, DATE_TRUNC('week', e.event_timestamp)
23),
24
25cohort_retention AS (
26    SELECT
27        uc.cohort_week,
28        DATEDIFF('week', uc.cohort_week, uwa.activity_week) AS weeks_since_signup,
29        COUNT(DISTINCT uc.user_id) AS active_users
30    FROM user_cohorts uc
31    INNER JOIN user_weekly_activity uwa
32        ON uc.user_id = uwa.user_id
33        AND uwa.activity_week >= uc.cohort_week
34    GROUP BY uc.cohort_week, DATEDIFF('week', uc.cohort_week, uwa.activity_week)
35),
36
37cohort_sizes AS (
38    SELECT
39        cohort_week,
40        COUNT(DISTINCT user_id) AS cohort_size
41    FROM user_cohorts
42    GROUP BY cohort_week
43)
44
45SELECT
46    cr.cohort_week,
47    cs.cohort_size,
48    cr.weeks_since_signup,
49    cr.active_users,
50    ROUND(cr.active_users * 100.0 / cs.cohort_size, 1) AS retention_pct
51FROM cohort_retention cr
52JOIN cohort_sizes cs ON cr.cohort_week = cs.cohort_week
53WHERE cr.weeks_since_signup IN (0, 1, 2, 4, 8, 12)
54ORDER BY cr.cohort_week, cr.weeks_since_signup;

La query de cohortes: CTEs para claridad, JOIN para retener solo activos

1Resultados segmentados por plan:
2
3Plan FREE:
4- Cohorte Ene: W1 = 65%, W4 = 38%, W8 = 25%
5- Cohorte Mar: W1 = 58%, W4 = 30%, W8 = -
6
7Plan PRO:
8- Cohorte Ene: W1 = 82%, W4 = 68%, W8 = 55%
9- Cohorte Mar: W1 = 80%, W4 = 65%, W8 = -
10
11Plan BUSINESS:
12- Cohorte Ene: W1 = 91%, W4 = 82%, W8 = 76%
13- Cohorte Mar: W1 = 89%, W4 = 80%, W8 = -
14
15DESCUBRIMIENTO:
16La retención de cada plan individual casi NO ha cambiado.
17Free bajo 7pp, Pro bajo 2pp, Business bajo 2pp.
18Pero el AGREGADO bajo 11pp (de 72% a 61%).
19
20Por que? Porque la PROPORCION de usuarios free aumento.
21En enero el 40% de signups eran free. En marzo el 65%.
22Es la PARADOJA DE SIMPSON.

1Sandra Vidal (call, 14:00):
2
3"A ver, enseneme esos números... Ajam. OK, la query está bien.
4Pero tengo una pregunta: en la cohorte de enero, el tamaño
5es 842 usuarios. Pero en dim_users tengo 891 signups esa
6semana. De donde sale la diferencia?"
7
8Tu: "Mi tabla solo tiene usuarios que hicieron al menos 1
9evento core. Los 49 que faltan se registraron pero nunca
10hicieron nada -- ni crearon una tarea."
11
12Sandra: "Eso es correcto para el cálculo de retención. Pero
13necesitamos documentar que la cohorte se define como
14'usuarios que tuvieron al menos 1 evento core en su W0'.
15Si no, alguien va a comparar con el dato de signups totales
16y no va a cuadrar."
17
18Tu: "Hecho. Lo documento en el Notion."
19
20Sandra: "Bien. Siguiente pregunta: los usuarios que cambiaron
21de plan durante el período... los pones en el plan actual o
22en el plan que tenian en el momento del signup?"
23
24Tu: "Plan actual (dim_workspaces). Lo documente como
25'plan a fecha de consulta, no plan histórico'."
26
27Sandra: "Correcto para un primer corte. Pero ojo: cuando
28un usuario cambia de free a pro, su retención SUBE porque
29ya está engaged. Si los pones en Pro retrospectivamente,
30inflas la retención de Pro. Para la demo de mañana está
31bien, pero el modelo definitivo necesita plan-at-signup.
32Lo anoto como mejora."
33
34Tu: "Entendido. Lo resolvemos con la tabla de historial
35de planes la semana que viene?"
36
37Sandra: "Exacto. Y lo de Simpson... buenisimo hallazgo.
38Pablo va a flipar. Preparalo bien para la demo porque eso
39cambia la narrativa completamente ??"

1Lucia Fernandez  15:32 PM
2Ey, Pablo me ha adelantado los números de cohortes ??
3Tengo unas preguntas:
4
51. El 61% de W1... es solo free o incluye todos?
62. Si los Pro retienen al 80%... por que no convertimos
7   más gente a Pro? Que hacen diferente?
83. Puedes calcular CUANDO churnan los free? Es en la
9   primera semana o después?
10
11Sorry por el bombardeo pero esto es oro para el board ??
12
13Tu  15:45 PM
14Hola Lucia! Te respondo:
151. El 61% es el agregado (todos los planes mezclados).
16   Free solo es 58%, Pro es 80%, Business es 89%.
172. Esa es LA pregunta. Manana en la demo te presento
18   un análisis de features: que hacen los Pro que no
19   hacen los Free en su primera semana.
203. Puedo calcular eso! Spoiler: la mayoria del churn
21   free pasa entre día 3 y día 7. Si sobreviven la
22   primera semana, la retención mejora mucho.
23
24Lucia Fernandez  15:47 PM
25Ey esto es EXACTAMENTE lo que necesito
26Lo del día 3-7 es super actionable. Si sabemos que
27features usan antes de irse podemos hacer algo
28Eres un crack ????
29
30    ?? 1   ?? 1

1Pablo Herrera  16:05 PM (DM)
2He visto tu conversacion con Lucia. Los hallazgos son
3excelentes. ??
4
5Dos cosas:
61. Para la demo de mañana, empieza con Simpson. Es lo más
7   impactante y va a captar la atención de Andres.
82. El "aha moment" (automation_created) -- quiero que
9   tengas cuidado con como lo comunicas. Di "correlaciona
10   con" no "causa". Sandra va a preguntar exactamente eso.
11
12Buen trabajó está semana. Manana se lo demuestras a todos.
13
14Tu  16:10 PM
15Gracias Pablo! Entendido -- correlación, no causalidad.
16Tengo preparada una propuesta de A/B test por si preguntan
17"y entonces que hacemos con esto?"
18
19Pablo Herrera  16:12 PM
20?? Excelente. Eso es pensar un pasó adelante. Me gusta.