Весенняя эра открыта 😎
Время обновить дизайн на теплые оттенки согревающие душу. Собрал новый дизайн, что бы каждый пост отдавал весенней энергией 🔥
Такс пойдем теперь за вопросы продуктовые поговорим 👇
Тебе дают две таблицы - 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 - это просто лопата, которой ты выкапываешь цифру.
Если ты не можешь объяснить, почему люди перестали возвращаться,
то сами по себе твои проценты ни о чем не расскажут 🤷♀️
У вас бывали проблемы на продуктовом блоке? И вообще любо ли вам продуктовое направление или больше нравиться инженерная часть ? 👇
