Кратко о проекте

Клиент

Azbuka Food (ООО «Азбука Фуд Трейд») — дистрибьютор продуктов питания для HoReCa с ассортиментом более 2 500 SKU и клиентской базой свыше 2 000 компаний.

Задача

Автоматизировать аналитику продаж и создать инструмент, который поможет торговым представителям (ТП) выявлять потенциал клиентов и определять, какие категории и товары предложить для увеличения прогрузки — доли потенциально релевантного ассортимента, который клиент закупает у компании.

Решение

Экспертами Qlever построено корпоративное хранилище DWH, объединившее данные из 1С, несистемные и пользовательские источники Excel, а также разработано BI-приложение для торговых представителей. Система анализирует продажи клиентов схожего профиля и помогает ТП определять потенциальные категории и SKU, объем продаж, ценовой ориентир и наличие товара на складе для следующего релевантного предложения.

Результат

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

Решение позволило увеличить долю дополнительных продаж среди действующих клиентов до 30% от планируемого потенциала за счет расширения поставляемого им ассортимента продуктов.

О клиенте

Azbuka Food — один из крупнейших поставщиков продуктов питания для сегмента HoReCa в Нижнем Новгороде и Нижегородской области.

Компания располагает тремя филиалами со складскими комплексами и торговыми командами в Нижнем Новгороде, Чебоксарах и Липецке и осуществляет поставки более чем 2 000 клиентам в шести регионах России. В Нижнем Новгороде также работает собственное современное производство полуфабрикатов.

Ассортиментный портфель Azbuka Food насчитывает более 2 500 SKU и включает рыбу и морепродукты, мясо, молочную продукцию, бакалею, овощи, полуфабрикаты, напитки и другие категории продуктов питания.

Бизнес-контекст

Торговый представитель — ключевое звено бизнеса продуктового дистрибьютора. В Azbuka Food ТП отвечают не только за формирование заказов действующих клиентов, но и за развитие продаж:

• расширяют ассортимент поставок и вводят новые товарные категории;
• выявляют дополнительный потенциал клиентов;
• увеличивают долю Azbuka Food в общем объеме закупок клиента.

При ассортименте более 2 500 SKU торговому представителю необходимо понимать, какие именно товары предложить конкретному ресторану, кафе или отелю, чтобы увеличить объем продаж и одновременно сделать предложение релевантным потребностям клиента.

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

Поэтому для развития продаж Azbuka Food необходимо постоянно совершенствовать три направления:

• скорость взаимодействия с клиентом — быстрее конкурентов формировать релевантное предложение;
• глубину работы с клиентом — понимать структуру его закупок, потенциал и возможные потребности;
• управление ассортиментом — определять, какие товарные категории и SKU предложить конкретному клиенту и по какой цене.

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

Задача клиента

До начала проекта анализ и планирование продаж в Azbuka Food строились на разрозненной отчетности. Специалисты компании вручную выгружали Excel-файлы из 1С: Управление торговлей. Затем данные обрабатывались, консолидировались и использовались для формирования отчетности в BI-системе.

Готовые отчеты ежедневно получали более 50 сотрудников: торговые представители, специалисты отделов продаж, закупок и логистики, а также финансового подразделения.

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

Что предложить конкретному клиенту сегодня, чтобы увеличить объем его закупок у Azbuka Food?

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

Ключевым элементом решения должен был стать BI-дашборд, который помогает торговому представителю:

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

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

Для реализации проекта Azbuka Food обратилась к Qlever Solutions — российскому интегратору с экспертизой в построении корпоративных хранилищ данных (DWH), внедрении BI-платформ и разработке аналитических решений для управления продажами.

Реализация проекта

Команда Qlever Solutions спроектировала для Azbuka Food комплексную аналитическую систему, включающую корпоративное хранилище данных (DWH) и BI-дашборд для торговых представителей.

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

На первом этапе было проведено предпроектное обследование: специалисты Qlever Solutions проанализировали существующую ИТ-инфраструктуру, источники и процессы подготовки данных, собрали требования к дашборду и сформировали целевую архитектуру решения.

Построение DWH

Основой аналитической системы стало корпоративное хранилище данных, в которое были интегрированы источники данных:

1. 1С:Предприятие 8.3 «Управление торговлей» — регистры, отчеты и справочники с данными о движении товаров, продажах, ценах, остатках и других показателях.
2. Excel-файлы — справочники с дополнительными данными о торговых представителях, товарах и профилях клиентов, в том числе поддерживаемые сотрудниками вручную.

Архитектура DWH построена на современном технологическом стеке и охватывает весь цикл работы с данными — от их извлечения из исходных систем до подготовки аналитических витрин для BI.

• Интеграция данных

Для автоматизированного извлечения данных из 1С используется Экстрактор 1С от Денвик Аналитика. Загрузка данных из Excel организована с помощью Dagster.

• Хранение данных

Для консолидации и централизованного хранения исходных и детализированных данных используется PostgreSQL.

• Аналитические витрины

На основе консолидированных данных команда Qlever Solutions разработала 8 специализированных аналитических витрин, необходимых для работы BI-приложения. Совокупность витрин позволяет сопоставлять в BI профиль клиента, историю его закупок, ассортимент, цены и текущие складские остатки.

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

• Обработка данных

Трансформация и подготовка данных выполняются с помощью dbt (data build tool). Управление расписанием и зависимостями процессов обработки данных реализовано с помощью оркестратора Apache Airflow. Инструменты обеспечивают регулярное автоматизированное обновление аналитических данных и воспроизводимость расчетов.

