Когорты и retention в SQL: как считать возвращаемость по датам

Коротко

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

• Дата старта и событие возврата должны быть определены отдельно.
• Размер когорты — число уникальных пользователей, а не событий.
• D1 означает активность на первый день после старта, если так договорилась команда.
• Незрелые когорты нельзя сравнивать с когортиками, у которых уже прошли 30 дней.
• Сравнивай retention по каналам только при одинаковом определении событий и окна.

Как определить когорту и возврат

Для простого примера дата когорты — `users.signup_date`, а возвращение — событие `core_action` в таблице `events`. В реальном продукте стартом может быть первая активация, первая сессия или первый заказ. Если взять регистрацию для одного продукта и первую ценность для другого, retention станет несопоставимым.

Сначала получи активные дни пользователя

Таблица events содержит много строк на одного пользователя. Перед расчётом retention уберём повторы: для каждой пары “пользователь × день после старта” оставим одну строку. Это важно, потому что пять core actions в один день не должны превратить одного вернувшегося пользователя в пять.

with active_days as (
  select distinct
    u.user_id,
    u.signup_date as cohort_date,
    e.event_date,
    e.event_date - u.signup_date as days_since_signup
  from users as u
  join events as e using (user_id)
  where e.event_name = 'core_action'
    and e.event_date >= u.signup_date
)
select *
from active_days
order by cohort_date, user_id, event_date;

D1 и D7 по когортам

Теперь можно посчитать размер каждой когорты и число пользователей, активных на первом и седьмом дне. В запросе `COUNT(DISTINCT user_id)` защищает от повторных событий, а деление на размер когорты превращает количество в retention rate.

В `cohort_users` одна строка означает одного пользователя, поэтому `max(case...)` превращает любое подходящее событие в одну отметку. LEFT JOIN сохраняет пользователей, которые не вернулись: без них знаменатель когорты стал бы слишком маленьким.

with cohort_users as (
  select
    u.user_id,
    u.signup_date as cohort_date,
    u.channel,
    max(
      case when e.event_date - u.signup_date = 1
        then 1 else 0 end
    ) as retained_d1,
    max(
      case when e.event_date - u.signup_date = 7
        then 1 else 0 end
    ) as retained_d7
  from users as u
  left join events as e
    on e.user_id = u.user_id
   and e.event_name = 'core_action'
   and e.event_date >= u.signup_date
  group by u.user_id, u.signup_date, u.channel
),
cohort_summary as (
  select
    cohort_date,
    count(*) as cohort_size,
    sum(retained_d1) as d1_users,
    sum(retained_d7) as d7_users
  from cohort_users
  group by cohort_date
)
select
  cohort_date,
  cohort_size,
  round(d1_users::numeric / nullif(cohort_size, 0), 3) as d1_retention,
  round(d7_users::numeric / nullif(cohort_size, 0), 3) as d7_retention
from cohort_summary
order by cohort_date;

Retention-матрица: когорта × возраст

Для heatmap обычно нужны не только D1 и D7, а значения по нескольким возрастам: D0, D1, D3, D7, D14 и D30. В строках лежит когорта, в колонках — возраст пользователя. Цвет помогает быстро заметить когорту, которая стала возвращаться хуже после релиза или смены канала.

Сравни retention по каналам аккуратно

Канал с высоким D7 не обязательно лучший, если он приводит очень мало пользователей или дорогой трафик. Но разрез по каналам помогает увидеть качество привлечения: где люди не только регистрируются, но и возвращаются к core action.

В отчёте показывай и размер когорты, и число вернувшихся. Канал с D7 35% на 20 пользователях и канал с D7 22% на 2 000 пользователях требуют разных решений и разного уровня уверенности.

with user_retention as (
  select
    u.user_id,
    u.channel,
    max(
      case when e.event_date - u.signup_date = 7
        then 1 else 0 end
    ) as retained_d7
  from users as u
  left join events as e
    on e.user_id = u.user_id
   and e.event_name = 'core_action'
   and e.event_date >= u.signup_date
  group by u.user_id, u.channel
)
select
  channel,
  count(*) as cohort_size,
  sum(retained_d7) as d7_users,
  round(sum(retained_d7)::numeric / nullif(count(*), 0), 3) as d7_retention
from user_retention
group by channel
order by d7_retention desc;

Частые ошибки в retention

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

• Считать события вместо уникальных пользователей.
• Включить активность до даты регистрации из-за неверного JOIN.
• Считать D7 для свежих когорт как ноль вместо незрелого значения.
• Использовать регистрацию как старт для продукта, где ценность появляется позже.
• Смешать календарный D1 и rolling 24 hours в одном отчёте.
• Сравнивать каналы без размера когорты и стоимости привлечения.
• Не исключить тестовые аккаунты, внутренние команды и технические события.

Мини-практика и следующий шаг

Начни с расчёта D1 для одной когорты, затем добавь D7 и разрез channel. После этого собери матрицу по возрасту пользователя и пометь незрелые ячейки как NULL. Сравни получившийся retention с общей активностью и воронкой: высокий первый возврат не гарантирует, что пользователь дошёл до ценности.