Я работаю аналитиком в e‑commerce крупной FMCG‑компании. Несколько юридических лиц на каждой площадке, большие объёмы поставок, сложная логистика — в маркетплейсах это иногда ощущается как слон в посудной лавке.

Отдельной болью был расчёт заказа на Ozon. Для менеджера это был регулярный трудоёмкий процесс: выгрузить несколько Excel‑файлов из личного кабинета, сопоставить с файлом по товарам в пути, протянуть формулы. При этом данные нужно было смотреть не только на уровне SKU, но и в разрезе кластеров — регионов, через которые Ozon управляет хранением и доставкой. Out‑of‑stock на маркетплейсе слишком дорогая ошибка, чтобы допускать её из‑за ручной работы.

Дополнительно нужно было учитывать локализацию — насколько эффективно остатки распределены по кластерам: совпадает ли место отгрузки с регионом, где товар покупают. В личном кабинете это было неудобно: продажи по кластерам смотрелись только точечно. А в API‑методе по остаткам было поле cluster_to — оно позволяло считать локализацию сразу по всему ассортименту. Для FBO‑схемы это напрямую влияло на экономику: в FMCG даже небольшая доплата на единицу товара становится заметной на больших объёмах.

При выборе инструмента нужно было учитывать не только техническую сторону, но и реальность бизнес‑процесса. Менеджеру был нужен не статичный отчёт, а рабочая среда с возможностью внесения ручных корректировок. Самая удобная для этого — Excel. Корпоративная интеграция в DWH была в планах, но бизнесу нужен был инструмент уже сейчас.

Так я пришла к API‑запросам в Power Query. Вместо ритуального скачивания файлов менеджеру оставалось нажать «Обновить» — под капотом Excel сам забирал данные по API и пересчитывал заказ.

В этой статье не будет много кода, который при желании можно сгенерить с помощью ИИ. Я хочу показать не синтаксис, а подход: как подружить API маркетплейса, Power Query и Excel, чтобы убрать хотя бы часть ручной работы. Речь пойдёт о FBO‑схеме Ozon, но принцип применим и к другим задачам e‑commerce аналитики.

Общая схема автоматизации процесса:

Первым шагом нужно было понять, какие данные участвуют в расчёте заказа. В документации Ozon я нашла API‑методы, которые заменяли часть ручных выгрузок из личного кабинета: остатки и заказы. Справочник номенклатуры и товары в пути остались на стороне менеджера — так их было проще контролировать. В итоге получалась смешанная схема источников, а Power Query подтягивал всё это в единый набор данных для расчёта.

Подготовительные таблицы в Excel

Перед тем как писать API‑загрузчики в Power Query, нужно было подготовить несколько умных таблиц на листах Excel — как точку управления для менеджера: менять параметры можно прямо на листе, без правки кода.

Первая — credentials: Client‑Id — номер личного кабинета и Api‑Key, также известный как токен, которые передаются в Headers каждого запроса.

Вторая — таблица дат для Body запроса. Обычно это последние 28–40 дней с верхней датой «вчера» — считается формулами Excel. Если нужно поменять период вручную перед промо или в сезон — просто правишь даты на листе.

Третья — справочник кластеров clusters: количество дней запаса и логистическое плечо для каждого кластера Ozon. В Power Pivot эти значения суммируются и участвуют в расчёте заказа.

Как устроены API‑загрузчики в Power Query

Прежде чем идти в Power Query, я тестирую каждый метод в Postman: прописываю Headers, собираю Body и добиваюсь статуса 200. Postman сразу показывает ошибки и их описание — разбираться в них там значительно проще, чем потом внутри Power Query.

Оба загрузчика используют Client‑Id и Api‑Key из таблицы credentials. Дальше логика у методов расходится.

Загрузчик остатков

Для получения остатков я использовала метод v1/analytics/stocks. В Body он требует массив SKU — вручную не соберёшь, особенно если ассортимент большой. Файл номенклатуры от менеджера здесь не подходит: я забираю остатки по всему ассортименту независимо от того, что есть в его рабочей таблице. Поэтому список SKU тоже забирается по API, через комбо v1/report/products/create и v1/report/info: первый создаёт задачу на выгрузку номенклатуры, второй забирает результат. В Power Query это два последовательных запроса, результат первого передаётся во второй.

