Юнит-экономика в SQL: маржа заказа, CAC и LTV по когортам
Семь выполняемых запросов на данных учебного магазина: доход после возвратов, маржа заказа, когорты, CAC и месяц окупаемости. С результатами и проверками.
Содержание статьи
В отчёте магазина «маржа заказа» выглядит простой разницей. В SQL ошибка возникает раньше вычитания: один заказ содержит несколько товаров и возвратов, а строка рекламного бюджета относится сразу к месяцу и каналу. Прямой JOIN размножает деньги. На учебном магазине Wave соберём расчёт с зерном одной строки на оплаченный заказ, затем перейдём к когортам, CAC и окупаемости. Запросы ниже рассчитаны для DuckDB и дают проверяемые таблицы.
Коротко: порядок SQL-расчёта
Сначала сведите order_items и returns отдельно до order_id. Присоедините эти итоги к оплаченному заказу: так одна оплата остаётся одной строкой. Доход заказа — net_total после скидки минус известные возвраты; вклад — доход минус себестоимость с учётом восстановленного товара и прочие переменные затраты. После этого найдите месяц первой покупки клиента, накопите его вклад по возрасту когорты и сравните с бюджетом того же канала и месяца.
Для июльской paid_ads-когорты 180 новых покупателей сделали 218 заказов за июль–август. Вклад 328 604,93 ₽ даёт маржинальный LTV M1 1 825,58 ₽ на человека; модельный бюджет 250 000 ₽ — CAC 1 388,89 ₽. Отношение LTV/CAC равно 1,31. Это срез двух месяцев, не «пожизненная» ценность. Если нужен общий порядок расчёта для бизнеса, начните с хаба юнит-экономики.
LTV M1 = сумма вклада его когорты за M0 и M1 / число новых покупателей; CAC = бюджет канала в M0 / то же число покупателейОдинаковая когорта в двух знаменателях — обязательное условие сравнения.
- Агрегируйте товар и возврат до заказа.
- Назовите дату и окно возвратов.
- Возьмите первый оплаченный заказ для когорты, а не регистрацию.
- Присоединяйте месячный бюджет только к агрегату канала и месяца.
Зерно таблиц: где JOIN умножает деньги
В Wave orders хранит один заказ, order_items — товарные позиции, returns — события возврата, users — канал пользователя. Если у заказа три позиции и два возврата, сырое соединение позиций с возвратами даст шесть строк. Сумма net_total будет повторена шесть раз. COUNT(DISTINCT order_id) может исправить число заказов, но не сумму денег; ошибка остаётся в SUM.
Мы берём только status = paid, а возврат привязываем к исходному заказу независимо от даты возврата. CSV заканчиваются 31 октября 2024 года: поздние возвраты не видны. Сравнение сентябрьских и октябрьских заказов без одинакового окна возврата сместит вывод в пользу более молодых заказов. Затраты в примере — синтетические допущения, не данные реального магазина; SQL подставляет их из единого файла модели.
| Источник | Зерно | Как присоединять |
|---|---|---|
| orders | один заказ | основа; фильтр оплаченных |
| order_items | позиция внутри заказа | сначала SUM себестоимости по order_id |
| returns | событие возврата | сначала SUM денег и восстановленного товара по order_id |
| users | один пользователь | канал к заказу; проверять стабильность поля |
| бюджет модели | месяц × канал | к агрегату новых покупателей, не к каждому заказу |
Шаг 1. Доход после скидок и возвратов
gross_total показывает сумму до скидок, net_total — оплату после них. Возвраты лежат отдельно. Сведите их по заказу и только затем вычтите: LEFT JOIN оставляет заказы без возврата, а COALESCE превращает отсутствующий возврат в ноль. Полуоткрытый интервал дат включает весь сентябрь и не захватывает 1 октября даже при временной метке.
Результат: 1 433 оплаченных сентябрьских заказа, 8 672 855 ₽ после скидок и 605 982 ₽ известных возвратов. Остаётся 8 066 873 ₽. В 15 заказах генератор вернул сумму больше цены после скидки, суммарно на 17 640 ₽. Мы не обрезаем эти события до нуля: в рабочей базе их пришлось бы сверить с платежами, а здесь они остаются видимыми.
| Заказов | После скидки | Возвраты | Доход |
|---|---|---|---|
| 1 433 | 8 672 855 ₽ | 605 982 ₽ | 8 066 873 ₽ |
WITH refunds_by_order AS (
SELECT order_id, SUM(refund_rub) AS refund_rub
FROM returns GROUP BY order_id
)
SELECT COUNT(*) AS orders, SUM(o.net_total) AS paid_after_discount,
SUM(COALESCE(r.refund_rub, 0)) AS refunds,
SUM(o.net_total - COALESCE(r.refund_rub, 0)) AS revenue
FROM orders o LEFT JOIN refunds_by_order r USING (order_id)
WHERE o.status = 'paid'
AND o.placed_at >= DATE '2024-09-01'
AND o.placed_at < DATE '2024-10-01'Шаг 2. Себестоимость и вклад заказа
Себестоимость позиции в модели — unit_price × qty × ставка категории. В файле допущений ставки от 42% для beauty до 68% для electronics. Доставка 220 ₽ и упаковка 55 ₽ относятся к оплаченной покупке. Эквайринг 1,8% начисляем на net_total; при возврате не пересчитываем. Обработка возврата — 140 ₽ за событие. Восстановленная себестоимость товара уменьшает расход, но не возвращает покупателю деньги: это две разные строки.
Запрос ниже делает два отдельных агрегата и соединяет их с заказом. Результат сентября — 2 459 009,05 ₽ вклада до маркетинга, или 1 715,99 ₽ на оплаченный заказ. Вклад не равен чистой прибыли: ещё есть бюджет привлечения, 3 млн ₽ модельных постоянных расходов и не заданная налоговая база. Удобнее хранить результат запроса как представление с одной строкой на заказ и сверять COUNT(*) = COUNT(DISTINCT order_id).
| Заказов | Доход | Вклад до маркетинга | На заказ |
|---|---|---|---|
| 1 433 | 8 066 873 ₽ | 2 459 009,05 ₽ | 1 715,99 ₽ |
WITH item_cost AS (
SELECT order_id, SUM(unit_price * qty * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END)) AS cogs_initial
FROM order_items GROUP BY order_id
), refund_cost AS (
SELECT order_id, COUNT(*) AS return_count, SUM(refund_rub) AS refunds,
SUM(refund_rub * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END) * 0.75) AS recovered_cogs
FROM returns GROUP BY order_id
), order_economics AS (
SELECT o.order_id, o.user_id, o.placed_at, u.channel,
o.gross_total, o.net_total, i.cogs_initial,
COALESCE(r.refunds, 0) AS refunds,
COALESCE(r.return_count, 0) AS return_count,
o.net_total - COALESCE(r.refunds, 0) AS revenue,
o.net_total - COALESCE(r.refunds, 0)
- i.cogs_initial + COALESCE(r.recovered_cogs, 0)
- 275
- o.net_total * 0.018
- COALESCE(r.return_count, 0) * 140 AS contribution
FROM orders o JOIN users u USING (user_id)
JOIN item_cost i USING (order_id)
LEFT JOIN refund_cost r USING (order_id)
WHERE o.status = 'paid'
)
SELECT COUNT(*) AS orders, ROUND(SUM(revenue), 2) AS revenue,
ROUND(SUM(contribution), 2) AS contribution,
ROUND(AVG(contribution), 2) AS contribution_per_order
FROM order_economics
WHERE placed_at >= DATE '2024-09-01'
AND placed_at < DATE '2024-10-01'Как менять допущения, не переписывая смысл метрики
Числа себестоимости, доставки, эквайринга и обработки возврата в SQL — параметры модели, а не поля Wave. Для своего магазина лучше вынести их в отдельную таблицу ставок с датой действия и категорией. Тогда товарные позиции соединяются со ставкой своей категории, а отчёт можно пересчитать для прошлого периода без подмены сегодняшними условиями. До такого справочника явный блок допущений в запросе честнее, чем невидимый процент в BI.
На 1 433 заказах сентября изменение средней маржи на 100 ₽ означает изменение месячного вклада на 143 300 ₽ при том же объёме. Но новый тариф доставки может зависеть от веса, региона или склада. Если измерение отсутствует, нельзя обещать такой же результат на другом составе заказов. Подставьте ставку в одном месте, затем проверьте, что итог по заказам сходится с суммой по категориям.
| Параметр | Значение | Что меняется |
|---|---|---|
| Закупка по категориям | 42–68% цены позиции | Себестоимость товара |
| Доставка + упаковка | 220 + 55 ₽ | Вклад каждого оплаченного заказа |
| Эквайринг | 1,8% после скидки | Вклад, но не доход |
| Возврат | 140 ₽; восстановление 75% себестоимости | Вклад возвращённого заказа |
Шаг 3. Когорта — месяц первой оплаченной покупки
Регистрация не равна покупке. Для экономического LTV когорта начинается с первого оплаченного заказа пользователя. ROW_NUMBER() выбирает его при сортировке по времени и order_id; второй ключ снимает случайную ничью по времени. Канал здесь — постоянное поле users.channel. Если реальные каналы меняются между сессиями, нужна отдельная модель атрибуции и зафиксированное правило назначения канала когорте.
В июле 2024 года было 580 новых покупателей: 180 paid_ads, 176 organic, 109 email, 67 social и 48 referral. Это исходные знаменатели для LTV и CAC. Не делите июльские расходы на всех июльских посетителей или на тех, кто купил уже в августе. У разных знаменателей может получиться красивое, но бессмысленное отношение.
| Канал | Покупатели |
|---|---|
| 109 | |
| organic | 176 |
| paid_ads | 180 |
| referral | 48 |
| social | 67 |
WITH item_cost AS (
SELECT order_id, SUM(unit_price * qty * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END)) AS cogs_initial
FROM order_items GROUP BY order_id
), refund_cost AS (
SELECT order_id, COUNT(*) AS return_count, SUM(refund_rub) AS refunds,
SUM(refund_rub * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END) * 0.75) AS recovered_cogs
FROM returns GROUP BY order_id
), order_economics AS (
SELECT o.order_id, o.user_id, o.placed_at, u.channel,
o.gross_total, o.net_total, i.cogs_initial,
COALESCE(r.refunds, 0) AS refunds,
COALESCE(r.return_count, 0) AS return_count,
o.net_total - COALESCE(r.refunds, 0) AS revenue,
o.net_total - COALESCE(r.refunds, 0)
- i.cogs_initial + COALESCE(r.recovered_cogs, 0)
- 275
- o.net_total * 0.018
- COALESCE(r.return_count, 0) * 140 AS contribution
FROM orders o JOIN users u USING (user_id)
JOIN item_cost i USING (order_id)
LEFT JOIN refund_cost r USING (order_id)
WHERE o.status = 'paid'
), first_buy AS (
SELECT * EXCLUDE (rn) FROM (
SELECT oe.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY placed_at, order_id) AS rn
FROM order_economics oe
) WHERE rn = 1
)
SELECT channel, COUNT(*) AS new_buyers
FROM first_buy
WHERE placed_at >= DATE '2024-07-01'
AND placed_at < DATE '2024-08-01'
GROUP BY channel ORDER BY channelШаг 4. Накопленный маржинальный LTV
Для июльской paid_ads-когорты сначала отберите 180 user_id первой покупки. Затем заберите все их оплаченные заказы до 1 сентября: повторные покупки не должны исчезнуть из LTV. Возраст M0 — июль, M1 — август. SUM(...) OVER накопит вклад по возрасту; знаменатель всё время остаётся 180, даже если часть людей больше не покупала. Делить августовский вклад только на вернувшихся — значит завысить ценность всей исходной когорты.
В M0 вклад на покупателя равен 1 760,00 ₽, к концу M1 — 1 825,58 ₽. Дополнительные 65,58 ₽ не означает, что любой будущий месяц принесёт столько же. В сентябре эта когорта дала ещё деньги, но сравнение каналов в этой статье останавливается на одинаковом M1. Возвраты заказов известны только до 31 октября: у старых когорт окно наблюдения шире, чем у новых.
| Возраст | Вклад месяца | LTV на клиента |
|---|---|---|
| M0 | 316 800,39 ₽ | 1 760,00 ₽ |
| M1 | 11 804,54 ₽ | 1 825,58 ₽ |
M1 — август; вклад на исходные 180 новых покупателей, затраты на привлечение — модельный бюджет июля.
WITH item_cost AS (
SELECT order_id, SUM(unit_price * qty * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END)) AS cogs_initial
FROM order_items GROUP BY order_id
), refund_cost AS (
SELECT order_id, COUNT(*) AS return_count, SUM(refund_rub) AS refunds,
SUM(refund_rub * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END) * 0.75) AS recovered_cogs
FROM returns GROUP BY order_id
), order_economics AS (
SELECT o.order_id, o.user_id, o.placed_at, u.channel,
o.gross_total, o.net_total, i.cogs_initial,
COALESCE(r.refunds, 0) AS refunds,
COALESCE(r.return_count, 0) AS return_count,
o.net_total - COALESCE(r.refunds, 0) AS revenue,
o.net_total - COALESCE(r.refunds, 0)
- i.cogs_initial + COALESCE(r.recovered_cogs, 0)
- 275
- o.net_total * 0.018
- COALESCE(r.return_count, 0) * 140 AS contribution
FROM orders o JOIN users u USING (user_id)
JOIN item_cost i USING (order_id)
LEFT JOIN refund_cost r USING (order_id)
WHERE o.status = 'paid'
), first_buy AS (
SELECT * EXCLUDE (rn) FROM (
SELECT oe.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY placed_at, order_id) AS rn
FROM order_economics oe
) WHERE rn = 1
), acquired AS (
SELECT user_id, placed_at AS first_paid_at
FROM first_buy WHERE channel = 'paid_ads'
AND placed_at >= DATE '2024-07-01' AND placed_at < DATE '2024-08-01'
), by_age AS (
SELECT date_diff('month', date_trunc('month', a.first_paid_at),
date_trunc('month', o.placed_at)) AS age_month,
SUM(o.contribution) AS contribution
FROM acquired a JOIN order_economics o USING (user_id)
WHERE o.placed_at < DATE '2024-09-01'
GROUP BY 1
)
SELECT age_month, ROUND(contribution, 2) AS contribution,
ROUND(SUM(contribution) OVER (ORDER BY age_month) / 180, 2) AS cumulative_ltv
FROM by_age ORDER BY age_monthШаг 5. CAC на те же каналы и месяц
Расходы на рекламу не хранятся в CSV Wave. В запросе ниже budget — синтетическая таблица июля из единого файла допущений: медиа, команда и инструменты. Бюджет paid_ads равен 250 000 ₽. Присоединяем его к пяти уже посчитанным агрегатам новых покупателей, а не к 218 заказам или товарным позициям. Так 250 000 ₽ остаются одной строкой канала.
CAC paid_ads равен 250 000 / 180 = 1 388,89 ₽. Organic здесь не бесплатен: в модели у него 30 000 ₽ на команду, инструменты и небольшое медиа, поэтому CAC 170,45 ₽. Эти расходы не фактическая бухгалтерия магазина. Если в своём бизнесе часть команды обслуживает и текущих клиентов, явно выберите правило распределения; механическое включение всей зарплаты в привлечение исказит CAC.
| Канал | Новых | Бюджет | CAC |
|---|---|---|---|
| 109 | 40 000 ₽ | 366,97 ₽ | |
| organic | 176 | 30 000 ₽ | 170,45 ₽ |
| paid_ads | 180 | 250 000 ₽ | 1 388,89 ₽ |
| referral | 48 | 25 000 ₽ | 520,83 ₽ |
| social | 67 | 85 000 ₽ | 1 268,66 ₽ |
WITH item_cost AS (
SELECT order_id, SUM(unit_price * qty * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END)) AS cogs_initial
FROM order_items GROUP BY order_id
), refund_cost AS (
SELECT order_id, COUNT(*) AS return_count, SUM(refund_rub) AS refunds,
SUM(refund_rub * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END) * 0.75) AS recovered_cogs
FROM returns GROUP BY order_id
), order_economics AS (
SELECT o.order_id, o.user_id, o.placed_at, u.channel,
o.gross_total, o.net_total, i.cogs_initial,
COALESCE(r.refunds, 0) AS refunds,
COALESCE(r.return_count, 0) AS return_count,
o.net_total - COALESCE(r.refunds, 0) AS revenue,
o.net_total - COALESCE(r.refunds, 0)
- i.cogs_initial + COALESCE(r.recovered_cogs, 0)
- 275
- o.net_total * 0.018
- COALESCE(r.return_count, 0) * 140 AS contribution
FROM orders o JOIN users u USING (user_id)
JOIN item_cost i USING (order_id)
LEFT JOIN refund_cost r USING (order_id)
WHERE o.status = 'paid'
), first_buy AS (
SELECT * EXCLUDE (rn) FROM (
SELECT oe.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY placed_at, order_id) AS rn
FROM order_economics oe
) WHERE rn = 1
), acquired AS (
SELECT channel, COUNT(*) AS new_buyers FROM first_buy
WHERE placed_at >= DATE '2024-07-01' AND placed_at < DATE '2024-08-01'
GROUP BY channel
), budget(channel, marketing_rub) AS (
VALUES ('email', 40000), ('organic', 30000), ('paid_ads', 250000), ('referral', 25000), ('social', 85000)
)
SELECT a.channel, a.new_buyers, b.marketing_rub,
ROUND(b.marketing_rub / a.new_buyers, 2) AS cac
FROM acquired a JOIN budget b USING (channel) ORDER BY a.channelШаг 6. Найти первый месяц окупаемости
Окупаемость — первый возраст когорты, в котором накопленный вклад на одного исходного клиента не меньше CAC той же когорты. У paid_ads июля уже M0 даёт 1 760,00 ₽ против 1 388,89 ₽ CAC, поэтому первый наблюдённый месяц окупаемости — M0. Это не обещание о новых покупателях: их цена привлечения и состав заказов могут отличаться.
В запросе лимит до 1 сентября ограничивает горизонт M0–M1. Если порог за этот срок не достигнут, MIN(CASE...) возвращает NULL. В отчёте напишите «не окупился за наблюдаемый горизонт», а не «никогда не окупится». При нескольких каналах повторите запрос с разделением по каналу и одним CAC на каждую когорту; поздние когорты сравнивайте только на том возрасте, который доступен всем.
| Первый месяц | LTV M1 | CAC |
|---|---|---|
| M0 | 1 825,58 ₽ | 1 388,89 ₽ |
WITH item_cost AS (
SELECT order_id, SUM(unit_price * qty * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END)) AS cogs_initial
FROM order_items GROUP BY order_id
), refund_cost AS (
SELECT order_id, COUNT(*) AS return_count, SUM(refund_rub) AS refunds,
SUM(refund_rub * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END) * 0.75) AS recovered_cogs
FROM returns GROUP BY order_id
), order_economics AS (
SELECT o.order_id, o.user_id, o.placed_at, u.channel,
o.gross_total, o.net_total, i.cogs_initial,
COALESCE(r.refunds, 0) AS refunds,
COALESCE(r.return_count, 0) AS return_count,
o.net_total - COALESCE(r.refunds, 0) AS revenue,
o.net_total - COALESCE(r.refunds, 0)
- i.cogs_initial + COALESCE(r.recovered_cogs, 0)
- 275
- o.net_total * 0.018
- COALESCE(r.return_count, 0) * 140 AS contribution
FROM orders o JOIN users u USING (user_id)
JOIN item_cost i USING (order_id)
LEFT JOIN refund_cost r USING (order_id)
WHERE o.status = 'paid'
), first_buy AS (
SELECT * EXCLUDE (rn) FROM (
SELECT oe.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY placed_at, order_id) AS rn
FROM order_economics oe
) WHERE rn = 1
), acquired AS (
SELECT user_id, placed_at AS first_paid_at FROM first_buy
WHERE channel = 'paid_ads' AND placed_at >= DATE '2024-07-01'
AND placed_at < DATE '2024-08-01'
), by_age AS (
SELECT date_diff('month', date_trunc('month', a.first_paid_at),
date_trunc('month', o.placed_at)) AS age_month,
SUM(o.contribution) AS contribution
FROM acquired a JOIN order_economics o USING (user_id)
WHERE o.placed_at < DATE '2024-09-01' GROUP BY 1
), running AS (
SELECT age_month, SUM(contribution) OVER (ORDER BY age_month) / 180 AS ltv
FROM by_age
)
SELECT MIN(CASE WHEN ltv >= 250000.0 / 180 THEN age_month END) AS payback_month,
ROUND(MAX(ltv), 2) AS ltv_m1,
ROUND(250000.0 / 180, 2) AS cac
FROM runningСценарии: что изменит вывод о канале
Проверенный скрипт меняет один параметр, оставляя 180 покупателей и 218 заказов. Дополнительная скидка 10% от уже сниженной суммы заказа уменьшает LTV M1 до 1 146,77 ₽ и отношение к тому же CAC до 0,83. Возвраты +5 п. п. снижают LTV до 1 644,48 ₽ и отношение до 1,18. При CAC +20% вклад не меняется, но отношение становится 1,10. Это сценарии, не прогноз спроса: скидка может изменить число покупок, а расширение рекламы — состав покупателей.
В SQL такие сценарии лучше оформить отдельными параметрами рядом с моделью затрат. Не меняйте фактические net_total или returns задним числом. Записывайте номер сценария и сравнивайте одинаковый возраст когорт. При ином сроке возвратов пересчитайте доход и восстановленную себестоимость вместе, иначе «улучшение LTV» может означать только пропавший возврат.
| Изменение | LTV / клиент | LTV/CAC | Что проверить |
|---|---|---|---|
| База | 1 825,58 ₽ | 1,31 | Окно M0–M1 |
| Ещё 10% скидки | 1 146,77 ₽ | 0,83 | Спрос может измениться |
| Возвраты +5 п. п. | 1 644,48 ₽ | 1,18 | Время возврата и восстановление товара |
| CAC +20% | 1 825,58 ₽ | 1,10 | Цена следующих покупателей |
Контроль качества перед отправкой в BI
На всём учебном наборе 6 328 оплаченных заказов. Для каждого order_id в модельной таблице должна остаться ровно одна строка. Если COUNT(*) и COUNT(DISTINCT order_id) расходятся, следующие суммы недостоверны. Второй контроль — после группировки по каналу сумма дохода должна сходиться с общей суммой без разреза. Третий — возврат должен относиться к существующему товару исходного заказа.
Запрос ниже проверяет зерно и отсутствие пропущенной себестоимости. Он не доказывает, что платежи и возвраты верны: 15 сентябрьских переплат по возвратам остались в синтетической выгрузке. В реальном потоке добавьте карантин для таких случаев и не подменяйте проверку автоматическим GREATEST(0, ...) — он создаст красивые, но необъяснимые суммы.
| Строк | Заказов | Без себестоимости |
|---|---|---|
| 6 328 | 6 328 | 0 |
WITH item_cost AS (
SELECT order_id, SUM(unit_price * qty * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END)) AS cogs_initial
FROM order_items GROUP BY order_id
), refund_cost AS (
SELECT order_id, COUNT(*) AS return_count, SUM(refund_rub) AS refunds,
SUM(refund_rub * (CASE category WHEN 'beauty' THEN 0.42 WHEN 'books' THEN 0.62 WHEN 'clothing' THEN 0.48 WHEN 'electronics' THEN 0.68 WHEN 'home' THEN 0.52 WHEN 'sport' THEN 0.55 END) * 0.75) AS recovered_cogs
FROM returns GROUP BY order_id
), order_economics AS (
SELECT o.order_id, o.user_id, o.placed_at, u.channel,
o.gross_total, o.net_total, i.cogs_initial,
COALESCE(r.refunds, 0) AS refunds,
COALESCE(r.return_count, 0) AS return_count,
o.net_total - COALESCE(r.refunds, 0) AS revenue,
o.net_total - COALESCE(r.refunds, 0)
- i.cogs_initial + COALESCE(r.recovered_cogs, 0)
- 275
- o.net_total * 0.018
- COALESCE(r.return_count, 0) * 140 AS contribution
FROM orders o JOIN users u USING (user_id)
JOIN item_cost i USING (order_id)
LEFT JOIN refund_cost r USING (order_id)
WHERE o.status = 'paid'
)
SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS orders,
SUM(CASE WHEN cogs_initial IS NULL THEN 1 ELSE 0 END) AS missing_cost
FROM order_economicsОшибки, которые меняют решение
Первая — соединить позиции и возвраты до агрегации: сумма заказа размножится. Вторая — использовать gross_total вместо net_total: скидки исчезнут, маржа вырастет на бумаге. Третья — отнести возврат только к месяцу возврата при сравнении когорт заказов: старая когорта получит чужой минус, молодая покажется прибыльной. Четвёртая — вычитать всю себестоимость возвращённого товара, не учитывая, пригоден ли он к повторной продаже.
Пятая — строить когорту по регистрации и делить бюджет на покупателей: знаменатель CAC и знаменатель LTV окажутся разными. Шестая — соединить бюджет месяца с каждым заказом: расходы умножатся на число строк. Седьмая — считать LTV по выручке и сравнивать с CAC: товар, доставка и возвраты выпадут. Восьмая — назвать M1 «пожизненным» и экстраполировать его на будущих клиентов без одинакового окна наблюдения.
Проверяйте эти ошибки в указанном порядке. Если зерно неверно, спорить о коэффициенте эквайринга бессмысленно: до нужной формулы запрос ещё не дошёл. Сохраните контрольные суммы на каждом переходе: от исходных оплат к заказам после возврата, от заказов к когортам, от когорт к бюджетам. Когда цифра внезапно меняется, такая цепочка покажет конкретный JOIN или фильтр, а не заставит искать ошибку во всём отчёте.
Частые вопросы и следующий шаг
«Можно ли считать LTV прямо из таблицы заказов?» Да, после того как вы определили первый оплаченный заказ и фиксированный размер исходной когорты. «Почему COUNT(DISTINCT) не чинит JOIN?» Он исправляет только счётчик заказов, но повторённая выручка остаётся в SUM. «Нужен ли SQL, если есть калькулятор?» Для одного планового товара — нет; для тысяч заказов, возвратов и каналов SQL показывает, из каких строк вырос ввод калькулятора.
«Почему paid_ads окупился в M0, а LTV/CAC всего 1,31?» Окупаемость означает пересечение CAC накопленным вкладом; 1,31 показывает небольшой запас на M1, а не противоречит пересечению. «Можно ли перенести SQL в другую СУБД?» Логика зерна переносится, но функции дат и чтение CSV меняются. Этот текст использует DuckDB; синтаксис date_trunc и date_diff описан в официальной документации.
Хотите потренировать JOIN и оконные функции на выполняемых задачах — откройте SQL-тренажёр. Для своего запроса без задания есть песочница симулятора. Плановую экономику одного заказа можно сравнить в калькуляторе.
Материалы по теме
PARTITION BY и OVER в SQL: окно, группы и отличие от GROUP BY
PARTITION BY и OVER в SQL: окно и отличие от GROUP BY, доля от итога, накопительный итог, рамки ROWS и RANGE, фильтр по оконной функции.
LAG и LEAD в SQL: предыдущая и следующая строка на примерах
LAG и LEAD в SQL и PostgreSQL на учебной базе: синтаксис с offset и default, паузы между событиями, DAU к вчера и к прошлой неделе, следующий шаг после события, ничьи в ORDER BY, QUALIFY и IGNORE NULLS.
INTERVAL в SQL: прибавить дни, «последние 7 дней» и date_trunc
INTERVAL в PostgreSQL и DuckDB на учебной базе: синтаксис, дата плюс дни, окно «последние 7 дней» без now(), границы месяца через date_trunc, скользящее окно и календарь.