Плейбук: Ozon «Заказы 7 дней / 1 день» с рекламой и CPO

Срабатывает, когда пользователь просит:

• «заказы за 7 дней»;
• «рекламный отчёт за 7 дней»;
• «заказы за день»;
• «за вчера заказы и CPO»;
• «Артикул / Заказы с отм. / Сумма / Реклама / CPO».

Базовый формат:

Артикул | Заказы с отм. | Сумма | Реклама | CPO

Отчёт специально считает заказы с отменами, потому что в интерфейсе Ozon рекламные/товарные отчёты часто показывают заказы с отменами. Так меньше расхождений при сверке с кабинетом.

Магазин

Если магазин не указан — брать из текущего контекста. Для текущего сценария:

Проверка:

SELECT id, name, marketplace FROM shops ORDER BY id;

Периоды

Заказы за 7 дней

По умолчанию брать последние 7 закрытых календарных дней.

Если сегодня 2026-06-29, то:

SINCE = 2026-06-22
TO    = 2026-06-28

Заказы за 1 день / вчера

Если сегодня 2026-06-29, то «за вчера»:

SINCE = 2026-06-28
TO    = 2026-06-28

В ответе всегда писать абсолютную дату.

Гранулярность

Размеры объединяются в один артикул:

ELS04-black-S / ELS04-black-M / ELS04-black-L -> ELS04-black
skirt-milk-s / skirt-milk-m / skirt-milk-l -> skirt-milk

Алгоритм:

1. Взять offer_id.
2. Отрезать размерный суффикс:

-(XXXL|XXL|XL|L|M|S|XS|XXS|\d{2,3})$

3. Применить aliases:

{
    "els04-black": "els004-black",
    "els04-brown": "els004-brown",
    "els04-grafit": "els004-graphit",
    "els04-graphite": "els004-graphit",
}

4. Для вывода вернуть человекочитаемый вид:

els004-black -> ELS04-black
els004-brown -> ELS04-brown
els004-graphit -> ELS04-grafit

Источники

Период заказов — по ozon_postings.created_at.

Период рекламы — по ozon_ad_metrics_daily.date.

Формулы

Заказы с отм. = SUM(ozon_posting_items.quantity)
Сумма = SUM(ozon_posting_items.price × quantity)
Реклама = SUM(ozon_ad_metrics_daily.money)
CPO = Реклама / Заказы с отм.

Отмены не исключать.

Если нужны обе версии для контроля, можно дополнительно посчитать:

Заказы без отмен = все заказы - cancelled/canceled
CPO без отмен = Реклама / Заказы без отмен

Но в базовом отчёте показывать только Заказы с отм..

Прогноз товаров в пути с учётом выкупа

Срабатывает на запросы «что сейчас едет к клиентам», «сколько денег принесут товары в пути», «учти средний выкуп», «добавь юбки».

Сначала обновить постинги

Статусы быстро меняются, поэтому перед расчётом обязательно получить свежий срез Seller API:

.venv/bin/python scripts/ingest_ozon.py --shop-id 4 postings --days 60

В ответе указывать абсолютную дату среза и проверять MAX(fetched_at).

Какие статусы считать

Основной результат показывать для delivering, затем отдельной строкой — весь активный хвост. Не называть awaiting_packaging товаром в дороге.

Зрелый выкуп

Последние заказы ещё не успели доехать, поэтому их нельзя использовать в знаменателе. Для оперативного прогноза брать 30-дневную когорту, закончившуюся за 15 дней до даты среза:

mature_to    = as_of - 15 дней
mature_since = mature_to - 29 дней
выкуп SKU    = delivered / (delivered + cancelled)

Незавершённые статусы из mature-когорты не включать ни в числитель, ни в знаменатель, но показать их количество при проверке качества когорты.

Считать выкуп по каждому размеру offer_id, а объединять в цветовой артикул только после прогноза. Это существенно точнее для ELS04 и skirt-milk:

ожидаемые выкупы = Σ(active_qty_sku × mature_buyout_sku)
ожидаемая выручка = Σ(active_order_value_sku × mature_buyout_sku)
active_order_value_sku = SUM(posting_items.price × quantity)

Поддерживаемые группировки текущего магазина:

ELS04-black-S/M/L  -> ELS04-black
ELS04-brown-S/M/L  -> ELS04-brown
ELS04-grafit-S/M/L -> ELS04-grafit
skirt-milk-S/M/L/XL -> skirt-milk

Что означает «сколько денег принесут»

Не смешивать три уровня денег:

1. Сумма заказов — полная текущая стоимость товаров в постингах.
2. Ожидаемая выручка — сумма заказов после поправки на зрелый выкуп; это основной ответ.
3. Ожидаемо к перечислению — опциональная оценка через исторический payout на net-продажу из ozon_finance_operations. После вычета costs получается вклад до рекламы, налогов и общих расходов, а не чистая прибыль.

Если среди delivering есть заказы старше 15 дней, показать их отдельно: общий средний выкуп может завысить прогноз по зависшему хвосту.

Проверочный срез: Siluetta Ozon, 2026-07-16

После свежего ingest: delivering = 285 шт. ELS04 + skirt-milk на 1 541 924 ₽; размерно-взвешенный прогноз = 93.7 выкупа и 509 323 ₽ выручки. Весь активный хвост = 313 шт. на 1 693 034 ₽; прогноз = 103.7 выкупа и 563 475 ₽ выручки.

SQL-заготовка

SELECT
  i.offer_id,
  SUM(i.quantity) AS qty_with_cancel,
  SUM(COALESCE(i.price, 0) * i.quantity) AS amount_with_cancel
FROM ozon_postings p
JOIN ozon_posting_items i
  ON i.shop_id = p.shop_id
 AND i.posting_number = p.posting_number
WHERE p.shop_id = :shop_id
  AND p.created_at BETWEEN :since AND :to_ts
GROUP BY i.offer_id;

Реклама:

SELECT
  offer_id,
  SUM(money) AS ad_spend
FROM ozon_ad_metrics_daily
WHERE shop_id = :shop_id
  AND date BETWEEN :since AND :to
GROUP BY offer_id;

После SQL объединить размеры в base-артикул и сложить строки.

Формат ответа

Период: YYYY-MM-DD…YYYY-MM-DD
Магазин: Siluetta Ozon
Размеры объединены в артикул. Заказы считаются с отменами.

| Артикул | Заказы с отм. | Сумма | Реклама | CPO |
|---|---:|---:|---:|---:|
...
| ИТОГО | ... |

Сортировка: по рекламе убыванию, затем по заказам убыванию.

Чек-лист

1. Период указан абсолютными датами.
2. Заказы считаются по created_at.
3. Отмены не исключены.
4. Размеры объединены до артикула.
5. Реклама сходится с SUM(ozon_ad_metrics_daily.money).
6. CPO = Реклама / Заказы с отм..
7. Есть строка ИТОГО.
8. Для прогноза товаров в пути постинги обновлены непосредственно перед расчётом.
9. Выкуп рассчитан по зрелой когорте и отдельно по размерам.
10. delivering не смешан с ожиданием передачи/упаковки.