retention: когортный анализ удержания

Day 1, Day 7, Day 30 retention — ключевые метрики product analytics. Сколько пользователей, впервые пришедших на этой неделе, вернулись на следующей? Классический подход через PIVOT и CASE WHEN требует отдельного запроса на каждую точку времени и сложной логики группировки. ClickHouse предоставляет встроенную функцию retention(), которая возвращает матрицу удержания за один проход данных.

Синтаксис retention

retention(cond1, cond2, cond3, ...)

Возвращает Array(UInt8) — массив длиной N, где N равно числу условий. Элемент массива равен 1 если пользователь попал в условие i и в условие 1 (первое условие — “когорта”). Иначе 0.

Правила:

• Условие 1 — это определение когорты (например, “был активен на неделе 0”)
• Условия 2..N — это периоды удержания (неделя 1, неделя 2 и т.д.)
• До 32 условий
• Если пользователь не попал в условие 1, весь массив будет [0, 0, ...]

Матрица удержания по неделям

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

-- Создаём таблицу событий пользователей
CREATE TABLE user_events (
    uid        UInt32,
    event_time DateTime
) ENGINE = MergeTree()
ORDER BY (uid, event_time);

-- Шаг 1: вычислить retention-массив для каждого пользователя
-- toMonday() нормализует дату к началу недели
SELECT
    toMonday(min(event_time)) AS cohort_week,
    uid,
    retention(
        toMonday(event_time) = toMonday(today()),
        toMonday(event_time) = toMonday(today()) + INTERVAL 7 DAY,
        toMonday(event_time) = toMonday(today()) + INTERVAL 14 DAY
    ) AS r
FROM user_events
GROUP BY uid;

Результат этого подзапроса — одна строка на пользователя с массивом r:

Пользователь 102: в когорте недели 0 (r[1]=1), не вернулся на неделю 1 (r[2]=0), вернулся на неделю 2 (r[3]=1).

-- Шаг 2: агрегировать по когортам
SELECT
    cohort_week,
    sum(r[1]) AS week_0,
    sum(r[2]) AS week_1,
    sum(r[3]) AS week_2,
    round(sum(r[2]) * 100.0 / sum(r[1]), 1) AS pct_week_1,
    round(sum(r[3]) * 100.0 / sum(r[1]), 1) AS pct_week_2
FROM (
    SELECT
        toMonday(min(event_time)) AS cohort_week,
        uid,
        retention(
            toMonday(event_time) = toMonday(today()),
            toMonday(event_time) = toMonday(today()) + INTERVAL 7 DAY,
            toMonday(event_time) = toMonday(today()) + INTERVAL 14 DAY
        ) AS r
    FROM user_events
    GROUP BY uid
)
GROUP BY cohort_week
ORDER BY cohort_week;

Вариации: дневная и месячная retention

-- Дневная retention (Day 0, Day 1, Day 2, Day 3)
SELECT
    toDate(min(event_time)) AS cohort_day,
    uid,
    retention(
        toDate(event_time) = toDate(today()),
        toDate(event_time) = toDate(today()) + INTERVAL 1 DAY,
        toDate(event_time) = toDate(today()) + INTERVAL 2 DAY,
        toDate(event_time) = toDate(today()) + INTERVAL 3 DAY
    ) AS r
FROM user_events
GROUP BY uid;

-- Месячная retention (Month 0, Month 1, Month 2)
SELECT
    toStartOfMonth(min(event_time)) AS cohort_month,
    uid,
    retention(
        toStartOfMonth(event_time) = toStartOfMonth(today()),
        toStartOfMonth(event_time) = toStartOfMonth(today()) + INTERVAL 1 MONTH,
        toStartOfMonth(event_time) = toStartOfMonth(today()) + INTERVAL 2 MONTH
    ) AS r
FROM user_events
GROUP BY uid;

Не изобретайте велосипед

PIVOT с CASE WHEN — распространённый, но неэффективный подход:

-- Самописный retention через CASE WHEN: O(N^2) по числу когорт
SELECT
    cohort_week,
    count(DISTINCT CASE WHEN activity_week = cohort_week THEN uid END) AS week_0,
    count(DISTINCT CASE WHEN activity_week = cohort_week + INTERVAL 7 DAY THEN uid END) AS week_1,
    count(DISTINCT CASE WHEN activity_week = cohort_week + INTERVAL 14 DAY THEN uid END) AS week_2
FROM (
    SELECT uid, toMonday(min(event_time)) AS cohort_week, toMonday(event_time) AS activity_week
    FROM user_events GROUP BY uid, activity_week
)
GROUP BY cohort_week;
-- Проблема: каждый новый период удержания требует нового CASE WHEN
-- Добавление 30-дневного retention: переписывать весь запрос

-- retention(): один проход, добавление нового периода = один элемент в списке
-- Добавить Day 30? Добавить ещё одно условие в retention():
retention(
    ...,
    toDate(event_time) = toDate(today()) + INTERVAL 30 DAY
)
-- Это единственное изменение, которое нужно внести

Ключевые выводы

1. retention(cond1, cond2, ...) — возвращает Array(UInt8) длиной N. Элемент r[i]=1 если пользователь в условии i и в условии 1 (когорте).
2. Обязательный двухшаговый паттерн: GROUP BY uid с retention() в подзапросе, затем SUM(r[i]) + деление для процентов во внешнем запросе.
3. Первое условие — определение когорты. Если пользователь не попал в первое условие, массив всегда [0, 0, ...].
4. До 32 периодов удержания в одном вызове. Для 30-дневной retention нужно 31 условие (день 0 как когорта + дни 1-30).
5. Функция не вычисляет проценты — только 0/1. Процент = sum(r[N]) / sum(r[1]) * 100.
6. Эффективнее PIVOT+CASE WHEN: один проход данных, добавление нового периода — одно условие.