Метрики продаж: выручка, число заказов и AOV с учётом статусов и возвратов

В предыдущих уроках фильтр status='paid' и агрегация по order_id уже встречались при построении временных рядов. Здесь эти же операции получают точное управленческое определение: они становятся частью формулы, по которой считаются выручка, число заказов и средний чек.

Управленческое определение выручки: фильтр по статусу и корректировка на возвраты

В таблице orders каждый заказ имеет статус paid или cancelled. В выбранном учебном управленческом определении в выручку включаются только paid-заказы — это явный методологический выбор. Cancelled-заказы исключаются независимо от того, заполнено ли у них поле gross_revenue положительным числом. При этом статус сам по себе не доказывает историю платежа, отгрузки или возврата конкретного заказа: cancelled лишь означает, что данный заказ не попадает в метрику paid-продаж по принятому определению.

После фильтрации по статусу выручка считается в два шага.

Шаг 1 — gross_revenue paid-заказов: gross_revenue_paid = SUM(gross_revenue) по строкам orders, где status = 'paid' и order_date попадает в нужный период.

Шаг 2 — вычет возвратов: net_revenue = gross_revenue_paid − SUM(refund_amount по тем же order_id, где refund_date ≤ дата среза)

Дата среза фиксируется явно. В учебном наборе «Маяка» возвраты считаются известными на конец 2025-08-31. Это означает: для отчёта за январь 2024 берутся все возвраты по январским paid-заказам, зарегистрированные до этой даты, — не только те, что поступили в январе.

Для января 2024: gross_revenue paid-заказов составляет 933 000 руб., после вычета возвратов net_revenue равен 911 612 руб. Разница в 21 388 руб. — это суммарные refund_amount по январским paid-заказам на дату среза.

Что происходит при включении cancelled-заказов? Их gross_revenue прибавляется к числителю в нарушение принятого определения. Если cancelled-заказы за январь имеют ненулевые суммы, итоговая выручка завышается — и пропорционально завышается AOV, если знаменатель остаётся корректным. Направление ошибки однозначное: cancelled-заказы добавляют к числителю положительные суммы.

Важная граница: возврат уменьшает числитель (выручку), но не исключает заказ из числа заказов. Клиент разместил и оплатил заказ — этот факт фиксируется в знаменателе независимо от последующего возврата. Корректировка на refund_amount затрагивает только числитель; знаменатель остаётся равным COUNT(DISTINCT order_id) по всем paid-заказам периода, включая те, по которым были возвраты.

Grain метрики «число заказов» и ошибка суммирования по товарным сегментам

Число заказов — это количество уникальных order_id среди paid-заказов за выбранный период. Grain метрики — один order_id, один раз, независимо от того, сколько позиций (строк в order_items) он содержит.

Правильный способ считать:

SELECT COUNT(DISTINCT order_id) AS paid_orders
FROM orders
WHERE status = 'paid'
  AND strftime('%Y-%m', order_date) = '2024-01';

Для января 2024 этот запрос даёт 82 заказа. Именно это число стоит в знаменателе AOV за январь.

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

Предположим, заказ O000017 содержит позиции в категориях «Электроника» и «Аксессуары». В таблице order_items это две отдельные строки с одним и тем же order_id. Если посчитать COUNT(DISTINCT order_id) внутри каждой категории, заказ O000017 войдёт в счётчик обеих категорий — по одному разу в каждой.

При сложении этих категориальных счётчиков:

count_orders(Электроника) = 1
count_orders(Аксессуары)  = 1
Сумма = 2, но уникальных заказов = 1

Чем больше многокатегорийных заказов в периоде, тем сильнее завышение. Если в январе значительная часть из 82 заказов содержит позиции более чем одной категории, сумма категориальных счётчиков может быть существенно выше 82.

Отдельный нюанс: даже COUNT(order_id) без DISTINCT по таблице order_items считает строки-позиции, а не заказы. Один заказ с тремя позициями одной категории даст COUNT = 3.

Правило: для общего числа заказов всегда идти к таблице orders, где grain — один заказ, и применять COUNT(DISTINCT order_id) или nunique() в pandas. Категориальные агрегаты из order_items показывают охват категорий, но не суммируются в общий итог.

