Как не занизить D30 retention: незрелые когорты и расчёт на SQL

Представим: в отчёт добавили новые регистрации, и общий D30 retention снизился с 20% до 10%. При этом ни один из пользователей, для которых D30 уже наступил, не изменил своё поведение. Ошибка может оказаться в знаменателе: туда попали люди, чей тридцатый день ещё впереди.

Разберём эту ситуацию на условных данных и воспроизведём расчёт в SQLite. Отдельно проверим три случая: пользователь вернулся, пользователь не вернулся и результат пока нельзя измерить.

Что именно будем считать

Здесь D30 retention — доля пользователей, совершивших выбранное действие именно на тридцатый календарный день после регистрации. День регистрации обозначим D0. Возвратным событием выберем task_completed — завершение задачи.

Например, для регистрации 1 сентября D30 приходится на 1 октября. В зачёт идут события за весь этот календарный день. Завершение задачи 30 сентября или 2 октября в D30 не попадёт.

Это важно зафиксировать до расчёта. Возвращение именно на D30, хотя бы одно возвращение за первые 30 дней и возвращение на D30 или позже отвечают на разные вопросы.

В примере используются дневные когорты по первой регистрации: каждый пользователь входит ровно в одну когорту. Все даты заранее приведены к одному часовому поясу. Используются календарные дни, а не интервалы по 24 часа от точного момента регистрации.

Откуда берётся ошибочное падение

Срез сделан после окончания 27 октября 2026 года. Считаем, что все события по эту дату включительно уже загружены.

Если октябрьской когорте присвоить 0% и включить всех её участников в знаменатель, получится:

20 вернувшихся / 200 зарегистрированных = 10%

Но у 100 октябрьских пользователей ещё не было возможности совершить действие в нужный день. Отсутствие события сейчас ничего не говорит об их будущем D30.

По доступной для измерения части выборки расчёт другой:

20 вернувшихся / 100 пользователей с завершённым D30 = 20%

Эти 20% описывают зрелую часть выборки. Распространять результат на октябрьскую когорту нельзя: её D30 пока неизвестен. Само исключение незрелых пользователей исправляет расчёт, но не доказывает, что качество новых регистраций осталось прежним.

Подготовим данные

Следующий пример можно выполнить в пустой базе SQLite. В users одна строка на пользователя, в events — одна строка на событие. Даты хранятся как корректные строки YYYY-MM-DD, без времени.

CREATE TABLE users (  user_id INTEGER PRIMARY KEY,  registered_on TEXT NOT NULL
);
CREATE TABLE events (  event_id INTEGER PRIMARY KEY,  user_id INTEGER NOT NULL,  event_on TEXT NOT NULL,  event_name TEXT NOT NULL
);
WITH RECURSIVE numbers(n) AS (  VALUES (1)  UNION ALL  SELECT n + 1 FROM numbers WHERE n < 100
)
INSERT INTO users (user_id, registered_on)
SELECT n, '2026-09-01' FROM numbers
UNION ALL
SELECT n + 100, '2026-10-10' FROM numbers;
INSERT INTO events (user_id, event_on, event_name)
SELECT user_id, '2026-10-01', 'task_completed'
FROM users
WHERE user_id <= 20;

Получились две когорты по 100 человек. У первых 20 пользователей есть возвратное событие на D30. У октябрьской когорты таких событий пока нет, поскольку 9 ноября ещё не наступило.

Сохраним три состояния до агрегации

Для каждого пользователя заведём признак returned_d30:

• 1 — нужный день завершён, данные загружены, возврат был;

• 0 — нужный день завершён, данные загружены, возврата не было;

• NULL — результат ещё нельзя измерить.

В запросе complete_through — последняя дата, за которую события считаются полностью загруженными. Это явно заданное условие учебного примера. В рабочем отчёте такую границу должен подтверждать процесс загрузки; максимальная дата найденного события сама по себе не доказывает полноту данных.

