Нужно построить когортный анализ retention по месяцу регистрации, чтобы понять, где теряются пользователи. Как это сделать в SQL и как интерпретировать результат для диагностики оттока?

Короткий ответ

Группирую пользователей в когорты по месяцу регистрации, считаю долю каждой когорты, оставшейся активной на 1-й, 2-й, 3-й... месяц после регистрации, и сравниваю кривые retention между когортами, чтобы увидеть, ухудшается ли удержание со временем.

Как это работает подробнее

когортный анализ разделяет «эффект времени с момента регистрации» (естественное снижение активности) и «эффект календарного периода» (изменение продукта/привлечения), что невозможно увидеть в агрегированном по всем пользователям retention.

Пример кода

analysis.sqlКопировать

WITH cohorts AS (
  SELECT user_id, date_trunc('month', signup_date) AS cohort_month
  FROM users
),
activity AS (
  SELECT c.user_id, c.cohort_month,
         date_trunc('month', e.event_date) AS activity_month
  FROM cohorts c
  JOIN events e ON e.user_id = c.user_id
)
SELECT cohort_month,
       (extract(year FROM activity_month) - extract(year FROM cohort_month)) * 12
         + (extract(month FROM activity_month) - extract(month FROM cohort_month)) AS month_number,
       count(DISTINCT user_id) AS active_users
FROM activity
GROUP BY 1, 2
ORDER BY 1, 2;

Что использовать на практике

• На практике: для каждого пользователя фиксирую месяц регистрации (cohort_month) и для каждого последующего календарного месяца проверяю, было ли активное событие; свожу в таблицу cohort_month × month_number с долей активных. Сравниваю кривые разных когорт: если retention у новых когорт хуже, чем у старых на том же шаге — это сигнал о проблеме в продукте/привлечении за последнее время, а не об общем «естественном» оттоке.
• Проверка: разделить active_users на размер когорты (count в cohorts по cohort_month) и построить таблицу долей; устойчивое падение доли на одном и том же month_number у более новых когорт подтверждает ухудшение удержания.

Подводные камни

• Частые ошибки: путать календарный месяц с номером месяца жизни когорты при визуализации; не нормализовать по размеру когорты (смотреть абсолютные числа вместо долей); делать вывод о причине ухудшения retention без дальнейшей сегментации (например, по каналу привлечения новой когорты).