Воронка конверсии в SQL: расчёт пошагово без боли

💡 Никогда не работал с SQL? Сначала пройди серию SQL с нуля для аналитика — 10 связанных частей от «что такое БД» до оконных функций.

Воронка — главный инструмент продуктовой аналитики. «Из 1000 юзеров видевших страницу, 200 нажали кнопку, 50 заполнили форму, 20 оплатили». Где отвал больше всего — там фокус оптимизации.

Этот гайд — как посчитать воронку в чистом SQL. С нюансами: сохранение порядка шагов, кохортные воронки, сравнение А/Б-групп, защита от багов.

Простейший случай: считаем юзеров на каждом шаге

Допустим, у тебя таблица событий:

CREATE TABLE events (
 user_id INT,
 event_name TEXT, -- 'visit', 'signup', 'add_to_cart', 'purchase'
 event_ts TIMESTAMP
);

Самый прямой запрос:

SELECT
 COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'visit') AS visits,
 COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'signup') AS signups,
 COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'add_to_cart') AS carts,
 COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'purchase') AS purchases
FROM events
WHERE event_ts >= CURRENT_DATE - INTERVAL '30 days';

Получишь:

Конверсии:

• visit → signup: 35%
• signup → cart: 34.3%
• cart → purchase: 26.7%
• visit → purchase (overall): 3.2%

Строгая воронка: каждый шаг ПОСЛЕ предыдущего

Если хотим «юзер сделал signup ПОСЛЕ visit, потом purchase ПОСЛЕ signup» — нужен self-join или оконные функции.

Подход с MIN(timestamp) для каждого события:

WITH user_steps AS (
 SELECT
 user_id,
 MIN(CASE WHEN event_name = 'visit' THEN event_ts END) AS visit_ts,
 MIN(CASE WHEN event_name = 'signup' THEN event_ts END) AS signup_ts,
 MIN(CASE WHEN event_name = 'add_to_cart' THEN event_ts END) AS cart_ts,
 MIN(CASE WHEN event_name = 'purchase' THEN event_ts END) AS purchase_ts
 FROM events
 WHERE event_ts >= CURRENT_DATE - INTERVAL '30 days'
 GROUP BY user_id
)
SELECT
 COUNT(*) FILTER (WHERE visit_ts IS NOT NULL) AS step1_visit,
 COUNT(*) FILTER (WHERE visit_ts IS NOT NULL AND signup_ts > visit_ts) AS step2_signup,
 COUNT(*) FILTER (WHERE signup_ts > visit_ts AND cart_ts > signup_ts) AS step3_cart,
 COUNT(*) FILTER (WHERE cart_ts > signup_ts AND purchase_ts > cart_ts) AS step4_purchase
FROM user_steps;

Теперь каждый последующий шаг считается ТОЛЬКО если он произошёл после предыдущего.

Воронка по дням / неделям / когортам

Чаще всего хочешь видеть динамику. Группируем по дате первого visit:

WITH user_steps AS (
 SELECT
 user_id,
 DATE(MIN(CASE WHEN event_name = 'visit' THEN event_ts END)) AS cohort_day,
 MIN(CASE WHEN event_name = 'visit' THEN event_ts END) AS visit_ts,
 MIN(CASE WHEN event_name = 'signup' THEN event_ts END) AS signup_ts,
 MIN(CASE WHEN event_name = 'purchase' THEN event_ts END) AS purchase_ts
 FROM events
 WHERE event_ts >= CURRENT_DATE - INTERVAL '30 days'
 GROUP BY user_id
)
SELECT
 cohort_day,
 COUNT(*) AS visits,
 COUNT(*) FILTER (WHERE signup_ts > visit_ts) AS signups,
 COUNT(*) FILTER (WHERE signup_ts > visit_ts AND purchase_ts > signup_ts) AS purchases,
 ROUND(100.0 * COUNT(*) FILTER (WHERE signup_ts > visit_ts) / COUNT(*), 2) AS visit_to_signup_pct,
 ROUND(100.0 * COUNT(*) FILTER (WHERE purchase_ts > signup_ts) / NULLIF(COUNT(*) FILTER (WHERE signup_ts > visit_ts), 0), 2) AS signup_to_purchase_pct
FROM user_steps
GROUP BY cohort_day
ORDER BY cohort_day DESC;

Сравнение воронок A/B-групп

WITH user_steps AS (...) -- как выше, плюс JOIN с тестовой группой
SELECT
 test_group,
 COUNT(*) AS visits,
 COUNT(*) FILTER (WHERE signup_ts > visit_ts) AS signups,
 ROUND(100.0 * COUNT(*) FILTER (WHERE signup_ts > visit_ts) / COUNT(*), 2) AS conv_pct
FROM user_steps us
JOIN ab_assignments a ON a.user_id = us.user_id
GROUP BY test_group;

Получишь:

Дальше chi2 для статистической значимости (см. A/B-тесты в Python).

Время на этап (drop-off analysis)

Важно знать не только «сколько юзеров отвалилось», но и «через сколько времени». Долгий этап — место для оптимизации.

WITH user_steps AS (...)
SELECT
 AVG(EXTRACT(EPOCH FROM (signup_ts - visit_ts))) AS avg_visit_to_signup_sec,
 AVG(EXTRACT(EPOCH FROM (purchase_ts - signup_ts))) AS avg_signup_to_purchase_sec,
 PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (signup_ts - visit_ts))) AS median_visit_to_signup
FROM user_steps
WHERE signup_ts IS NOT NULL;

Если median visit→signup = 2 минуты, average = 30 минут → у тебя выбросы (юзеры которые думают долго).

ClickHouse: нативные функции воронки

В CH есть windowFunnel() — посчитает строгую воронку за один проход:

SELECT
 sum(funnel_level = 1) AS visit,
 sum(funnel_level = 2) AS signup,
 sum(funnel_level = 3) AS cart,
 sum(funnel_level = 4) AS purchase
FROM (
 SELECT
 user_id,
 windowFunnel(7 * 86400)(event_ts,
 event_name = 'visit',
 event_name = 'signup',
 event_name = 'add_to_cart',
 event_name = 'purchase'
 ) AS funnel_level
 FROM events
 GROUP BY user_id
);

Параметр 7 * 86400 — окно в секундах (7 дней). Юзер должен пройти все шаги в пределах окна. Очень быстро на больших данных.

Подводные камни

Дубли событий

Юзер может сделать несколько signup (если разлогинился и заново регнулся через другой email). MIN(timestamp) для каждого события решает.

Юзеры без visit (входящие через прямую ссылку)

В строгой воронке они потеряются. Если важно — отдельный сегмент «no_visit_first».

Mobile vs Web — две разные воронки

Часто разные продукты. Считай отдельно по платформам.

Влияние новых юзеров

Если за период резко набралась когорта новых — voronka может казаться лучше/хуже из-за их специфики. Всегда смотри по когортам.

Не забывай unit

Воронка по уникальным юзерам vs по сессиям vs по визитам — разные числа. На дашборде явно подписывай unit.

Связанные материалы

• SQL-тренажёр: задачи на воронки — реальные собесные задачи
• RFM-сегментация в SQL — другой ключевой подход
• Когортный анализ retention — близкая тема
• Оконные функции SQL — продвинутые воронки
• Метрики продукта — conversion rate, drop-off

Открой SQL-тренажёр, найди задачу с тегом «funnel» или «conversion» — реализуй на реальной схеме данных. Это лучше чем читать любой гайд.