Посчитайте конверсию между шагами воронки, если шаг засчитывается только после предыдущего
Проверила команда Сопровод · Обновлено
Короткий ответ
Найдите у каждого пользователя первое время каждого шага через MIN(created_at) FILTER (WHERE step = ...) и засчитывайте шаг, только если он позже предыдущего засчитанного. Порядок шагов задайте числом, конверсию от прошлого шага возьмите через LAG, от первого — через FIRST_VALUE. Простой COUNT(DISTINCT user_id) по шагам завышает воронку.
Почему этот вопрос задают
Воронку с порядком шагов дают сеньорам, потому что сырые события почти всегда грязные: повторы, сбои трекинга, шаги не по порядку. Важно увидеть, где запрос ошибается, и что это меняет в решении о продукте.
Условие задачи
Страховой сервис продает ОСАГО онлайн в три шага: расчет цены (quote), анкета (form), оплата (payment). Журнал events(user_id, step, created_at) пишет каждый заход на шаг, повторы тоже. Посчитайте по шагам число пользователей и конверсию в процентах до десятых от прошлого шага и от первого (step, users, conv_from_prev_pct, conv_from_first_pct). Шаг засчитывается, только если пользователь прошел его после предыдущего.
Как подойти к решению
- Найдите первое время каждого шага у пользователя через
MINсFILTER - Засчитайте анкету, если она позже расчета, и оплату, если она позже засчитанной анкеты
- Задайте порядок шагов числом и соберите шаги в одну таблицу через
UNION ALL - Посчитайте конверсию от прошлого шага через
LAGи от первого черезFIRST_VALUE
Решение
Анкету по правилам заполнили трое из шести: у четвертого она раньше расчета. Оплата засчитана одна: третий платил без анкеты. Полис без анкеты не оформить, это похоже на сбой трекинга: проверьте событие анкеты. Подсчет без порядка дал бы 4 анкеты и 2 оплаты, конечная конверсия выросла бы вдвое. Сильнее всего воронка теряет на оплате.
WITH f AS (
SELECT user_id,
MIN(created_at) FILTER (WHERE step = 'quote') AS quote_at,
MIN(created_at) FILTER (WHERE step = 'form') AS form_at,
MIN(created_at) FILTER (WHERE step = 'payment') AS pay_at
FROM events GROUP BY user_id
), funnel AS (
SELECT 1 AS pos, 'quote' AS step, COUNT(quote_at) AS users FROM f
UNION ALL
SELECT 2, 'form', COUNT(*) FILTER (WHERE form_at > quote_at) FROM f
UNION ALL
SELECT 3, 'payment', COUNT(*) FILTER (WHERE pay_at > form_at AND form_at > quote_at)
FROM f
)
SELECT step, users,
ROUND(100.0 * users / NULLIF(LAG(users) OVER w, 0), 1) AS conv_from_prev_pct,
ROUND(100.0 * users / NULLIF(FIRST_VALUE(users) OVER w, 0), 1) AS conv_from_first_pct
FROM funnel
WINDOW w AS (ORDER BY pos)
ORDER BY pos;step | users | conv_from_prev_pct | conv_from_first_pct ---------+-------+--------------------+--------------------- quote | 6 | | 100.0 form | 3 | 50.0 | 50.0 payment | 1 | 33.3 | 16.7 (3 rows)
Что спросят следом
Как засчитать анкету, если пользователь заполнил ее до расчета, а потом еще раз после?
Как ограничить воронку по времени, например оплатой в течение суток после расчета?
Доступно по подписке
Ответы на эти вопросы
Частые ошибки
«Посчитаю
COUNT(DISTINCT user_id)по каждому шагу»Так в оплату попадет третий пользователь без анкеты, в анкету — четвертый, заполнивший ее до расчета. Конечная конверсия выйдет 33,3% вместо 16,7%
«Отсортирую шаги по
step»По алфавиту выйдет
form,payment,quote, иLAGсравнит шаги в чужом порядке. Задайте позицию шага числом или справочником шагов«Пользователя с оплатой без анкеты выброшу из данных»
Расчет он прошел, и без него конверсия в анкету выйдет 60,0% вместо 50,0%. Оставьте его в расчете, а оплату без анкеты вынесите строкой сбоев
«Засчитаю шаг, если он позже расчета»
Тогда пройдет оплата третьего пользователя без анкеты: она позже его расчета. Сравнивайте каждый шаг с предыдущим засчитанным
Сигналы для интервьюера
Сильный ответ
Сверяет итог запроса с ручным подсчетом по двум-трем пользователям из журнала, прежде чем отдавать воронку менеджеру продукта
Тревожный сигнал
Строит воронку верно, но на вопрос, где продукт теряет больше всего людей, отвечает общими словами
Красный флаг
Объясняет низкую конверсию в оплату неудобной формой, не проверив, не теряет ли события трекинг
Как еще могут спросить
- Как посчитать воронку в SQL, если события пришли не по порядку?
- Посчитайте конверсию из шага в шаг по журналу событий с учетом последовательности
- Как убедиться, что пользователь прошел шаги воронки в нужном порядке?
- Почему
COUNT(DISTINCT user_id)по каждому шагу завышает конверсию воронки?











