Retention анализ в SQL: расчёт удержания пользователей

Прямой ответ

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

WITH first_visit AS (
  SELECT user_id, MIN(event_date) AS d0
  FROM events
  GROUP BY user_id
)
SELECT
  f.d0,
  e.event_date - f.d0 AS day_n,
  COUNT(DISTINCT e.user_id) AS returned,
  COUNT(DISTINCT f.user_id) AS cohort_size
FROM first_visit f
LEFT JOIN events e ON e.user_id = f.user_id
GROUP BY f.d0, e.event_date - f.d0;

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

Когорта и период возврата

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

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

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

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

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

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

Расчёт удержания и кривая

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

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

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

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

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

Сравнение когорт и проверка показателя

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

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

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

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

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

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

Практика: расчёт retention

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

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

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

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

5. Стройте кривую удержания по периодам. Изменение доли вдоль периодов показывает скорость сокращения активности.

6. Исключайте неполные периоды наблюдения. Когорта без данных по дальним периодам занижает удержание.

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

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

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

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

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

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

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

FAQ

Что показывает retention? Долю пользователей когорты, вернувшихся в продукт через период после первого визита. Значение считается от размера когорты.

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

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

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

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