Retention-треугольник когорт — когорты по месяцу регистрации на месяцы с регистрации
Таблицы users(user_id, signup_date) и activity(user_id, activity_date). Постройте retention-треугольник когорт — для каждой когорты по месяцу регистрации и каждого целого месяца с регистрации долю когорты, активной в этот месяц. Месяц 0 — месяц регистрации.
-- cohort_month, months_since, retention_pct (доля активной когорты)Напишите запрос.
Относят регистрацию к месяцу когорты и считают размер COUNT(*). months_since — разница года и месяца между месяцем активности и когорты; делят уникальных активных на (когорту, months_since) на этот фиксированный размер когорты, а не на скользящую базу.
- ✗Брать активных прошлого месяца как скользящий знаменатель
- ✗Приближать прошедшие месяцы как разницу дней делить на 30
- ✗Считать размер когорты по активным месяца 0, а не по всем регистрациям
- →Почему фиксированный знаменатель по размеру когорты лучше скользящего?
- →Как заполнить месяцы, где у когорты было ноль активных?
Bucket each user into their signup month, measure the cohort size once, then for every active month express distinct actives as a percent of that fixed size:
WITH cohort AS (
SELECT user_id, date_trunc('month', signup_date)::date AS cohort_month
FROM users
),
sizes AS (
SELECT cohort_month, COUNT(*) AS cohort_size
FROM cohort
GROUP BY cohort_month
),
active AS (
SELECT c.cohort_month,
12 * (EXTRACT(YEAR FROM a.activity_date) - EXTRACT(YEAR FROM c.cohort_month))
+ (EXTRACT(MONTH FROM a.activity_date) - EXTRACT(MONTH FROM c.cohort_month)) AS months_since,
a.user_id
FROM cohort c
JOIN activity a ON a.user_id = c.user_id
)
SELECT a.cohort_month,
a.months_since,
ROUND(100.0 * COUNT(DISTINCT a.user_id) / s.cohort_size, 2) AS retention_pct
FROM active a
JOIN sizes s ON s.cohort_month = a.cohort_month
GROUP BY a.cohort_month, a.months_since, s.cohort_size
ORDER BY a.cohort_month, a.months_since;The denominator is the cohort's total signups, held constant across all months_since — that is what makes the triangle comparable (month 0 = 100%, later months decay). months_since is the whole-month difference, not days / 30, which would drift. Sizing the cohort from month-0 actives would silently drop users who signed up but were not active that month.
