Воронка продаж в SQL: анализ конверсии пошагово

Воронка продаж показывает, сколько пользователей проходит каждый этап — от первого касания до покупки. SQL позволяет строить воронки любой сложности прямо из сырых данных.

Структура данных

Предположим, есть таблица событий:

-- user_events: user_id | event | event_date
-- Возможные события:
-- 'page_view'   — просмотр страницы товара
-- 'add_to_cart' — добавление в корзину
-- 'checkout'    — начало оформления
-- 'purchase'    — успешная покупка

Вертикальная воронка (базовая)

SELECT
    stage,
    users,
    ROUND(users::numeric / first_value(users) OVER (ORDER BY stage_order) * 100, 1) AS pct_from_top,
    ROUND(users::numeric / lag(users) OVER (ORDER BY stage_order) * 100, 1) AS pct_from_prev
FROM (
    VALUES
        (1, 'Просмотр товара',  (
            SELECT COUNT(DISTINCT user_id) FROM user_events
            WHERE event = 'page_view' AND event_date >= '2026-03-01'
        )),
        (2, 'Добавил в корзину', (
            SELECT COUNT(DISTINCT user_id) FROM user_events
            WHERE event = 'add_to_cart' AND event_date >= '2026-03-01'
        )),
        (3, 'Начал оформление', (
            SELECT COUNT(DISTINCT user_id) FROM user_events
            WHERE event = 'checkout' AND event_date >= '2026-03-01'
        )),
        (4, 'Купил', (
            SELECT COUNT(DISTINCT user_id) FROM user_events
            WHERE event = 'purchase' AND event_date >= '2026-03-01'
        ))
) AS t(stage_order, stage, users);

Горизонтальная воронка (один запрос)

Классический способ через CASE WHEN — один проход по данным:

SELECT
    COUNT(DISTINCT user_id)                                                          AS viewed,
    COUNT(DISTINCT CASE WHEN event IN ('add_to_cart','checkout','purchase')
                        THEN user_id END)                                            AS added_to_cart,
    COUNT(DISTINCT CASE WHEN event IN ('checkout','purchase')
                        THEN user_id END)                                            AS checked_out,
    COUNT(DISTINCT CASE WHEN event = 'purchase'
                        THEN user_id END)                                            AS purchased,
    ROUND(
        COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END)::numeric
        / COUNT(DISTINCT user_id) * 100, 2
    )                                                                                AS overall_cvr
FROM user_events
WHERE event_date BETWEEN '2026-03-01' AND '2026-03-31';

Строгая воронка: учёт порядка событий

Предыдущий вариант считает пользователей, которые совершили действие в любом порядке. Строгая воронка требует последовательности:

WITH user_funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event = 'page_view'   THEN 1 ELSE 0 END) AS did_view,
        MAX(CASE WHEN event = 'add_to_cart' THEN 1 ELSE 0 END) AS did_add,
        MAX(CASE WHEN event = 'checkout'    THEN 1 ELSE 0 END) AS did_checkout,
        MAX(CASE WHEN event = 'purchase'    THEN 1 ELSE 0 END) AS did_purchase
    FROM user_events
    WHERE event_date >= '2026-03-01'
    GROUP BY user_id
),
strict_funnel AS (
    SELECT
        user_id,
        did_view,
        did_view * did_add      AS added_after_view,
        did_view * did_add * did_checkout AS checked_after_add,
        did_view * did_add * did_checkout * did_purchase AS purchased_all
    FROM user_funnel
    WHERE did_view = 1
)
SELECT
    COUNT(*) AS viewed,
    SUM(added_after_view) AS added,
    SUM(checked_after_add) AS checked_out,
    SUM(purchased_all) AS purchased
FROM strict_funnel;

Воронка с временным окном (7-дневная)

Часто важно: пользователь должен пройти этапы в течение N дней.

