Когортный анализ SQL: кто вернулся через месяц

SQL Studio / Разборы SQL / Когортный анализ SQL: кто вернулся через месяц

Когорта — клиенты, у которых первая покупка пришлась на один период, например месяц. M0 — месяц первой покупки, M1 — следующий календарный месяц, M2 — через два месяца. Здесь retention означает долю клиентов когорты, купивших в конкретном месяце, а не долю всех, кто когда-либо вернулся.

1. Определить месяц первой покупки

Создадим десять событий пяти клиентов прямо в запросе. У клиента №3 две покупки в феврале, но при расчёте размера когорты он должен считаться один раз.

WITH e(customer_id, day) AS (
  VALUES
    (1, '2026-01-03'), (1, '2026-02-05'), (1, '2026-03-09'),
    (2, '2026-01-22'), (2, '2026-03-01'),
    (3, '2026-02-11'), (3, '2026-02-20'),
    (4, '2026-02-16'), (4, '2026-03-15'),
    (5, '2026-03-02')
),
first_month AS (
  SELECT customer_id, MIN(substr(day, 1, 7)) AS cohort_ym
  FROM e
  GROUP BY customer_id
)
SELECT cohort_ym, COUNT(*) AS customers_n
FROM first_month
GROUP BY cohort_ym
ORDER BY cohort_ym;

2. Посчитать помесячный retention

Сначала определяем когорту каждого человека, затем для каждого события считаем номер месяца относительно первого. DISTINCT оставляет одного клиента на пару «когорта — месяц», даже если он сделал несколько покупок.

WITH e(customer_id, day) AS (
  VALUES
    (1, '2026-01-03'), (1, '2026-02-05'), (1, '2026-03-09'),
    (2, '2026-01-22'), (2, '2026-03-01'),
    (3, '2026-02-11'), (3, '2026-02-20'),
    (4, '2026-02-16'), (4, '2026-03-15'),
    (5, '2026-03-02')
),
first_month AS (
  SELECT customer_id, MIN(substr(day, 1, 7)) AS cohort_ym
  FROM e GROUP BY customer_id
),
activity AS (
  SELECT DISTINCT f.cohort_ym, e.customer_id,
         (CAST(substr(e.day, 1, 4) AS INTEGER) * 12
          + CAST(substr(e.day, 6, 2) AS INTEGER))
       - (CAST(substr(f.cohort_ym, 1, 4) AS INTEGER) * 12
          + CAST(substr(f.cohort_ym, 6, 2) AS INTEGER)) AS month_n
  FROM e JOIN first_month AS f USING (customer_id)
),
sizes AS (
  SELECT cohort_ym, COUNT(*) AS cohort_size
  FROM first_month GROUP BY cohort_ym
)
SELECT a.cohort_ym, a.month_n,
       COUNT(*) AS active_customers, s.cohort_size,
       ROUND(100.0 * COUNT(*) / s.cohort_size, 2) AS retention_pct
FROM activity AS a
JOIN sizes AS s USING (cohort_ym)
GROUP BY a.cohort_ym, a.month_n, s.cohort_size
ORDER BY a.cohort_ym, a.month_n;

Человек может не купить в M1, но вернуться в M2. Поэтому помесячный retention не обязан монотонно уменьшаться. Клиента, совершившего две покупки в одном месяце, мы считаем только один раз.

3. Не присваивать ноль месяцу, который ещё не наступил

Последний месяц в примере — март. Для мартовской когорты M1 относится к апрелю, которого в данных нет. Поэтому в отчёт за M1 включим только когорты, у которых следующий месяц уже был доступен для наблюдения.

WITH e(customer_id, day) AS (
  VALUES
    (1, '2026-01-03'), (1, '2026-02-05'), (1, '2026-03-09'),
    (2, '2026-01-22'), (2, '2026-03-01'),
    (3, '2026-02-11'), (3, '2026-02-20'),
    (4, '2026-02-16'), (4, '2026-03-15'),
    (5, '2026-03-02')
),
first_month AS (
  SELECT customer_id, MIN(substr(day, 1, 7)) AS cohort_ym
  FROM e GROUP BY customer_id
),
m1_buyers AS (
  SELECT DISTINCT f.cohort_ym, e.customer_id
  FROM first_month AS f JOIN e ON e.customer_id = f.customer_id
  WHERE (CAST(substr(e.day, 1, 4) AS INTEGER) * 12
         + CAST(substr(e.day, 6, 2) AS INTEGER))
      - (CAST(substr(f.cohort_ym, 1, 4) AS INTEGER) * 12
         + CAST(substr(f.cohort_ym, 6, 2) AS INTEGER)) = 1
),
sizes AS (
  SELECT cohort_ym, COUNT(*) AS cohort_size
  FROM first_month GROUP BY cohort_ym
)
SELECT s.cohort_ym, s.cohort_size,
       COUNT(m.customer_id) AS m1_buyers,
       ROUND(100.0 * COUNT(m.customer_id) / s.cohort_size, 2) AS m1_pct
FROM sizes AS s
LEFT JOIN m1_buyers AS m USING (cohort_ym)
WHERE s.cohort_ym < (SELECT MAX(substr(day, 1, 7)) FROM e)
GROUP BY s.cohort_ym, s.cohort_size
ORDER BY s.cohort_ym;

Результат: январь — 50%, февраль — 50%. Март не попал в таблицу, вместо ложных 0%. Для настоящих данных дата окончания наблюдения должна задаваться отдельно; максимум даты покупки здесь используется только как удобная граница учебного набора.

Попробовать на практике

Сначала пронумеруйте покупки клиентов, затем запустите когортные запросы в песочнице: каждый пример полностью самодостаточен.

По теме: повторные покупки · RFM-сегментация · границы периода.

Используемые функции строк и дат описаны в документации SQLite. Правила когорт — определения из текста, а не стандарт SQLite.

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.