Когортний аналіз

Когортний аналіз групує об'єкти (зазвичай користувачів) за єдиною подією старту і порівнює, як і наскільки довго вони залишаються активними і цінними. Такий підхід відокремлює ефект часу в системі (сезони, акції) від ефекту віку когорти (дні з моменту старту).

1) Базові визначення

Когорта (cohort): безліч гравців, об'єднаних подією «народження» - реєстрація, перший депозит, перша гра, перша покупка.
Вісь календаря (calendar time): фактичні дати (2025-10-01,...).
Вісь віку когорти (cohort age): дні/тижні з моменту «народження» (D0, D1,...).
Метрики утримання: D1/D7/D30 (Exact и Rolling), WAU/MAU, Stickiness (DAU/MAU).
Монетизація: ARPU/ARPPU, кумулятивний LTV (на D7/D30/D90).
Одиниця обліку: користувач (user/master_id) - фіксуйте в паспорті.

💡 Золоте правило: заздалегідь зафіксуйте подію народження, TZ, вікно активності та виключення (боти/QA/фрод).

2) Види когорт і коли їх вибирати

Acquisition-когорти: за датою реєстрації/першого візиту - оцінка каналів залучення та онбордингу.
Activation/Monetization-когорти: за першим депозитом/покупкою - оцінка early-monetization і промо.
Feature-когорти: по першому використанню фічі/ігрової категорії - ефект релізів.
Behavior-когорти: по RFM/патерну старту (наприклад, «нічні мобільні»).

3) Осі та сітки: Як дивитися матрицю

Матриця когорт: рядки - когорти (календар), колонки - вік (D0... D90).
Сезонність: порівнюйте діагоналі (однаковий календарний день), щоб відокремлювати сезонні ефекти.
Нормалізація: відносні метрики (CR, частки) + кумулятивні (LTV), показуйте обидві.

4) Паспорт когорти і метрик (template)

COHORT: `REG_DAY`	`FIRST_DEPOSIT_WEEK`
Вісь віку: день (D), горизонти D1/D7/D30/D90.
Активність: ≥1 сесія або ≥1 ставка (зафіксувати).
Винятки: боти/фрод/QA/дублікати.
Типові сегменти: країна, платформа, канал, контент-категорія, ціновий сегмент.
Метрики: CR, Rolling/Exact retention, кумулятивний LTV, ARPU/ARPPU,% платять.
Версія: 'COHORT _ RET _ v3', власники, дата ревізії.

5) Псевдо-SQL: матриця retention (Exact Dn)

sql
WITH regs AS (
SELECT user_id, DATE_TRUNC('day', MIN(ts)) AS cohort_day
FROM event_register
GROUP BY 1
),
act AS (
SELECT user_id, DATE_TRUNC('day', ts) AS act_day
FROM event_activity
),
ages AS (
SELECT r. user_id, r. cohort_day, a. act_day,
(a. act_day - r. cohort_day) AS age_days
FROM regs r
JOIN act a ON a. user_id = r. user_id
),
exact AS (
SELECT cohort_day,
age_days,
COUNT(DISTINCT user_id) AS users_active
FROM ages
GROUP BY 1,2
),
coh_size AS (
SELECT cohort_day, COUNT(DISTINCT user_id) AS cohort_size
FROM regs GROUP BY 1
)
SELECT e. cohort_day,
e. age_days,
e. users_active::decimal / NULLIF(c. cohort_size,0) AS exact_retention
FROM exact e
JOIN coh_size c USING (cohort_day)
WHERE age_days IN (1,7,30,90)
ORDER BY cohort_day, age_days;

Rolling Dn (активність в 1... n день)

sql
WITH days AS (... as above...),
roll AS (
SELECT cohort_day,
CASE WHEN age_days BETWEEN 1 AND 7 THEN 7
WHEN age_days BETWEEN 1 AND 30 THEN 30 END AS bucket,
COUNT(DISTINCT user_id) AS any_active
FROM days
WHERE age_days BETWEEN 1 AND 30
GROUP BY 1,2
)
SELECT r. cohort_day, r. bucket AS Dn,
r. any_active::decimal / s. cohort_size AS rolling_retention
FROM roll r
JOIN (SELECT cohort_day, COUNT(DISTINCT user_id) cohort_size FROM regs GROUP BY 1) s USING (cohort_day)
ORDER BY cohort_day, Dn;

6) Когортний LTV і монетизація

Кумулятивний LTV (Dn): сума доходу на користувача когорти до Dn.
ARPU/ARPPU: дохід на користувача/на що сплачує за Dn.
% платять: частка з ≥1 платежем до Dn.