AOV: формула, асимметрия числителя и знаменателя, защита от деления на ноль

AOV (Average Order Value, средний чек) — это выручка, делённая на число заказов:

AOV = net_revenue / paid_orders

Для января 2024: AOV = 911 612 / 82 ≈ 11 117 руб. Именно это значение содержит поле aov в sales_monthly за период 2024-01.

АOV — составная метрика, и ошибки в числителе и знаменателе действуют по-разному.

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

Ошибка в знаменателе: удаление возвращённых заказов. Допустим, аналитик исключает из знаменателя те paid-заказы, по которым есть запись в refunds, — оставляя только «чистые» заказы без возвратов. Логика кажется разумной: «возврат был, значит, заказ не состоялся». Но это неверно: клиент разместил и оплатил заказ, факт события зафиксирован. Возврат корректирует полученную выручку (числитель), а не отменяет сам заказ. Удаление таких order_id из знаменателя его занижает. При положительной net_revenue и положительном оставшемся числе заказов AOV завышается — возникает иллюзия более высокого среднего чека.

Если же net_revenue равна нулю, удаление части заказов оставит AOV нулём; если удалить все возвращённые заказы и знаменатель обнулится, AOV станет неопределённым (NULL / NaN).

Симметричное правило: числитель уменьшается на refund_amount, знаменатель остаётся COUNT(DISTINCT order_id) по всем paid-заказам периода, включая те, по которым были возвраты.

Разберём на учебном примере (данные условные):

order_id | status    | gross_revenue
101      | paid      | 2 000 руб.
102      | paid      | 5 000 руб.   ← возврат 1 000 руб.
103      | cancelled | 1 500 руб.
104      | paid      | 3 000 руб.

Правильный расчёт:

• gross_revenue paid: 2 000 + 5 000 + 3 000 = 10 000 руб.
• возврат по order 102: 1 000 руб.
• net_revenue = 10 000 − 1 000 = 9 000 руб.
• paid_orders = 3 (orders 101, 102, 104)
• AOV = 9 000 / 3 = 3 000 руб.

Ошибка 1 — включить cancelled (order 103), сохраняя вычет возврата:

• числитель = 9 000 + 1 500 = 10 500 руб., знаменатель = 3 → AOV = 3 500 руб. (завышен)

Ошибка 2 — исключить order 102 из знаменателя как «возвращённый»:

• числитель = 9 000 руб., знаменатель = 2 → AOV = 4 500 руб. (завышен, так как net_revenue положительна и остаётся хотя бы один заказ)

Ошибка 3 — не вычесть возврат из числителя:

• числитель = 10 000 руб., знаменатель = 3 → AOV ≈ 3 333 руб. (завышен)

Защита от деления на ноль. Если за выбранный период нет ни одного paid-заказа, знаменатель равен нулю и деление не выполняется. Корректный результат — NULL в SQL или NaN в pandas. SQL-защита с net-числителем (предполагается, что возвраты предагрегированы в переменную r.refund_amount до JOIN):

CASE WHEN COUNT(DISTINCT o.order_id) = 0
     THEN NULL
     ELSE (SUM(o.gross_revenue) - COALESCE(SUM(r.refund_amount), 0))
          * 1.0 / COUNT(DISTINCT o.order_id)
END AS aov

В pandas защита выглядит так:

aov = net_revenue / paid_orders if paid_orders > 0 else float('nan')

Возвращать ноль вместо NULL/NaN неверно: ноль означал бы, что средний чек равен нулю, хотя продаж просто не было.

Условный пример из теории: зелёный столбец — правильный AOV 9000/3. Оранжевые сверху вниз: включение cancelled в числитель, исключение возвращённого заказа из знаменателя, пропуск вычета возврата. Остальные компоненты в каждом сравнении фиксированы.

Реализация расчёта в SQL и pandas: предагрегация возвратов и итоговые метрики

Главная техническая сложность при соединении orders с refunds — избежать дублирования строк. Если у одного order_id несколько записей в refunds и сделать прямой LEFT JOIN без предагрегации, строка заказа размножится. COUNT(DISTINCT order_id) устоит против этого, но SUM(gross_revenue) посчитает одну и ту же выручку несколько раз.

