Если вы работали с API Ozon, то наверняка испытывали смешанные чувства, поняв, что отчет по транзакциям формируется по отправлениям. а не по товарам, как в WB. И проблема в том, что в одной строке ответа API метода v3/finance/transaction/list мы имеем данные по отправлению, в котором может быть несколько товаров. При этом указанная сумма покупки (accruals_for_sale), комиссии, логистики и других начислений в отчете указана одна, то есть на все отправление в целом. А значит встает задача, посчитать сколько из общей суммы приходится на единицу товара.

В этом статья поделюсь, как я решил эту проблему в своей системе аналитики продаж через Wildberries и Ozon. Напомню, небольшой обзор своей системы WBOZYA‑dash я делал в первой статье. А как получать данные из API маркетплейсов без ошибок 429 и 50x во второй статье.

Выход вообщем‑то очевиден: как‑то разделить общую сумму стоимости отправления по товарам.

Самый простой способ — разделить поровну по всем товарам в отправлении. Однако у такого подхода есть большой недостаток — могут быть погрешности, особенно если цены товаров существенно различаются. Например, если в одном отправлении есть товар за 1000 рублей и за 200 рублей, то после такого деления accruals_for_sale «поровну» у каждого будет стоимость по 600 рублей. Несмотря на этот недостаток, такой подход все равно имеет право на существования — все лучше чем, ничего или просто отбрасывать отправления с несколькими товарами.

Второй способ — разделить пропорционально стоимости товаров, буквально вычислять пропорцию стоимости одного товара к общей сумме. Очевидно, для этого потребуется откуда то брать стоимость товаров. С учетом того, что цены постоянно меняются, надо брать цену именно из тех отправлений, для которых мы делим раскладку транзакций. А значит должна быть еще и база данных, где будут храниться старые отправления. Вообщем второй способ технически выглядит более сложным, чем первый, но не тройной интеграл =)

Делим поровну

Рассмотрим реализацию такого первого подхода при ETL обработке данных из API. На Python код получения транзакций через API OZ выглядит примерно так:

url = “https://api-seller.ozon.ru/v3/finance/transaction/list”
ipage = 1
tract = pd.DataFrame([])
while True:
  data = { 
  "filter": {"date": { "from": fromdate, "to": todate }}, 
  "page": ipage, 
  "page_size": 1000 }

  dd = pd.DataFrame([])
  apioz_response = get_oz(url, headers, data)
  if apioz_response:
  	dd = pd.DataFrame(apioz_response['result']['operations'])
  	tract = pd.concat([tract, dd], ignore_index=True)
    
  if len(dd) < 1000:  break
  ipage +=1

Проблемные строки с несколькими товарами в одном отправлении выглядят так:

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

tract['sku'] = tract['items'].apply(lambda x: list(re.findall(r"'sku': (\d+)", str(x))))
tract['quanti'] = tract['items'].apply(lambda x: len(re.findall(r"'sku': (\d+)", str(x))))

Получим две новые колонки: колонку, в которой сидит массив SKU, которые есть в отправлении, и колонку с общим количеством товаров в отправлении.

Полученный массив можно легко «раскрыть» командой.explode(). Однако после такого преобразования может получится одна неочевидная загвоздка — дубликаты. Если в одном отправлении несколько одинаковых SKU, то строки с ними после эксплода буду полными дубликатами. Если в логике ETL или при экспорте в БД есть проверка на дубли (а она должна быть), то часть данных потеряется. Чтобы этого избежать надо пронумеровать товары, который были в одной строке транзакций. А чтобы нумерация не зависела от порядка их вывода в поле items, предварительно отсортируем строчки по operation_id и SKU. Сделать это можно вот так:

tr_bysku = tract.explode('sku', ignore_index=True)
tr_bysku = tr_bysku.sort_values(by=['operation_id', 'sku']).reset_index(names='ii')
tr_bysku['add_id'] = tr_bysku.groupby('operation_id')['ii'].rank().astype('int')