Метод стоков возвращает срез на момент запроса: остатки по каждому SKU в разрезе кластеров. Ключевые поля для расчёта — sku, cluster_name и stock.

Если нужна только текущая картина — этого достаточно. Для полноценного расчёта нужна не точка, а период: метод историю не хранит, поэтому её нужно копить самостоятельно. Самый простой способ — каждый день сохранять файл через Save As с датой в названии. Следующий шаг — автоматизировать это макросом VBA: он сам обновит подключения и сохранит файл. При желании в тот же макрос можно вынести и другие загрузчики.

На диске накапливается папка с файлами вида stocks_04.05.2026.xlsx. Power Query подключается к ней и фильтрует нужный период по таблице дат:

[Date] >= dates{0}[d1] and [Date] <= dates{0}[d2]

Где Date — это столбец с датами из названия файла, которую можно извлечь в Power Query при чтении папки, d1 и d2 — границы периода. Если они рассчитаны формулами от текущей даты, каждое обновление автоматически захватывает нужный диапазон.

Загрузчик заказов

Для выгрузки заказов я использовала метод v3/posting/fbo/list. Даты для фильтра берутся из той же таблицы дат, дополнительные поля подключаются через параметр with.

Главная особенность этого метода — курсорная пагинация: за один запрос можно получить максимум 100 записей. Если данных больше, в ответе приходят has_next: true и cursor — его передаёшь в следующий запрос, пока has_next не вернёт false.

В Power Query логику одного дня удобно вынести в отдельную функцию GetFBOByDate. Пагинация внутри неё устроена через List.Generate: первый запрос уходит с пустым курсором, каждый следующий берёт курсор из предыдущего ответа. Функция останавливается сама, когда has_next возвращает false — количество страниц заранее знать не нужно. В основном запросе список дат между d1 и d2 разворачивается в последовательность отдельных дней, к каждому применяется функция, результаты объединяются в одну таблицу.

M‑код: функция GetFBOByDate
(date as date) =>
let
    FormatDate = (d as date) =>
    Text.From(Date.Year(d)) & "-" &
    Text.PadStart(Text.From(Date.Month(d)), 2, "0") & "-" &
    Text.PadStart(Text.From(Date.Day(d)), 2, "0"),
    
    GetPage = (cursor as text) =>
        let
            body = Json.FromValue([
                cursor = cursor,
                filter = [
                    since = FormatDate(date) & "T00:00:00.000Z",
                    to = FormatDate(date) & "T23:59:59.999Z"
                ],
                limit = 100,
                sort_dir = "ASC",
                with = [
                    analytics_data = true,
                    financial_data = true
                ]
            ]),
            response = Json.Document(
                Web.Contents(
                    "https://api-seller.ozon.ru/v3/posting/fbo/list",
                    [
                        Headers = [
                            #"Client-Id" = clientId,
                            #"Api-Key" = apiKey,
                            #"Content-Type" = "application/json"
                        ],
                        Content = body
                    ]
                )
            )
        in
            response,

    pages = List.Generate(
        () => [page = GetPage(""), hasNext = true],
        each [hasNext],
        each [
            page = GetPage([page][cursor]),
            hasNext = [page][has_next]
        ],
        each [page][postings]
    ),

    allPostings = List.Combine(pages)
in
    allPostings