Решение: сначала свернуть refunds до одной строки на order_id, потом присоединять.

SQL — базовый случай (без возвратов):

SELECT
  COUNT(DISTINCT o.order_id)                                      AS paid_orders,
  SUM(o.gross_revenue)                                            AS gross_rev,
  ROUND(
    SUM(o.gross_revenue) * 1.0
    / NULLIF(COUNT(DISTINCT o.order_id), 0),
  2)                                                              AS aov
FROM orders o
WHERE o.status = 'paid'
  AND strftime('%Y-%m', o.order_date) = '2024-01';

Для января 2024 результат: paid_orders = 82, gross_rev = 933 000, aov ≈ 11 378 руб. (до вычета возвратов).

SQL — с учётом возвратов:

SELECT
  COUNT(DISTINCT o.order_id)                                      AS paid_orders,
  SUM(o.gross_revenue) - COALESCE(SUM(r.refund_amount), 0)        AS net_revenue,
  ROUND(
    (SUM(o.gross_revenue) - COALESCE(SUM(r.refund_amount), 0))
    * 1.0 / NULLIF(COUNT(DISTINCT o.order_id), 0),
  2)                                                              AS aov
FROM orders o
LEFT JOIN (
  SELECT order_id, SUM(refund_amount) AS refund_amount
  FROM refunds
  WHERE refund_date <= '2025-08-31'
  GROUP BY order_id
) r ON o.order_id = r.order_id
WHERE o.status = 'paid'
  AND strftime('%Y-%m', o.order_date) = '2024-01';

Подзапрос агрегирует refunds по order_id до JOIN. COALESCE(SUM(r.refund_amount), 0) обрабатывает заказы, у которых нет строк в refunds: для них r.refund_amount будет NULL после LEFT JOIN, и COALESCE заменяет его нулём. NULLIF(COUNT(DISTINCT order_id), 0) заставляет деление вернуть NULL, если paid-заказов нет.

Для января 2024 запрос даёт: paid_orders = 82, net_revenue = 911 612, aov ≈ 11 117 руб.

pandas — тот же маршрут:

import pandas as pd

orders  = pd.read_csv('orders.csv',  parse_dates=['order_date'])
refunds = pd.read_csv('refunds.csv', parse_dates=['refund_date'])

# Шаг 1. Фильтр по статусу и месяцу
paid = orders.loc[
    (orders['status'] == 'paid') &
    (orders['order_date'].dt.to_period('M') == '2024-01')
].copy()

# Шаг 2. Базовые числа
paid_orders = paid['order_id'].nunique()      # 82
gross_rev   = paid['gross_revenue'].sum()     # 933 000

# Шаг 3. Предагрегация возвратов по order_id
ref_agg = (
    refunds.loc[refunds['refund_date'] <= pd.Timestamp('2025-08-31')]
    .groupby('order_id', as_index=False)
    .agg(refund_amount=('refund_amount', 'sum'))
)

# Шаг 4. LEFT JOIN: присоединить возвраты к paid-заказам
paid_ref = paid.merge(ref_agg, on='order_id', how='left')
paid_ref['refund_amount'] = paid_ref['refund_amount'].fillna(0)

# Шаг 5. Итоговые метрики
net_revenue = paid_ref['gross_revenue'].sum() - paid_ref['refund_amount'].sum()
aov_net     = net_revenue / paid_orders if paid_orders > 0 else float('nan')

print(f'paid_orders:   {paid_orders}')
print(f'gross_revenue: {gross_rev:.2f}')
print(f'net_revenue:   {net_revenue:.2f}')
print(f'AOV (net):     {aov_net:.2f}')

Вывод для января 2024:

paid_orders:   82
gross_revenue: 933000.00
net_revenue:   911612.00
AOV (net):     11117.22

Логика шагов та же, что в SQL: предагрегация возвратов делается до merge, fillna(0) выполняет роль COALESCE. Метод nunique() на поле order_id эквивалентен COUNT(DISTINCT order_id) — он считает уникальные значения, не строки. Защита от деления на ноль — явная проверка if paid_orders > 0 перед делением.