• Управление и сопровождение

Для документирования структуры данных и аналитических моделей используется dbt Docs, а управление версиями ETL-проектов организовано в GitLab.

• Потребители данных

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

BI-приложение для торговых представителей

На основе подготовленных витрин данных команда Qlever Solutions разработала BI-приложение, которое помогает торговым представителям определять клиентов с потенциалом роста и подбирать для них товары для дополнительной прогрузки.

Анализ клиентской базы и потенциала продаж

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

С помощью дашборда ТП может анализировать показатели по каждому клиенту:

• выполнение плана по товарообороту и валовой прибыли;
• прогноз выполнения плана;
• долю прогрузки потенциального ассортимента;
• конверсию прогрузки;
• количество дней с момента последней отгрузки;
• ...и другие показатели.

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

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

Анализ потенциала ассортимента и прогрузки клиентов

Приложение сопоставляет продажи выбранному клиенту с фактическими продажами другим клиентам аналогичного профиля. Анализ доступен на уровне товарных категорий с последующей детализацией до конкретных SKU.

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

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

• продажи выбранному клиенту;
• продажи клиентам аналогичного профиля;
• средний объем продаж;
• минимальная и средняя цена реализации;
• текущие складские остатки;
• доля товара в общих продажах;
• активная клиентская база по товару.

При переключении между показателями аналитическая таблица автоматически перестраивается.

При определении цены ТП видит минимальную и среднюю цену реализации товара и может учитывать их для формирования предложения и управлении собственной наценкой с учетом задачи по увеличению валовой прибыли.

После определения категорий и SKU для дополнительной прогрузки ТП может выгрузить подготовленные данные в Excel и использовать их для формирования предварительного коммерческого предложения.

Разграничение доступа и оптимизация модели данных

В готовом BI-приложении необходимо было одновременно реализовать два сценария работы:

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

Для настройки доступа к клиентским данным было реализовано RLS (Row-Level Security) — разграничение доступа на уровне строк. После авторизации система определяет пользователя и отображает ему только закрепленную за ним клиентскую базу.

Применение тех же ограничений ко всему приложению сделало бы невозможным полноценный анализ ассортиментного потенциала: ТП потерял бы возможность сравнивать своих клиентов с клиентами из других территорий и использовать накопленные данные о продажах компании.

Первоначальный вариант с объединением необходимых данных в одной аналитической витрине оказался нецелесообразным. Из-за необходимости учитывать комбинации торговых представителей, клиентов, номенклатуры, городов и других аналитических разрезов объем такой витрины мог достигнуть 70 млн строк, что существенно усложнило бы модель и увеличило объем обрабатываемых данных.

Для решения этой проблемы команда Qlever Solutions переработала модель данных. Вместо одной большой денормализованной витрины была реализована модель непосредственно в BI с использованием связей и bridge-таблиц.

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

• в блоке анализа клиентской базы каждый торговый представитель видит только своих клиентов;
• в блоке анализа ассортиментного потенциала он может выбирать другие города и анализировать агрегированные данные о продажах по всей компании;
• данные всей компании используются для сравнения клиентов схожего профиля и поиска категорий и SKU с потенциалом дополнительной прогрузки.

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

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

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

DWH автоматизирует процессы загрузки, трансформации и подготовки данных из 1С и Excel, снижая зависимость от ручной подготовки многочисленных Excel-выгрузок и создавая единый слой данных для BI-аналитики.

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

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

Решение позволило увеличить долю дополнительных продаж среди действующих клиентов до 30% от планируемого потенциала за счет расширения поставляемого им ассортимента продуктов.

По итогам проекта мы получили именно тот результат, который хотели видеть. Готовое решение положительно оценили сами торговые представители Azbuka Food — оно соответствует их реальным задачам и может использоваться в ежедневной работе с клиентами. Успешная реализация проекта помогла укрепить внутри компании доверие к развитию аналитики, поэтому мы решили продолжить работу с Qlever Solutions по реализации двух дополнительных дашбордов и уже готовимся ко второй очереди проекта. — Инна Андреева, руководитель проекта со стороны Azbuka Food

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

Архитектура аналитического решения изначально строилась таким образом, чтобы не создавать критической зависимости от конкретной BI-платформы. Сбор, хранение, трансформация и подготовка данных о продажах вынесены в DWH-контур, а BI используется как слой аналитики и визуализации. Такой подход создает основу для дальнейшей миграции: при необходимости клиент сможет заменить имеющееся решение на другую BI-платформу, сохранив процессы интеграции и обработки данных, бизнес-логику и аналитические витрины. — Андрей Харлак, технический директор Qlever Solutions

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

• Источники данных: 1С:Предприятие 8.3 «Управление торговлей», Microsoft Excel
• Интеграция данных: Экстрактор 1С от «Денвик Аналитика», Dagster
• Хранение данных: PostgreSQL, Microsoft SQL Server
• Оркестрация процессов загрузки и обработки данных: Apache Airflow
• Каталог данных: dbt Docs
• Обработка данных (Data Processing): dbt
• Вспомогательные технологии: GitLab
• BI и визуализация данных: Power BI

Функциональные области

Продажи, Продвижение, Дистрибьюция

Пользователи аналитической системы: Торговые представители, Отдел продаж

Бизнес растет и требует развития аналитики?

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

ОБСУДИТЬ ЗАДАЧИ