Преобразование воронки в SQL и шаг, который показывает 100%

1. Что должен выдавать запрос воронки

Воронка – это количество вещи который достиг каждого из упорядоченного списка шагов. Три решения предшествуют любому SQL, и все три меняют числа.

Что именно считается? Сессии, пользователи, заказы, заявки. Пользователь, который посещает сайт пять раз, — это один пользователь и пять сеансов, и эти воронки совершенно не похожи друг на друга.

Какие действия и в каком порядке? Порядок — это ваше утверждение о процессе, а пятый раздел — это то, что происходит, когда данные с ним не совпадают.

Над каким окном? Постоянно, в день, в течение тридцати минут после первого шага. Воронка без окна спокойно считает конверсией тех, кто посмотрел в январе и совершил покупку в мае.

Запишите эти три, прежде чем писать запрос. Это такая же дисциплина, как и любые другие показатели, и определение метрики это более длинная версия причины.

2. Считайте сеансы, а не события

До пояснения: десять сессий произвели двадцать четыре события. Скажите, сколько из этих событий являются просмотрами.

SELECT step, COUNT(*) AS events, COUNT(DISTINCT session_id) AS sessions
FROM events GROUP BY 1;

view     | 11 | 10
cart     |  7 |  7
checkout |  3 |  3
purchase |  3 |  3

Одиннадцать просмотров событий, десять сеансов. Сеанс S10 просмотрен дважды, что является совершенно нормальным поведением и совершенно неправильно считать дважды: вершиной воронки является количество сеансов, которые зашли так далеко, а не количество раз, когда они это сделали.

Разрыв здесь невелик, и он никогда не бывает мал в реальных данных, потому что первый шаг воронки — это именно тот шаг, который люди повторяют. Просмотр домашней страницы, регистрируемый при каждой навигации, может привести к тому, что число посетителей в верхней части воронки в несколько раз превысит число посетителей, из-за чего каждый коэффициент конверсии ниже него будет выглядеть ужасным без всякой причины.

Таким образом, каждый счетчик в воронке COUNT(DISTINCT thing_id). Если верхнее число вашей воронки больше, чем количество посетителей, вот почему.

3. Условная агрегация: вся воронка в одну строку

Приведенная выше сгруппированная версия дает одну строку на шаг, что хорошо для диаграммы и неудобно для расчета ставок, поскольку для каждой скорости требуется две строки. Вместо этого при условном агрегировании вся воронка помещается в одну строку.

SELECT COUNT(DISTINCT CASE WHEN step='view'     THEN session_id END) AS viewed,
       COUNT(DISTINCT CASE WHEN step='cart'     THEN session_id END) AS carted,
       COUNT(DISTINCT CASE WHEN step='checkout' THEN session_id END) AS checked_out,
       COUNT(DISTINCT CASE WHEN step='purchase' THEN session_id END) AS purchased
FROM events;

10 | 7 | 3 | 3

Этот механизм стоит понять, а не копировать. CASE возвращает идентификатор сеанса, когда шаг соответствует и NULL когда это не так, и COUNT игнорирует нули, поэтому в каждом столбце учитываются только сеансы, достигшие этого шага. Это тот же трюк, что и поворот вручную, и руководство по ДЕЛУ охватывает само выражение.

Вам нужна одна строка из четырех чисел, потому что теперь каждая ставка представляет собой арифметику между столбцами, а не соединение между строками.

4. Шаг за шагом и в целом

Два разных вопроса, и в отчетах обычно один отображается, а другой помечается как другой.

viewed | carted | checked_out | purchased
    10 |      7 |           3 |         3

view to cart          70.0%
cart to checkout      42.9%
checkout to purchase 100.0%
overall (view to purchase) 30.0%

Шаг за шагом делит каждый счет на тот, который находится над ним, и отвечает: «сколько людей сюда попали, сколько осталось». Общий делится по верху и отвечает "всех, кто начал, сколько закончило". Умножение скоростей шагов дает общую скорость, что является полезной проверкой: 0,700 × 0,429 × 1,000 равно 0,300.

Оба упомянуты в отчете. Шаг за шагом подскажет вам, куда направить свои усилия; в целом говорит вам, чего стоит этот процесс. Публикация только показателей шагов позволяет странице с показателем 90% выглядеть успешной, когда ее посещают только сорок человек в месяц.

5. 100% означает, что шаг пропущен.

Перед пояснением: оформление покупки вышло ровно на 100,0%. Скажите, что бы это значило, если бы это было правдой.

Чтобы ни один человек, дошедший до страницы оформления заказа, не совершил покупку: ни брошенных корзин, ни неудавшихся карточек, ни раздумий. В любой реальной проверке, которая не является результатом, это симптом, и вопрос, который следует задавать, заключается не в том, «почему проверка такая хорошая», а в том, «действительно ли проверка на пути».

На него отвечает один запрос. Сократите каждый сеанс до флагов, а затем найдите те, которые достигли более позднего шага без предыдущего.

WITH s AS (
  SELECT session_id,
         MAX(step='checkout')::INT AS did_checkout,
         MAX(step='purchase')::INT AS did_purchase
  FROM events GROUP BY 1)
SELECT session_id FROM s WHERE did_purchase = 1 AND did_checkout = 0;

S9

С9 пошёл просмотр, тележка, покупка. Три сессии проверены и три куплены, но это не одни и те же три, и соотношение между двумя разными наборами по три вовсе не является коэффициентом конверсии.

