Если вы работали с 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...
