Когорты и 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 в одном отчёте.
• Сравнивать каналы без размера когорты и стоимости привлечения.
• Не исключить тестовые аккаунты, внутренние команды и технические события.

Зрелость когорты — обязательное поле

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

Добавь в результат cohort_date, cohort_size, age_days, returned_users, retention и is_mature. Тогда heatmap можно фильтровать по зрелости, а читатель понимает, почему часть ячеек пустая. Это также помогает сравнить отчёт с продуктовой аналитикой, где когорты часто определяются иначе.

Когорту лучше хранить на уровне пользователя, а не сразу сворачивать в проценты. Пользовательская таблица позволяет повторно посчитать D1, D7, D30, изменить return event и проверить сегменты без повторного чтения сырых событий.

Retention и reactivation — не одно и то же

Если пользователь вернулся через 60 дней после старта, это важный сигнал, но он не должен автоматически попадать в D7. Для retention нужно заранее определить окно и событие возврата. Отдельно можно считать reactivation: долю пользователей, которые были неактивны заданное число дней и снова совершили core action.

Разные определения отвечают на разные вопросы. D7 показывает раннее закрепление привычки, D30 — более длинное удержание, reactivation — возможность вернуть ушедшую аудиторию. Не смешивай их в одну кривую и не называй любой повторный event retention.

Для команды полезно показывать рядом cohort retention и календарную активность. Если D7 стабилен, но общий DAU падает, проблема может быть в привлечении новых пользователей. Если новые когорты хуже старых, ищи изменения онбординга, каналов или продукта.

Три способа считать retention, которые дают разные числа

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

N-day retention — самый строгий: пользователь засчитывается, только если был активен именно в день N. Он хорошо ловит привычку, но занижает результат у продуктов, которыми пользуются нерегулярно: человек зашёл на шестой и восьмой день, а в D7 не попал.

Unbounded, он же rolling retention, засчитывает пользователя, если тот был активен в день N или позже. Число получается выше и лучше подходит там, где цикл использования редкий — например, продукту, которым пользуются раз в месяц. Обратная сторона: метрика меняется задним числом, потому что вчерашний «не вернувшийся» может вернуться завтра.

Bracket retention считает активность в интервале — например, между третьим и седьмым днём. Это компромисс: он терпимее к нерегулярности, чем N-day, и не переписывает прошлое, как unbounded.

Ни один из трёх не правильнее других. Неправильно другое — сравнивать числа, посчитанные по разным формулам, и не писать рядом, какая использована.

Сквозной пример: D1 по дням регистрации на учебной базе

Посчитаем retention целиком на базе SQL-тренажёра. Продукт «Маяк», первая неделя июня, восемнадцать пользователей — по три в день с 1 по 6 июня. Когортой будет день регистрации, возвратом — любое событие на следующий день.

Расчёт распадается на три шага, и каждый проверяется отдельно. Сначала получаем активные дни пользователя — по одной строке на пару «человек + дата». Затем к каждому пользователю приписываем дату его регистрации. И наконец считаем, у кого нашлась активность ровно на день позже.

Результат по учебной базе выглядит так: из 18 пользователей на следующий день вернулись 10, то есть D1 равен 55,6%. Но общее число здесь наименее интересное — разрез по дням показывает то, чего среднее не видно.

WITH active_days AS (          -- зерно: пользователь + день
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day
  FROM events
)
SELECT
  u.signup_date                                   AS cohort_date,
  count(*)                                        AS cohort_size,
  count(a.user_id)                                AS returned_d1,
  round(100.0 * count(a.user_id) / count(*), 1)   AS d1_retention
FROM users u
LEFT JOIN active_days a
  ON a.user_id = u.user_id
  AND a.day = u.signup_date + INTERVAL '1 day'
GROUP BY u.signup_date
ORDER BY u.signup_date;

Почему нельзя объявлять, что retention упал втрое

Соблазн прочитать эту таблицу как обвал велик: 100% в начале недели и 33% в конце. На слайде это выглядело бы как катастрофа с ясным виновником — «что-то сломали в среду».

Но у поздних когорт есть техническая причина выглядеть хуже. База обрывается 7 июня: у когорты 6 июня для D1 есть ровно один день наблюдения, и он последний, а значит неполный. Кроме того, каждая когорта здесь — три человека. Один вернувшийся пользователь двигает метрику на 33 пункта, поэтому разница между 66,7% и 33,3% — это разница в одного человека.

Правило простое и жёсткое: когорта попадает в сравнение только тогда, когда прожила весь период наблюдения. Для D1 это сутки после регистрации, для D7 — неделя. Незрелые когорты не показывают нулём или маленьким числом — их либо исключают, либо помечают явно, оставляя ячейку пустой.