WITH viewed AS (
    SELECT user_id, MIN(event_date) AS view_date
    FROM user_events WHERE event = 'page_view'
    GROUP BY user_id
),
added AS (
    SELECT DISTINCT e.user_id
    FROM user_events e
    JOIN viewed v ON v.user_id = e.user_id
    WHERE e.event = 'add_to_cart'
      AND e.event_date BETWEEN v.view_date AND v.view_date + INTERVAL '7 days'
),
purchased AS (
    SELECT DISTINCT e.user_id
    FROM user_events e
    JOIN viewed v ON v.user_id = e.user_id
    WHERE e.event = 'purchase'
      AND e.event_date BETWEEN v.view_date AND v.view_date + INTERVAL '7 days'
)
SELECT
    (SELECT COUNT(*) FROM viewed)    AS viewed,
    (SELECT COUNT(*) FROM added)     AS added,
    (SELECT COUNT(*) FROM purchased) AS purchased,
    ROUND((SELECT COUNT(*) FROM purchased)::numeric /
          (SELECT COUNT(*) FROM viewed) * 100, 2) AS overall_cvr;

Когортная воронка: по неделям регистрации

WITH user_cohort AS (
    SELECT user_id, DATE_TRUNC('week', created_at)::date AS cohort_week
    FROM users
),
funnel AS (
    SELECT
        c.cohort_week,
        COUNT(DISTINCT c.user_id) AS registered,
        COUNT(DISTINCT CASE WHEN e.event = 'add_to_cart' THEN e.user_id END) AS added,
        COUNT(DISTINCT CASE WHEN e.event = 'purchase'    THEN e.user_id END) AS purchased
    FROM user_cohort c
    LEFT JOIN user_events e ON e.user_id = c.user_id
        AND e.event_date <= c.cohort_week + INTERVAL '30 days'
    GROUP BY c.cohort_week
)
SELECT
    cohort_week,
    registered,
    added,
    purchased,
    ROUND(added::numeric / registered * 100, 1) AS add_rate,
    ROUND(purchased::numeric / registered * 100, 1) AS purchase_rate
FROM funnel
ORDER BY cohort_week;

Поиск узкого места (где теряем больше всего)

WITH funnel AS (
    SELECT
        1000 AS viewed,
        650  AS added,
        380  AS checked_out,
        190  AS purchased
),
steps AS (
    SELECT
        'Просмотр → Корзина'    AS step,
        added::numeric / viewed * 100 AS cvr,
        viewed - added          AS lost_users
    FROM funnel

    UNION ALL

    SELECT 'Корзина → Оформление', checked_out::numeric / added * 100, added - checked_out FROM funnel
    UNION ALL
    SELECT 'Оформление → Покупка', purchased::numeric / checked_out * 100, checked_out - purchased FROM funnel
)
SELECT step, ROUND(cvr, 1) AS conversion_pct, lost_users
FROM steps
ORDER BY cvr ASC;  -- Сортируем по конверсии: самый слабый шаг первый

Сравнение воронок: период к периоду

SELECT
    stage,
    SUM(CASE WHEN period = 'current' THEN users END) AS current_users,
    SUM(CASE WHEN period = 'previous' THEN users END) AS prev_users,
    ROUND(
        (SUM(CASE WHEN period = 'current' THEN users END)::numeric
         - SUM(CASE WHEN period = 'previous' THEN users END))
        / NULLIF(SUM(CASE WHEN period = 'previous' THEN users END), 0) * 100, 1
    ) AS change_pct
FROM (
    SELECT 'viewed' AS stage, 'current' AS period, COUNT(DISTINCT user_id) AS users
    FROM user_events WHERE event = 'page_view' AND event_date >= CURRENT_DATE - 30

    UNION ALL

    SELECT 'viewed', 'previous', COUNT(DISTINCT user_id)
    FROM user_events WHERE event = 'page_view'
    AND event_date BETWEEN CURRENT_DATE - 60 AND CURRENT_DATE - 31

    -- Аналогично для остальных событий...
) t
GROUP BY stage;

Итог: паттерны воронок

Воронка — первый инструмент диагностики роста. Найдите узкое место → сгенерируйте гипотезу → запустите A/B тест → снова измерьте воронку.