В последние годы крупный ритейл стал одним из лидеров в области цифровизации бизнеса, внедрения платформ бизнес-аналитики и корпоративных хранилищ данных (КХД, DWH). DWH консолидирует всю важную бизнес-информацию, которая становится основой для отчетности и визуализации в BI.

Для проведения многомерного анализа продаж и разработки дальнейшей стратегии, необходимо учитывать не только розничные и онлайн-продажи, но и проводить анализ результатов торговли и результатов конкурентов на маркетплейсах: объем заказов, выкупа и возвратов, остатки на складах, затраты на логистику, сравнение с конкурентами и т. д.

Получить единый доступ к данным из Wildberries, Ozon, Яндекс Маркет и платформ аналитики маркетплейсов (Маяк, SellerFox, Moneyplace, MPStats) помогут корпоративное хранилище данных и аналитические приложения на базе современной BI-платформы.

В статье на примере дашбордов, разработанных для крупного производителя детской одежды с более 70 магазинами в России и странах СНГ, рассказываем, как DWH и BI могут помочь:

• Разрабатывать новые стратегии маркетинга и продаж на маркетплейсах

• Проводить ABC-XYZ анализ товаров

• Планировать отгрузки продукции на склады

Ситуация

Для разработки стратегии развития компания регулярно отслеживала не только динамику розничных и онлайн-продаж, но и результаты торговли на маркетплейсах Wildberries и Ozon.

Специалисты компании использовали возможности бесплатной встроенной аналитики для продавцов в личных кабинетах маркетплейсов. Показатели продаж конкурентов компания отслеживала во внешнем сервисе аналитики маркетплейсов MPStats. Более детальная информация о продажах хранилась на платформе 1С: Управление торговлей. 

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

Сервисы аналитики не могли предоставить достаточно детализированные данные, чтобы понимать:

• Какие категории товаров нужно усилить для выполнения плана продаж?

• Какую выручку приносят конкретные SKU?

• Почему по некоторым товарам установился низкий процент выкупа заказов?

• Как оптимизировать процессы отгрузки товаров на склады маркетплейсов в зависимости от спроса?

• Какие меры принять, чтобы смягчить последствия меняющейся политики самих МП (платные возвраты, предоплату по некоторым товарам)?

Для принятия решений компания оценивала количество продаж, заказов, возвратов и остатков, процент заказов от остатков, процент выкупа, средние цены у себя и у выбранных конкурентов.

Разрозненные системы не позволяли проводить real-time аналитику этих ключевых показателей, которая необходима для отстройки от конкурентов и принятия своевременных мер по корректировке цен, акций, ассортимента.

Для формирования единой отчетности аналитикам приходилось вручную выгружать данные из всех источников в формате xlsx, сверять коды номенклатур на разных площадках, объединять их, а затем производить расчеты метрик в Excel-таблицах.

Задача осложнялась объемом данных, исчисляющимся сотнями мегабайт, что повышало вероятность ошибок при ручном сборе.

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

Чтобы понять рентабельность конкретной модели товара, руководитель направления маркетплейсов просматривал сразу несколько таких таблиц, что сказывалось на скорости принятия решений.

Для решения этих проблем было принято решение о проекте цифровизации продаж.

Первым этапом стало создание корпоративного хранилища данных и аналитических приложений на базе PIX BI, помогающих своевременно реагировать на изменения рынка.

Решение

В компании уже предпринимали начальные шаги по организации работы с данными и построению бизнес-аналитики для нужд операционного маркетинга.

На базе PostgreSQL было организовано хранение данных из 1С и CRM-системы Mindbox. Внедрена BI-платформа PIX BI, где реализованы базовые аналитические дашборды и отчеты для маркетинга.

Для запланированной автоматизации аналитики не только по показателям маркетинга, но и по остальным направлениям: маркетплейсам, собственной рознице, интернет-магазину, франчайзи, HR - было недостаточно реализованного способа хранения данных.

Для этих целей было разработано полноценное DWH (КХД, корпоративное хранилище данных), выступающее единым репозиторием данных из маркетплейсов.

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

1. Данные из маркетплейсов Wildberries и OZON, получаемые с помощью REST API

2. Данные из 1С: Управление торговлей, выгруженные в формате xml по протоколу SFTP

3. Данные из 1С: ERP, загруженные через шину данных 1С: Шина

4. Мастер-данные в виде файлов, например, справочники с маппингом баркодов товаров на WB, OZON и в 1C, подготовленные экспертами Qlever

5. Данные из внешнего сервиса аналитики маркетплейсов MPStats, выгруженные через REST API

У каждого маркетплейса своя архитектура, подход к расчету показателей и обозначения процессов.

В связи с ручным заполнением карточек на маркетплейсах, идентичные позиции в 1C, OZON и Wildberries не совпадали. Для решения этой проблемы было проведено исследование данных и подготовлен справочник на 25 000 позиций. 75% справочника были сгенерированы автоматически на базе правил преобразования данных исходных систем, остальная часть заполнена вручную специалистами по данным.

Чтобы решить вопрос унификации данных, инженеры данных подготовили единые таблицы по доменным областям, объединяющие в себе данные из OZON и Wildberries по продажам, остаткам, заказам, воронкам продаж, затратам и т. д.

В ходе реализации проекта выяснилось, что на WB возврат возможен без совершения транзакций, а на OZON только после проведения транзакции, то есть, после продажи товара. Вопрос разнородности показателей удалось решить сравнением количества продаж и транзакций и приведением данных к единым структурам и ключам на Core Layer DWH.

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

Для решения задачи был разработан регламент выгрузки данных из MPStats, где были зафиксированы периоды выгрузки по категориям, показателям и конкретным отчетам, в зависимости от того, есть ли ошибка в логике отчета или нет.

