SQL для cohort retention analysis

Что такое cohort retention

Когортный retention — один из главных инструментов продуктовой аналитики. Он показывает, какой процент пользователей, пришедших в определённое время, возвращается в продукт через N дней/недель.

Пример: когорта пользователей, зарегистрировавшихся 1 апреля. Из 1000 человек через 7 дней активны 350. D7 retention = 35%.

Разные когорты сравниваются между собой. Если retention новой когорты упал — что-то изменилось: релиз, качество трафика, сезонность.

Построить эту матрицу в SQL — навык, который спрашивают на любом продуктовом собесе.

Базовый подход: две CTE

Классическая структура запроса:

WITH cohort AS (
    -- Определяем когорту каждого пользователя
    SELECT
        user_id,
        DATE_TRUNC('week', MIN(event_time))::DATE AS cohort_week
    FROM events
    GROUP BY user_id
),
activity AS (
    -- Активности каждого пользователя с указанием когорты
    SELECT
        c.cohort_week,
        c.user_id,
        DATE_TRUNC('week', e.event_time)::DATE AS active_week
    FROM cohort c
    JOIN events e USING (user_id)
)
SELECT
    cohort_week,
    EXTRACT(WEEK FROM active_week - cohort_week)::int AS weeks_since_signup,
    COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY cohort_week, weeks_since_signup
ORDER BY cohort_week, weeks_since_signup;

Результат — long format. Для каждой когорты и каждой недели со starttime показано число активных.

Для визуализации обычно разворачивают в широкий формат (pivot). Это удобнее сделать в pandas или BI-инструменте, чем в SQL.

Retention в процентах

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

WITH cohort AS (
    SELECT user_id, DATE_TRUNC('week', MIN(event_time))::DATE AS cohort_week
    FROM events GROUP BY user_id
),
cohort_size AS (
    SELECT cohort_week, COUNT(*) AS size FROM cohort GROUP BY cohort_week
),
activity AS (
    SELECT
        c.cohort_week,
        c.user_id,
        EXTRACT(WEEK FROM DATE_TRUNC('week', e.event_time)::DATE - c.cohort_week)::int AS weeks_since
    FROM cohort c JOIN events e USING (user_id)
)
SELECT
    a.cohort_week,
    a.weeks_since,
    COUNT(DISTINCT a.user_id) AS active_users,
    cs.size AS cohort_size,
    ROUND(COUNT(DISTINCT a.user_id) * 100.0 / cs.size, 1) AS retention_pct
FROM activity a
JOIN cohort_size cs USING (cohort_week)
GROUP BY a.cohort_week, a.weeks_since, cs.size
ORDER BY a.cohort_week, a.weeks_since;

Retention_pct всегда 100% на неделе 0 (все из когорты были активны в свою неделю). Дальше падает.

Classic vs rolling retention

Есть разные определения retention.

Classic (N-day) retention. Пользователь активен ровно в день N после регистрации.

-- D7 classic retention
SELECT
    COUNT(DISTINCT a.user_id) * 100.0 / (SELECT COUNT(*) FROM cohort) AS d7_classic
FROM cohort c
JOIN activity a ON a.user_id = c.user_id
    AND a.active_day = c.cohort_day + INTERVAL '7 day';

Rolling retention. Пользователь активен в день N или позже.

-- D7 rolling retention
SELECT
    COUNT(DISTINCT a.user_id) * 100.0 / (SELECT COUNT(*) FROM cohort) AS d7_rolling
FROM cohort c
JOIN activity a ON a.user_id = c.user_id
    AND a.active_day >= c.cohort_day + INTERVAL '7 day';

Unbounded retention. Активен хотя бы раз после регистрации. Только один раз снизу граница.

Разные определения дают разные цифры. Для одних и тех же данных classic D7 может быть 20%, rolling D7 — 35%, unbounded — 45%. Всегда уточняйте, какое определение используется.

Разбираться в тонкостях когортного анализа — основа продуктовой аналитики. В тренажёре Карьерник есть задачи на когорты разной сложности с разборами.

Retention matrix в одном запросе

Для широкого формата (который отображается как таблица), можно использовать CASE WHEN:

WITH cohort AS (
    SELECT user_id, DATE_TRUNC('week', MIN(event_time))::DATE AS cohort_week
    FROM events GROUP BY user_id
),
retention AS (
    SELECT
        c.cohort_week,
        c.user_id,
        BOOL_OR(e.event_time >= c.cohort_week + INTERVAL '1 week'
                AND e.event_time < c.cohort_week + INTERVAL '2 week') AS w1,
        BOOL_OR(e.event_time >= c.cohort_week + INTERVAL '2 week'
                AND e.event_time < c.cohort_week + INTERVAL '3 week') AS w2,
        BOOL_OR(e.event_time >= c.cohort_week + INTERVAL '3 week'
                AND e.event_time < c.cohort_week + INTERVAL '4 week') AS w3,
        BOOL_OR(e.event_time >= c.cohort_week + INTERVAL '4 week'
                AND e.event_time < c.cohort_week + INTERVAL '5 week') AS w4
    FROM cohort c
    LEFT JOIN events e USING (user_id)
    GROUP BY c.cohort_week, c.user_id
)
SELECT
    cohort_week,
    COUNT(*) AS cohort_size,
    SUM(CASE WHEN w1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS w1_retention,
    SUM(CASE WHEN w2 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS w2_retention,
    SUM(CASE WHEN w3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS w3_retention,
    SUM(CASE WHEN w4 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS w4_retention
FROM retention
GROUP BY cohort_week
ORDER BY cohort_week;

Результат сразу в широком формате: строки — когорты, столбцы — W1, W2, W3, W4 retention.

Retention с сегментацией

Часто нужно сравнить retention по каналам или платформам. Добавляем сегмент в когорту:

WITH cohort AS (
    SELECT
        u.user_id,
        u.acquisition_channel,
        DATE_TRUNC('week', u.registered_at)::DATE AS cohort_week
    FROM users u
)
SELECT
    c.cohort_week,
    c.acquisition_channel,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT CASE
        WHEN a.active_date >= c.cohort_week + INTERVAL '7 day'
        AND a.active_date < c.cohort_week + INTERVAL '14 day'
        THEN a.user_id END
    ) AS d7_active
FROM cohort c
LEFT JOIN activity a USING (user_id)
GROUP BY c.cohort_week, c.acquisition_channel
ORDER BY c.cohort_week, d7_active DESC;

Теперь видно retention по каждому каналу привлечения. Обычно organic имеет лучший retention, чем performance.

Ловушки

Новые когорты ещё не имеют данных. Когорта этой недели не может иметь W4 retention — прошло меньше 4 недель. Для retention matrix это приведёт к NULL или 0 в правом верхнем углу.

Решение — фильтровать когорты по возрасту:

WHERE cohort_week <= CURRENT_DATE - INTERVAL '4 week'

Оставлять только когорты, которым хватило времени.

Неправильная работа с timezone. Если события в UTC, а бизнес в МСК, граница «день регистрации» отличается на 3 часа. Для пользователей, зарегистрированных в 23:30 МСК, retention D7 будет посчитан неправильно. Приводите к бизнес-timezone через AT TIME ZONE.

Активность ≠ ценность. Retention на «открытие приложения» может быть 50%, но если пользователи только открывают и закрывают — это плохой retention. Лучше считать retention на «meaningful action» — совершил покупку, послал сообщение, посмотрел N контента.

Продвинутые паттерны

Weekly cohorts vs monthly. Для продукта с долгим lifecycle (SaaS B2B, страховка) weekly слишком шумные. Используйте monthly.

Rolling cohorts. Вместо строгого «когорта 1 апреля» можно «когорта первой недели апреля», «первой декады» и т.д. Сглаживает.

Retention curves for visualization. Для графиков — линия на когорту, где y — % retention, x — дни с регистрации. Видно, какие когорты лучше/хуже.

Compare pre/post launch. Разбить когорты на «до релиза» и «после», сравнить retention curves. Если пост-релизные выше — фича работает.

pandas для дальнейшей обработки

SQL даёт long format, для визуализации удобнее pandas pivot:

import pandas as pd

df = pd.read_sql("""
    -- SQL из примера выше
""", conn)

cohort_matrix = df.pivot(
    index='cohort_week',
    columns='weeks_since',
    values='retention_pct'
)

# Красивая визуализация
import seaborn as sns
import matplotlib.pyplot as plt

plt.figure(figsize=(14, 8))
sns.heatmap(cohort_matrix, annot=True, fmt='.0f', cmap='YlGnBu')
plt.title('Retention Cohort Heatmap')
plt.show()

Heatmap — классический способ показать retention матрицу. Строки — когорты, столбцы — недели, цвет — процент retention.

Читайте также

• Задачи на когортный анализ
• Как считать retention
• Когортный анализ в SQL
• Когортный анализ: гайд

FAQ

Weekly или monthly когорты?

Weekly для молодых продуктов (до года). Monthly для зрелых с длинным lifecycle. Daily — только для очень интенсивных продуктов вроде игр.

Как считать, если у пользователя несколько first-events разных типов?

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

Что делать с пользователями-ботами?

Фильтровать в cohort CTE. Обычно есть флаг is_bot или user_type = 'real'.

Retention упал — это всегда плохо?

Не всегда. Если вы агрессивно привлекаете новую аудиторию, новые когорты могут иметь хуже retention, чем старые. Смотрите total active users — они могут расти, несмотря на падающий retention.