PrepBro
← Назад к вопросам

SQL: Retention пользователей по когортам

2.0 Middle🔥 141 комментариев
#SQL и базы данных#Статистика и A/B тестирование

Условие

Дана таблица событий пользователей с полями: user_id, event_date, event_type.

Рассчитайте retention по когортам (месяц регистрации) за первые 3 месяца.

Когорта определяется по дате первого события пользователя.

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

SQL: Retention пользователей по когортам

Определения

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

Retention — процент пользователей из когорты, вернувшихся в конкретный период

Retention по месяцам:

  • Month 0 (M0): период регистрации (100% по определению)
  • Month 1 (M1): процент, активных в месяц после регистрации
  • Month 2 (M2): процент, активных спустя 2 месяца после регистрации
  • и т.д.

SQL Решение

Шаг 1: Создание таблицы данных

CREATE TABLE user_events (
    user_id INT,
    event_date DATE,
    event_type VARCHAR(50)
);

INSERT INTO user_events VALUES
(1, '2024-01-05', 'signup'),
(1, '2024-01-15', 'purchase'),
(1, '2024-02-10', 'login'),
(2, '2024-01-10', 'signup'),
(2, '2024-01-20', 'login'),
(3, '2024-02-01', 'signup'),
(3, '2024-02-15', 'purchase'),
(3, '2024-03-05', 'login'),
(4, '2024-02-05', 'signup'),
(4, '2024-03-10', 'purchase');

Шаг 2: Полный SQL запрос

WITH user_cohorts AS (
    -- Определяем когорту (месяц первого события каждого пользователя)
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(event_date))::DATE AS cohort_month
    FROM user_events
    GROUP BY user_id
),
user_activity AS (
    -- Для каждого пользователя определяем месяцы активности
    SELECT 
        uc.user_id,
        uc.cohort_month,
        DATE_TRUNC('month', ue.event_date)::DATE AS activity_month
    FROM user_cohorts uc
    INNER JOIN user_events ue ON uc.user_id = ue.user_id
    GROUP BY uc.user_id, uc.cohort_month, activity_month
),
user_months AS (
    -- Вычисляем месяц жизни пользователя (M0, M1, M2, ...)
    SELECT 
        user_id,
        cohort_month,
        activity_month,
        EXTRACT(MONTH FROM activity_month) - EXTRACT(MONTH FROM cohort_month) +
        (EXTRACT(YEAR FROM activity_month) - EXTRACT(YEAR FROM cohort_month)) * 12 
        AS months_since_cohort
    FROM user_activity
    WHERE activity_month >= cohort_month  -- Только события после регистрации
),
retention_matrix_raw AS (
    -- Считаем уникальных пользователей в каждом месяце для каждой когорты
    SELECT 
        cohort_month,
        months_since_cohort,
        COUNT(DISTINCT user_id) AS users_count
    FROM user_months
    GROUP BY cohort_month, months_since_cohort
),
cohort_sizes AS (
    -- Размер каждой когорты (M0)
    SELECT 
        cohort_month,
        users_count AS cohort_size
    FROM retention_matrix_raw
    WHERE months_since_cohort = 0
)
-- Итоговая матрица retention
SELECT 
    rmr.cohort_month,
    CASE 
        WHEN rmr.months_since_cohort = 0 THEN 'M0'
        WHEN rmr.months_since_cohort = 1 THEN 'M1'
        WHEN rmr.months_since_cohort = 2 THEN 'M2'
        WHEN rmr.months_since_cohort = 3 THEN 'M3'
    END AS period,
    rmr.users_count,
    cs.cohort_size,
    ROUND(100.0 * rmr.users_count / cs.cohort_size, 2) AS retention_percent
FROM retention_matrix_raw rmr
INNER JOIN cohort_sizes cs ON rmr.cohort_month = cs.cohort_month
WHERE rmr.months_since_cohort <= 3
ORDER BY rmr.cohort_month, rmr.months_since_cohort;

Результат

cohort_month | period | users_count | cohort_size | retention_percent
2024-01-01   | M0     | 2           | 2           | 100.00
2024-01-01   | M1     | 2           | 2           | 100.00
2024-01-01   | M2     | 1           | 2           | 50.00
2024-02-01   | M0     | 2           | 2           | 100.00
2024-02-01   | M1     | 1           | 2           | 50.00

Альтернативный подход: PIVOT таблица

