Построение когорт по дате регистрации

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

Основы построения когорт

Ключевая идея когортного анализа — зафиксировать момент "рождения" пользователя в системе и затем измерять его активность относительно этой точки отсчета. Для этого используется концепция возраста когорты (cohort age) — количество дней, недель или месяцев, прошедших с момента первого действия пользователя.

Функция DATE_TRUNC является основным инструментом для группировки дат в когорты. Она "обрезает" дату до указанной точности (день, неделя, месяц), приводя все даты внутри периода к одному значению:

-- Группировка по месяцам
DATE_TRUNC('month', registration_date) -- '2024-03-15' → '2024-03-01'

-- Группировка по неделям (начало недели — понедельник)
DATE_TRUNC('week', registration_date)  -- '2024-03-15' → '2024-03-11'

-- Группировка по дням
DATE_TRUNC('day', registration_date)   -- оставляет дату без изменений

Практический пример: создание когорт пользователей

Предположим, у нас есть таблица users с датами регистрации и таблица events с активностью пользователей. Первый шаг — определить когорту для каждого пользователя:

WITH user_cohorts AS (
    SELECT 
        user_id,
        registration_date,
        DATE_TRUNC('month', registration_date) AS cohort_month
    FROM users
)
SELECT 
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohorts
GROUP BY cohort_month
ORDER BY cohort_month;

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

Расчет возраста когорты

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

WITH user_cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('week', registration_date) AS cohort_week
    FROM users
),
user_activity AS (
    SELECT 
        e.user_id,
        e.event_date,
        uc.cohort_week,
        DATE_TRUNC('week', e.event_date) AS activity_week
    FROM events e
    JOIN user_cohorts uc ON e.user_id = uc.user_id
)
SELECT 
    user_id,
    cohort_week,
    activity_week,
    -- Возраст когорты в неделях
    (activity_week - cohort_week) / 7 AS cohort_age_weeks
FROM user_activity;

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

Для более гибкого расчета возраста когорты используются интервалы:

SELECT 
    user_id,
    cohort_month,
    event_date,
    -- Разница в месяцах
    EXTRACT(YEAR FROM AGE(event_date, cohort_month)) * 12 + 
    EXTRACT(MONTH FROM AGE(event_date, cohort_month)) AS months_since_registration
FROM user_cohorts uc
JOIN events e ON uc.user_id = e.user_id;

Функция AGE() возвращает интервал между двумя датами, из которого можно извлечь компоненты.

Подготовка данных для retention-анализа

Финальная структура данных для когортного анализа обычно включает три ключевых столбца:

WITH cohort_base AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', registration_date) AS cohort_month,
        registration_date
    FROM users
)
SELECT 
    cb.cohort_month,
    cb.user_id,
    DATE_TRUNC('month', e.event_date) AS activity_month,
    EXTRACT(YEAR FROM AGE(e.event_date, cb.cohort_month)) * 12 + 
    EXTRACT(MONTH FROM AGE(e.event_date, cb.cohort_month)) AS month_number
FROM cohort_base cb
LEFT JOIN events e ON cb.user_id = e.user_id
WHERE e.event_date >= cb.registration_date;

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