M‑код: основной запрос
let
    credentials     = Excel.CurrentWorkbook(){[Name="credentials"]}[Content],
    clientId  = credentials{0}[value],
    apiKey    = credentials{1}[value],

    dates     = Excel.CurrentWorkbook(){[Name="dates"]}[Content],
    d1        = dates{0}[d1],
    d2        = dates{0}[d2],

    dateList  = List.Dates(d1, Duration.Days(d2 - d1) + 1, #duration(1,0,0,0)),
    result    = List.Combine(List.Transform(dateList, each GetFBOByDate(_))),
    table     = Table.FromList(result, Splitter.SplitByNothing()),
    expanded  = Table.ExpandRecordColumn(table, "Column1", {"posting_number", "order_id", "status", "products"})
in
    expanded

Последний шаг в Power Query — формирование ключа SKU‑кластер: остатки, продажи и логистика у одного товара на разных кластерах могут сильно отличаться, поэтому заказ считается отдельно для каждого. Из таблиц остатков и заказов я вытащила все уникальные кластеры и объединила в динамический справочник — если Ozon добавит новый, он появится автоматически. Этот справочник присоединяется к запросу номенклатуры, и каждая строка получает ключ SKU‑кластер.

Power Pivot и расчёт заказа

Все источники собрала в модель данных Power Pivot. Связующая вкладка — wallet, таблица номенклатуры, к которой присоединены остатки, заказы, товары в пути и справочник кластеров. Для расчёта понадобились две меры:

Первая — turnover, оборачиваемость в днях: на сколько дней хватит текущего запаса с учётом товаров в пути. Числитель — остатки плюс товары в пути, знаменатель — среднедневные продажи. Если продаж нет, безопасное деление вернёт 1000 вместо ошибки.

turnover:=DIVIDE(SUM(wallet[stocks])+SUM(wallet[incomes]);AVERAGE(wallet[orders]);1000)

Вторая — to_order, итоговый расчёт заказа:

to_order:=ROUNDUP(DIVIDE(IF((AVERAGE(wallet[days])-[turnover])<=0;BLANK();AVERAGE(wallet[days])-[turnover])*AVERAGE(wallet[orders]);1;BLANK());0)

Логика такая: если текущей оборачиваемости уже хватает на нужный период — days, сумма дней запаса и логистического плеча из справочника кластеров — заказывать ничего не нужно, мера ничего не вернёт. Если нет — считаем дефицит в днях, умножаем на среднедневные продажи и получаем количество к заказу.

Если в ассортименте есть составные товары с разной кратностью упаковки, формулу можно дополнить делением и умножением на НОК кратностей — это позволит сразу округлять заказ до нужного короба.

Лист с расчётом для менеджера

Результат выводится в сводную таблицу Power Pivot: в строках — SKU с атрибутами из номенклатуры, в столбцах — кластеры, в значениях — мера to_order. Рядом выведены остатки и товары в пути — чтобы менеджер понимал, из чего сложился расчёт. При необходимости можно добавить класс опасности и считать заказ для бытовой химии или, например, товаров под давлением отдельно, как того требуют правила площадки.

Сводную нельзя редактировать напрямую, поэтому рядом сделали второй лист — копию на обычных формулах Excel, которая ссылается на значения из сводной. Здесь менеджер вносит корректировки с учётом того, что автоматика не знает: ограничения по поставкам, циклы производства — и формирует итоговый заказ.

Что изменилось

Ограничения и риски

Инструмент рабочий, но живёт не сам по себе — есть несколько вещей, за которыми нужно следить.

Ручные файлы требуют поддержки. Матрица номенклатуры и товары в пути — на стороне менеджера. Если они не обновляются вовремя, расчёт будет неполным или неточным. То же касается справочника кластеров: если Ozon добавит новый кластер, его нужно внести в таблицу clusters на листе Excel вручную, иначе логистическое плечо для него просто не подтянется.

Токены нужно беречь. Api‑Key лежит в файле Excel, и важно следить за тем, чтобы файл не уходил в чужие руки — например, по почте или в общий доступ. Также токены имеют срок жизни: когда он заканчивается, загрузчики перестанут работать, и их нужно обновить в таблице credentials.

API меняется. Пока я писала эту статью, метод заказов успел перейти с v2 на v3 — с другой структурой пагинации и изменёнными параметрами. Пришлось переписывать запрос. Это нормальная часть работы с API, но за документацией нужно периодически поглядывать — особенно если Ozon присылает уведомления об устаревших методах.

Всё иногда ломается. API возвращает ошибки, файлы на диске могут съехать, просто что‑то может пойти не так. Инструмент требует присутствия человека на поддержке, который понимает, как он устроен, и может быстро разобраться, что произошло.

Этот инструмент появился из конкретной бизнес‑потребности — убрать рутину и дать менеджеру возможность сосредоточиться на том, что действительно требует человеческого решения.

Я убеждена, что проблема ручных выгрузок в слишком длинной череде механических действий, которая рано или поздно приводит к ошибкам — это просто свойство такого процесса. Автоматизация убирает эту последовательность и высвобождает время менеджера для того, что машина сделать не может: анализа текущей ситуации на рынке и принятия стратегических решений.

P. S. При написании статьи я прибегала к помощи ИИ: ChatGPT и Claude помогали с редактурой текста и генерацией M‑кода и картинки.