sql
WITH reg AS (
SELECT user_id, DATE_TRUNC('day', MIN(ts)) AS cohort_day
FROM event_register GROUP BY 1
),
pay AS (
SELECT user_id, amount, DATE_TRUNC('day', ts) AS pay_day
FROM fact_payments
),
ltv AS (
SELECT r. cohort_day,
(pay_day - r. cohort_day) AS age_days,
SUM(amount) AS rev
FROM reg r JOIN pay p USING (user_id)
WHERE pay_day >= r. cohort_day
GROUP BY 1,2
),
cum AS (
SELECT cohort_day, age_days,
SUM(rev) OVER (PARTITION BY cohort_day ORDER BY age_days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rev_cum
FROM ltv
)
SELECT c. cohort_day, c. age_days,
c. rev_cum::decimal / NULLIF(sz. cohort_size,0) AS ltv_per_user
FROM cum c
JOIN (SELECT cohort_day, COUNT(DISTINCT user_id) cohort_size FROM reg GROUP BY 1) sz USING (cohort_day)
WHERE age_days IN (7,30,90)
ORDER BY cohort_day, age_days;

7) Survival/hazard для утримання

Kaplan–Meier: немодельна крива виживаності (S (t)) - частка не «відтеклих».
Hazard-моделі (Сох/логіт-по-днях): вплив ознак (канал, країна, платформа, бонуси, контент) на ризик відтоку.
Практика: будуємо KM за сегментами, потім пояснюємо різницю моделлю hazard.

8) Сезонність, TZ і календар

TZ: зберігайте події в UTC, аналізуйте в локальній TZ ринку; будьте консистентні.
Календар: свята/зарплатні/матчі/релізи - як прапори; порівнюйте когорти аналогічних тижнів.
Ковзне вікно: для тижневих/місячних когорт - кратність святам і звітним періодам.

9) Сегментація та атрибуція

Сегменти: канал залучення, платформа/OS, гео, перший контент, ціна/ліміти, платіжний метод.
Атрибуція когорти: «хто привів» користувача - фіксуйте алгоритм (last non-direct, data-driven).
LTV-зважування: порівнюйте не тільки CR, але і LTV (D30/D90) по каналах/сегментах.

10) Візуалізація

Теплокарта матриці когорт (CR/LTV).
Лінії тренда D1/D7/D30 за календарем.
Survival/Hazard графіки.
Bridge «що змінило LTV до D30»: внесок платять, частоти, середнього чека.

11) Експерименти і причинність

A/B: онбординг, туторіали, paywall, оффери. Основна метрика - D7/D30 retention і LTV (D30).
Квазіексперименти: DiD/синтетичний контроль для викату по ринках.
Uplift-моделі: таргетуйте приріст повернення в ре-активації (Qini/AUUC, uplift @k).

12) Експлуатація та говернанс

Версіонування: `RET_D7_vN`, `LTV_D30_vN`; changelog при зміні визначення активності/валюти.
SLO свіжості: щоденні когорти - готовність до 06:00 лок.; лаг даних ≤ 1 ч.
Якість: coverage подій, частка дублікатів, частка ботів/фрода поза когортами.
Доступ: RLS/CLS, маскування PII; експорт - тільки агрегати.
Runbooks: падіння D1 (онбординг), D7 (контент), злам подій/ідентичностей.

13) Часті помилки (анти-патерни)

Змішання осей: порівнюють різні віки когорт в різні сезони без поправок.
Rolling vs Exact: трактують як одне й те саме.
Змішування одиниць: сесії в знаменнику, користувачі в чисельнику.
Агрегування «середнім середнім»: замість підсумовування чисельників/знаменників.
Ігнор TZ/календаря: зміщення D1 на межах днів/свят.
Немає фільтра ботів/фрода/QA.
Невраховані рестарти: спліт/merge облікових записів без мостів ідентичності.

14) Чек-лист перед публікацією когортного звіту

Визначено подію народження, одиниця, TZ, вікна активності
Виключені боти/фрод/QA; ідентичності зведені (golden record)
Побудовані матриці CR (Exact/Rolling) і LTV до D7/D30/D90
Враховані календар/свята; сегменти по каналу/платформі/гео
Додані survival/hazard-графіки і bridge LTV
Документовані версії метрик і алгоритм атрибуції
Налаштовані SLO свіжості, моніторинг coverage/дублікатів/помилок
Готові runbooks по падіннях D1/D7 і обривах подій

Підсумок

Когортний аналіз - це дві осі і дисципліна: фіксований «момент народження», коректні вікна і TZ, матриці утримання і LTV, сегментація і причинна перевірка змін. Такий підхід допомагає не тільки спостерігати криві, а й приймати рішення: де лагодити онбординг, які канали масштабувати, який контент і оффери тримають гравців довше і збільшують LTV.

Когортний аналіз