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

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

• «выкуп WB»;
• «выкуп %»;
• «заказы и продажи по размерам»;
• «привяжи продажи/возвраты к ID»;
• «сколько выкупили по SKU»;
• «разложи выкуп по размерам».

Основной сценарий: посчитать выкуп по конкретным заказным ID (srid), чтобы возвраты привязывались к тому же заказу, а не смешивались агрегатами по датам.

Магазины

Если магазин не указан, брать из контекста. Если контекста нет — уточнить.

Источники

Главный ключ связки: shop_id + srid.

sales обязательно матчить к orders по srid; не считать продажи и возвраты отдельно по датам, иначе свежие периоды и возвраты будут искажать выкуп.

Что считать основным выкупом

Для анализа SKU/размеров основной показатель:

Выкуп net = заказной ID, у которого есть продажа и нет возврата по тому же srid

Статусы ID:

Формулы

Заказы ID = COUNT(orders.srid)
Отмены = SUM(is_cancel)
Продажи ID = COUNT(DISTINCT srid с продажей)
Возвраты ID = COUNT(DISTINCT srid с возвратом)
Выкуп net = COUNT(DISTINCT srid с продажей и без возврата)
В пути = COUNT(заказов без отмены, продажи и возврата)

Два процента выкупа:

Выкуп от всех = Выкуп net / Заказы ID
Выкуп по закрытым = Выкуп net / (Выкуп net + Отмены + Возвраты)

Для свежих периодов показывать оба, но вывод делать по Выкуп по закрытым: он не штрафует свежие заказы, которые еще едут и не могли стать продажами.

Выкуп от всех полезен как консервативный нижний ориентир.

Периоды

Если пользователь говорит «с мая», брать:

orders.date >= 'YYYY-05-01'

Но в ответе обязательно указать фактический старт данных по SKU:

SELECT MIN(date), MAX(date)
FROM orders
WHERE shop_id = :shop_id
  AND sku = :sku;

Для свежих недель не делать вывод только по sales/orders: продажи имеют лаг доставки, а возвраты приходят позже продажи.

SQL: раскладка по размерам через srid

WITH sales_by AS (
  SELECT
    shop_id,
    srid,
    MAX(CASE WHEN lower(coalesce(sale_id, '')) NOT LIKE 'r%' THEN 1 ELSE 0 END) AS has_sale,
    MAX(CASE WHEN lower(coalesce(sale_id, '')) LIKE 'r%' THEN 1 ELSE 0 END) AS has_return,
    SUM(CASE WHEN lower(coalesce(sale_id, '')) NOT LIKE 'r%' THEN 1 ELSE 0 END) AS sale_rows,
    SUM(CASE WHEN lower(coalesce(sale_id, '')) LIKE 'r%' THEN 1 ELSE 0 END) AS return_rows,
    MIN(CASE WHEN lower(coalesce(sale_id, '')) NOT LIKE 'r%' THEN date END) AS first_sale_date,
    MIN(CASE WHEN lower(coalesce(sale_id, '')) LIKE 'r%' THEN date END) AS first_return_date
  FROM sales
  WHERE shop_id = :shop_id
    AND sku = :sku
  GROUP BY shop_id, srid
),
ids AS (
  SELECT
    o.srid,
    o.tech_size,
    o.date AS order_date,
    o.is_cancel,
    COALESCE(s.has_sale, 0) AS has_sale,
    COALESCE(s.has_return, 0) AS has_return,
    COALESCE(s.sale_rows, 0) AS sale_rows,
    COALESCE(s.return_rows, 0) AS return_rows,
    s.first_sale_date,
    s.first_return_date,
    CASE
      WHEN COALESCE(s.has_sale, 0) = 1
       AND COALESCE(s.has_return, 0) = 0
      THEN 1 ELSE 0
    END AS buyout_net,
    CASE
      WHEN o.is_cancel = 0
       AND COALESCE(s.has_sale, 0) = 0
       AND COALESCE(s.has_return, 0) = 0
      THEN 1 ELSE 0
    END AS unresolved
  FROM orders o
  LEFT JOIN sales_by s
    ON s.shop_id = o.shop_id
   AND s.srid = o.srid
  WHERE o.shop_id = :shop_id
    AND o.sku = :sku
    AND o.date >= :since
    AND o.date < :to
)
SELECT
  tech_size,
  COUNT(*) AS orders_ids,
  SUM(is_cancel) AS cancels,
  SUM(has_sale) AS sold_ids,
  SUM(has_return) AS returned_ids,
  SUM(buyout_net) AS buyout_net_ids,
  SUM(unresolved) AS unresolved_ids,
  ROUND(100.0 * SUM(buyout_net) / NULLIF(COUNT(*), 0), 1) AS buyout_all_pct,
  ROUND(
    100.0 * SUM(buyout_net)
    / NULLIF(SUM(buyout_net) + SUM(is_cancel) + SUM(has_return), 0),
    1
  ) AS buyout_closed_pct,
  ROUND(100.0 * SUM(has_return) / NULLIF(SUM(has_sale), 0), 1) AS return_of_sold_pct
