Cohortes y retención del mes 1

Avanzado

Una cohorte es el grupo de usuarios que empezó en el mismo período, por ejemplo, todos los que se registraron en marzo. La retención del mes 1 es el porcentaje de esa cohorte que volvió a tener actividad en el mes calendario siguiente al de su registro. El denominador es siempre la cohorte completa, incluidos los que nunca volvieron.

Sintaxis

WITH cohortes AS (
  SELECT id AS user_id, date_trunc('month', signup_at)::date AS cohorte
  FROM users
)
-- activos en [cohorte + 1 mes, cohorte + 2 meses)
-- LEFT JOIN a la cohorte y count(activos) / count(*)

Ejemplo

WITH cohortes AS (
  SELECT id AS user_id, date_trunc('month', signup_at)::date AS cohorte
  FROM users
  WHERE signup_at >= '2025-03-01'
    AND signup_at < '2025-07-01'
), activos_mes_1 AS (
  SELECT DISTINCT c.user_id
  FROM cohortes AS c
  INNER JOIN plays AS p ON p.user_id = c.user_id
  WHERE p.played_at >= c.cohorte + interval '1 month'
    AND p.played_at < c.cohorte + interval '2 months'
)
SELECT
  to_char(c.cohorte, 'YYYY-MM') AS cohorte,
  count(*) AS usuarios,
  count(a.user_id) AS activos_mes_1,
  round(100.0 * count(a.user_id) / count(*), 1) AS retencion_mes_1_pct
FROM cohortes AS c
LEFT JOIN activos_mes_1 AS a ON a.user_id = c.user_id
GROUP BY c.cohorte
ORDER BY c.cohorte;

Resultado

4 filas · Ritmo

4 filas · Ritmo
cohorte	usuarios	activos_mes_1	retencion_mes_1_pct
2025-03	305	234	76.7
2025-04	287	217	75.6
2025-05	313	257	82.1
2025-06	325	261	80.3

Cómo leerlo

De los 305 oyentes que se registraron en marzo de 2025 (meses en UTC), 234 reprodujeron algo en abril: una retención del mes 1 de 76.7 %. DISTINCT en activos_mes_1 cuenta personas y no reproducciones, y el LEFT JOIN mantiene en el denominador a quienes no volvieron. Solo se incluyen cohortes cuyo mes 1 ya terminó; una cohorte de agosto todavía no tendría septiembre completo.

Error común

Así no

SELECT
  date_trunc('month', u.signup_at) AS cohorte,
  count(DISTINCT u.id) AS activos_mes_1
FROM users AS u
INNER JOIN plays AS p ON p.user_id = u.id
WHERE p.played_at >= date_trunc('month', u.signup_at) + interval '1 month'
  AND p.played_at < date_trunc('month', u.signup_at) + interval '2 months'
GROUP BY date_trunc('month', u.signup_at);

Calcular la cohorte con un INNER JOIN a la actividad. Los usuarios sin reproducciones en el mes 1 desaparecen antes de contar, así que el tamaño de la cohorte queda igual a la cantidad de activos: si divides una cifra por la otra, la retención sale 100 % en todas las cohortes. El tamaño de la cohorte se cuenta sobre la tabla de usuarios, y la actividad se une con LEFT JOIN o se consulta con EXISTS.

En otros motores: PostgreSQL, Snowflake, BigQuery, SQL Server, Oracle, MySQL

PostgreSQLSnowflake
date_trunc('month', fecha) lleva la fecha al primer día del mes.
BigQuerySQL ServerOracleMySQL
BigQuery usa DATE_TRUNC(fecha, MONTH) (con la unidad al final); SQL Server 2022 tiene DATETRUNC(month, fecha) y en versiones anteriores se usa DATEFROMPARTS(YEAR(fecha), MONTH(fecha), 1); Oracle, TRUNC(fecha, 'MM'); MySQL no tiene date_trunc.

Estos fragmentos no se ejecutan en el curso: se muestran como referencia de cada motor.

WITH cohortes AS ( SELECT id AS user_id, date_trunc('month', signup_at)::date AS cohorte FROM users ) -- activos en [cohorte + 1 mes, cohorte + 2 meses) -- LEFT JOIN a la cohorte y count(activos) / count(*)

WITH cohortes AS ( SELECT id AS user_id, date_trunc('month', signup_at)::date AS cohorte FROM users WHERE signup_at >= '2025-03-01' AND signup_at < '2025-07-01' ), activos_mes_1 AS ( SELECT DISTINCT c.user_id FROM cohortes AS c INNER JOIN plays AS p ON p.user_id = c.user_id WHERE p.played_at >= c.cohorte + interval '1 month' AND p.played_at < c.cohorte + interval '2 months' ) SELECT to_char(c.cohorte, 'YYYY-MM') AS cohorte, count(*) AS usuarios, count(a.user_id) AS activos_mes_1, round(100.0 * count(a.user_id) / count(*), 1) AS retencion_mes_1_pct FROM cohortes AS c LEFT JOIN activos_mes_1 AS a ON a.user_id = c.user_id GROUP BY c.cohorte ORDER BY c.cohorte;

cohorte

usuarios

activos_mes_1

retencion_mes_1_pct

2025-03

305

234

76.7

2025-04

287

217

75.6

2025-05

313

257

82.1

2025-06

325

261

80.3

SELECT date_trunc('month', u.signup_at) AS cohorte, count(DISTINCT u.id) AS activos_mes_1 FROM users AS u INNER JOIN plays AS p ON p.user_id = u.id WHERE p.played_at >= date_trunc('month', u.signup_at) + interval '1 month' AND p.played_at < date_trunc('month', u.signup_at) + interval '2 months' GROUP BY date_trunc('month', u.signup_at);