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

Коротко

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

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

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

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

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

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

Запрос показывает одну когорту — пришедших 1 июня — за дни с нулевого по седьмой: 111 строк на 32 человека.

with active_days as (
  select distinct
    u.user_id,
    u.signup_date as cohort_date,
    cast(e.event_time as date) as event_date,
    cast(e.event_time as date) - u.signup_date as days_since_signup
  from users as u
  join events as e on e.user_id = u.user_id
  where e.event_name = 'app_open'
    and cast(e.event_time as date) >= u.signup_date
    and u.signup_date = date '2026-06-01'   -- одна когорта: результат умещается в 200 строк
)
select *
from active_days
where days_since_signup <= 7
order by cohort_date, user_id, event_date;

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

Теперь можно посчитать размер каждой когорты и число пользователей, активных на первом и седьмом дне. В запросе флаг на пользователя защищает от повторных событий, а деление на размер когорты превращает количество в retention rate.

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

Запрос считает июньские когорты: оба дня у них давно закрыты. У когорты 1 июня из 32 человек на первый день вернулись 15, на седьмой — 6.

with cohort_users as (
  select
    u.user_id,
    u.signup_date as cohort_date,
    u.channel,
    max(
      case when cast(e.event_time as date) - u.signup_date = 1
        then 1 else 0 end
    ) as retained_d1,
    max(
      case when cast(e.event_time as 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 = 'app_open'
   and cast(e.event_time as date) >= u.signup_date
  where u.signup_date < date '2026-07-01'   -- июньские когорты: оба дня давно закрыты
  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,
  d1_users,
  d7_users,
  round(100.0 * d1_users / nullif(cohort_size, 0), 1) as d1_retention,
  round(100.0 * d7_users / nullif(cohort_size, 0), 1) as d7_retention
from cohort_summary
order by cohort_date;

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

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

Месячные когорты: M1 у пришедшего 31 января наступает на следующий день

Дневная матрица держится, пока в когорте сотни человек. Когда в день приходит по тридцать, строки превращаются в шум, и команда укрупняет когорту до месяца. Условный пример: майская когорта — 1 240 человек. В июне из них вернулись 521, в июле 347, в августе 260. В долях от исходного размера это 42%, 28% и 21% — и это уже не D1, D3, D7, а M1, M2, M3.

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

Механика короткая. Месяц старта — date_trunc('month', ...) от первого значимого события пользователя. Месяц активности — то же усечение от каждого дня, когда он возвращался, с обязательным distinct: пять заходов в июле дают одну строку. Возраст — разница этих двух месяцев в месяцах. Знаменатель — размер когорты на старте, зафиксированный раз и навсегда; если делить на число активных в текущем месяце, retention получится больше ста процентов ровно в тех когортах, где кто-то вернулся после паузы.

У месячного возраста есть особенность, которая ломает чтение первых колонок. month_age = 1 означает «следующий календарный месяц», а не «через тридцать дней». У пользователя, зарегистрированного 3 января, M0 длится двадцать девять дней; у пришедшего 31 января — один вечер. Первого февраля оба оказываются в M1, хотя успели прожить в продукте несовместимо разное время. Поэтому M0 в месячной матрице почти не несёт информации, а M1 смешивает людей с разной экспозицией: читать такую матрицу честно можно начиная с M2, а если нужен ранний сигнал — брать возраст в днях, как в дневной матрице выше.

На учебной базе запрос возвращает три строки: M0 и M1 июньской когорты (1 042 человека, M1 — 97,1%) и M0 июльской (1 676). Такой M1 и есть смешанная экспозиция: пришедшему 30 июня достаточно зайти назавтра.

Зрелость для месяцев считается так же, как для дней, только шаг крупнее. M3 имеет право на значение, когда с месяца старта прошло четыре полных календарных месяца: февральская когорта получит свой M3 только в июне. До этого в ячейке стоит пустота, а не число, — иначе свежие когорты обвалят правый край матрицы и покажут падение, которого нет. В учебной базе август не закрыт, поэтому M2 июньской когорты и M1 июльской пусты; M2 к тому же обрезан: события пользователя записаны только за первые 59 дней после регистрации.

WITH first_seen AS (            -- зерно: один пользователь
  SELECT user_id, CAST(date_trunc('month', min(event_time)) AS date) AS cohort_month
  FROM events
  WHERE event_name = 'app_open'
  GROUP BY user_id
),
activity AS (                   -- зерно: пользователь + месяц возврата
  SELECT DISTINCT user_id, CAST(date_trunc('month', event_time) AS date) AS activity_month
  FROM events
  WHERE event_name = 'app_open'
),
sizes AS (
  SELECT cohort_month, count(*) AS cohort_size
  FROM first_seen
  GROUP BY cohort_month
),
bounds AS (                     -- месяц последнего события ещё не закрыт
  SELECT CAST(date_trunc('month', max(event_time)) AS date) AS open_month
  FROM events
)
SELECT
  f.cohort_month,
  s.cohort_size,
  12 * (extract(year  FROM a.activity_month) - extract(year  FROM f.cohort_month))
     + (extract(month FROM a.activity_month) - extract(month FROM f.cohort_month)) AS month_age,
  count(DISTINCT a.user_id)                                                        AS returned_users,
  round(100.0 * count(DISTINCT a.user_id) / s.cohort_size, 1)                      AS retention
FROM first_seen f
JOIN activity a USING (user_id)
JOIN sizes    s USING (cohort_month)
CROSS JOIN bounds b
-- зрелость: месяц возврата должен быть полностью закрыт
WHERE a.activity_month < b.open_month
GROUP BY f.cohort_month, s.cohort_size, month_age
ORDER BY f.cohort_month, month_age;

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

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

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

На учебной базе запрос берёт регистрации с 1 июля, когда запущен платный поиск, по 22 августа — у них седьмой день закрыт. D7: referral 25,9%, organic 23,8%, partner 20,8%, paid_search 11,7% (101 из 865).

with user_retention as (
  select
    u.user_id,
    u.channel,
    max(
      case when cast(e.event_time as 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 = 'app_open'
   and cast(e.event_time as date) >= u.signup_date
  where u.signup_date >= date '2026-07-01'   -- paid_search запущен 1 июля
    and u.signup_date < date '2026-08-23'    -- седьмой день закрыт: события полны по 29 августа
  group by u.user_id, u.channel
)
select
  channel,
  count(*) as cohort_size,
  sum(retained_d7) as d7_users,
  round(100.0 * sum(retained_d7) / nullif(count(*), 0), 1) as d7_retention
from user_retention
group by channel
order by d7_retention desc, channel;

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

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

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

Список исключений — часть определения, а не фильтр, дописанный в WHERE

В списке выше есть строка про тестовые аккаунты, и она короткая, потому что поломка выглядит безобидно. Кто-то дописывает в конец запроса and u.email not like '%@our-company.ru', retention меняется на пункт-полтора, число становится «почище», и на этом история заканчивается. Через месяц тот же отчёт у соседа показывает другое значение, потому что у него в хвосте стоит другой not like.

Первое, что ломается, — симметрия числителя и знаменателя. Таблицы исключений в учебной базе нет, запрос ниже собирает список сам: по сотой доле user_id на каждое из трёх правил. Из 4 575 зарегистрированных по 28 августа он убирает 92, остаётся 4 483. Если правило попало только в присоединение событий, эти 92 остаются в размере когорты, но уже не могут вернуться: при честном D1 53,4% (2 395 из 4 483) отчёт покажет 2 395 / 4 575 = 52,3%. Один пункт — ровно та величина, которую потом объясняют сезонностью.

Второе — ключ. Почта меняется, внутренние сотрудники регистрируются с личных адресов, а у части нагрузочных аккаунтов стоят вполне клиентские домены. Исключение живёт на user_id и хранится отдельной таблицей, где у каждой строки есть причина и дата, с которой правило действует. Тогда запрос присоединяет её через NOT EXISTS, а не накапливает условия в хвосте.

Третье — историчность. Аккаунт, ставший внутренним в марте, убирать из январской когорты или нет? Оба ответа защищаемы: убирать — если нужна чистая картина поведения, оставлять — если нужен неизменный исторический ряд. Недопустим только третий вариант, при котором у правила нет даты: тогда каждое новое имя в списке исключений переписывает retention прошлых месяцев, и вчерашняя матрица сегодня выглядит иначе без единого изменения в данных. В учебном списке из 46 QA-аккаунтов когорты исключены 29: 17 пришедших до 15 июля остались в своих когортах.

Четвёртое — видимость. Число исключённых объектов показывают рядом с результатом, а не прячут в подзапросе. Строка «исключено 92 пользователя по трём правилам» закрывает вопрос о разных знаменателях до того, как два отчёта начнут спорить. Заодно она работает как проверка: если исключённых вдруг стало 920, дело не в retention, а в том, что правило поймало лишнее.

WITH metric_exclusions AS (    -- учебная вставка: такой таблицы в базе нет
  SELECT user_id, 'сотрудники компании' AS reason, DATE '2026-06-01' AS effective_from
  FROM users WHERE user_id % 100 = 0
  UNION ALL
  SELECT user_id, 'QA-аккаунты', DATE '2026-07-15' FROM users WHERE user_id % 100 = 7
  UNION ALL
  SELECT user_id, 'нагрузочные боты', DATE '2026-08-01' FROM users WHERE user_id % 100 = 13
),
cohort AS (                    -- день D1 закрыт: события полны по 29 августа
  SELECT user_id, signup_date FROM users WHERE signup_date < DATE '2026-08-29'
),
eligible AS (                  -- когорта после исключений
  SELECT c.user_id, c.signup_date
  FROM cohort c
  WHERE NOT EXISTS (
    SELECT 1
    FROM metric_exclusions x
    WHERE x.user_id = c.user_id
      AND x.effective_from <= c.signup_date
  )
),
active_days AS (
  SELECT DISTINCT e.user_id, CAST(e.event_time AS DATE) AS day
  FROM events e
  JOIN eligible g ON g.user_id = e.user_id   -- то же правило для числителя
  WHERE e.event_name = 'app_open'
)
SELECT
  (SELECT count(*) FROM cohort)                 AS users_before,
  (SELECT count(*) FROM eligible)               AS users_after,
  count(a.user_id)                              AS returned_d1,
  round(100.0 * count(a.user_id) / count(*), 1) AS d1_retention
FROM eligible g
LEFT JOIN active_days a
  ON a.user_id = g.user_id AND a.day = g.signup_date + INTERVAL '1 day';

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

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.

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

Классический, rolling и «в течение N дней» на одной когорте

Пример — на той же учебной базе; события берём по 29 августа: 30 августа записано только до 12:59. Когорта — зарегистрированные за неделю с 27 июля по 2 августа, 404 человека; возраст каждого считается от его даты регистрации, возврат — app_open. Три определения из раздела выше дают на ней три числа. Ровно на седьмой день вернулись 79 человек — 19,6%. Хотя бы раз с первого по седьмой день — 360, это 89,1%. На седьмой день или позже — 379, это 93,8%. В статье о когортном анализе у неё же 70,0% на второй неделе жизни: там окно — дни 7–13, то есть bracket.

Rolling не может быть ниже классического: каждый, кто пришёл ровно на седьмой день, пришёл и «на седьмой или позже». Обратное неверно, поэтому разрыв между 19,6% и 93,8% — разница вопросов, а не ошибка расчёта. А вот «в течение 7 дней» и rolling друг в друга не вложены: первое смотрит на дни с первого по седьмой, второе — на седьмой и дальше. У когорты 27 июля rolling выше, у когорты 10 августа — ниже (80,2% против 91,2%): так сложился горизонт, а не формулы.

Rolling зависит от того, сколько времени прошло после когорты. У когорты 27 июля он 93,8%, у когорты 3 августа — 90,7%, у когорты 10 августа — 80,2%. Выглядит как падение удержания за две недели, но у поздней когорты просто на две недели меньше времени, чтобы вернуться. Та же когорта 27 июля, посчитанная по данным на конец 9 августа, показала бы 54,5%, на конец 16 августа — 80,2%, на конец 23 августа — 89,6%. Метрика дорастает задним числом, и сравнивать по ней когорты разного возраста нельзя.

Фиксированный горизонт сравнивать можно. «В течение 7 дней» у тех же трёх когорт — 89,1%, 92,3% и 91,2%: у каждого участника было ровно семь дней. Разброс в три пункта на когортах по 400–450 человек — не тренд: 95%-й интервал разницы соседних когорт — около ± 4 пунктов.

У когорты 24 августа, 471 человек, седьмой день лежит за концом данных. Без проверки зрелости запрос вернул бы классический и rolling retention 0%. Ноль здесь означает «день ещё не наступил», поэтому в клетке должна стоять пустота: CASE без ELSE возвращает NULL, когда седьмой день пришедшего последним, cohort_week + 13, позже последнего полного дня. Пуста и когорта 17 августа: у пришедших 23 августа седьмой день — оборванное 30 августа. Если же вопрос не про возврат когорты, а про регулярность всей аудитории, нужна другая метрика — stickiness, отношение DAU к MAU: её разбирает статья о DAU и MAU.

WITH user_days AS (            -- зерно: пользователь + день
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS active_day
  FROM events
  WHERE event_name = 'app_open'
    AND event_time < TIMESTAMP '2026-08-30 00:00:00'   -- 30 августа неполное
),
flags AS (                     -- зерно: один пользователь
  SELECT u.user_id,
    CAST(date_trunc('week', u.signup_date) AS DATE) AS cohort_week,
    MAX(CASE WHEN d.active_day = u.signup_date + 7 THEN 1 ELSE 0 END) AS on_day_7,
    MAX(CASE WHEN d.active_day > u.signup_date
              AND d.active_day <= u.signup_date + 7 THEN 1 ELSE 0 END) AS within_7_days,
    MAX(CASE WHEN d.active_day >= u.signup_date + 7 THEN 1 ELSE 0 END) AS day_7_or_later
  FROM users AS u
  LEFT JOIN user_days AS d ON d.user_id = u.user_id
  WHERE u.signup_date >= DATE '2026-07-27'
  GROUP BY u.user_id, u.signup_date
)
-- cohort_week + 13 — седьмой день того, кто пришёл в воскресенье
SELECT cohort_week, COUNT(*) AS cohort_size,
  CASE WHEN cohort_week + 13 <= DATE '2026-08-29'
       THEN ROUND(100.0 * SUM(on_day_7) / COUNT(*), 1) END AS classic_d7,
  CASE WHEN cohort_week + 13 <= DATE '2026-08-29'
       THEN ROUND(100.0 * SUM(within_7_days) / COUNT(*), 1) END AS within_7_days,
  CASE WHEN cohort_week + 13 <= DATE '2026-08-29'
       THEN ROUND(100.0 * SUM(day_7_or_later) / COUNT(*), 1) END AS rolling_d7
FROM flags
GROUP BY cohort_week
ORDER BY cohort_week;

Флаг на пользователя через MAX(CASE)

В запросе выше есть шаг, который легко пропустить: MAX(CASE WHEN … THEN 1 ELSE 0 END) с группировкой по пользователю. После соединения «пользователь и его активные дни» у пользователя столько строк, сколько дней он заходил. Флаг сворачивает их в одну строку со значением 0 или 1: условие выполнилось хотя бы в одной строке. Делить можно только после этого.

У когорты 27 июля после соединения 2 377 строк на 404 пользователя. Если посчитать долю прямо по ним, знаменатель считает пользователе-дни: 825 строк с активностью в дни с первого по седьмой из 2 377 — 34,7% вместо 89,1%. Если оставить в знаменателе пользователей, а числитель не свернуть, получится 825 / 404 = 204,2%: пользователь, заходивший три дня из семи, посчитан трижды.

Без шага «пользователь + день» знаменателем становятся события. В учебной базе app_open записан не чаще раза в день, поэтому событий столько же, сколько строк, и ошибка та же — 34,7%. В журнале, где заходов в день несколько, доля сдвинется ещё и от их частоты; даже похожая на правильную, она остаётся долей событий, а не людей.

Порядок один для любой метрики вида «сколько пользователей сделали X»: сначала одна строка на пользователя с флагами, потом SUM(флаг) / COUNT(*). COUNT(DISTINCT CASE WHEN … THEN user_id END) даёт тот же числитель, но флаги удобнее, когда условий несколько: три определения retention считаются за один проход, а рядом лежат размер когорты и проверка зрелости.

В pandas тот же шаг — groupby('user_id').max() по булевым колонкам. Блок ниже считает пятерых пользователей, заданных прямо в коде, и печатает 5 2 3 4: размер когорты и три числителя.

WITH user_days AS (
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS active_day
  FROM events
  WHERE event_name = 'app_open'
    AND event_time < TIMESTAMP '2026-08-30 00:00:00'
)
SELECT COUNT(*) AS joined_rows,                -- 2 377 строк «пользователь + день»
       COUNT(DISTINCT u.user_id) AS users,     -- 404 пользователя
       SUM(CASE WHEN d.active_day > u.signup_date
                 AND d.active_day <= u.signup_date + 7 THEN 1 ELSE 0 END) AS rows_in_window,
       ROUND(100.0 * SUM(CASE WHEN d.active_day > u.signup_date
                 AND d.active_day <= u.signup_date + 7 THEN 1 ELSE 0 END) / COUNT(*), 1) AS wrong_pct
FROM users AS u
JOIN user_days AS d ON d.user_id = u.user_id
WHERE u.signup_date >= DATE '2026-07-27' AND u.signup_date < DATE '2026-08-03';

import pandas as pd

# учебная вставка: пользователь и день жизни, в который он заходил (0 — день регистрации)
days = pd.DataFrame(
    [(1, 0), (1, 1), (1, 3), (1, 7), (2, 0), (3, 0), (3, 2), (3, 9),
     (4, 0), (4, 7), (5, 0), (5, 12)],
    columns=['user_id', 'age'],
)
days['on_day_7'] = days['age'] == 7
days['within_7_days'] = days['age'].between(1, 7)
days['day_7_or_later'] = days['age'] >= 7

# одна строка на пользователя: True, если условие выполнилось хотя бы в один из его дней
flags = days.groupby('user_id')[['on_day_7', 'within_7_days', 'day_7_or_later']].max()
print(len(flags), *flags.sum().tolist())

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

Посчитаем retention целиком на учебной базе из песочницы: 4 613 пользователей, 90 дневных когорт с 1 июня по 29 августа. Когортой будет день регистрации, возвратом — app_open на следующий день.

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

Запрос возвращает 90 строк, в таблице ниже — последние семь. По всей базе на следующий день вернулись 2 454 из 4 613, D1 — 53,2%. Но общее число здесь наименее интересное — разрез по дням показывает то, чего среднее не видно.

WITH active_days AS (          -- зерно: пользователь + день
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day
  FROM events
  WHERE event_name = 'app_open'
)
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 упал втрое

Соблазн прочитать последнюю строку как обвал велик: 26,3% после 48–57% у пяти предыдущих когорт. С D7 картина ещё резче: у когорты 23 августа он 6,1% при 21,4% по зрелым когортам — втрое ниже, а у шести когорт после неё — ноль. На слайде это выглядело бы как катастрофа с ясным виновником — «что-то сломали в конце августа».

Но у поздних когорт есть техническая причина выглядеть хуже. События в базе обрываются 30 августа в 12:59: у когорты 29 августа день D1 записан наполовину, у когорты 23 августа то же с D7, а у пришедших позже седьмой день не наступил вовсе. Кроме того, дневные когорты малы: в когорте 23 августа 33 человека, и один вернувшийся двигает D1 на три пункта.

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

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

WITH bounds AS (              -- день последнего события не закрыт: граница — день перед ним
  SELECT CAST(max(event_time) AS DATE) - 1 AS last_full_day FROM events
),
active_days AS (
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day
  FROM events
  WHERE event_name = 'app_open'
)
SELECT
  u.signup_date AS cohort_date,
  count(*)      AS cohort_size,
  CASE
    WHEN u.signup_date + INTERVAL '1 day' <= b.last_full_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_full_day
ORDER BY u.signup_date;

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

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

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

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

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

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

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

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

Практика простая: считайте ту же кривую отдельно по сегменту плательщиков. На учебной базе запрос берёт зарегистрированных по 9 августа, у кого срок первой оплаты уже вышел: платили 756 из 3 207, их D1 — 56,3% против 53,1% у остальных (разница в пределах шума). Оплата в базе приходит на 3–20-й день, так что это возврат ещё до неё; чтобы увидеть, возвращаются ли люди после того, как заплатили, стартом когорты берут дату первой оплаты. Retention платящих напрямую связан с окупаемостью привлечения: чем дольше живёт когорта, тем больше платежей она успевает сделать.

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

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

WITH active_days AS (
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day
  FROM events
  WHERE event_name = 'app_open'
),
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'
WHERE u.signup_date < DATE '2026-08-10'   -- срок первой оплаты (3–20 дней) уже вышел
GROUP BY segment
ORDER BY segment;

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

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

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

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

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

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

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

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

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

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

Частые вопросы

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

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