AI Asist
v2
Д
Д

Плейбук: выкуп Ozon по ID и размерам

← Заметки  ·  Раздел: Ozon  ·  Файл: data/notes/ozon/buyout-playbook.md

Плейбук: выкуп Ozon по ID и размерам

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

  • «процент выкупа Ozon»;
  • «выкуп по размерам»;
  • «проверь предыдущие два месяца по ID»;
  • «сколько из едущего выкупят / вернётся»;
  • «учти невыкуп при расчёте поставки».

Цель: по каждому размерному offer_id связать товар с конкретным заказом, дождаться зрелой когорты и отдельно показать выкуп, невыкуп и незакрытый хвост. Для планирования запаса считать штуки, а order_id и posting_number использовать как контроль идентичности и дублей.

Магазины и источники

Магазин shop_id
Мой Ozon / Shakti 2
Siluetta Ozon 4

Основные таблицы:

Данные Таблица / поле
Заказ ozon_postings.order_id, posting_number, created_at, status
Товар и размер ozon_posting_items.offer_id, quantity, price
Связка shop_id + posting_number; товар внутри отправления — offer_id
Свежесть ozon_postings.fetched_at

order_id может объединять несколько отправлений, а в одном posting_number теоретически может быть несколько товаров или quantity > 1. Поэтому:

идентичность строки = shop_id + posting_number + offer_id
операционный процент запаса = по SUM(quantity)
контроль заказов = COUNT(DISTINCT order_id) и COUNT(DISTINCT posting_number)

Нельзя дедуплицировать только по order_id: так можно потерять часть физического товара.

Шаг 1. Обновить постинги

Статусы меняются после доставки и отказа, поэтому перед расчётом повторно получить всё окно от начала когорты до текущей даты. Запускать из корня проекта:

.venv\Scripts\python.exe scripts\ingest_ozon.py --shop-id 4 postings --from YYYY-MM-DD --to YYYY-MM-DD

На Linux/Hetzner использовать .venv/bin/python. После ингеста проверить:

SELECT MAX(fetched_at), MIN(created_at), MAX(created_at)
FROM ozon_postings
WHERE shop_id = :shop_id;

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

Шаг 2. Выбрать зрелую когорту

Последние заказы не успели закрыться, поэтому по умолчанию используем 60 зрелых дней, заканчивающихся за 15 дней до среза:

mature_to_inclusive = as_of_date − 15 дней
mature_since        = mature_to_inclusive − 59 дней
SQL upper bound     = mature_to_inclusive + 1 день (exclusive)

Пример при срезе 20.08.2026:

зрелая когорта = 07.06.2026 … 05.08.2026
SQL            = created_at >= '2026-06-07'
                 AND created_at < '2026-08-06'

Если пользователь просит «два предыдущих календарных месяца», посчитать их отдельным контрольным срезом, но основной прогноз делать по зрелой 60-дневной когорте. Если два окна расходятся более чем на 5 п.п., показать оба и объяснить изменение спроса/выкупа.

Шаг 3. Классифицировать каждый ID

Класс Условие
Выкуп status = 'delivered'
Невыкуп / отмена status IN ('cancelled', 'canceled')
Незакрыто любой другой статус внутри зрелой когорты

Незакрытые строки не включать ни в числитель, ни в знаменатель выкупа, но обязательно показывать как контроль качества когорты.

cancelled — операционный невыкуп/отмена FBO-постинга, а не обещание, что единица уже снова доступна к продаже. Физическое возвращение проходит отдельный путь и проверяется по return_from_customer_stock_count / /v1/returns/list. Не прибавлять невыкуп к FBO до фактического возврата.

Если нужен net-выкуп после возврата уже вручённого товара, дополнительно сопоставить /v1/returns/list с исходным posting_number + offer_id. Возврат, чей исходный posting уже cancelled, второй раз из выкупа не вычитать.

Шаг 4. Считать строго по размеру

Основная гранулярность — полный offer_id:

ELS04-black-S
ELS04-black-M
ELS04-black-L

Сначала рассчитать каждый размер, затем при необходимости объединить прогноз в цветовой артикул. Нельзя сначала объединить размеры: у одежды разница выкупа между S/M/L может быть десятки процентных пунктов.

Формулы:

closed_units       = delivered_units + cancelled_units
buyout_rate        = delivered_units / closed_units
non_buyout_rate    = cancelled_units / closed_units
unresolved_share   = unresolved_units / all_units

expected_buyouts   = delivering_units × buyout_rate размера
expected_returns   = delivering_units × non_buyout_rate размера

Итог по артикулу считать взвешенно:

ожидаемые выкупы = Σ(delivering_qty_size × buyout_rate_size)
ожидаемые невыкупы = Σ(delivering_qty_size × non_buyout_rate_size)

Среднее арифметическое процентов размеров использовать нельзя.

SQL: выкуп по ID и размерам

