windowFunnel: анализ продуктовых воронок

Конверсионная воронка — фундаментальная метрика product analytics. Сколько пользователей дошли от просмотра до покупки? На каком шаге больше всего отказов? Самописные решения через CTEs и JOIN работают, но содержат скрытые угловые случаи с временными окнами и порядком событий. ClickHouse предоставляет встроенную функцию windowFunnel(), которая обрабатывает все эти случаи корректно.

Синтаксис windowFunnel

windowFunnel(window_seconds [, mode])(timestamp, cond1, cond2, ..., condN)

Функция принимает:

• window_seconds — временное окно в секундах (например, 86400 = 24 часа)
• mode — необязательный режим (strict, strict_order, strict_increase)
• timestamp — столбец с временной меткой (DateTime или UInt32)
• cond1..condN — условия для шагов воронки

Возвращает UInt8 — максимальный достигнутый шаг воронки для данного пользователя в пределах временного окна.

Воронка view -> cart -> purchase

Полный пример: воронка за 24 часа

-- Создаём таблицу событий
CREATE TABLE events (
    user_id  UInt32,
    event_type String,
    ts       DateTime
) ENGINE = MergeTree()
ORDER BY (user_id, ts);

-- Вставляем тестовые данные
INSERT INTO events VALUES
    (1, 'view',     '2025-01-01 10:00:00'),
    (1, 'cart',     '2025-01-01 10:15:00'),
    (1, 'purchase', '2025-01-01 10:45:00'),
    (2, 'view',     '2025-01-01 09:00:00'),
    (2, 'cart',     '2025-01-01 09:30:00'),
    (3, 'view',     '2025-01-01 11:00:00'),
    (4, 'view',     '2025-01-01 08:00:00'),
    (4, 'purchase', '2025-01-03 12:00:00');  -- за пределами 24-часового окна

-- Шаг 1: вычислить максимальный достигнутый уровень для каждого пользователя
SELECT
    user_id,
    windowFunnel(86400)(  -- временное окно 24 часа
        ts,
        event_type = 'view',
        event_type = 'cart',
        event_type = 'purchase'
    ) AS level
FROM events
GROUP BY user_id;

User 4 сделал view и purchase, но с разрывом в 2 дня — за пределами 24-часового окна. windowFunnel правильно возвращает 1 (только первый шаг).

-- Шаг 2: агрегированный отчёт по уровням воронки
SELECT
    level,
    count() AS users
FROM (
    SELECT
        user_id,
        windowFunnel(86400)(
            ts,
            event_type = 'view',
            event_type = 'cart',
            event_type = 'purchase'
        ) AS level
    FROM events
    GROUP BY user_id
)
GROUP BY level
ORDER BY level DESC;

Режимы strict

По умолчанию windowFunnel допускает любые события между шагами. Три режима ужесточают требования:

-- Режим strict_order: между view и purchase не должно быть пропусков назад
SELECT
    user_id,
    windowFunnel(86400, 'strict_order')(
        ts,
        event_type = 'view',
        event_type = 'cart',
        event_type = 'purchase'
    ) AS level
FROM events
GROUP BY user_id;

Когда использовать: если между view и purchase могут быть любые промежуточные события (search, compare, wishlist) — используйте режим по умолчанию. strict нужен только когда каждый шаг должен встретиться ровно один раз.

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

Самописный эквивалент через CTEs и JOIN:

-- Самописная воронка: 15+ строк, угловые случаи
WITH
    viewers AS (SELECT user_id FROM events WHERE event_type = 'view'),
    carters AS (
        SELECT v.user_id
        FROM viewers v
        JOIN events c ON v.user_id = c.user_id
        WHERE c.event_type = 'cart'
          AND c.ts > (SELECT min(ts) FROM events WHERE user_id = v.user_id AND event_type = 'view')
          AND c.ts < (SELECT min(ts) FROM events WHERE user_id = v.user_id AND event_type = 'view')
              + INTERVAL 86400 SECOND
    )
SELECT count() FROM carters;
-- Проблемы: нет обработки повторных событий, CTE в ClickHouse менее эффективны

-- windowFunnel: 5 строк, правильная обработка временного окна
SELECT count() FROM (
    SELECT windowFunnel(86400)(ts, event_type='view', event_type='cart') AS level
    FROM events GROUP BY user_id
)
WHERE level >= 2;

windowFunnel обрабатывает временное окно, порядок событий и повторные события корректно и эффективно.

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

1. windowFunnel(window_seconds)(timestamp, cond1, cond2, ...) — встроенная функция для конверсионных воронок. Возвращает UInt8 — максимальный достигнутый шаг.
2. Обязательный паттерн: GROUP BY user_id в подзапросе, затем GROUP BY level, count() для агрегированного отчёта.
3. Временное окно (window_seconds) — ключевой параметр: события вне окна игнорируются (user_id=4 в примере).
4. Режимы: по умолчанию — промежуточные события допустимы; strict — каждый шаг один раз; strict_order — строгая последовательность; strict_increase — возрастающие timestamps.
5. Не изобретайте CTEs+JOIN — windowFunnel корректнее, компактнее и эффективнее самописного аналога.