В проекте КХД реализована многоуровневая (слоеная) структура LSA – Layered Scalable Architecture со следующим набором слоев:

• Стейджинг, или операционный слой первичных данных, (Staging/Primary Data Layer)

• Ядро (Core Data Layer)

• DDS (Detail Data Storage)

• Слой витрин данных (Data Mart Layer)

• Сервисный слой (Service Layer)

Выбран следующий технологический стек для DWH:

• Для сбора данных используется Dagster (оркестрация) и Python (экстракция, загрузка данных, сбор метаданных)

• В качестве хранилища и системы контроля за доступом к данным выбрана аналитическая СУБД ClickHouse

• Основной инструмент трансформации данных - DBT

На текущий момент на стейджинг слое хранятся

• 62 тыс строк из 1С

• 40,3 млн строк из Wildberries

• 9,4 млн строк из OZON

• 11,8 млн строк из MPStats

В ядре хранилища

• 40,1 млн строк из 1С

• 6,4 млн из Wildberries

• 5,1 млн строк из OZON

• 2 млн строк MPStats

В DDS -слое – 9,1 млн строк данных, в слое витрин данных – порядка 64 млн строк данных.

В общей сложности в DWH хранятся порядка 230 млн строк данных по маркетплейсам (более 9Гб данных).

На следующем этапе проекта на основе объединенных в DWH данных были разработаны аналитические приложения на платформе PIX BI.

Дашборды позволяют отслеживать данные по продажам на маркетплейсах, сравнивать показатели с результатами деятельности конкурентов, проводить мониторинг рынка детской одежды в целом.

1.Приложение «Аналитика по товарам»

Приложение предназначено для ежедневного наблюдения за отчётными показателями по заказам и продажам собственных товаров. Состоит из листов «Сводный отчет», «Аналитика по периодам» и «Данные по SKU».

Лист «Сводный отчет» дает возможность оценить динамику заказов и продаж, средних цен, остатков, процента выкупа и наценки в разных временных разрезах (по дням/неделям/месяцам/кварталам и т.д.).

Также на этом листе можно провести сравнение показателей продаж и заказов к предыдущим периодам (например, к прошлой неделе).

Лист «Аналитика по периодам» используется для анализа количества и цены заказов и продаж, и динамики изменения этих показателей с возможностью сравнения по категориям, маркетплейсам, коллекциям, сезонам и периодам.

Разработана итоговая таблица, отображающая поступления (в штуках и в базовом прайсе), а также все показатели, присутствующие на листе в разрезе SKU и товарных категорий/сезона/категорий по сезонам.

Лист «Данные по SKU» используется для анализа артикулов, продаж и наценки по ABC и XYZ анализу в разрезе маркетплейсов.

Итоговая таблица содержит поступления и все показатели, присутствующие в приложении, с их смещением на прошлую неделю/год в разрезе модели, сезона, товарной категории, сезонов, ABC и XYZ анализа.

2.Приложение «Метрики МП»

BI-монитор для ежедневного отслеживания эффективности работы компании на маркетплейсах и анализа показателей просмотров, добавлений в карточки, добавлений в корзину, себестоимости, выручки, отгрузки, остатков, с возможностью сравнения с предыдущими периодами, разными маркетплейсами и категориями.

Состоит из листов «Основные», «Воронка» и «Управление запасами и движением остатков».

Листы используются для:

• Анализа выручки, себестоимости, маржи, количества продаж и динамики изменения этих показателей в разрезе категории, маркетплейса и периода

• Анализа информации по рекламной воронке: просмотров, переходов в карточку товара, добавлений в корзину, заказов; отслеживание динамики этих показателей и их конверсии в разрезе периода и маркетплейса

• Анализа остатков и отгрузок в разрезе категории, периода, маркетплейсов

Полученные результаты

В результате внедрения корпоративного хранилища сотрудники и руководители компании получили простой доступ к данным по продажам на маркетплейсах.

С внедрением КХД была решена проблема ограниченного периода хранения информации об остатках на маркетплейсах. DWH предоставило возможность собирать детальные исторические данные по продажам, заказам, остаткам на Wildberries и OZON, которые ранее не сохранялись в личных кабинетах маркетплейсов.

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

Аналитические приложения «Аналитика по товарам» и «Метрики МП» в PIX BI предлагают детализированную картину эффективности компании на маркетплейсах в разрезе дня/недели/месяца.

Отслеживая ежедневную динамику продаж у себя и новые активности у конкурентов, специалисты компании получили возможность своевременно выставлять скидки или менять ассортимент для повышения количества заказов.

Аналитика в одном окне экономит до 3 часов в день, ранее затрачиваемых на сбор таблиц и подсчеты метрик в Excel.

Наглядное представление информации о заказах и остатках помогло устранить до 80% ошибок, которые ранее возникали при планировании отгрузок товаров на маркетплейсы.  Логистика между складами OZON и WB была оптимизирована таким образом, чтобы излишки с одного склада в случае необходимости оперативно транспортировались на другой.

Своевременное принятие решений на основе данных обеспечило рост удовлетворенности клиентов и повышение прибыли, расширило возможности масштабирования продаж.

Создание дашбордов стало первым шагом к полной цифровизации и автоматизации управления запасами, ценообразования, планирования, маркетинговой деятельности компании на маркетплейсах.

Среди задач компании в бэклоге - ввести расширенный анализ по конкурентам, предиктивное прогнозирование и систему динамического ценообразования по МП на основе внедренных аналитических приложений.

Источники и стек технологий

Wildberries, Ozon, 1С: Управление торговлей, 1C: Шина, MPStats, DWH, Dagster, Elementary, DBT, Gitlab, MinIO, OpenMetadata, REST API, ClickHouse, PIX BI, Python