Расчёт retention и горизонт наблюдения

Арифметика календарных месяцев: вычисление номера месяца активности

Для расчёта когортного удержания (retention) каждое последующее действие клиента нужно соотнести с моментом его первой покупки или регистрации. Это смещение выражают не числом прошедших дней, а порядковым номером календарного месяца относительно старта когорты: нулевой месяц — месяц входа, первый — следующий календарный месяц и так далее.

Разница дней, делённая на 30 или 30.4375, здесь непригодна. Февраль содержит 28 или 29 дней, а действия от 31 января и 1 февраля разделены лишь одним днём, хотя принадлежат разным календарным месяцам. Чтобы получить точное целочисленное смещение month_number, вычисляют разность лет, умноженную на 12, и складывают с разностью номеров месяцев.

Подготовим схему данных для расчёта:

DROP TABLE IF EXISTS cohort_sizes;
DROP TABLE IF EXISTS user_cohorts;
DROP TABLE IF EXISTS user_actions;
DROP TABLE IF EXISTS month_grid;

CREATE TABLE cohort_sizes (
    cohort_month TEXT PRIMARY KEY,
    cohort_size INTEGER NOT NULL
);

CREATE TABLE user_cohorts (
    user_id INTEGER PRIMARY KEY,
    cohort_month TEXT NOT NULL
);

CREATE TABLE user_actions (
    action_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    action_date TEXT NOT NULL
);

CREATE TABLE month_grid (
    month_number INTEGER PRIMARY KEY
);

INSERT INTO cohort_sizes (cohort_month, cohort_size) VALUES
('2024-01-01', 4),
('2024-02-01', 2);

INSERT INTO user_cohorts (user_id, cohort_month) VALUES
(101, '2024-01-01'),
(102, '2024-01-01'),
(103, '2024-01-01'),
(104, '2024-01-01'),
(201, '2024-02-01'),
(202, '2024-02-01');

INSERT INTO user_actions (action_id, user_id, action_date) VALUES
(1, 101, '2024-01-10'),
(2, 102, '2024-01-15'),
(3, 103, '2024-01-20'),
(4, 104, '2024-01-25'),
(5, 101, '2024-02-05'),
(6, 102, '2024-02-14'),
(7, 201, '2024-02-02'),
(8, 202, '2024-02-18'),
(9, 201, '2024-03-12'),
(10, 201, '2024-03-20');

INSERT INTO month_grid (month_number) VALUES
(0), (1), (2), (3);

Соединим пользователей с их действиями и вычислим month_number для каждого события:

SELECT DISTINCT
    uc.user_id,
    uc.cohort_month,
    strftime('%Y-%m-01', ua.action_date) AS activity_month,
    (CAST(strftime('%Y', ua.action_date) AS INTEGER) - CAST(strftime('%Y', uc.cohort_month) AS INTEGER)) * 12
    + (CAST(strftime('%m', ua.action_date) AS INTEGER) - CAST(strftime('%m', uc.cohort_month) AS INTEGER)) AS month_number
FROM user_cohorts uc
JOIN user_actions ua ON uc.user_id = ua.user_id
ORDER BY uc.cohort_month, uc.user_id, activity_month;

Результат запроса:

user_id	cohort_month	activity_month	month_number
101	2024-01-01	2024-01-01	0
101	2024-01-01	2024-02-01	1
102	2024-01-01	2024-01-01	0
102	2024-01-01	2024-02-01	1
103	2024-01-01	2024-01-01	0
104	2024-01-01	2024-01-01	0
201	2024-02-01	2024-02-01	0
201	2024-02-01	2024-03-01	1
202	2024-02-01	2024-02-01	0

В строке пользователя 201 действие 2024-03-12 даёт activity_month = '2024-03-01'. При вычитании cohort_month = '2024-02-01' разность лет равна (2024 - 2024) * 12 = 0, а разность месяцев 3 - 2 = 1. Итоговый month_number равен 1. Для января этой же когорты вычисление не производилось, так как первое действие зафиксировано в феврале. Формула корректно работает и при переходе через границы календарного года: например, для января 2025 года разность лет (2025 - 2024) * 12 = 12, а разность месяцев 1 - 2 = -1, что даёт 12 + (-1) = 11 месяцев смещения.

Горизонт наблюдения на дату среза и разделение истинного нуля от недоступного будущего

