Когортный анализ удержания в SQL: рабочая формула и защита от пересчётов

10 апреля 2026 г. 15K

Когортный retention — одна из тех метрик, которую все считают по-разному, и каждый раз получается «свой» график. Если ты приходишь в компанию и видишь дашборду «retention D7 = 42%», первый вопрос — что такое «активность», какая дата первого визита и считаем ли мы только новых пользователей за период. Если на эти три вопроса нет однозначного ответа — цифра ничего не значит.

Разберу формулу, которую использую в e-commerce и SaaS уже несколько лет. Она работает на Postgres и ClickHouse с минимальными правками, не ломается при пересчёте задним числом и не зависит от того, кто и когда переименует поле events.event_type.

Что такое когорта и зачем фиксировать определение

Когорта — группа пользователей, объединённых общим событием в общий период. Самый частый вариант — когорта по дате первого визита (или регистрации, или первой покупки). Дальше для каждой когорты считаешь, какая доля вернулась в день N после события когорты.

Главная ошибка — не зафиксировать три вещи перед началом расчёта:

Что считается событием когорты (регистрация? первый платёж? первое полезное действие?).
Что считается «возвратом» — любой login или конкретное действие.
Окно агрегации — день, неделя, месяц.

Если эти три определения живут в разных дашбордах в разных вариантах — у тебя три разных retention, и спорить о них бесполезно.

Базовая SQL-формула для Postgres

Допустим, есть таблица events со столбцами user_id, event_type, created_at. Считаем weekly retention с когортой по неделе первой регистрации, активность — любое событие.

WITH first_seen AS (
    SELECT
        user_id,
        date_trunc('week', MIN(created_at))::date AS cohort_week
    FROM events
    GROUP BY user_id
),
activity AS (
    SELECT
        e.user_id,
        date_trunc('week', e.created_at)::date AS activity_week
    FROM events e
    GROUP BY 1, 2
),
joined AS (
    SELECT
        f.cohort_week,
        f.user_id,
        ((a.activity_week - f.cohort_week) / 7)::int AS week_number
    FROM first_seen f
    JOIN activity a ON a.user_id = f.user_id
    WHERE a.activity_week >= f.cohort_week
)
SELECT
    cohort_week,
    week_number,
    COUNT(DISTINCT user_id) AS users,
    COUNT(DISTINCT user_id)::numeric
        / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
            PARTITION BY cohort_week ORDER BY week_number
          ) AS retention
FROM joined
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;

Что здесь важно. first_seen — определение когорты, ровно одно место. activity — определение возврата, ровно одно место. Если завтра продакт скажет «считаем активностью только покупки» — правишь один CTE, не пятнадцать дашбордов.

Версия для ClickHouse

В ClickHouse date_trunc работает, но обычно используют toStartOfWeek. И uniqExact вместо COUNT(DISTINCT) — значительно быстрее на больших объёмах.

WITH first_seen AS (
    SELECT
        user_id,
        toStartOfWeek(min(created_at)) AS cohort_week
    FROM events
    GROUP BY user_id
),
joined AS (
    SELECT
        f.cohort_week AS cohort_week,
        f.user_id AS user_id,
        toUInt32((toStartOfWeek(e.created_at) - f.cohort_week) / 7) AS week_number
    FROM events e
    INNER JOIN first_seen f USING (user_id)
)
SELECT
    cohort_week,
    week_number,
    uniqExact(user_id) AS users,
    uniqExact(user_id)
        / first_value(uniqExact(user_id)) OVER (
            PARTITION BY cohort_week ORDER BY week_number
          ) AS retention
FROM joined
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;

На 200 млн событий и 5 млн пользователей такой запрос на ClickHouse у меня отрабатывает за 3–6 секунд при условии, что таблица отсортирована по (user_id, created_at) и есть индекс по дате. На Postgres та же выборка без агрессивных индексов — 40+ секунд.

Защита от пересчётов задним числом

