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

Зачем Rolling Retention

Команда смотрит классический D7 retention — 18%. Кажется плохим. Меняют онбординг, всё равно 18-20%. Менеджмент в панике.

Аналитик смотрит rolling D7 — 47%. Почти втрое больше. Объяснение: «не активен в день 7» ≠ «потерян». Пользователь мог вернуться на день 8, 12, 30 — он жив. Rolling считает живых, а не идеально дисциплинированных.

Rolling retention популярен в продуктах с непрерывным потреблением (онлайн-кинотеатры, банк, доставка), где «активность строго в день N» — неправильная метрика. В статье — SQL, отличие от classic и подводные камни.

Rolling vs Classic Retention

Classic (точечный)

D7 retention = доля пользователей когорты, активных именно в день 7 после регистрации.

Rolling

Rolling D7 retention = доля пользователей когорты, активных в любой день из [day 7, ∞) на момент расчёта.

В отчёте Mixpanel это часто называется «retention curves» — классический показывает «зубчатый» график, rolling — гладкий монотонно убывающий.

Когда что использовать:

• Classic: высокая частота продукта (соцсеть, мессенджер), важна дисциплина регулярного захода
• Rolling: средняя/низкая частота (банк, маркетплейс, доставка раз в неделю), важна жизнь пользователя в продукте

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

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

WITH cohort AS (
    SELECT
        user_id,
        signup_date::DATE AS signup_date
    FROM users
    WHERE signup_date >= '2026-04-01'
      AND signup_date <  '2026-04-08'
),
rolling_active AS (
    SELECT DISTINCT
        c.user_id,
        c.signup_date
    FROM cohort c
    JOIN events e ON e.user_id = c.user_id
    WHERE e.event_date >= c.signup_date + INTERVAL '7 days'
)
SELECT
    (SELECT COUNT(*) FROM cohort) AS cohort_size,
    COUNT(DISTINCT ra.user_id) AS rolling_d7_users,
    COUNT(DISTINCT ra.user_id) * 100.0
        / NULLIF((SELECT COUNT(*) FROM cohort), 0) AS rolling_d7_pct
FROM rolling_active ra;

Логика: «было ли хоть одно событие на день 7 или позже».

Важно: rolling retention имеет смысл только если у тебя данные за достаточный период после когорты. Если когорта 1 апреля, а сегодня 5 апреля — rolling D7 не вычислить.

Rolling Retention по когортам

WITH cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('week', signup_date)::DATE AS cohort_week
    FROM users
    WHERE signup_date >= '2026-01-01'
),
days AS (
    SELECT 1 AS d UNION ALL SELECT 7 UNION ALL SELECT 14
    UNION ALL SELECT 30 UNION ALL SELECT 60 UNION ALL SELECT 90
)
SELECT
    c.cohort_week,
    d.d AS rolling_day,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT CASE
        WHEN EXISTS (
            SELECT 1 FROM events e
            WHERE e.user_id = c.user_id
              AND e.event_date >= c.cohort_week + (d.d || ' days')::INTERVAL
        ) THEN c.user_id
    END) * 100.0
    / NULLIF(COUNT(DISTINCT c.user_id), 0) AS rolling_retention_pct
FROM cohort c
CROSS JOIN days d
WHERE c.cohort_week + (d.d || ' days')::INTERVAL <= CURRENT_DATE
GROUP BY c.cohort_week, d.d
ORDER BY c.cohort_week, d.d;

EXISTS — оптимально для проверки «было ли хоть одно событие». COUNT(DISTINCT) с CASE — стандартная техника для условных подсчётов.

По сегментам

«У какой группы пользователей retention выше»:

WITH cohort AS (
    SELECT u.user_id, u.signup_date::DATE AS signup_date, u.acquisition_channel
    FROM users u
    WHERE u.signup_date >= '2026-03-01' AND u.signup_date < '2026-04-01'
)
SELECT
    c.acquisition_channel,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT CASE
        WHEN EXISTS (
            SELECT 1 FROM events e
            WHERE e.user_id = c.user_id
              AND e.event_date >= c.signup_date + INTERVAL '7 days'
        ) THEN c.user_id
    END) * 100.0
    / NULLIF(COUNT(DISTINCT c.user_id), 0) AS rolling_d7_pct
FROM cohort c
GROUP BY c.acquisition_channel
ORDER BY rolling_d7_pct DESC;

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

Ошибка 1. Сравнивать rolling и classic

Это разные метрики. Rolling всегда выше classic (любая активность считается). Не пишите «D7 retention» без уточнения формулы.

Ошибка 2. Брать слишком свежую когорту

Rolling D90 на когорту, которой 30 дней — некорректно. Нужно ждать 90+ дней с момента регистрации.

Ошибка 3. Считать первое событие как retention

Иногда регистрация сама порождает событие. Тогда rolling D1 = 100%. Фильтруйте WHERE event_date > signup_date (строго больше).

Ошибка 4. EXISTS vs DISTINCT count

EXISTS отрабатывает быстрее на больших таблицах (останавливается на первом совпадении). DISTINCT COUNT прогоняет весь подзапрос.

Ошибка 5. Не учитывать свежесть данных

Если данные за вчера ещё не загружены — rolling за последний день будет занижен. Добавьте WHERE event_date <= CURRENT_DATE - INTERVAL '1 day' если ETL отстаёт.

Ошибка 6. Невалидные даты в когорте

Если у части пользователей signup_date = NULL или дата в будущем (баг ETL) — они попадут в когорту и испортят расчёт. Фильтр WHERE signup_date IS NOT NULL AND signup_date <= CURRENT_DATE обязателен.

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

• Как посчитать D1/D7/D30 retention в SQL
• Что такое retention
• Cohort analysis простыми словами
• Кейс: retention упал

FAQ

Rolling или classic — что показывать менеджменту?

Зависит от продукта и его частоты. Соцсети и мессенджеры честнее смотреть по classic — там важна регулярность захода. Для банка, маркетплейса или доставки ближе к правде rolling. В идеале показывайте обе метрики рядом и явно подписывайте, где какая, чтобы никто не сравнивал их вслепую.

Rolling N+ retention — это то же, что unbounded retention?

Да, это одно и то же: «unbounded retention» — просто другое название для rolling. Слово подчёркивает, что окно не ограничено сверху — засчитывается любая активность в день N или позже, без верхней границы.

Можно ли посчитать rolling между датами?

Да, окно можно ограничить с обеих сторон — например, считать активность в диапазоне [D7, D30]. Это компромисс между classic и rolling: окно шире одной точки, но не уходит в бесконечность. Такой вариант удобен для сегментации по «жизненной фазе» пользователя.

Rolling всегда выше classic?

Да, всегда. Rolling засчитывает любую активность в день N или позже, а classic — только строго в день N. Поэтому любой пользователь, попавший в classic, автоматически попадает и в rolling, но не наоборот — отсюда и разрыв между метриками.

Что лучше для A/B-теста retention?

Classic обычно точнее показывает разницу между вариантами. Rolling сглаживает шум, но заодно приглушает и сам сигнал. Если тестируете онбординг и ранние шаги — берите classic; если ловите долгосрочный эффект на удержание — rolling.