Конверсия сессий: как правильно привязать покупку

Покупателей двое, но сессий девять. Запрос может показать убедительные 66,67% и при этом отвечать совсем не на тот вопрос, который нужен бизнесу.

SQL Studio / Разборы SQL / Конверсия сессий и привязка заказа

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

Данные: девять сессий, три оплаченных заказа

В CTE sessions у пользователя №1 пять сессий, у №2 — две, у №3 — одна; визит №401 анонимный (user_id IS NULL). Две оплаченные покупки пользователя №1 относятся к сессии №104; одна покупка пользователя №3 — к №301. Отменённый заказ пользователя №2 не является конверсией. Здесь session_id заказа записан источником как факт, а не восстановлен догадкой по времени. Главное отличие от общего разбора конверсии в SQL — здесь мы проверяем именно связь заказа с идентификатором сессии. Без такой связи нельзя автоматически приписывать покупку последнему посещению.

Мы анализируем один фиксированный синтетический период и только статус paid; уникальные идентификаторы сессий и клиентов считаем стабильными в этом наборе. Для реального отчёта границы дат сессий и целевых событий должны совпадать; поздние покупки и переходы через границу требуют заранее согласованной политики.

1. Правдоподобная ошибка: соединить только по пользователю

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

WITH sessions(session_id, user_id) AS (
  VALUES (101,1),(102,1),(103,1),(104,1),(105,1),
         (201,2),(202,2),(301,3),(401,NULL)
),
orders(order_id, session_id, user_id, status) AS (
  VALUES (1001,104,1,'paid'),
         (1002,104,1,'paid'),
         (1003,301,3,'paid'),
         (1004,NULL,2,'cancelled')
)
SELECT COUNT(*) AS rows_after_join,
       COUNT(DISTINCT s.session_id) AS supposedly_converted_sessions
FROM sessions AS s
JOIN orders AS o ON o.user_id = s.user_id
WHERE o.status = 'paid';
Неверное число конвертирующих сессий
rows_after_joinsupposedly_converted_sessions
116

Получилось 11 строк и шесть якобы конвертирующих сессий. Это не «шесть успешных визитов»: на самом деле покупка была только в сессиях №104 и №301. Даже COUNT(DISTINCT s.session_id) не спасает, когда условие связи ошибочно.

2. Правильно: вернуть одну строку на сессию

Присоединяем заказ к сессии одновременно по session_id и user_id, и только для paid. MAX(...) превращает несколько покупок в один флаг конверсии сессии. Затем отдельно подсчитываем все сессии, известные лица и покупателей; анонимный визит включён в сессионную аудиторию, но не в знаменатель известных пользователей.

WITH sessions(session_id, user_id) AS (
  VALUES (101,1),(102,1),(103,1),(104,1),(105,1),
         (201,2),(202,2),(301,3),(401,NULL)
),
orders(order_id, session_id, user_id, status) AS (
  VALUES (1001,104,1,'paid'),
         (1002,104,1,'paid'),
         (1003,301,3,'paid'),
         (1004,NULL,2,'cancelled')
),
per_session AS (
  SELECT s.session_id, s.user_id,
         MAX(CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END) AS converted
  FROM sessions AS s
  LEFT JOIN orders AS o
    ON o.session_id = s.session_id
   AND o.user_id = s.user_id
   AND o.status = 'paid'
  GROUP BY s.session_id, s.user_id
)
SELECT COUNT(*) AS sessions_n,
       SUM(converted) AS converting_sessions_n,
       ROUND(100.0 * SUM(converted) / NULLIF(COUNT(*), 0), 2) AS session_conversion_pct,
       COUNT(DISTINCT user_id) AS known_users_n,
       COUNT(DISTINCT CASE WHEN converted = 1 THEN user_id END) AS buying_users_n,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN converted = 1 THEN user_id END)
         / NULLIF(COUNT(DISTINCT user_id), 0), 2) AS user_conversion_pct
FROM per_session;
Разные единицы подсчёта на одних данных
sessions_nconverting_sessions_nsession_conversion_pctknown_users_nbuying_users_nuser_conversion_pct
9222,22%3266,67%

22,22% — доля покупающих сессий (2 из 9). 66,67% — доля известных посетителей с хотя бы одной покупкой (2 из 3). Нельзя назвать эти показатели эффектом канала: сессии не содержат каналы, а покупка связана с визитом только по ключу.

3. Без ключа сессии нельзя честно получить точную конверсию сессий

Представим, что заказ хранит лишь user_id. Мы всё ещё знаем, кто из известных посетителей покупал, но не знаем, какая именно из пяти сессий первого пользователя привела к продаже. Не выдаём JOIN по клиенту или «ближайший визит» за подтверждённый факт: ближайшее событие — отдельная атрибуционная модель, а не восстановление потерянного поля.

WITH sessions(session_id, user_id) AS (
  VALUES (101,1),(102,1),(103,1),(104,1),(105,1),
         (201,2),(202,2),(301,3),(401,NULL)
), orders_without_session(order_id, user_id) AS (
  VALUES (1001,1),(1002,1),(1003,3)
), known_users AS (
  SELECT DISTINCT user_id FROM sessions WHERE user_id IS NOT NULL
), buyers AS (
  SELECT DISTINCT o.user_id
  FROM orders_without_session AS o
  JOIN known_users AS k ON k.user_id = o.user_id
)
SELECT (SELECT COUNT(*) FROM known_users) AS known_users_n,
       (SELECT COUNT(*) FROM buyers) AS buying_users_n,
       CAST(NULL AS REAL) AS session_conversion_pct;
Что можно установить, когда order.session_id отсутствует
known_users_nbuying_users_nsession_conversion_pct
32NULL

NULL означает «не определено по имеющимся связям», а не нулевую сессионную конверсию. Для продакшена дополнительно проверьте уникальность session_id между устройствами, идентичность владельца заказа и согласие на сбор событий.

4. Крайний случай: пустая аудитория

Когда в выбранном периоде нет ни одной сессии, делить не на что. NULLIF даёт NULL, вместо мнимых 0%: у пустой аудитории нет измеримой доли.

WITH sessions(session_id, user_id) AS (
  SELECT 1, 1 WHERE 0
)
SELECT COUNT(*) AS sessions_n,
       100.0 * SUM(1) / NULLIF(COUNT(*), 0) AS session_conversion_pct
FROM sessions;
Пустой знаменатель
sessions_nsession_conversion_pct
0NULL

Проверка для читателя

В бесплатной SQLite-песочнице добавьте третий оплаченный заказ в сессию №104. Что изменится в COUNT(o.order_id) и что не должно измениться в долях сессий и пользователей? Затем уберите анонимную сессию №401 и заново определите знаменатель.

Для продолжения: как определить аудиторию в конверсии, COUNT DISTINCT и NULL, почему WHERE после LEFT JOIN теряет непокупателей.

См. справку SQLite о COUNT, MAX и агрегировании. Здесь используется SQLite; в отдельном курсе Stepik — MySQL 8, у него другие средства работы со временем сессий, хотя принцип единицы подсчёта тот же.

Сравните единицы подсчёта: задача P120 «Конверсия клиентов в покупателей» считает уникальных клиентов, а здесь мы отдельно проверяем сессии по session_id. Числители и знаменатели менять местами нельзя.


Если нужен структурированный курс на MySQL 8 с итоговым аналитическим отчётом, доступен отдельный курс Stepik. SQL Studio остаётся бесплатным.