Retention по когортам в SQL: знаменатель, дубли и периоды

Retention может измениться из-за неправильного знаменателя, даже если поведение пользователей осталось прежним. Для когортного расчёта сначала зафиксируйте группу людей, событие возврата и временное окно. Затем считайте долю вернувшихся относительно исходной когорты.

В этой статье определение конкретное: среди зарегистрировавшихся в январе ищем тех, у кого было хотя бы одно целевое событие в феврале. Это месячный календарный retention M1. Он отличается от возврата строго на 30-й день и от rolling retention.

Ручной расчёт до SQL

Есть четыре пользователя январской когорты. Первый совершил два события в феврале, второй — одно, третий не вернулся, четвёртый совершил одно событие. Вернулись три человека из четырёх: 75%.

Пользователь	Событий в феврале	Вклад в число вернувшихся
1	2	1
2	1	1
3	0	0
4	1	1

Если посчитать события вместо людей, получится 4/4 = 100%. Если взять знаменателем только тех, у кого нашлись события после JOIN, снова получится 100%. Обе цифры скрывают пользователя, который не вернулся.

Воспроизводимый запрос SQLite

Запрос содержит исходные данные в CTE. Даты в примере — строки формата YYYY-MM-DD с одинаковой календарной зоной; для timestamp в рабочей базе сначала согласуйте часовой пояс.

WITH users(user_id, signup_date) AS (
  VALUES (1,'2026-01-05'), (2,'2026-01-08'),
         (3,'2026-01-10'), (4,'2026-01-20')
), events(user_id, event_date) AS (
  VALUES (1,'2026-02-02'), (1,'2026-02-03'),
         (2,'2026-02-08'), (4,'2026-02-20')
), cohort AS (
  SELECT user_id FROM users
  WHERE signup_date >= '2026-01-01'
    AND signup_date < '2026-02-01'
), returned AS (
  SELECT DISTINCT e.user_id FROM events e
  JOIN cohort c ON c.user_id = e.user_id
  WHERE e.event_date >= '2026-02-01'
    AND e.event_date < '2026-03-01'
)
SELECT (SELECT COUNT(*) FROM cohort) AS cohort_size,
       (SELECT COUNT(*) FROM returned) AS returned_users,
       1.0 * (SELECT COUNT(*) FROM returned)
         / NULLIF((SELECT COUNT(*) FROM cohort),0) AS retention;

Результат: cohort_size = 4, returned_users = 3, retention = 0.75. DISTINCT в returned устраняет повторные события одного человека. Знаменатель берётся из исходной когорты независимо от событий. Множитель 1.0 обеспечивает дробное деление.

Что делать с неполным периодом

Если отчёт строится 10 февраля, январской когорте ещё доступен не весь месяц M1. Сравнение её 10 дней с полным февралем другой когорты будет некорректным. Для такого отчёта явно отметьте незавершённый период или применяйте одинаковые окна наблюдения.

Когда строите таблицу когорт, будущая ячейка не равна нулю. Ноль означает, что период завершён и возвратов нет. Отсутствие наблюдения означает, что число пока неизвестно. В интерфейсе эти состояния тоже должны отличаться.

Выберите событие и идентичность пользователя

Вход на сайт, решение задачи и оплата дают разные виды retention. Выберите событие под бизнес-вопрос и не меняйте его незаметно между периодами. Если один человек имеет несколько аккаунтов или идентификаторы объединяются, политика объединения повлияет на числитель и знаменатель.

Для пустой когорты доля не определена. В запросе NULLIF предотвращает деление на ноль и оставляет NULL. Это полезнее, чем показывать «0% удержания» для группы, в которой не было людей.

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

1. Начните с когорты из нескольких известных пользователей.
2. Добавьте повторное событие: число вернувшихся не должно вырасти.
3. Удалите все события одного участника: размер когорты остаётся прежним.
4. Добавьте событие вне окна: M1 не меняется.
5. Проверьте пустую когорту и ещё не завершённый период.

После технической проверки остаётся интерпретация. Снижение retention может сопровождать смену источников трафика, сезонность или изменение продукта. Один график не доказывает причину. Сначала исключите изменение определения метрики и качества данных, затем проверяйте бизнес-гипотезы.

Как перейти от разбора к практике

Если основы запросов уже знакомы, продвинутый SQL поможет практиковать многоэтапные расчёты, окна и аналитические задачи. Этот пример — диагностика: важно суметь изменить условия и объяснить результат самостоятельно. Материал подготовлен Latorn; ссылка ведёт на наш платный курс. Перед покупкой сравните программу и требования со своей задачей.

Посмотреть программу курса. Для самостоятельной диагностики доступна подборка упражнений.

Источники и проверка

Примеры в статье авторские и учебные. Технические справочники: SQLite: даты и время.