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

Зачем Rolling Retention

Команда смотрит классический D7 retention — 18%. Кажется плохим. Меняют onboarding, всё равно 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;

Логика: «был ли хоть один event на день 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 — оптимально для проверки «был ли хоть один event». 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. Считать первый event как retention

Иногда registration сама порождает event. Тогда 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» подчёркивает, что окно не ограничено сверху (любая активность после дня N).

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

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

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

Да, всегда. Любой день, где юзер активен после N, удовлетворяет rolling, но не classic.

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

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