Как посчитать Cohort Retention в SQL

Зачем Cohort Retention

Общий retention 30% — звучит средне. Cohort retention показывает картину глубже: январская когорта — 45%, февральская — 35%, мартовская — 22%. Тренд падает — значит, что-то сломалось в привлечении или онбординге. Когортный разрез вскрывает то, что среднее по всем скрывает.

Что такое Cohort

Cohort (когорта) — группа пользователей с общей точкой входа (например, дата регистрации). Cohort retention — доля когорты, активная через N периодов после старта.

Базовый расчёт

Данные: users(user_id, signup_date), events(user_id, event_date).

WITH cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signup_date) AS cohort_month
    FROM users
    WHERE signup_date >= '2026-01-01'
),
activity AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', event_date) AS active_month
    FROM events
)
SELECT
    c.cohort_month,
    a.active_month,
    EXTRACT(YEAR FROM AGE(a.active_month, c.cohort_month)) * 12
        + EXTRACT(MONTH FROM AGE(a.active_month, c.cohort_month)) AS month_num,
    COUNT(DISTINCT c.user_id) AS active_users
FROM cohort c
LEFT JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, a.active_month
ORDER BY c.cohort_month, a.active_month;

EXTRACT(YEAR FROM AGE(...)) * 12 + EXTRACT(MONTH FROM AGE(...)) — правильная формула для месяцев между датами (не голое EXTRACT(MONTH FROM AGE), которая возвращает 0-11).

Retention-кривые

Преобразование в сводную таблицу (pivot) для графика:

WITH cohort AS (
    SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month
    FROM users WHERE signup_date >= '2026-01-01'
),
events_monthly AS (
    SELECT user_id, DATE_TRUNC('month', event_date) AS active_month
    FROM events
    GROUP BY user_id, DATE_TRUNC('month', event_date)
),
joined AS (
    SELECT
        c.cohort_month,
        (EXTRACT(YEAR FROM AGE(e.active_month, c.cohort_month)) * 12
         + EXTRACT(MONTH FROM AGE(e.active_month, c.cohort_month)))::INT AS month_offset,
        c.user_id
    FROM cohort c
    LEFT JOIN events_monthly e ON e.user_id = c.user_id
)
SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size,
    COUNT(DISTINCT CASE WHEN month_offset = 1 THEN user_id END)::NUMERIC
        * 100 / NULLIF(COUNT(DISTINCT user_id), 0) AS retention_m1,
    COUNT(DISTINCT CASE WHEN month_offset = 3 THEN user_id END)::NUMERIC
        * 100 / NULLIF(COUNT(DISTINCT user_id), 0) AS retention_m3,
    COUNT(DISTINCT CASE WHEN month_offset = 6 THEN user_id END)::NUMERIC
        * 100 / NULLIF(COUNT(DISTINCT user_id), 0) AS retention_m6
FROM joined
GROUP BY cohort_month
ORDER BY cohort_month;

По сегментам

WITH cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signup_date) AS cohort_month,
        acquisition_channel
    FROM users
    WHERE signup_date >= '2026-01-01'
)
-- Дальше JOIN с activity и calculation как выше, но GROUP BY acquisition_channel
SELECT acquisition_channel, ...

Частые ошибки

Ошибка 1. EXTRACT(MONTH FROM AGE) без YEAR×12. Возвращает 0-11. Надо: YEAR*12 + MONTH.

Ошибка 2. INNER JOIN когорты и активности. Теряете когорты без активности — берите LEFT JOIN.

Ошибка 3. Слишком свежая когорта. У когорты февраля 2026 ещё нет 6-месячного retention — прошло меньше полугода. Фильтруйте по cohort_month <= CURRENT_DATE - INTERVAL '6 months'.

Ошибка 4. Двойной счёт. Если пользователь был активен 5 раз за месяц, COUNT(*) даст 5, а COUNT(DISTINCT user_id) — 1.

Ошибка 5. Сезонность каналов привлечения. Январская когорта из Instagram и мартовская из органики — это разные когорты, сравнивать их в лоб нечестно.

Связанные темы

• Как посчитать retention в SQL
• Как посчитать rolling retention в SQL
• Как посчитать D1/D7/D30 retention в SQL
• Как посчитать reactivation в SQL

FAQ

Недельные или месячные когорты?

Зависит от того, как часто пользователи взаимодействуют с продуктом. Для SaaS и мобильных приложений обычно берут недельные когорты — там жизнь пользователя измеряется днями. Для e-commerce и банкинга логичнее месячные, потому что там события реже.

Какой retention считается хорошим?

Зависит от типа продукта, сравнивать имеет смысл только внутри своей категории. Для мобильных приложений на первый месяц (M1) 25–35% — норма, а 40%+ — отлично. Для e-commerce на горизонте 6 месяцев ориентир 20–30%, для SaaS B2B на 12 месяцев — 70% и выше.

Что делать с retention-кривой, которая «выравнивается»?

Это нормально и даже хорошо. Пользователи, которые остались с продуктом через 12 месяцев, обычно уже не уходят — это ваше устойчивое ядро. Выход кривой на плато — здоровый признак, а не проблема.

Cohort retention падает — что делать?

Разложите метрику по каналам привлечения, платформам и сегментам. Найдите когорту с самым сильным падением и посмотрите, что общего у этих пользователей — часто причина в конкретном канале или в изменении онбординга.

Можно ли сравнивать когорты разной длины?

Напрямую — нельзя: у старых когорт было больше времени добрать retention. Сравнивайте когорты на одинаковом горизонте — retention на одном и том же N-м месяце жизни.