Когортный
анализ: SQL, heatmap
и интерпретация

Когорта — группа пользователей, объединённых по времени какого-то события. Чаще всего — по месяцу регистрации. Все, кто зарегистрировался в январе — когорта «Январь». В феврале — когорта «Февраль».

Когортный анализ — это способ отслеживать поведение этих групп со временем. Вместо того чтобы смотреть «средний retention за месяц» (который смешивает всех пользователей в кучу), вы смотрите: «как ведёт себя январская когорта через 1, 2, 3 месяца? А февральская?».

Это даёт принципиально другую картину. Общий retention может стоять на месте, а в когортах — расти. Или наоборот: общее среднее растёт за счёт притока новых, а старые когорты гибнут.

Почему средние врут, а когорты — нет

Зачем аналитику когортный анализ

Запрос 1: Когортный Retention (основной)

Самый важный запрос — его спрашивают на каждом втором собесе. Строит полную матрицу «когорта × период» с процентом retention.

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', registration_date) AS cohort
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', activity_date) AS active_month
  FROM user_activity
),
cohort_data AS (
  SELECT
    c.cohort,
    DATE_PART('month',
      AGE(a.active_month, c.cohort)
    )::INT AS period,
    COUNT(DISTINCT c.user_id) AS active_users
  FROM cohorts c
  JOIN activity a ON c.user_id = a.user_id
  WHERE a.active_month >= c.cohort
  GROUP BY c.cohort, period
)
SELECT
  cohort,
  period,
  active_users,
  ROUND(100.0 * active_users
    / FIRST_VALUE(active_users) OVER (
        PARTITION BY cohort ORDER BY period
      ), 1
  ) AS retention_pct
FROM cohort_data
ORDER BY cohort, period;

Запрос 2: Когортный Revenue (LTV по когортам)

WITH cohorts AS (
  SELECT user_id,
    DATE_TRUNC('month', registration_date) AS cohort
  FROM users
),
cohort_revenue AS (
  SELECT
    c.cohort,
    DATE_PART('month',
      AGE(DATE_TRUNC('month', o.order_date), c.cohort)
    )::INT AS period,
    SUM(o.amount) AS revenue,
    COUNT(DISTINCT c.user_id) AS cohort_size
  FROM cohorts c
  JOIN orders o ON c.user_id = o.user_id
    AND o.order_date >= c.cohort
  GROUP BY c.cohort, period
)
SELECT
  cohort, period,
  ROUND(revenue / cohort_size, 0) AS arpu_period,
  ROUND(
    SUM(revenue) OVER (
      PARTITION BY cohort ORDER BY period
    ) / cohort_size, 0
  ) AS cumulative_ltv
FROM cohort_revenue
ORDER BY cohort, period;

Этот запрос строит кривую LTV для каждой когорты. Можно наложить кривые друг на друга и увидеть: новые когорты платят больше или меньше, чем старые?

Запрос 3: Когортная таблица для pivot / heatmap

Чтобы визуализировать heatmap, нужно «развернуть» таблицу — столбцы = периоды. В PostgreSQL это можно сделать через условную агрегацию.

WITH base AS (
  -- используем результат запроса 1 (cohort, period, retention_pct)
  ...
)
SELECT
  cohort,
  MAX(CASE WHEN period = 0 THEN retention_pct END) AS m0,
  MAX(CASE WHEN period = 1 THEN retention_pct END) AS m1,
  MAX(CASE WHEN period = 2 THEN retention_pct END) AS m2,
  MAX(CASE WHEN period = 3 THEN retention_pct END) AS m3,
  MAX(CASE WHEN period = 4 THEN retention_pct END) AS m4,
  MAX(CASE WHEN period = 5 THEN retention_pct END) AS m5
FROM base
GROUP BY cohort
ORDER BY cohort;

Heatmap — стандартный способ визуализации когортного анализа. Строки — когорты, столбцы — периоды жизни, ячейки — retention. Цвет кодирует значение: яркий = высокий, тусклый = низкий.

Три направления чтения

Паттерн 1: Плато — продукт нашёл ядро

Retention падает первые 2–3 месяца, потом стабилизируется. Это значит, что часть пользователей нашла ценность и остаётся надолго. Это хорошо. Чем выше плато — тем сильнее product-market fit. Для e-commerce плато 10–15% — нормально. Для SaaS 60–70% — ожидаемо.

Паттерн 2: Retention не стабилизируется — протечка

Каждый месяц retention продолжает падать без выхода на плато. Кривая стремится к нулю. Это плохо. Продукт не удерживает ядро. Нужно срочно разбираться: что не так с ценностью, онбордингом, повторным вовлечением.

Паттерн 3: Улучшение по когортам — продукт растёт

M1 retention: 38% → 41% → 45% → 48% — каждая следующая когорта лучше удерживается. Это отлично. Значит, изменения в продукте работают. Важно: убедитесь, что рост не объясняется изменением микса каналов привлечения.

Паттерн 4: Деградация по когортам — тревога

M1: 48% → 45% → 41% → 38% — каждая следующая когорта хуже. Нужно разбираться. Причины: сменился канал привлечения (пришли менее целевые), сломался онбординг, конкуренты перехватывают, сезонность.

Паттерн 5: Одна когорта выбивается — ищите событие

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

Retention — самый популярный, но не единственный вариант когортного анализа. Те же принципы работают для любой метрики, которую вы хотите отследить по когортам.

Когортный анализ по каналам привлечения

Мощный приём: строить отдельные когортные таблицы для каждого канала. Это показывает не просто «сколько стоит привлечение», а «какое качество пользователей приводит каждый канал». Канал с дешёвым CAC, но плохим retention — возможно, не лучший выбор.

WITH cohorts AS (
  SELECT
    user_id,
    acquisition_channel,
    DATE_TRUNC('month', registration_date) AS cohort
  FROM users
),
activity AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', activity_date) AS active_month
  FROM user_activity
)
SELECT
  c.acquisition_channel,
  c.cohort,
  DATE_PART('month', AGE(a.active_month, c.cohort))::INT AS period,
  COUNT(DISTINCT c.user_id) AS active_users,
  ROUND(100.0 * COUNT(DISTINCT c.user_id)
    / FIRST_VALUE(COUNT(DISTINCT c.user_id)) OVER (
        PARTITION BY c.acquisition_channel, c.cohort
        ORDER BY DATE_PART('month', AGE(a.active_month, c.cohort))
      ), 1
  ) AS retention_pct
FROM cohorts c
JOIN activity a ON c.user_id = a.user_id
WHERE a.active_month >= c.cohort
GROUP BY c.acquisition_channel, c.cohort,
  DATE_PART('month', AGE(a.active_month, c.cohort))
ORDER BY 1, 2, 3;