Весенняя эра открыта 😎

Время обновить дизайн на теплые оттенки согревающие душу. Собрал новый дизайн, что бы каждый пост отдавал весенней энергией 🔥

Такс пойдем теперь за вопросы продуктовые поговорим 👇

Тебе дают две таблицы - users и events. И 20 минут на задачу:
«Посчитай батенька retention по когортам M0–M3»

И вот на этом ловят ступор.
А с чего вообще стартовать? 🤔

Проще всего - не с SQL, а с картинки в голове. Представь спортзал.
100 человек купили абонемент - это твоя когорта.
Дальше обычно эту когорту принимают за 100%. Это и есть M0.
Но вот нюанс: это не закон природы, а просто негласное соглашение.
На следующий день пришли 60 человек - Day 1 = 60%.
Через неделю пришли 40 - Day 7 = 40%.

Дальше логика не меняется.
Просто вместо дней - месяцы.
И вместо спортзала - продукт.

1️⃣ Junior-уровень - понять, сколько людей вообще пришло в каждую когорту
SELECT
  DATE_TRUNC('month', registration_date) AS cohort_month,
  COUNT(DISTINCT user_id) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

Если уже здесь ошибка, все суши весла. Потому что DATE_TRUNC и DISTINCT - это база продуктового блока.
Хотя бы для PostgreSQL и похожих диалектов.

2️⃣ Следующий уровень - понять, кто вернулся
WITH cohort_dec AS (
  SELECT user_id, registration_date
  FROM users
  WHERE registration_date >= '2023-12-01'
    AND registration_date < '2024-01-01'
)
SELECT
  COUNT(*) AS cohort_size,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 1 THEN user_id END) AS day1,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 7 THEN user_id END) AS day7
FROM cohort_dec u
LEFT JOIN events e ON u.user_id = e.user_id;

Тут под ноги бросаются грабли 🤔

Такой код нормален, если даты у тебя хранятся как DATE, без времени.
Но если это TIMESTAMP, простое равенство может ломать расчёт.
Потому что у одного событие в 2023-12-08 00:01, у другого в 2023-12-08 19:42, и формально это уже не то же самое значение.

Значит, нужно либо приводить к дате, либо считать через диапазоны.
То есть логика всегда одна и та же:
✅ зафиксировал когорту → посмотрел, кто вернулся

Для M1, M2, M3 всё почти то же самое, но есть принципиальный момент:
👉 сравнивают не просто даты, а смещение по календарным месяцам относительно месяца регистрации ( да сложно звучит сейчас объясню )

То есть не +30 дней. Месяцы разной длины, поэтому такой расчёт съезжает.
Для месячного retention смотрят смещение по календарным месяцам, а не просто прибавляют 30 дней.

❌ Где еще грабли поджидают

1️⃣ Считают события вместо людей
Один пользователь сделал 5 событий.
И внезапно retention у тебя больше 100%.
Значит, ты считаешь не возврат людей, а активность.

2️⃣ Ставят INNER JOIN
И в выборке остаются только те, кто вернулся.
Все, кто отвалился, просто исчезают.
Получается красивая цифра. И полностью фальшивая картина.

3️⃣ Не фиксируют базу расчёта
Размер когорты обычно принимают за 100%, и уже от него считают всё дальше.
Если база у тебя гуляет, проценты превращаются в мусор.

А дальше начинается главная беда... тест на мышление и софты. Вот уж где самая тернистая тропа если мало опыта.
Потому что на собесе тебя не спросят:
«Как посчитать retention?»

А спросят:
«D30 упал. Почему?»

Приехали...И вот тут SQL уже никого не впечатляет.

✅ Нормальный ход мысли такой:
- разложить retention по когортам
- посмотреть каналы привлечения
- проверить activation
- понять, не ломался ли онбординг
- сравнить поведение до и после релизов
Можно копать еще глубже, но для старта достаточно этих основ.

❌ Ответ который сразу поставит крест на вашем диалоге
«Ну… retention снизился, потому что пользователи стали хуже возвращаться..»

Спасибо товарищ Капитан. Но это не ответ аналитика, а догадка.

Если простыми словами:
Retention - это не про SQL, это про поведение.
SQL - это просто лопата, которой ты выкапываешь цифру.

Если ты не можешь объяснить, почему люди перестали возвращаться,
то сами по себе твои проценты ни о чем не расскажут 🤷‍♀️

У вас бывали проблемы на продуктовом блоке? И вообще любо ли вам продуктовое направление или больше нравиться инженерная часть ? 👇