В итоге получим новый датасет, в котором в каждой строке один SKU. Однако при этом в поле accruals_for_sale все еще общая сумма для всего отправления. Зато есть поле с количеством товаров в отправлении. То есть для распределения общей стоимости по SKU поровну надо будет просто разделить общую стоимость отправления accruals_for_sale на общее количество товаров quanti.

Можно применить такой же подход к полям sale_commission и amount и даже services, тем самым раскидываем по SKU еще и комиссии, затраты на логистику и прочие начисления Ozon.

Деление пропорционально цене товара

Так как второй способ деления предполагает подтягивание таблицы отправления из базы данных, реализуем его с помощью SQL запроса. Правда, сначала немного доработать код, описанный выше. В частности надо выделать номер отправления, который содержится в json поле posting..

Сделать это также можно регулярным выражением. Прчием можно заменить, что у части транзакций номер отправления имеет вид «dddddddd‑dddd‑ddd», а у других «dddddddd‑dddd» — это номер заказа, а не отправления. Это объясняется, тем что таких транзакции отражено начисление на весь заказ, например, экваринг. Итого предлагаю выделять не только номер отправления, но и номер заказа, причем в разные колонки.

Итого python код будет такой:

url = “https://api-seller.ozon.ru/v3/finance/transaction/list”
ipage = 1
tract = pd.DataFrame([])
while True:
  data = { 
  "filter": {"date": { "from": fromdate, "to": todate }}, 
  "page": ipage, 
  "page_size": 1000 }

  dd = pd.DataFrame([])
  apioz_response = get_oz(url, headers, data)
  if apioz_response:
  	dd = pd.DataFrame(apioz_response['result']['operations'])
  	tract = pd.concat([tract, dd], ignore_index=True)
    
  if len(dd) < 1000:  break
  ipage +=1

# Ведяем sku, posting_id, order_id
tract['sku'] = tract['items'].apply(lambda x: list(re.findall(r"'sku': (\d+)", str(x))))
tract['quanti'] = tract['items'].apply(lambda x: len(re.findall(r"'sku': (\d+)", str(x))))
tract['posting_id'] = tract['posting'].apply(lambda x: ''.join(re.findall(r"'posting_number': '(\d+-\d+-\d+)", str(x))))
tract['order_id'] = tract['posting'].apply(lambda x: ''.join(re.findall(r"'posting_number': '(\d+-\d+)", str(x))))

# Раскрываем массив с товарами 
tr_bysku = tract.explode('sku', ignore_index=True)
tr_bysku = tr_bysku.sort_values(by=['operation_id', 'sku']).reset_index(names='ii')
tr_bysku['add_id'] = tr_bysku.groupby('operation_id')['ii'].rank().astype('int')

# Заполняем пропуски
tr_bysku['accruals_for_sale'] = tr_bysku['accruals_for_sale'].fillna(0)
tr_bysku['sale_commission'] = tr_bysku['sale_commission'].fillna(0)
tr_bysku['amount'] = tr_bysku['amount'].fillna(0)
tr_bysku['service_amount'] = tr_bysku['service_amount'].fillna(0)
tr_bysku['sku'] = tr_bysku['sku'].fillna('0').astype('int64')
            
export_to_sql('oz_transactions_bysku', tr_bysku)

Дальше переходим на SQL. Сначала надо выбрать из таблицы транзакций данные за нужный период. Фильтр sku > 1000 применяется, чтобы выбрать только транзакции в которых указан реальный sku (что делать с транзакциями без sku оставим за скобками)

SELECT
    operation_id 
    , operation_date
    , operation_type_name
    , posting_id
    , order_id
    , sku
    , 1 as ed_count
    , accruals_for_sale
    , sale_commission
    , service_amount
    , amount
FROM    
    oz_transactions_bysku
