Месячный retention в SQL: как сравнивать когорты по возрасту

Рабочий вопрос

Как сравнить пользователей, которые пришли в разные месяцы, не перепутав календарный месяц с возрастом когорты?

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

Для регистрации и первой покупки дата старта будет разной. Если сменить событие без обновления определения, график сохранит форму, но перестанет отвечать на прежний вопрос.

Определение и grain

Промежуточный слой — user × cohort_month × activity_month, финальный — cohort_month × month_age. Повторные события пользователя в одном месяце считаются один раз.

Найди cohort_month, построй user activity months, вычисли month_age и раздели активных на размер исходной когорты. Исключи незрелые последние месяцы из сравнения.

Если показатель строится по событиям, отдельно проверь повторную отправку, идентичность пользователя и часовой пояс. Если данные приходят из бизнес-системы, сопоставь техническое поле с тем, как команда реально принимает решение.

Разбор примера

Для регистрации и первой покупки дата старта будет разной. Если сменить событие без обновления определения, график сохранит форму, но перестанет отвечать на прежний вопрос.

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

WITH first_seen AS (\n  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month\n  FROM events GROUP BY user_id\n), activity AS (\n  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS activity_month\n  FROM events\n)\nSELECT f.cohort_month, DATE_PART('month', AGE(a.activity_month, f.cohort_month)) AS month_age, COUNT(DISTINCT a.user_id) AS active_users\nFROM first_seen f JOIN activity a USING (user_id)\nGROUP BY 1, 2;

Где расчёт ломается

Опасны смешение регистрации и activation, деление на текущий размер когорты, отрицательный month_age и показ последних незрелых когорт рядом со зрелыми.

Отдельно протестируй пустой результат, NULL, дубль, граничную дату и объект без связанной записи. Эти случаи не являются редкими исключениями: именно они чаще всего превращают рабочий показатель в красивую, но неверную цифру.

• Не смешивай зрелые и незрелые периоды без отдельной подписи.
• Не заменяй проверку качества тем, что запрос просто выполнился.
• Не делай причинный вывод из одного разреза.

Что показывает график

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

На визуализации «Retention по возрасту месячной когорты» сравнивай не только максимальное значение. Проверь, одинаковы ли базы, не скрыта ли неполная дата и не меняется ли знаменатель от категории к категории.

Проверь на своих данных

Собери три когорты по пять пользователей, добавь один неполный месяц и вручную проверь M0, M1 и M2. Затем сравни retention по каналам.

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

• Собери контрольный пример из пяти-десяти строк.
• Сверь итог с независимым способом расчёта.
• Проверь хотя бы один крайний случай.
• Зафиксируй дату, версию схемы и ограничение.

Решение и продолжение

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

Сильный аналитический материал не обещает абсолютной уверенности. Он показывает, как получить число, где оно может ошибиться и какое решение можно принять уже сейчас. Такой формат хорошо переносится в дашборд, SQL-тренажёр, собеседование и рабочее обсуждение с командой.