WITH item_ids AS (
  SELECT
    p.shop_id,
    p.order_id,
    p.posting_number,
    i.offer_id,
    p.status,
    SUM(i.quantity) AS qty
  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 >= :mature_since
    AND p.created_at < :mature_to_exclusive
    AND i.offer_id IN (...)
  GROUP BY
    p.shop_id,
    p.order_id,
    p.posting_number,
    i.offer_id,
    p.status
)
SELECT
  offer_id,
  COUNT(*) AS posting_sku_ids,
  COUNT(DISTINCT order_id) AS order_ids,
  COUNT(DISTINCT posting_number) AS postings,
  SUM(qty) AS all_units,
  SUM(CASE WHEN status = 'delivered' THEN qty ELSE 0 END) AS delivered_units,
  SUM(CASE WHEN status IN ('cancelled','canceled') THEN qty ELSE 0 END) AS cancelled_units,
  SUM(CASE
        WHEN status NOT IN ('delivered','cancelled','canceled') THEN qty
        ELSE 0
      END) AS unresolved_units,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'delivered' THEN qty ELSE 0 END)
    / NULLIF(SUM(CASE
        WHEN status IN ('delivered','cancelled','canceled') THEN qty
        ELSE 0
      END), 0),
    1
  ) AS buyout_closed_pct
FROM item_ids
GROUP BY offer_id
ORDER BY offer_id;

Для расследования конкретных ID вывести order_id, posting_number, offer_id, qty, status из item_ids, не публикуя персональные данные покупателя.

Текущий клиентский хвост

После расчёта процента получить свежие активные количества:

SELECT
  i.offer_id,
  p.status,
  SUM(i.quantity) AS units,
  COUNT(DISTINCT p.posting_number) AS postings,
  COUNT(DISTINCT p.order_id) AS order_ids
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.status IN ('delivering','awaiting_deliver','awaiting_packaging')
  AND i.offer_id IN (...)
GROUP BY i.offer_id, p.status;

Статусы показывать раздельно:

Срез Статусы
Уже едут клиенту delivering
Ждут передачи awaiting_deliver
Упаковка awaiting_packaging

awaiting_packaging не называть товаром в дороге. В live FBO-режиме он соответствует reserved внутри present, поэтому второй раз в имущественный остаток не прибавляется.

Как учитывать выкуп в поставке

Базовый дефицит FBO считать по data/notes/ozon/supply-playbook.md без изменения:

base_deficit = max(
  0,
  target − available − valid − confirmed_inbound
)

Прогноз невыкупа — не текущий FBO, поэтому базовый дефицит им молча не уменьшать. Если пользователь просит учесть возвратность, рядом построить отдельный сценарий:

expected_non_buyout = Σ(delivering_qty_size × non_buyout_rate_size)

return_adjusted_future_pool =
  available
  + valid
  + confirmed_inbound
  + active_return_flow × sellable_recovery_factor
  + expected_non_buyout

return_adjusted_deficit = max(0, target − return_adjusted_future_pool)

sellable_recovery_factor фиксировать явно. 1.0 допустим только когда пользователь подтвердил, что возвраты этой категории обычно возвращаются в продаваемый FBO; иначе дать диапазон или использовать консервативный коэффициент.

Критические правила:

  • ожидаемый невыкуп влияет на будущую доступность, но не увеличивает полный имущественный остаток: эти единицы уже находятся в корзине «к клиенту»;
  • активный return_from_customer_stock_count и детализацию /v1/returns/list не складывать;
  • delivering и уже активный возвратный поток — разные физические стадии, но одна единица не должна одновременно находиться в обеих после сопоставления ID;
  • при высокой возвратности поставку можно делить на волны, но после первой волны делать новый live-срез; старую кластерную матрицу для другого объёма не переносить.

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

Магазин: Siluetta Ozon (shop 4)
Срез постингов: DD.MM.YYYY HH:MM МСК
Зрелая когорта: DD.MM.YYYY … DD.MM.YYYY

Размер | ID закрыто | Выкуп | Невыкуп | Не закрылось | Выкуп % | Невыкуп %
S      | ...        | ...   | ...     | ...          | ...     | ...
M      | ...        | ...   | ...     | ...          | ...     | ...
L      | ...        | ...   | ...     | ...          | ...     | ...

Сейчас delivering: N шт
Ожидаемо выкупят: X шт
Ожидаемо не выкупят: Y шт

Для решения по поставке после таблицы показать два результата:

  1. базовый дефицит по supply-плейбуку;
  2. сценарий с ожидаемыми возвратами и явно указанным sellable_recovery_factor.

Чек-лист

  1. Проверен правильный shop_id и свежий MAX(fetched_at)?
  2. Период абсолютный, 60 дней закончились за 15 дней до среза?
  3. Связка построена по shop_id + posting_number + offer_id, а не только по order_id?
  4. Штуки (SUM(quantity)) не перепутаны с количеством ID?
  5. Выкуп рассчитан отдельно по каждому размеру offer_id?
  6. Незакрытые ID исключены из процента и показаны отдельно?
  7. delivering, awaiting_deliver, awaiting_packaging не смешаны?
  8. Проценты размеров объединены взвешенно, не средним арифметическим?
  9. Невыкуп не прибавлен к FBO до физического возврата?
  10. Analytics returns и /v1/returns/list не задвоены?
  11. Для supply показаны базовый и return-adjusted сценарии?
  12. Если объём поставки изменён, кластерная матрица пересчитана заново?
Фильтры