Когортный анализ retention в SQL: пошаговый расчёт

«Какой retention на 30-й день у пользователей мартовской когорты?» — вопрос, который слышит каждый продуктовый аналитик минимум раз в неделю. И каждый раз кто-то путает понятия: classic retention vs rolling retention, M+0 vs M+1, считать от регистрации или от первой покупки.

Эта статья — пошаговый расчёт когортного retention в SQL. От разбивки пользователей на когорты до итоговой матрицы N×M, готовой к визуализации в Tableau/Superset.

Что такое когорта и зачем она

Когорта — группа пользователей, объединённая по событию в один временной период. Самые частые когорты:

• По регистрации: все юзеры, зарегистрировавшиеся в марте 2026
• По первой покупке: все, кто впервые купил в марте
• По источнику: все из канала «инфлюенсер X»

Зачем нужны: видеть как меняется поведение со временем для каждой группы. Если retention мартовской когорты на 30-й день — 25%, а апрельской — 35%, значит апрель что-то улучшилось (новый онбординг, фича, бренд).

Шаг 1: определить когорту

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', registered_at)::date AS cohort_month
    FROM users
)
SELECT * FROM cohorts LIMIT 5;
-- user_id | cohort_month
-- 42      | 2026-03-01
-- 43      | 2026-03-01
-- 44      | 2026-04-01

Каждый юзер привязан к одной когорте — месяцу его регистрации.

Шаг 2: считать активность по периодам

Активность = делал любое целевое действие (зашёл, купил, открыл приложение). Берём из таблицы событий.

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', registered_at)::date AS cohort_month FROM users
),
activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', event_ts)::date AS active_month
    FROM events
    GROUP BY user_id, DATE_TRUNC('month', event_ts)
)
SELECT * FROM activity LIMIT 5;

Шаг 3: соединить и посчитать «месяц жизни»

Главная фишка — «месяц с момента регистрации» (M+0, M+1, M+2 …):

WITH cohorts AS (...),
activity AS (...),
joined AS (
    SELECT
        c.cohort_month,
        c.user_id,
        a.active_month,
        EXTRACT(MONTH FROM AGE(a.active_month, c.cohort_month))::int AS month_offset
    FROM cohorts c
    JOIN activity a ON a.user_id = c.user_id
    WHERE a.active_month >= c.cohort_month
)
SELECT cohort_month, month_offset, COUNT(DISTINCT user_id) AS active_users
FROM joined
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

Получится:

Шаг 4: посчитать retention в процентах

Делим активных в M+N на размер когорты:

WITH ... (предыдущие CTE),
cohort_size AS (
    SELECT cohort_month, COUNT(*) AS size
    FROM cohorts
    GROUP BY cohort_month
)
SELECT
    j.cohort_month,
    j.month_offset,
    j.active_users,
    cs.size AS cohort_size,
    ROUND(j.active_users * 100.0 / cs.size, 1) AS retention_pct
FROM joined j
JOIN cohort_size cs ON cs.cohort_month = j.cohort_month
ORDER BY j.cohort_month, j.month_offset;

Это и есть когортная матрица. Визуализируется как heatmap — строки когорт, колонки M+N, цвет = retention.

Classic vs Rolling retention

Classic retention M+N: «активен ли юзер именно в N-й месяц?»

Юзер был активен в M+1, M+2, M+5 → classic M+5 = да, classic M+3 = нет

Rolling retention M+N: «был ли юзер активен в любой из N+, N+1, N+2 ... месяцев?»

Юзер был активен в M+5 → rolling retention M+3 = да (потому что был позже)

Rolling добрее, classic строже. Какой использовать — зависит от продукта:

• Подписка — classic (отвалился = плохо)
• App — rolling (вернулся через 3 месяца = жив)

-- Rolling retention M+1 для мартовской когорты:
-- кто из мартовских юзеров был активен в любой месяц >= апрель
SELECT COUNT(DISTINCT user_id) * 100.0 / 1000 AS rolling_m1_pct
FROM activity a
JOIN cohorts c ON c.user_id = a.user_id
WHERE c.cohort_month = '2026-03-01'
  AND a.active_month >= '2026-04-01';

Edge cases

Когорты разной длительности жизни

Мартовская когорта живёт 2 месяца (есть данные за апрель). Майская когорта живёт 0 месяцев — её M+1 ещё не наступил.

В выдаче будет «дырка» — это нормально. Не показывай retention для тех месяцев, для которых физически нет данных.

WHERE j.active_month <= CURRENT_DATE  -- только реальные данные

Юзер активен один день в M+1 — считать?

Зависит от определения. Часто берут «активен ≥ 1 дня в месяце». Иногда «≥ N сессий», «≥ Y минут на странице». Уточняй у продакта.

Гранулярность: дни/недели/месяцы

Меняй DATE_TRUNC('month', ...) на 'week' / 'day' для других гранулярностей. Дневной retention для ранних дней (D1, D7), недельный для апп-метрик, месячный для долгих когорт.

Готовый SQL для всего отчёта

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', registered_at)::date AS cohort_month
    FROM users
),
activity AS (
    SELECT user_id, DATE_TRUNC('month', event_ts)::date AS active_month
    FROM events
    GROUP BY user_id, DATE_TRUNC('month', event_ts)
),
cohort_size AS (
    SELECT cohort_month, COUNT(*) AS size FROM cohorts GROUP BY cohort_month
),
matrix AS (
    SELECT
        c.cohort_month,
        EXTRACT(MONTH FROM AGE(a.active_month, c.cohort_month))::int AS m_offset,
        COUNT(DISTINCT c.user_id) AS active_users
    FROM cohorts c
    JOIN activity a ON a.user_id = c.user_id
    WHERE a.active_month >= c.cohort_month
      AND a.active_month <= CURRENT_DATE
    GROUP BY c.cohort_month, m_offset
)
SELECT
    m.cohort_month,
    m.m_offset,
    cs.size AS cohort_size,
    m.active_users,
    ROUND(m.active_users * 100.0 / cs.size, 1) AS retention_pct
FROM matrix m
JOIN cohort_size cs ON cs.cohort_month = m.cohort_month
ORDER BY m.cohort_month, m.m_offset;

Подставь свою таблицу users (или events) — получишь готовую когортную матрицу.

Связанные материалы

• SQL-тренажёр: задачи на retention и когорты — реальные собесные задачи
• Оконные функции SQL — нужны для rolling retention и rank
• CTE в SQL — как раскладывать сложные расчёты на шаги
• Метрики продукта: retention — формулы и SQL для всех product-метрик
• Продуктовые кейсы — где retention применяется в реальных кейсах

Открой SQL-тренажёр и найди задачу с тегом «retention» или «когорты». На реальной схеме разобрать когорты — лучший способ научиться.