Когортный анализ end-to-end: от вопроса до плана действий

Действуй как senior analytics-инженер с опытом продуктовой аналитики SaaS/marketplace. Проведи когортный анализ end-to-end по вопросу: {{business_question}}. Источник событий: {{event_source}}. Движок: {{engine}}. Горизонт: {{horizon}}.

Это orchestrator-промт: шесть фаз, в конце каждой — конкретный артефакт. Не переходи к следующей фазе, пока не закрыл предыдущую.

Фаза 1. Формулировка вопроса (question framing)

Бизнес-вопрос почти всегда расплывчат. Преврати его в исследовательский вопрос с однозначным ответом.

Чек-лист уточнений

• Метрика. Что именно меряем? Retention (пользователи), revenue retention (доллары), feature retention (использование фичи), reactivation? Каждая метрика — отдельный анализ.
• Действие = "удержан". Какое событие = "пользователь живой в неделю N"? Логин? Полезное действие? Платёж? "Любая активность" — плохой выбор, потому что включает email open и push view.
• Период. Дневные / недельные / месячные когорты? Дневные — для онбординга, недельные — стандарт SaaS, месячные — для контракт-based.
• Горизонт. Сколько периодов вперёд смотрим? Должен быть таким, чтобы последняя когорта имела ≥1 период данных.
• Cut-off. Берём пользователей, которые могли наблюдаться весь горизонт, или включаем неполные? Для retention curve — только полные. Для heatmap — все, но обрезаем NULL-углы.

Артефакт фазы 1

## Исследовательский вопрос
"Какова weekly retention пользователей, зарегистрированных в W01-W20 2026, измеренная по событию <X>, на горизонте 12 недель, в разрезе <канал привлечения>?"

## Решения
- Метрика: ...
- Действие = удержан: ...
- Период когорты: weekly (понедельник-воскресенье, UTC)
- Горизонт: 12 weeks
- Cut-off: только когорты с полным горизонтом для curve; все когорты для heatmap

Фаза 2. Определение когорты (cohort definition)

Тип когорты

• Acquisition cohort — по дате первого события (signup, first purchase). Самый частый тип.
• Behavioral cohort — по совершённому действию (например, "сделал onboarding step 3"). Используй, когда исследуешь impact фичи.
• Subscription cohort — по дате начала подписки. Для revenue retention.

Что включить, что исключить

• Сотрудники компании, тестовые аккаунты — исключить (фильтр по email-домену, флагу).
• Боты — исключить (UA, rate-limit, флаг).
• Возвраты (chargeback, refund в первые 7 дней) — отдельный анализ, не маскировать.
• Дубликаты пользователей (один human = несколько аккаунтов) — задокументировать ограничение, не пытаться разрешить в этом анализе.

Сегментация (важно!)

Сразу реши, по каким срезам будешь сравнивать. Обычно полезные оси:

• Канал привлечения (organic / paid / referral / partnership)
• Гео (страна или регион)
• Тип устройства (web / iOS / Android)
• Тариф (free / pro / enterprise)
• Месяц регистрации (для seasonality)

Не больше 2 осей одновременно — иначе теряешь sample size.

Артефакт фазы 2

## Cohort definition
Тип: acquisition cohort, недельная
Дата привязки: дата первого события subscription_started
Фильтры:
  - is_employee = false
  - is_bot = false
  - country IN (...) — рынки A-tier
Сегменты для сравнения: acquisition_channel × plan_tier
Сэмпл: ~12k когортных пользователей за 20 недель

Фаза 3. SQL / запрос

Стандартный шаблон для cohort × age (период N после привязки):

WITH cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('week', MIN(event_ts)) AS cohort_week,
    -- сразу прицепляем сегменты
    FIRST_VALUE(acquisition_channel) OVER (PARTITION BY user_id ORDER BY event_ts) AS channel,
    FIRST_VALUE(plan_tier) OVER (PARTITION BY user_id ORDER BY event_ts) AS plan_tier
  FROM {{event_source}}
  WHERE event_name = 'subscription_started'
    AND is_employee = false
  GROUP BY user_id
),
activity AS (
  SELECT
    e.user_id,
    DATE_TRUNC('week', e.event_ts) AS active_week
  FROM events e
  WHERE e.event_name IN ('<полезные события>')
  GROUP BY e.user_id, DATE_TRUNC('week', e.event_ts)
),
joined AS (
  SELECT
    c.cohort_week,
    c.channel,
    c.plan_tier,
    c.user_id,
    DATE_DIFF('week', c.cohort_week, a.active_week) AS week_n
  FROM cohort c
  LEFT JOIN activity a ON a.user_id = c.user_id
  WHERE a.active_week >= c.cohort_week
    AND DATE_DIFF('week', c.cohort_week, a.active_week) <= 12
)
SELECT
  cohort_week,
  channel,
  plan_tier,
  week_n,
  COUNT(DISTINCT user_id) AS retained_users,
  COUNT(DISTINCT user_id) * 1.0 /
    FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
      PARTITION BY cohort_week, channel, plan_tier ORDER BY week_n
    ) AS retention_rate