WITH user_cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(event_date))::DATE AS cohort_month
    FROM user_events
    GROUP BY user_id
),
user_months AS (
    SELECT 
        uc.user_id,
        uc.cohort_month,
        EXTRACT(MONTH FROM ue.event_date) - EXTRACT(MONTH FROM uc.cohort_month) +
        (EXTRACT(YEAR FROM ue.event_date) - EXTRACT(YEAR FROM uc.cohort_month)) * 12 
        AS months_since_cohort
    FROM user_cohorts uc
    INNER JOIN user_events ue ON uc.user_id = ue.user_id
    WHERE DATE_TRUNC('month', ue.event_date) >= uc.cohort_month
    GROUP BY uc.user_id, uc.cohort_month, months_since_cohort
),
cohort_data AS (
    SELECT 
        cohort_month,
        months_since_cohort,
        COUNT(DISTINCT user_id) AS user_count
    FROM user_months
    GROUP BY cohort_month, months_since_cohort
),
cohort_sizes AS (
    SELECT 
        cohort_month,
        user_count
    FROM cohort_data
    WHERE months_since_cohort = 0
)
SELECT 
    cd.cohort_month,
    ROUND(100.0 * SUM(CASE WHEN cd.months_since_cohort = 0 THEN cd.user_count ELSE 0 END) / MAX(cs.user_count), 2) AS M0,
    ROUND(100.0 * SUM(CASE WHEN cd.months_since_cohort = 1 THEN cd.user_count ELSE 0 END) / MAX(cs.user_count), 2) AS M1,
    ROUND(100.0 * SUM(CASE WHEN cd.months_since_cohort = 2 THEN cd.user_count ELSE 0 END) / MAX(cs.user_count), 2) AS M2,
    ROUND(100.0 * SUM(CASE WHEN cd.months_since_cohort = 3 THEN cd.user_count ELSE 0 END) / MAX(cs.user_count), 2) AS M3
FROM cohort_data cd
CROSS JOIN cohort_sizes cs ON cd.cohort_month = cs.cohort_month
GROUP BY cd.cohort_month;

Ключевые техники SQL

1. CTE (WITH) — для разбиения задачи:

WITH step1 AS (SELECT ...),
     step2 AS (SELECT ... FROM step1),
     step3 AS (SELECT ... FROM step2)
SELECT * FROM step3;

2. DATE_TRUNC — для группировки по месяцам:

DATE_TRUNC('month', event_date)  -- '2024-01-01'
DATE_TRUNC('year', event_date)   -- '2024-01-01'

3. EXTRACT — для вычисления разниц между датами:

EXTRACT(MONTH FROM date)
EXTRACT(YEAR FROM date)
EXTRACT(DAY FROM date)

4. Расчёт месяцев между датами:

EXTRACT(MONTH FROM activity_month) - EXTRACT(MONTH FROM cohort_month) +
(EXTRACT(YEAR FROM activity_month) - EXTRACT(YEAR FROM cohort_month)) * 12

Оптимизация для больших данных

-- Индексы для ускорения запроса
CREATE INDEX idx_user_events_user_id ON user_events(user_id);
CREATE INDEX idx_user_events_event_date ON user_events(event_date);
CREATE INDEX idx_user_events_user_date ON user_events(user_id, event_date);

Интерпретация результатов

Здоровая когорта:

  • M0: 100%
  • M1: 40-60% (нормально терять 40-60% новых пользователей)
  • M2: 20-40%
  • M3: 10-25%

Признаки проблем:

  • M1 < 20% — плохая onboarding
  • Резкий drop между M1 и M2 — проблемы с product
  • Улучшение retention в поздних месяцах — хорошие улучшения

Python для анализа результатов

import pandas as pd
import matplotlib.pyplot as plt

# Загрузка данных из БД
query = """SELECT * FROM retention_matrix;"""
df = pd.read_sql(query, connection)

# Pivot таблица
pivot_table = df.pivot(index='cohort_month', columns='period', values='retention_percent')

# Визуализация
plt.figure(figsize=(12, 6))
for cohort in pivot_table.index:
    plt.plot(pivot_table.columns, pivot_table.loc[cohort], marker='o', label=str(cohort))

plt.xlabel('Month')
plt.ylabel('Retention %')
plt.title('Cohort Retention Over Time')
plt.legend()
plt.grid(True, alpha=0.3)
plt.show()

# Среднее retention
print("Average Retention by Period:")
print(pivot_table.mean())

Альтернативные метрики

N-day Retention (например, 7-day, 30-day):

SELECT 
    user_id,
    DATE(MIN(event_date)) AS first_day,
    MAX(CASE WHEN event_date <= DATE(MIN(event_date)) + INTERVAL 7 DAY 
             AND DATE(MIN(event_date)) < event_date 
             THEN 1 ELSE 0 END) AS returned_in_7_days
FROM user_events
GROUP BY user_id;

Rolling Retention: Процент пользователей, активных в конкретный день из когорты, не зависимо от предыдущих дней.

Churn Rate (обратный retention):

churn_rate = 1 - retention_rate

Выводы

  • Когортный анализ показывает жизненный цикл пользователей
  • SQL позволяет эффективно считать retention для больших данных
  • Нужны индексы для оптимизации на миллионах записей
  • Визуализация матрицы retention помогает увидеть тренды