Когортный анализ — что это, формула, пример SQL и Python

Что такое когорта

Когорта — группа пользователей объединённая по дате первой активности (регистрации, первого заказа, установки app). Обычно — месячные когорты (январская когорта = все кто зарегался в январе).

Зачем нужен когортный анализ

Без когорт ты видишь усреднённую картину. С когортами — видишь паттерн:

• Стало ли удержание лучше для новых юзеров vs полгода назад?
• В каком месяце retention падает максимально (3 месяц, 6 месяц)?
• Какие каналы привлечения дают лучший retention (organic vs paid)?
• Помог ли релиз новой фичи retention для post-релизной когорты?
• Где находится «aha-moment» — какие действия в 1-й день увеличивают retention в 1 месяц?

Формула retention

Retention(t) для когорты C = доля юзеров когорты C активных в период t (с момента acquisition):

Retention(cohort=C, month_since=t) =
    (active_users_in_cohort_C_at_month_t) /
    (cohort_size_C)
    * 100%

Пример:
Январская когорта = 1000 юзеров (cohort_size)
Через 1 месяц активны = 400 → Retention_M1 = 40%
Через 3 месяца активны = 200 → Retention_M3 = 20%
Через 6 месяцев активны = 120 → Retention_M6 = 12%

SQL: cohort retention за 3 месяца

Самый частый SQL-вопрос с собеса. Считаем retention для каждой когорты по месяцам:

WITH cohorts AS (
  -- Cohort month = месяц первой активности
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_date)) AS cohort_month
  FROM events
  GROUP BY user_id
),
activity AS (
  -- Все активные месяцы каждого юзера
  SELECT
    e.user_id,
    DATE_TRUNC('month', e.event_date) AS event_month,
    c.cohort_month,
    EXTRACT(MONTH FROM AGE(
      DATE_TRUNC('month', e.event_date),
      c.cohort_month
    )) AS months_since_acq
  FROM events e
  JOIN cohorts c USING (user_id)
)
SELECT
  cohort_month,
  months_since_acq,
  COUNT(DISTINCT user_id) AS active_users,
  ROUND(
    COUNT(DISTINCT user_id) * 100.0 /
    FIRST_VALUE(COUNT(DISTINCT user_id))
      OVER (PARTITION BY cohort_month ORDER BY months_since_acq),
    2
  ) AS retention_pct
FROM activity
WHERE months_since_acq <= 6
GROUP BY cohort_month, months_since_acq
ORDER BY cohort_month, months_since_acq;

Python pandas: тот же расчёт

Аналог на pandas — через groupby + transform + pivot:

import pandas as pd

def cohort_retention(events: pd.DataFrame) -> pd.DataFrame:
    # 1. Cohort month для каждого юзера
    events['cohort_month'] = events.groupby('user_id')['event_date'].transform('min').dt.to_period('M')
    events['event_month'] = events['event_date'].dt.to_period('M')

    # 2. Месяцев с момента acquisition
    events['months_since'] = (events['event_month'] - events['cohort_month']).apply(lambda x: x.n)

    # 3. Pivot: cohort × months_since → unique users
    cohort_table = events.groupby(['cohort_month', 'months_since'])['user_id'] \
        .nunique() \
        .unstack(fill_value=0)

    # 4. Retention % от cohort size (M0)
    cohort_sizes = cohort_table.iloc[:, 0]
    retention = cohort_table.divide(cohort_sizes, axis=0) * 100

    return retention.round(2)

Типичные ошибки

Что новички делают не так:

• Считают DAU вместо unique users в когорте — двойной счёт
• Берут все события юзера а не только в нужном месяце → завышение retention
• Игнорируют незрелые когорты (на 6-й месяц можно сказать только про cohorts ≥ 6 мес назад)
• Не учитывают сезонность — январь и декабрь несравнимы по retention
• Слишком короткий период (1 месяц мало для SaaS — нужно минимум 3-6)

Визуализация cohort retention

Стандартное представление — cohort heatmap: строки = когорты, колонки = месяцы с момента acquisition, цвет = retention %. Темнее = лучше. Видишь паттерны: одни когорты лучше, другие хуже.