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

Зачем Cohort Retention

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

Что такое Cohort

Cohort — группа пользователей с общей точкой входа (например, дата регистрации). Cohort retention — доля cohort, активная через 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 curves

Преобразование в 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 cohort + activity. Теряете когорты без активности. LEFT JOIN.

Ошибка 3. Слишком свежая cohort. Cohort февраля 2026 не имеет 6-month retention (ещё не прошло 6 месяцев). Фильтруйте по cohort_month <= CURRENT_DATE - INTERVAL '6 months'.

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

Ошибка 5. Сезонность acquisition channel. Январская cohort из Instagram vs мартовская из organic — разные cohorts, нечестное сравнение.

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

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

FAQ

Week- или month-cohort?

Зависит от частоты продукта. SaaS / mobile — weekly. E-com / banking — monthly.

Какой retention хороший?

Mobile app M1: 25-35% — норма, 40%+ — отлично. E-com 6mo: 20-30%. SaaS B2B 12mo: 70%+.

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

Это нормально. Юзеры, оставшиеся через 12 месяцев, обычно не уходят. Plateau — здоровый knaak.

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

Декомпозиция по acquisition channel, по platform, по segment. Найдите cohort с самым сильным падением.

Сравнение cohorts разной длины?

Нечестно. Используйте same-N-month retention.