То же касается размера. Процент на трёх наблюдениях не метрика, а анекдот; рядом с ним обязаны стоять числитель и знаменатель, чтобы никто не строил на нём план квартала.

WITH bounds AS (              -- граница наблюдения = последний день данных
  SELECT max(CAST(event_time AS DATE)) AS last_day FROM events
),
active_days AS (
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day FROM events
)
SELECT
  u.signup_date AS cohort_date,
  count(*)      AS cohort_size,
  CASE
    WHEN u.signup_date + INTERVAL '1 day' <= b.last_day
    THEN round(100.0 * count(a.user_id) / count(*), 1)
  END           AS d1_retention
FROM users u
CROSS JOIN bounds b
LEFT JOIN active_days a
  ON a.user_id = u.user_id AND a.day = u.signup_date + INTERVAL '1 day'
GROUP BY u.signup_date, b.last_day
ORDER BY u.signup_date;

Форма кривой важнее одной точки

Retention редко имеет смысл как единственное число. Гораздо больше говорит форма кривой по возрасту когорты, и у здорового продукта она состоит из трёх участков.

Первый — резкое падение в первые дни. Это нормально: часть пришедших никогда не собиралась пользоваться продуктом всерьёз. Глубина этого падения говорит о качестве трафика и первого опыта, а не о продукте в целом.

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

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

Отсюда практический вывод: сравнивать когорты нужно по форме, а не по отдельной точке. Две когорты с одинаковым D1, но разным уровнем плато — это две разные истории, и решения по ним принимаются противоположные.

Retention и деньги: зачем считать удержание платящих отдельно

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

Практика простая: считайте ту же кривую отдельно по сегменту плательщиков. На учебной базе таких восемь из восемнадцати, и по ним видно то, что для бизнеса важнее всего, — возвращаются ли люди после того, как заплатили. Retention платящих напрямую связан с окупаемостью привлечения: чем дольше живёт когорта, тем больше платежей она успевает сделать.

Второй разрез — по тарифу или размеру платежа. Если удержание на дешёвом тарифе заметно ниже, это может значить, что он привлекает не ту аудиторию либо не даёт достаточной ценности. Такой вопрос решается изменением продукта или упаковки, а не рекламным бюджетом.

Связывать retention с выручкой напрямую в одном запросе не обязательно — достаточно считать обе метрики на одном определении когорты. Тогда их можно положить рядом: сколько людей осталось и сколько денег они принесли за тот же период.

WITH active_days AS (
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day FROM events
),
payers AS (
  SELECT DISTINCT user_id FROM payments
)
SELECT
  CASE WHEN p.user_id IS NULL THEN 'без оплаты' ELSE 'платящие' END AS segment,
  count(*)                                      AS users,
  count(a.user_id)                              AS returned_d1,
  round(100.0 * count(a.user_id) / count(*), 1) AS d1_retention
FROM users u
LEFT JOIN payers p ON p.user_id = u.user_id
LEFT JOIN active_days a
  ON a.user_id = u.user_id AND a.day = u.signup_date + INTERVAL '1 day'
GROUP BY segment
ORDER BY segment;

Retention отвечает не на все вопросы про удержание

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

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

Она не объясняет причину ухода. Из данных видно, что человек не вернулся; почему — знают только он и, возможно, запись его сессии. Retention хорошо показывает, где смотреть, и плохо — что чинить.

И она сильно зависит от определения возврата. Если возвращением считать любое событие, метрика будет высокой и малоинформативной: автоматические открытия приложения и фоновые события засчитаются как активность. Если считать целевое действие — число упадёт, зато станет связано с ценностью. Первое годится для мониторинга технической активности, второе — для продуктовых решений, и путать их не стоит.

Что делать, когда когорты слишком маленькие

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

Первое решение — укрупнить когорту: перейти с дней на недели или месяцы регистрации. Метрика станет спокойнее, а сравнение — осмысленнее. Второе — сместить фокус с процента на абсолютные числа: «из 12 зарегистрировавшихся вернулись 4» честнее, чем «33,3%». Третье — смотреть на накопленную кривую по всем когортам сразу, если вопрос не «когда стало хуже», а «как в принципе устроено удержание».

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

• Когорты по неделям вместо дней, когда дневные слишком малы.
• Абсолютные числа рядом с процентом — всегда.
• Незрелые когорты исключены или помечены пустой ячейкой.
• Сравнение только между когортами одинаковой зрелости.
• Определение возврата названо: любое событие или целевое действие.

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

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