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

Прямой ответ

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

WITH first_event AS (
  SELECT user_id, MIN(event_date) AS cohort_date
  FROM events
  GROUP BY user_id
)
SELECT
  DATE_TRUNC('month', f.cohort_date) AS cohort,
  DATE_TRUNC('month', e.event_date) - DATE_TRUNC('month', f.cohort_date) AS period,
  COUNT(DISTINCT e.user_id) AS active_users
FROM events e
JOIN first_event f ON f.user_id = e.user_id
GROUP BY 1, 2;

Когорта определяется по первому событию пользователя, поэтому все пользователи группы начали в один период и сравнение между периодами корректно. Об удержании — в статье про retention-анализ.

Выделение когорт по первому событию

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

Первое событие. Минимальная дата события пользователя определяет когорту, поэтому пользователь попадает в одну группу.

Признак когорты. Период первого события приводится к месяцу либо неделе, поэтому размер когорты достаточен для сравнения.

Неизменность когорты. Пользователь остаётся в когорте первого события, поэтому изменение поведения не переносит его в другую группу.

Практическое следствие: когорта, определённая по последнему событию, меняется со временем, поэтому сравнение периодов теряет смысл.

Отдельный аспект — размер когорты. Когорта малого размера даёт долю удержания с большим разбросом, поэтому изменение в малой когорте не подтверждает вывод о продукте.

Расчёт удержания по периодам

Удержание считается как доля пользователей когорты, активных в периоде, поэтому значение выражается в процентах от размера когорты.

Период наблюдения. Разница между периодом события и периодом когорты даёт номер периода, поэтому первый период соответствует нулю.

Доля удержания. Число активных пользователей периода делится на размер когорты, поэтому значение выражается в долях.

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

Практическое следствие: удержание, посчитанное от числа активных в периоде вместо размера когорты, показывает долю среди активных, а не среди когорты.

Матрица когорт и чтение результата

Матрица когорт показывает когорты по строкам и периоды по столбцам, поэтому изменение удержания во времени видно по диагонали.

Строки матрицы. Каждая строка соответствует когорте, поэтому размер и удержание когорты видны в одной строке.

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

Сравнение когорт. Когорты, начатые после изменения продукта, сравниваются с когортами до изменения, поэтому эффект изменения виден.

Практическое следствие: матрица без размера когорты не позволяет отличить изменение удержания от изменения размера группы.

О оконных функциях для ранжирования событий — в статье про оконные функции SQL; о расчёте на ClickHouse — в статье про ClickHouse для аналитики.

Практика: расчёт когортного анализа

1. Определяйте когорту по первому событию. Принадлежность к когорте не меняется, поэтому сравнение периодов корректно.

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

3. Считайте размер когорты по первому событию. Знаменатель удержания не зависит от активности в периоде.

4. Нумеруйте периоды от нуля. Период когорты соответствует нулю, поэтому первый период наблюдения — единица.

5. Делите активных на размер когорты. Удержание выражается в долях от когорты, а не от активных.

6. Показывайте размер когорты рядом с удержанием. Малая когорта даёт разброс, поэтому вывод требует проверки размера.

7. Сравнивайте когорты до и после изменения. Эффект изменения продукта виден по различию удержания.

Рекомендации:

1. Не определяйте когорту по последнему событию. Принадлежность к группе меняется со временем, поэтому сравнение теряет смысл.
2. Учитывайте размер когорты при выводе. Малая когорта даёт большой разброс удержания.
3. Сопоставляйте период когорты с периодом продукта. Месячная когорта сглаживает недельные колебания.
4. Фиксируйте размер когорты в отчёте. Доля без знаменателя не позволяет оценить достоверность.
5. Пересматриваем признак когорты при изменении продукта. Новое событие регистрации меняет определение первого события.

Практический сценарий: удержание считалось от активных

Контекст: отчёт показывал стабильное удержание, но число пользователей когорт сокращалось, поэтому общая картина расходилась с ожиданиями.

1. Диагностика. Проверка показала, что знаменателем удержания выступало число активных пользователей в периоде, а не размер когорты. Доля оставалась высокой, поскольку сокращение числителя и знаменателя происходило одновременно.
2. Причина. Удержание, посчитанное от активных в периоде, показывает долю среди оставшихся, поэтому сокращение когорты не отражалось в метрике.
3. Действия. (а) Заменили знаменатель на размер когорты, посчитанный по первому событию; (б) добавили столбец с размером когорты в отчёт, чтобы доля читалась вместе со знаменателем; (в) пересчитали исторические периоды и сравнили с прежним отчётом; (г) проверили, что период когорты нумеруется от нуля, чтобы первый период не смещал ряд; (д) настроили ежемесячный пересчёт, чтобы когорты обновлялись регулярно.
4. Результат. Удержание отражает сокращение когорт, поэтому отчёт соответствует фактическому изменению продукта.

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

FAQ

Как определить когорту пользователя? По периоду первого события пользователя. Принадлежность к когорте не меняется со временем, поэтому сравнение периодов корректно.

Что считается знаменателем удержания? Размер когорты, посчитанный по первому событию. Число активных в периоде даёт долю среди оставшихся, а не среди когорты.

Почему важна размер когорты в отчёте? Когорта малого размера даёт большой разброс удержания, поэтому вывод без знаменателя недостоверен.

Как сравнить эффект изменения продукта? Сопоставлением когорт, начатых после изменения, с когортами до изменения. Различие удержания показывает эффект.

Источники: PostgreSQL date/time functions, ClickHouse date-time functions, PostgreSQL table expressions — ссылки в шапке.