Если вы работали с 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
1SQL-запрос, представленный выше, вычисляет только стоимость продавца, экваринг и логистику. Полная раскладка всех начисления по SKU несколько сложнее =)
Для сравнения приведу таблицы рентабельности, в которых применялись вычисления с делением по количеству товаров и по цене:
ЗЫ. пока дописывал статью Ozon объявил, что отключит метод /transaction/list 6 июля 2026 года. Вроде даже дает новый метод с раскидкой по SKU...