Конверсия в SQL: правильный числитель и знаменатель

SQL Studio / Разборы SQL / Конверсия в SQL: правильный числитель и знаменатель

Главное: конверсия — доля объектов, которые выполнили целевое действие, среди заранее определённой аудитории. Числитель должен быть подмножеством знаменателя, а период и единица подсчёта должны совпадать. В статье мы рассчитываем клиентскую конверсию известных посетителей в покупателей с доставкой, а не конверсию отдельных сессий.

1. Сначала определим аудиторию

В учебной базе девять сессий: восемь привязаны к клиентам и одна анонимная. Уникальных известных посетителей — четыре. За период с 31 августа до начала 8 сентября у троих из этих четырёх есть доставленный заказ. Сравним именно customer_id, а не число строк после соединения.

WITH audience AS (
  SELECT DISTINCT customer_id
  FROM sessions
  WHERE customer_id IS NOT NULL
    AND started_at >= '2026-08-31'
    AND started_at < '2026-09-08'
),
buyers AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE status = 'delivered'
    AND created_at >= '2026-08-31'
    AND created_at < '2026-09-08'
)
SELECT COUNT(*) AS visitors_n,
       COUNT(b.customer_id) AS buyers_n,
       ROUND(100.0 * COUNT(b.customer_id) / NULLIF(COUNT(*), 0), 2) AS conversion_pct
FROM audience AS a
LEFT JOIN buyers AS b ON b.customer_id = a.customer_id;

Мы не относим анонимное посещение к конкретному покупателю и не утверждаем, что именно эта сессия привела к заказу. Для настоящей конверсии визитов в заказ нужны связи между сессиями и покупками, правила атрибуции и окно наблюдения.

2. Ошибка JOIN: число строк — не число клиентов

Если напрямую соединить все сессии и все заказы по идентификатору клиента, одна сессия может сочетаться с несколькими заказами, а один заказ — с несколькими сессиями. Полученное число строк не имеет смысла в качестве знаменателя конверсии.

SELECT COUNT(*) AS rows_after_join,
       COUNT(DISTINCT s.customer_id) AS distinct_customers
FROM sessions AS s
JOIN orders AS o ON o.customer_id = s.customer_id
WHERE s.customer_id IS NOT NULL;

На наших данных соединение создаёт 14 строк, хотя уникальных клиентов в таком внутреннем JOIN всего 3: четвёртый клиент без заказа исчез. Это хорошая диагностика, но плохой способ считать аудиторию.

3. Почему нельзя складывать уникальных людей по каналам

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

SELECT channel,
       COUNT(*) AS sessions_n,
       COUNT(DISTINCT customer_id) AS known_customers_n
FROM sessions
GROUP BY channel
ORDER BY channel;

Результат: ads — 3 сессии и 2 известных клиента, email — 3 и 2, organic — 3 и 3. Сумма уникальных клиентов по каналам равна 7, но на всём сайте их 4. Без явной модели атрибуции нельзя назвать эти строки конверсией рекламных каналов.

4. Другой знаменатель — другая метрика

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

WITH audience AS (
  SELECT DISTINCT customer_id
  FROM sessions
  WHERE customer_id IS NOT NULL
),
deliveries AS (
  SELECT customer_id, COUNT(*) AS delivered_n
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
)
SELECT COUNT(*) AS visitors_n,
       SUM(CASE WHEN COALESCE(d.delivered_n, 0) >= 2 THEN 1 ELSE 0 END) AS repeat_buyers_n,
       ROUND(100.0 * SUM(CASE WHEN COALESCE(d.delivered_n, 0) >= 2 THEN 1 ELSE 0 END)
             / NULLIF(COUNT(*), 0), 2) AS repeat_share_pct
FROM audience AS a
LEFT JOIN deliveries AS d ON d.customer_id = a.customer_id;

Получится 4 известных посетителя, 1 повторный покупатель и 25%. Если бы мы считали только среди трёх покупателей, получили бы 33,33%. Оба числа математически правильны, но должны называться по-разному.

5. Три проверки перед публикацией отчёта

Уточните единицу подсчёта (клиент, сессия или заказ), определите одинаковый период наблюдения и убедитесь, что соединения не размножили строки. Пустую аудиторию обрабатывайте через NULLIF(знаменатель, 0), а не выдумывайте 0% конверсии.

Попробовать запросы

В бесплатной SQL Studio можно проверить эти вычисления на учебной модели сессий и заказов. Для продвинутой практики откройте задачу на конверсию клиентов.

Дальше: повторные покупки, RFM-анализ и COUNT DISTINCT.

Справка SQLite: COUNT и агрегаты, NULL и операторы сравнения. Названия и знаменатели конверсии определены в тексте до расчёта.

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.