Как выгрузить данные из Ozon в Excel и собрать отчёт по прибыли

Файл с заказами я могу собрать в красивую сводную, но себестоимость закупки от этого в нём не появится. Не появится там и полная картина расходов магазина. В своих магазинах я поэтому разделяю две задачи: посмотреть движение заказов и посчитать заработанные деньги. Ошибка начинается в тот момент, когда таблицу с заказами называют отчётом по прибыли и принимают решения по её последнему столбцу. Выгрузка данных из Озон в Excel помогает получить исходники, но сама по себе не связывает продажи, начисления, возвраты и закупочные расходы.

Мне нужен отчёт, в котором можно пройти от результата магазина до конкретного расхода и понять, откуда он взялся. Для этого недостаточно скачать всё, что доступно в кабинете, и сложить файлы в папку. Нужно заранее решить, за какой период я считаю результат, что считаю продажей и к чему отношу расходы. Иначе таблица будет сходиться только после ручных поправок, смысл которых к следующему обновлению забудется. Ниже я разложу этот процесс на исходные данные, соединение таблиц и расчёт, который можно повторить на своих выгрузках.

Какие выгрузки искать в Ozon Seller

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

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

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

Как выгрузить отчёт Озон в Excel и подготовить файлы

Чтобы выгрузить отчёт Озон в Excel, я открываю нужный раздел кабинета, задаю период и фильтры, затем ищу действие скачивания или формирования отчёта. Для заказов отдельно проверяю выбранную схему работы и статусы, чтобы случайно не получить только часть отправлений. Если отчёт формируется отдельно от экранной таблицы, я проверяю его параметры ещё раз: фильтры на экране не стоит считать гарантией состава файла. После скачивания смотрю первую и последнюю даты, набор статусов и наличие нужных идентификаторов. Если раздел отдаёт CSV, я импортирую его средствами Excel с явным выбором разделителя и типов данных, а не просто открываю двойным щелчком.

Исходные файлы я сохраняю отдельно и не исправляю в них суммы руками. Рядом фиксирую магазин, источник, выбранный период, смысл даты и момент формирования выгрузки. Это позволяет отличить новый файл за старый период от файла за новый период, когда появляются дополнительные операции. В рабочей книге у меня отдельно лежат заказы, реализация, финансовые операции, реклама и справочник себестоимости. Идентификаторы я загружаю как текст, потому что автоматическое преобразование Excel может испортить ключ, даже если внешне ячейка выглядит правдоподобно.

Перед соединением таблиц я отвечаю на простой вопрос: что означает отдельная строка в каждом источнике. Это может быть товар в отправлении, отправление целиком, финансовая операция или итог по периоду. Для связи использую самый подробный общий идентификатор, который действительно есть в обоих файлах; названия товара для этого недостаточно. В общей книге обязательно добавляю признак магазина, чтобы данные разных кабинетов не смешались. Затем проверяю строки без найденной пары и сравниваю суммы до соединения и после него, потому что незаметное размножение строк опаснее явной ошибки формулы.

Как собрать из выгрузок отчёт по прибыли

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

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

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

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

Выплату на расчётный счёт нельзя подставлять вместо выручки в формулу прибыли. Она относится к движению денег и может отличаться от результата продаж из-за сроков расчётов, удержаний и корректировок. Если начать с выплаты и снова вычесть удержанные площадкой расходы, часть затрат попадёт в расчёт повторно. Я всегда подписываю, что находится в исходной сумме: продажи, начисления после удержаний или перечисленные деньги.

Где соединение выгрузок ломает расчёт

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

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

Ручная ежемесячная выгрузка хуже, чем кажется, именно потому, что прошлый период не всегда остаётся неизменным. Новый файл может содержать операции, которых не было при предыдущем скачивании, а изменившееся название столбца способно сломать сохранённое преобразование. Если просто дописывать файлы в общую таблицу, появятся повторы; если бездумно заменять старые данные, можно потерять историю проверки. Я храню сведения об источнике и версии загрузки, проверяю уникальность операций по доступным ключам и отдельно контролирую пересекающиеся периоды. Автоматизация через Power Query убирает часть ручных действий, но не решает за меня, какая запись новая, а какая исправляет старую.

Когда Excel разумен и что я бы сделал завтра

Я оставляю ручной экспорт данных Ozon Seller там, где задача ограничена и результат можно проверить глазами. Например, нужно исследовать конкретное расхождение, собрать пробный управленческий отчёт или проверить новую схему распределения расходов. Excel удобен ещё и тем, что позволяет увидеть промежуточные расчёты без разработки отдельного интерфейса. Но у такой работы должны быть сохранённые исходники, повторяемые преобразования и понятные контрольные суммы. Если вместо этого отчёт держится на памяти о том, какие строки удалить и какую ячейку поправить, проблема уже не в размере файла.

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

SellOps я сделал для своих магазинов, потому что не нашёл готового сервиса, расчётам которого мог бы доверять. Для меня доверие начинается с возможности объяснить число, а не с красивого графика поверх загруженных файлов. Если нужен контекст, как устроен мой сервис, его стоит смотреть именно вместе с этой логикой проверки исходных данных. При этом я не считаю подключение сервиса заменой собственному учёту закупок и внешних расходов. Автоматизировать стоит уже понятную схему расчёта, иначе спорная формула просто начнёт пересчитываться быстрее.

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