FROM ids
GROUP BY tech_size
ORDER BY tech_size;

Для итога убрать GROUP BY tech_size и вернуть 'ИТОГО' AS tech_size.

SQL: детали возвратов по ID

WITH sales_by AS (
  SELECT
    shop_id,
    srid,
    GROUP_CONCAT(CASE WHEN lower(coalesce(sale_id, '')) NOT LIKE 'r%' THEN sale_id END) AS sale_ids,
    GROUP_CONCAT(CASE WHEN lower(coalesce(sale_id, '')) LIKE 'r%' THEN sale_id END) AS return_ids,
    MIN(CASE WHEN lower(coalesce(sale_id, '')) NOT LIKE 'r%' THEN date END) AS sale_date,
    MIN(CASE WHEN lower(coalesce(sale_id, '')) LIKE 'r%' THEN date END) AS return_date
  FROM sales
  WHERE shop_id = :shop_id
    AND sku = :sku
  GROUP BY shop_id, srid
)
SELECT
  o.tech_size,
  o.date AS order_date,
  o.srid,
  s.sale_ids,
  s.sale_date,
  s.return_ids,
  s.return_date
FROM orders o
JOIN sales_by s
  ON s.shop_id = o.shop_id
 AND s.srid = o.srid
WHERE o.shop_id = :shop_id
  AND o.sku = :sku
  AND o.date >= :since
  AND o.date < :to
  AND s.return_ids IS NOT NULL
ORDER BY o.tech_size, o.date;

Использовать, когда нужно объяснить, какие именно ID вернулись.

Кабинетный buyout_percent

funnel_period.buyout_percent можно показывать рядом как справку из кабинета WB.

Важно:

• это агрегат WB за окно периода;
• он может не равняться buyouts_count / orders_count;
• он не заменяет ID-связку, если пользователь просит понять возвраты и размеры;
• для маркетингового отчета по заказам брать funnel_period, для операционного выкупа по ID — связку orders + sales.

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

Период: 2026-05-01 … 2026-07-08
Фактические данные по SKU: с 2026-06-03

Размер | Заказы ID | Отмены | Продажи ID | Возвраты ID | Выкуп net | В пути | Выкуп от всех | Выкуп по закрытым
L      |        52 |     19 |         14 |           3 |        11 |     19 |        21.2% |            33.3%
M      |        84 |     36 |         17 |           1 |        16 |     31 |        19.0% |            30.2%
S      |        57 |     23 |         13 |           0 |        13 |     21 |        22.8% |            36.1%
ИТОГО  |       193 |     78 |         44 |           4 |        40 |     71 |        20.7% |            32.8%

После таблицы дать 1-3 вывода:

• какой размер лучший по Выкуп по закрытым;
• где высокая возвратность Возвраты ID / Продажи ID;
• сколько заказов еще не закрылось и почему свежий период нельзя считать финальным.

Чек-лист

1. sales сматчены к orders по shop_id + srid.
2. Размер взят из заказа (orders.tech_size), не из агрегата.
3. Возвраты считаются по тому же srid, что и продажа.
4. Свежие незакрытые заказы вынесены в В пути.
5. В ответе есть абсолютный период и фактический старт данных по SKU.
6. Если показан buyout_percent из кабинета WB, он явно отделен от ID-выкупа.