Когортный анализ на SQL: retention и отток пользователей

Когортный анализ — один из базовых инструментов продуктовой аналитики. Он отвечает на вопрос: насколько хорошо продукт удерживает пользователей? Без SQL тут не обойтись.

Что такое когортный анализ

Когорта — группа пользователей, объединённых общим событием в одном временном периоде. Чаще всего это месяц или неделя регистрации.

Типичный результат когортного анализа — матрица:

Каждая строка — новая когорта. Столбцы — какой процент остался активным через N месяцев.

Шаг 1: Определить когорту пользователя

Начнём с базовой структуры данных. Предположим, есть таблицы users и sessions (или orders, logins — любые события активности).

-- Находим дату первой активности (= когорту) для каждого пользователя
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM sessions
    GROUP BY user_id
)
SELECT * FROM user_cohorts LIMIT 5;

DATE_TRUNC('month', ...) обрезает дату до начала месяца — это и есть «метка» когорты.

Шаг 2: Retention за первую неделю

Самая важная метрика — Week-1 Retention: какой процент пользователей вернулся на второй неделе.

WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('week', MIN(created_at)) AS cohort_week
    FROM sessions
    GROUP BY user_id
),
activity AS (
    SELECT DISTINCT
        s.user_id,
        DATE_TRUNC('week', s.created_at) AS activity_week
    FROM sessions s
)
SELECT
    c.cohort_week,
    COUNT(DISTINCT c.user_id)                                          AS cohort_size,
    COUNT(DISTINCT CASE
        WHEN a.activity_week = c.cohort_week + INTERVAL '1 week'
        THEN a.user_id
    END)                                                               AS retained_week1,
    ROUND(
        100.0 * COUNT(DISTINCT CASE
            WHEN a.activity_week = c.cohort_week + INTERVAL '1 week'
            THEN a.user_id
        END) / COUNT(DISTINCT c.user_id), 1
    )                                                                  AS retention_pct
FROM user_cohorts c
LEFT JOIN activity a ON c.user_id = a.user_id
GROUP BY c.cohort_week
ORDER BY c.cohort_week;

Шаг 3: Полная когортная матрица

Ключевой запрос — вычислить для каждой пары (когорта, период_активности) разницу в месяцах. Это и есть номер колонки матрицы.

WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM sessions
    GROUP BY user_id
),
activity_months AS (
    SELECT DISTINCT
        s.user_id,
        DATE_TRUNC('month', s.created_at) AS activity_month
    FROM sessions s
),
cohort_data AS (
    SELECT
        c.cohort_month,
        -- Сколько месяцев прошло от когорты до активности
        EXTRACT(YEAR FROM AGE(a.activity_month, c.cohort_month)) * 12 +
        EXTRACT(MONTH FROM AGE(a.activity_month, c.cohort_month)) AS month_number,
        COUNT(DISTINCT c.user_id) AS users
    FROM user_cohorts c
    JOIN activity_months a ON c.user_id = a.user_id
    GROUP BY c.cohort_month, month_number
),
cohort_sizes AS (
    SELECT cohort_month, users AS cohort_size
    FROM cohort_data
    WHERE month_number = 0
)
SELECT
    d.cohort_month,
    s.cohort_size,
    d.month_number,
    d.users,
    ROUND(100.0 * d.users / s.cohort_size, 1) AS retention_pct
FROM cohort_data d
JOIN cohort_sizes s ON d.cohort_month = s.cohort_month
ORDER BY d.cohort_month, d.month_number;

Шаг 4: Поворот матрицы через crosstab

Для красивой матрицы используем расширение tablefunc:

-- Сначала подключаем расширение (один раз на БД)
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    $$
    SELECT
        cohort_month::text,
        month_number::int,
        ROUND(100.0 * users / cohort_size, 1)
    FROM cohort_matrix   -- результат предыдущего запроса, сохранённый как VIEW
    ORDER BY 1, 2
    $$,
    $$SELECT generate_series(0, 6)$$
) AS ct (
    cohort_month  text,
    m0  numeric, m1  numeric, m2  numeric,
    m3  numeric, m4  numeric, m5  numeric, m6  numeric
);

Результат — строки по когортам, колонки по месяцам. Именно такую таблицу можно вставить в дашборд или Notion.

Анализ оттока (Churn)

Churn — это дополнение retention до 100%. Но иногда считают его точнее: пользователь считается ушедшим, если не был активен N дней подряд.

WITH last_activity AS (
    SELECT
        user_id,
        MAX(created_at) AS last_seen
    FROM sessions
    WHERE created_at >= NOW() - INTERVAL '90 days'
    GROUP BY user_id
)
SELECT
    CASE
        WHEN last_seen >= NOW() - INTERVAL '7 days'  THEN 'Активный'
        WHEN last_seen >= NOW() - INTERVAL '30 days' THEN 'Засыпающий'
        WHEN last_seen >= NOW() - INTERVAL '60 days' THEN 'Отток (30-60 дней)'
        ELSE                                               'Потерян (60+ дней)'
    END AS user_segment,
    COUNT(*) AS cnt
FROM last_activity
GROUP BY user_segment
ORDER BY cnt DESC;

Частые ошибки

1. Не используют DISTINCT при подсчёте пользователей. Один пользователь может сделать 10 событий за день — COUNT(user_id) даст неверный результат, нужен COUNT(DISTINCT user_id).

2. Путают дату регистрации и дату первой активности. Пользователь мог зарегистрироваться, но ничего не сделать. Решайте заранее, что считать «стартом когорты».

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

WHERE cohort_month < DATE_TRUNC('month', NOW())

Хотите потренироваться в когортном анализе и оконных функциях на реальных задачах? Заходите в тренажёр SQLlab.