WHERE
    operation_date::date BETWEEN {{date_from}} AND {{date_to}}
	AND sku > 1000

Затем соединяем полученную таблицу с таблицей отправлений. Соединение проходит по полям posting_id или order_id и sku. В полученной таблице вычисляем пропорцию для цены конкретного товара от суммы цен всех товаров входящих в отправление по данной транзакции. В отправлении товаров может быть больше, но какие из них могут быть возвращены/отменены, поэтому берем только SKU, которые указаны в транзакции.

SELECT
    operation_id
    , operation_date
    , operation_type_name  
    , t1.sku
	, ed_count
    , accruals_for_sale
    , sale_commission
    , service_amount
    , amount
    , COALESCE(po.saller_price / NULLIF(SUM(po.saller_price) 
            OVER (PARTITION BY t1.order_id, t1.posting_id, operation_type_name), 0)::NUMERIC, 1) as sku_koef      
FROM
    t1
LEFT JOIN
    oz_postings as po
    ON
    t1.sku > 1000
    -- если нет posting_id - соединяем по order_id
    AND CASE WHEN t5.posting_id IS NULL THEN t5.order_id = po.order_id
        ELSE t5.posting_id = po.posting_id END
    AND t5.sku = po.sku

И вот осталось только провести группировку по SKU и просуммировать различные статьи транзакций, предварительно применив вычисленные выше коэффициенты для каждого товара. Итоговый код SQL‑запроса будет такой:

WITH
t1 as (
SELECT
    operation_id 
    , operation_date
    , operation_type_name
    , posting_id
    , order_id
    , sku
    , 1 as ed_count
    , accruals_for_sale
    , sale_commission
    , service_amount
    , amount
FROM    
    oz_transactions_bysku
WHERE
    operation_date::date BETWEEN {{date_from}} AND {{date_to}}
	AND sku > 1000 --отсекаем нулевые sku - они используются в другом месте	
),


t2 as (
SELECT
    operation_id
    , operation_date
    , operation_type_name  
    , t1.sku
	, ed_count
    , accruals_for_sale
    , sale_commission
    , service_amount
    , amount
    , COALESCE(po.saller_price / NULLIF(SUM(po.saller_price) 
            OVER (PARTITION BY t1.order_id, t1.posting_id, operation_type_name), 0)::NUMERIC, 1) as sku_koef      
FROM
    t1
LEFT JOIN
    oz_postings as po
    ON
    t1.sku > 1000
    -- если нет posting_id - соединяем по order_id
    AND CASE WHEN t5.posting_id IS NULL THEN t5.order_id = po.order_id
        ELSE t5.posting_id = po.posting_id END
    AND t5.sku = po.sku
)


SELECT
    sku
    , COALESCE(SUM(ed_count) FILTER (WHERE operation_type_name = 'Доставка покупателю'), 0) as ed_count
    , COALESCE(SUM(accruals_for_sale * sku_koef) FILTER (WHERE operation_type_name = 'Доставка покупателю'), 0) as accruals_for_sale     
    , COALESCE(SUM(sale_commission * sku_koef) FILTER (WHERE operation_type_name = 'Доставка покупателю'), 0) as sale_commission  
    , COALESCE(SUM(amount * sku_koef) FILTER (WHERE operation_type_name = 'Оплата эквайринга'), 0) as acquiring
FROM
    t2        
GROUP BY
    1

SQL‑запрос, представленный выше, вычисляет только стоимость продавца, экваринг и логистику. Полная раскладка всех начисления по SKU несколько сложнее =)

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

Деление начислений поровну на количество товаров в одной транзакции

Деление начислений пропорционально ценам товаров в одной транзакции

ЗЫ. пока дописывал статью Ozon объявил, что отключит метод /transaction/list 6 июля 2026 года. Вроде даже дает новый метод с раскидкой по SKU...

Раскидываем транзакции начислений Ozon по SKU