Retention Day 1 (Retention D1)

Что такое Retention Day 1 (Retention D1)?

Доля новой когорты, вернувшейся на следующий день после регистрации.

Формула

Retention D1 = Returned D1 / Cohort Size

Как считать

Для каждой регистрации смотрим активность в окне [D1, D2).

Зачем нужна

Ранний индикатор качества первого опыта пользователя.

SQL-запрос

WITH cohort AS (
  SELECT user_id, created_at AS signup_ts, created_at::date AS signup_date
  FROM users
  WHERE created_at >= CURRENT_DATE - INTERVAL '45 day'
),
returned AS (
  SELECT DISTINCT c.user_id
  FROM cohort c
  JOIN events e ON e.user_id = c.user_id
  WHERE e.event_name IN ('app_open','session_start','feature_use')
    AND e.event_timestamp >= c.signup_ts + INTERVAL '1 day'
    AND e.event_timestamp < c.signup_ts + INTERVAL '2 day'
)
SELECT
  signup_date,
  COUNT(*) AS cohort_size,
  COUNT(*) FILTER (WHERE r.user_id IS NOT NULL) AS returned_d1,
  ROUND(COUNT(*) FILTER (WHERE r.user_id IS NOT NULL)::decimal / NULLIF(COUNT(*), 0), 3) AS retention_d1
FROM cohort c
LEFT JOIN returned r ON r.user_id = c.user_id
GROUP BY 1
ORDER BY 1 DESC;