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_join | supposedly_converted_sessions |
|---|---|
| 11 | 6 |
Получилось 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_n | converting_sessions_n | session_conversion_pct | known_users_n | buying_users_n | user_conversion_pct |
|---|---|---|---|---|---|
| 9 | 2 | 22,22% | 3 | 2 | 66,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;
| known_users_n | buying_users_n | session_conversion_pct |
|---|---|---|
| 3 | 2 | NULL |
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_n | session_conversion_pct |
|---|---|
| 0 | NULL |
Проверка для читателя
В бесплатной SQLite-песочнице добавьте третий оплаченный заказ в сессию №104. Что изменится в COUNT(o.order_id) и что не должно измениться в долях сессий и пользователей? Затем уберите анонимную сессию №401 и заново определите знаменатель.
См. справку SQLite о COUNT, MAX и агрегировании. Здесь используется SQLite; в отдельном курсе Stepik — MySQL 8, у него другие средства работы со временем сессий, хотя принцип единицы подсчёта тот же.
Если нужен структурированный курс на MySQL 8 с итоговым аналитическим отчётом, доступен отдельный курс Stepik. SQL Studio остаётся бесплатным.