В реальных системах это имеет обычные причины. Путь в один клик или сохраненная карта пропускает страницу оформления заказа. Мобильное приложение запускает различные события с веб-сайта. Событие оформления заказа регистрируется только при отправке, поэтому тот, кто платит другим способом, никогда его не генерирует. Каждый из них стоит знать, и ни один из них не виден на воронкообразной диаграмме.

Произнесите вслух, как называется соотношение между двумя перекрывающимися, но разными множествами. Это не коэффициент конверсии, и, дав ему такое название, проблема с данными становится целью, которую кого-то просят улучшить.

6. Строгая воронка

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

WITH s AS (
  SELECT session_id,
         MAX(step='view')::INT     AS v,
         MAX(step='cart')::INT     AS c,
         MAX(step='checkout')::INT AS k,
         MAX(step='purchase')::INT AS p
  FROM events GROUP BY 1)
SELECT SUM(v)       AS viewed,
       SUM(v*c)     AS carted,
       SUM(v*c*k)   AS checked_out,
       SUM(v*c*k*p) AS purchased
FROM s;

10 | 7 | 3 | 2

Умножение флагов - это AND: сеанс считается шагом, только если у него есть этот флаг и все флаги перед ним. Первые три числа не изменились, а последнее уменьшилось с 3 до 2, поскольку S9 больше не соответствует критериям.

Теперь две основные ставки рядом:

any purchase at all        30.0%
completed the full path    20.0%

Разница в десять процентных пунктов в десяти сеансах, когда один клиент использует другой маршрут. Ни одно из чисел не является неверным. Первый отвечает «сколько купило», второй отвечает «сколько прошли процесс, который мы разработали», и в отчете должно быть указано, какой из них он показывает, а не оставлять читателю право предполагать.

7. Где фактически прекращаются сеансы

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

SELECT CASE WHEN p=1 THEN 'purchased'
            WHEN k=1 THEN 'stopped at checkout'
            WHEN c=1 THEN 'stopped at cart'
            ELSE        'stopped at view' END AS outcome,
       COUNT(*) AS sessions
FROM s GROUP BY 1 ORDER BY sessions DESC;

purchased           | 3
stopped at view     | 3
stopped at cart     | 3
stopped at checkout | 1

Три, три, три и один, в сумме получается десять, что соответствует количеству сеансов. Это согласование является причиной построить его таким образом: счетчики воронки перекрываются, поэтому их нельзя сверить с общей суммой, в то время как таблица самого дальнего шага разделяет сеансы и должна суммироваться.

тот CASE Порядок имеет значение и выполняет всю работу: сначала проверяется самый дальний шаг, поэтому каждый сеанс попадает ровно в одну корзину, что является тем же правилом упорядочивания «первый истинный выигрыш», что и любая другая цепочка условий.

Представьте на мгновение свою собственную воронку. Если вы перестроите ее как таблицу дальних шагов, добавятся ли ведра к числу вошедших людей? Если нет, то что-то в воронке учитывается в двух местах.

8. Порядок и временные окна

Два уточнения, которые будут иметь значение, как только данные станут реальными.

Заказ внутри сеанса. Приведенный выше подход с использованием флагов спрашивает, произошел ли шаг, а не произошел ли он после предыдущего. Сеанс покупки, а затем просмотра страницы продукта считается полным путем. Если последовательность имеет значение, сравнивайте временные метки, а не флаги: требуйте, чтобы время оформления заказа наступало после времени корзины, обычно с MIN(...) времени каждого шага за сеанс.

MIN(CASE WHEN step='cart'     THEN event_time END) AS cart_at,
MIN(CASE WHEN step='checkout' THEN event_time END) AS checkout_at
-- then: WHERE checkout_at > cart_at

Окно. Решите, сколько времени потребуется сеансу для завершения воронки. Без ограничений, повторное посещение через несколько недель засчитывается в исходный просмотр, что увеличивает конверсию и делает невозможным измерить любые изменения в процессе. При слишком узком лимите вы недооцениваете медленные, но настоящие конверсии. Правильного ответа нет, есть только заявленный, и он находится в заголовке отчета, а не в запросе.

Краевые случаи, разрушающие воронку

Шесть, которые стоит знать.

Названия шагов изменены. Релиз переименовывается add_to_cart к cart_add и середина воронки рушится в одночасье. Проверьте отдельные значения шага, прежде чем поверить в внезапное падение.

Шаг, который не везде логируется. Веб запускает его, приложение - нет. Затем воронка измеряет сочетание платформ, а не поведение, и она перемещается всякий раз, когда маркетинг меняет канал.

Сеансы, которые начинаются в середине воронки. Кто-то попадает на страницу продукта по электронной почте и никогда не видит домашнюю страницу. Они представляют собой настоящую конверсию и не проходят строгую воронку. Решите, действительно ли ваш верхний шаг необходим.

Боты. Они просматривают и никогда не покупают, поэтому раздувают верхнюю часть воронки и снижают все показатели ниже нее. Фильтруйте их перед подсчетом, а не после того, как подвергните сомнению цифры.

Сеансы, охватывающие полночь или тайм-аут. У одного человека получается две сессии, одна из которых просмотрена, а другая куплена, поэтому реальная конверсия выглядит как две неудачи. Это та же проблема идентификации, что и Разрешение объекта, применяется к сеансам, а не к клиентам.

Сравнение воронок разных групп населения. Воронка, которая улучшилась после кампании, возможно, просто началась с разных людей. Решение такое же, как и везде в аналитике: сравнивайте когорты, а не периоды, что и есть когортный анализ это для.