Самая болезненная история — когда retention внезапно «улучшился» на старых когортах. Причины обычно две.

1. Неустойчивое определение когорты

Если cohort_week считается через MIN(created_at) по всей таблице, и кто-то залил исторические данные за прошлый год — у пользователя минимум сместится назад, и его когорта поедет. Лечится материализацией:

CREATE TABLE user_cohort (
    user_id BIGINT PRIMARY KEY,
    cohort_week DATE NOT NULL,
    fixed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO user_cohort (user_id, cohort_week)
SELECT user_id, date_trunc('week', MIN(created_at))::date
FROM events
GROUP BY user_id
ON CONFLICT (user_id) DO NOTHING;

Запись делаешь один раз, потом джойнишь по user_cohort. Импорт исторических данных не сдвинет когорту.

2. Дубликаты событий

Если ETL внезапно стал писать события дважды (привет, перезапуск consumer'а без идемпотентности), uniqExact по user_id спасёт от двойного счёта пользователей, но сами счётчики действий поедут. Поэтому я всегда держу в события event_id с уникальным ключом и считаю активность через uniqExact(event_id), а пользователя — через uniqExact(user_id).

3. Часовые пояса

Самая коварная штука. Если события приходят в UTC, а аналитики живут в Москве, то date_trunc('week', created_at) отнесёт событие воскресенье 02:00 МСК в субботу. Когорта по локальному времени будет другой. Решение — договориться об одном часовом поясе во всех дашбордах и явно конвертировать:

date_trunc('week', created_at AT TIME ZONE 'Europe/Moscow')::date

Если этот AT TIME ZONE где-то забыли, числа уедут на полпроцента-процент, и долго будешь ловить, почему «почти то же самое» на двух дашбордах.

Как читать когортную таблицу

Классический вид — треугольник, где строки это когорты, а столбцы — недели после когорты. Что я смотрю в первую очередь.

Падение между W0 и W1. Если осталось меньше 30% — продукт не показывает ценность за первую неделю. Это onboarding, не retention.
Стабилизация. На какой неделе кривая выходит на плато. Если retention продолжает падать на W12, у продукта нет «ядра» возвратных пользователей.
Сравнение когорт. Если когорта декабря показывает D7 = 38%, а январь = 31% — что изменилось в продукте между декабрём и январём.

Если все когорты ведут себя одинаково — это нормально. Если резко различаются — ищи изменения в продукте, маркетинге или, чаще всего, в самом запросе.

Типичные ошибки в дашбордах

Что я чаще всего вижу в чужих когортах.

Retention W0 не равен 100%. Бывает, если когорта определена по событию А, а активность считается по событию Б, и не у всех пользователей есть событие Б в день регистрации. Технически корректно, но визуально вводит в заблуждение. Лучше явно подписать: «cohort = first_visit, activity = purchase».
Считают retention от MAU. То есть берут всех активных в месяце N и смотрят, сколько активны в N+1. Это не когорта, это user retention, и он всегда выше когортного. Смешивать их в одной дашборде нельзя.
Берут средний retention по всем когортам. Усреднение когорт скрывает тренды. Лучше показывать топ-5 последних когорт отдельными линиями.
Не учитывают сезонность. У e-commerce когорта ноября всегда лучше когорты января, потому что ноябрь — это распродажи. Сравнивать их «в лоб» некорректно. Сравнивай ноябрь с ноябрём.

Что сохранить себе

Когортный retention — это два определения (когорта и активность) и одна материализованная таблица для устойчивости. Если они зафиксированы в семантическом слое — DBT, LookML, dbt-metrics или просто SQL-вьюхой с комментарием — споры о цифрах исчезают.

Дальше копать стоит в две стороны: bucketing активности (purchase, login, key_action) и расчёт retention по доходу (revenue retention, GRR/NRR), который для SaaS важнее пользовательского. Оба считаются по тому же принципу: фиксированная когорта, фиксированное определение метрики, материализация когорт. Меняются только числители и знаменатели.