Как посчитать Day 1 retention для конкретной даты регистрации (SQL)?

Короткий ответ

Day 1 retention — доля пользователей, зарегистрировавшихся в день D, которые совершили целевое действие ровно на следующий календарный день D+1.

Как это работает подробнее

берём когорту пользователей по дате первой регистрации, затем проверяем, есть ли у каждого из них событие активности с датой ровно D+1, и делим число "вернувшихся" на размер всей когорты D.

Пример кода

product_metrics.sqlКопировать

-- PostgreSQL
WITH cohort AS (
  SELECT user_id, DATE '2024-03-10' AS reg_date
  FROM users
  WHERE registration_date = DATE '2024-03-10'
)
SELECT
  COUNT(DISTINCT c.user_id) AS cohort_size,
  COUNT(DISTINCT e.user_id) AS retained_d1,
  ROUND(COUNT(DISTINCT e.user_id)::numeric / COUNT(DISTINCT c.user_id), 3) AS d1_retention
FROM cohort c
LEFT JOIN events e
  ON e.user_id = c.user_id AND e.event_date = c.reg_date + 1;

Что использовать на практике

• Применение. нужно явно выбрать таймзону, в которой считаются календарные дни (пользователя или сервера) — расхождение в несколько часов меняет, в какой день попадёт событие; также важно договориться, что считается "целевым действием".
• Проверка или пример. Проверка: d1_retention должен быть в [0;1]; sanity check — сравнить cohort_size с числом регистраций за день в отдельном запросе.

Подводные камни

• Частая ошибка. путают "через 24–48 часов от момента регистрации" (скользящее окно) с "календарным днём D+1" — это разные подходы и дают разные числа; не исключают из когорты пользователей, зарегистрированных слишком поздно для расчёта D+1 (ещё не наступил D+1).