FROM joined
GROUP BY cohort_week, channel, plan_tier, week_n
ORDER BY cohort_week, channel, plan_tier, week_n;

Адаптируй под {{engine}} (DATE_DIFF — BigQuery; в Postgres (active_week - cohort_week)/7).

Sanity-чеки запроса

• week_n = 0 retention должен быть 100% для всех когорт. Если нет — баг в JOIN.
• Размер когорты (week_n = 0) должен совпадать с прямым COUNT(DISTINCT user_id) из CTE cohort.
• Сумма retained_users по неделям не должна превышать размер когорты.
• Последние когорты должны иметь меньше week_n — это нормально, не баг.

Артефакт фазы 3

• Запрос с комментариями
• Результат sanity-чеков (3 числа)
• CSV/таблица: cohort_week × week_n × segment → retention_rate

Фаза 4. Визуализация

4.1 Heatmap (acquisition cohort × age)

• Ось Y: cohort_week (новые сверху или снизу — зафиксируй)
• Ось X: week_n (0..12)
• Цвет: retention_rate, шкала 0-100%, viridis или RdYlGn
• Аннотируй размер когорты в подписи строки: W08 (n=420)
• Углы NULL (последние когорты × дальние недели) — серым, не белым

4.2 Retention curve (overlay)

• Ось X: week_n
• Ось Y: retention_rate %
• Несколько линий — по сегменту (channel или plan_tier)
• Логарифмический X? Нет — данные равноинтервальные.
• Доверительный интервал — Wilson 95% для каждой точки.
• Маркер первой "плоской" недели — там pure retention начинается.

4.3 N-day retention bar (для презентации руководству)

Только три числа: D1, D7, D30 retention. Сравнение с прошлым кварталом. Без когортной разбивки.

Артефакт фазы 4

Три графика с подписями. В подписи каждого: что показано, размер сэмпла, период данных, метод.

Фаза 5. Интерпретация

Что означают drops

Сравнение когорт

• Diagonals в heatmap — это календарные даты. Если diagonal плохой — значит был внешний инцидент в эту неделю.
• Rows (cohort_week) — качество привлечения этой недели. Плохая строка = плохое качество трафика этого периода.
• Columns (week_n) — структурное поведение продукта. Плохая колонка = product-level проблема.

Segment splits

Если кривые разных сегментов сильно расходятся — не усредняй. Aggregate retention будет mislead. Покажи каждый сегмент отдельно и взвешенный средний.

Артефакт фазы 5

## Ключевые наблюдения
1. Weekly retention упал на 7pp в Q1 (с 38% до 31% на week 4).
2. Падение сосредоточено в paid-acquisition cohort, organic стабилен.
3. Worst diagonal — W08 (релиз billing v2, известный incident).
4. После week 8 кривая выходит на плато 22% — это "true users", их количество стабильно.

## Гипотезы (ранжированы по правдоподобности)
- H1 (вероятно): paid-канал привёл низкокачественный трафик в Q1
- H2 (вероятно): онбординг для paid-пользователей рассинхронизирован после billing v2
- H3 (возможно): seasonal — Q1 исторически хуже

Фаза 6. План действий

Не "проведём ещё один анализ". Конкретные action items с owner и сроком.

Правила выбора N действий

• N = 3 для exec-аудитории, N = 5-7 для команды.
• Каждое действие должно убирать одну гипотезу или менять одну метрику.
• Для каждого: ожидаемый impact, effort (S/M/L), owner, deadline, как мы поймём что сработало.

Шаблон action item

[H1] Аудит paid-channel quality в Q1
  Owner: marketing analytics
  Effort: M (2 недели)
  Impact: понять, какой sub-channel плох; restructure budget
  Success: identified problem sub-channels с >80% confidence
  Deadline: 2026-06-15

Артефакт фазы 6

## Action plan (top 3 для exec)
1. ...
2. ...
3. ...

## Follow-up анализы
- Funnel первой сессии по paid vs organic
- Cohort retention с разбивкой по sub-channel
- A/B тест: новый onboarding для paid-cohort

## Что НЕ делаем сейчас (и почему)
- Не меняем pricing — нет сигнала что это причина
- Не редизайним продукт — падение сегментное, не глобальное

Финальный deliverable

Один документ:

1. TL;DR (3 строки — наблюдение / причина / действие)
2. Method (фазы 1-2 сжато)
3. Findings (фазы 4-5 с графиками)
4. Recommendations (фаза 6)
5. Appendix: SQL, sanity-чеки, ограничения данных

Анти-паттерны

• ❌ Один график "общий retention" без сегментации — Simpson's paradox прячется именно здесь
• ❌ Считать retention по "любой активности" — включает email/push, ничего не значит
• ❌ Сравнивать когорты разного horizon в одной таблице — обрезай до общего минимума
• ❌ Среднее retention по 4 неделям — теряешь форму кривой, главную информацию
• ❌ Action plan на 15 пунктов — никто не сделает, выбери top 3
• ❌ "Retention упал на 5%" без CI — может быть шум; всегда показывай sample size
• ❌ Heatmap без аннотации n= в строке — невозможно отличить шум от сигнала
• ❌ "Давайте проведём ещё один анализ" вместо действия — отложенный паралич