WITH params AS (  SELECT '2026-10-27' AS complete_through
), per_user AS (  SELECT    u.user_id,    u.registered_on,    CASE      WHEN date(u.registered_on, '+30 days')           <= p.complete_through      THEN EXISTS (        SELECT 1        FROM events e        WHERE e.user_id = u.user_id          AND e.event_on = date(u.registered_on, '+30 days')          AND e.event_name = 'task_completed'      )      ELSE NULL    END AS returned_d30  FROM users u  CROSS JOIN params p
)
SELECT  registered_on,  COUNT(*) AS cohort_users,  COUNT(returned_d30) AS measured_users,  SUM(returned_d30) AS returned_users,  ROUND(    100.0 * SUM(returned_d30)    / NULLIF(COUNT(returned_d30), 0),    1  ) AS d30_retention_pct
FROM per_user
GROUP BY registered_on
ORDER BY registered_on;

Результат:

COUNT(*) показывает полный размер когорты. COUNT(returned_d30) считает значения, отличные от NULL, включая 0 и 1: пользователей, для которых измерение уже возможно. SUM(returned_d30) даёт число вернувшихся. Если вся группа состоит из NULL, в SQLite сумма тоже будет NULL.

Проверка через EXISTS нужна для подсчёта людей. Если один пользователь завершил десять задач в нужный день, его признак всё равно равен единице. Количество событий не увеличивает retention.

Число 100.0 обеспечивает дробное деление. NULLIF явно оставляет процент неопределённым при нулевом размере измеримой выборки.

Заменять NULL на ноль через COALESCE здесь рано: такая замена смешает незавершённое наблюдение с подтверждённым отсутствием возврата. В интерфейсе вместо NULL можно показывать «D30 ещё не наступил» или «ожидаются данные», если причина известна.

Как проверить расчёт

Удобно начать с повторного события. Добавим ещё одно завершение задачи для первого пользователя:

INSERT INTO events (user_id, event_on, event_name)
VALUES (1, '2026-10-01', 'task_completed');

После повторного выполнения запроса сентябрьский результат должен остаться равным 20%. Если он вырос, в числитель, вероятно, попали события вместо уникальных пользователей.

Вторая проверка — настоящее отсутствие возвратов. Если удалить все события из учебной таблицы и повторить расчёт на прежнюю дату среза, у сентябрьской когорты должно получиться 0%, а у октябрьской — по-прежнему NULL. Именно это различие исходный ошибочный отчёт терял.

Третья проверка — граница D30. События пользователя из сентябрьской когорты за 30 сентября и 2 октября не должны влиять на его результат за 1 октября. Так можно обнаружить случайную подмену точного D30 более широким окном.

Наконец, стоит проверить дату среза. Когда 9 ноября полностью закончится и события за этот день будут загружены, октябрьская когорта станет измеримой. Если возвратов действительно не окажется, её результат будет равен 0%. До этого момента делать такой вывод нельзя.

Что показывать в итоговом отчёте

Для общего D30 по нескольким непересекающимся когортам нужно сложить числа вернувшихся и разделить на суммарный размер измеримой выборки. Простое среднее процентов даст тот же ответ только в частных случаях, например при одинаковых размерах измеримых когорт.

Рядом с процентом стоит оставить числитель, знаменатель и дату полноты данных. Тогда «20%» превращаются в понятное утверждение: «20 из 100 пользователей, для которых D30 полностью наблюдаем; события загружены по 27 октября».

Отдельно полезно указать, сколько пользователей ещё ожидают измерения. В нашем примере зарегистрировано 200 человек, измерить D30 можно для 100, ещё 100 ждут нужной даты. Это помогает заметить, когда общий показатель описывает лишь небольшую часть новых регистраций.

Для более раннего сравнения когорт можно использовать D7 после завершения соответствующего дня и загрузки событий. Это самостоятельный показатель: он даёт раннюю информацию о поведении пользователей, но не заменяет будущий D30.

В документации Amplitude неполные интервалы тоже отмечаются отдельно и исключаются из общего расчёта до завершения окна. При сравнении с готовым аналитическим сервисом нужно проверить настройки: календарные дни и интервалы по 24 часа могут давать разные результаты.

Ошибка с незрелой когортой возникает ещё до деления: неизвестный результат превращают в отсутствие возврата. Если сохранить это различие на уровне пользователя и явно задать границу полноты данных, календарь перестанет создавать искусственное падение D30.