Дата среза отчёта (cutoff_date) задаёт предел времени, за который в базе накоплены факты. Если анализ проводится по состоянию на 31 марта 2024 года (cutoff_month = '2024-03-01'), то мартовские события январской когорты соответствуют номеру 2: они уже могли произойти, но никто из пользователей действий не совершил. Это наблюдённый нулевой retention.

Иная ситуация у февральской когорты. Месяц 0 для неё — февраль, месяц 1 — март. Месяц 2 для февральской когорты наступит только в апреле 2024 года. На дату среза 31 марта апрель физически ещё не наступил. Если в аналитической витрине для февральской когорты на втором месяце вывести 0%, это исказит картину: будущий период будет ошибочно представлен как стопроцентный отток пользователей.

Посмотрим, к чему приводит наивное объединение когорт со справочником номеров месяцев через CROSS JOIN с последующей заменой пропусков через COALESCE(..., 0) без учёта горизонта среза:

WITH user_act_months AS (
    SELECT DISTINCT
        uc.cohort_month,
        uc.user_id,
        (CAST(strftime('%Y', ua.action_date) AS INTEGER) - CAST(strftime('%Y', uc.cohort_month) AS INTEGER)) * 12
        + (CAST(strftime('%m', ua.action_date) AS INTEGER) - CAST(strftime('%m', uc.cohort_month) AS INTEGER)) AS month_number
    FROM user_cohorts uc
    JOIN user_actions ua ON uc.user_id = ua.user_id
),
activity_totals AS (
    SELECT cohort_month, month_number, COUNT(DISTINCT user_id) AS active_users
    FROM user_act_months
    GROUP BY cohort_month, month_number
)
SELECT
    cs.cohort_month,
    mg.month_number,
    COALESCE(act.active_users, 0) AS active_users,
    ROUND(1.0 * COALESCE(act.active_users, 0) / cs.cohort_size, 4) AS naive_retention
FROM cohort_sizes cs
CROSS JOIN month_grid mg
LEFT JOIN activity_totals act
    ON cs.cohort_month = act.cohort_month AND mg.month_number = act.month_number
WHERE mg.month_number <= 2
ORDER BY cs.cohort_month, mg.month_number;

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

cohort_month	month_number	active_users	naive_retention
2024-01-01	0	4	1.0
2024-01-01	1	2	0.5
2024-01-01	2	0	0.0
2024-02-01	0	2	1.0
2024-02-01	1	1	0.5
2024-02-01	2	0	0.0

В строке ('2024-02-01', 2) появился показатель 0.0. Пользователи февральской когорты не могли совершить покупки в апреле, поскольку срез зафиксирован концом марта. Подстановка нуля исказила метрику: отсутствие календарной возможности приравнено к оттоку.

Предел наблюдения рассчитывается индивидуально для каждой когорты:

$$\text{max_month} = (Y_{\text{cutoff}} - Y_{\text{cohort}}) $\times$ 12 + (M_{\text{cutoff}}

• M_{\text{cohort}})$$

Пары (cohort_month, month_number), где month_number > max_month, лежат за горизонтом наблюдения и должны отсекаться до объединения с активностью.

Сетка когорт и номеров месяцев: пошаговый конвейер расчёта retention

Для построения корректной таблицы retention конструируется конвейер из пяти этапов:

1. user_act_months: отбирает уникальные пары (user_id, activity_month) до даты среза и вычисляет month_number разностью года и месяца.
2. activity_totals: группирует данные по (cohort_month, month_number) и считает число уникальных активных клиентов через COUNT(DISTINCT user_id).
3. observed_grid: формирует допустимую опорную сетку через CROSS JOIN витрины cohort_sizes со справочником month_grid. Пары, у которых month_number превышает разность между срезом и датой когорты, отсекаются в WHERE.
4. LEFT JOIN: соединяет допустимую сетку observed_grid с агрегатами activity_totals по составному ключу (cohort_month, month_number).
5. Финальный SELECT: заменяет пропуски внутри горизонта наблюдения на 0 через COALESCE и делит активных пользователей на базовый размер когорты cohort_size с вещественным множителем 1.0.

Запрос конвейера со срезом на 2024-03-31:

WITH user_act_months AS (
    SELECT DISTINCT
        uc.cohort_month,
        uc.user_id,
        (CAST(strftime('%Y', ua.action_date) AS INTEGER) - CAST(strftime('%Y', uc.cohort_month) AS INTEGER)) * 12
        + (CAST(strftime('%m', ua.action_date) AS INTEGER) - CAST(strftime('%m', uc.cohort_month) AS INTEGER)) AS month_number
    FROM user_cohorts uc
    JOIN user_actions ua ON uc.user_id = ua.user_id
    WHERE ua.action_date <= '2024-03-31'
),
activity_totals AS (
    SELECT cohort_month, month_number, COUNT(DISTINCT user_id) AS active_users
    FROM user_act_months
    GROUP BY cohort_month, month_number
),
observed_grid AS (
    SELECT
        cs.cohort_month,
        cs.cohort_size,
        mg.month_number
    FROM cohort_sizes cs
    CROSS JOIN month_grid mg
    WHERE mg.month_number <= (
        (2024 - CAST(strftime('%Y', cs.cohort_month) AS INTEGER)) * 12
        + (3 - CAST(strftime('%m', cs.cohort_month) AS INTEGER))
    )
)
SELECT
    og.cohort_month,
    og.month_number,
    COALESCE(at.active_users, 0) AS active_users,
    ROUND(1.0 * COALESCE(at.active_users, 0) / og.cohort_size, 4) AS retention_rate
FROM observed_grid og
LEFT JOIN activity_totals at
    ON og.cohort_month = at.cohort_month AND og.month_number = at.month_number
ORDER BY og.cohort_month ASC, og.month_number ASC;

Результат запроса:

cohort_month	month_number	active_users	retention_rate
2024-01-01	0	4	1.0
2024-01-01	1	2	0.5
2024-01-01	2	0	0.0
2024-02-01	0	2	1.0
2024-02-01	1	1	0.5

В этом конвейере агрегат COUNT(DISTINCT user_id) применяется как групповая функция внутри activity_totals, а не как оконная функция. Разделение подготовки сетки observed_grid и подсчёта фактов в activity_totals сохраняет в отчёте строки с нулевой активностью, но не создаёт строк за пределами доступного календаря.

Сквозной разбор матрицы retention: когорты января и февраля со срезом в марте

Разберём происхождение каждой строки итоговой таблицы на основе исходных таблиц cohort_sizes, user_cohorts и user_actions.

Для когорты 2024-01-01 базовый размер cohort_size = 4 (пользователи 101, 102, 103, 104):

• month_number = 0 (январь 2024): все 4 пользователя совершили действия (номера 1, 2, 3, 4). active_users = 4, retention_rate = 4 / 4 = 1.0.
• month_number = 1 (февраль 2024): действия совершили двое — 101 и 102 (действия 5 и 6). active_users = 2, retention_rate = 2 / 4 = 0.5.
• month_number = 2 (март 2024): предел наблюдения на дату 2024-03-31 равен (2024 - 2024) * 12 + (3 - 1) = 2. Значит, месяц 2 входит в диапазон наблюдения (2 <= 2). В user_actions нет ни одного действия от пользователей 101–104 за март. В CTE activity_totals строка ('2024-01-01', 2) отсутствует. LEFT JOIN с observed_grid возвращает NULL, который COALESCE преобразует в 0. Итоговый retention_rate = 0 / 4 = 0.0. Это подтверждённый нулевой возврат.

Для когорты 2024-02-01 базовый размер cohort_size = 2 (пользователи 201 и 202):

• month_number = 0 (февраль 2024): действия совершены обоими пользователями (действия 7 и 8). active_users = 2, retention_rate = 2 / 2 = 1.0.
• month_number = 1 (март 2024): пользователь 201 совершил два действия (9 и 10). Благодаря COUNT(DISTINCT user_id) он учтён ровно один раз. Пользователь 202 действий не совершал. active_users = 1, retention_rate = 1 / 2 = 0.5.
• month_number = 2 (апрель 2024): предел наблюдения для когорты равен (2024 - 2024) * 12 + (3 - 2) = 1. Условие mg.month_number <= 1 в CTE observed_grid исключило значение 2 из сетки. Строка ('2024-02-01', 2) не была сгенерирована и не попала в финальный результат.

Таким образом, мартовский ноль январской когорты зафиксирован как расчётная величина, а апрельская ячейка февральской когорты исключена из анализа до наступления соответствующего периода.