Анализ ассортимента - Анализ доли товаров разных производителей внутри одной фармакологической группы
Современная сеть аптек оперирует огромным ассортиментом, где каждое фармакологическое направление может включать множество производителей. Анализ доли товаров разных производителей внутри одной фармакологической группы позволяет понять концентрацию поставщиков, эффективность ассортимента и условия торговых соглашений. В данной главе рассмотрены архитектура данных, методики расчета и практические подходы к реализации аналитики, от моделирования данных до развёртывания на BI-платформах.
Введение затрагивает как теорию построения полноценных измерений для DWH, так и практические аспекты интеграции данных, контроля качества и ускорения процессов подготовки данных. Разобраны сценарии использования: от мониторинга диверсификации поставщиков внутри ATC-групп до оценки динамики рыночной доли по регионам и каналам продаж. Особое внимание уделено прозрачности расчётов, воспроизводимости и масштабируемости в условиях многоквартирной сети аптек.
- Как определить фармакологическую группу и связать её с производителями
- Как спроектировать схему данных и расчётные показатели
- Какие требования предъявляются к качеству данных и как обеспечить миграцию мастер-данных
- Как построить эффективные агрегаты и механизмы обновления
- Какие сценарии внедрения и визуализации применимы для руководства и закупок
Архитектура данных и модель данных
Архитектура данной аналитики строится на классической звездной схеме с выделением фактов по продажам и измерений по группе, производителю, товару и магазину. Основной факт - продажи по конкретному товару за конкретный период в конкретном магазине. В контексте анализа доли производителей внутри фармакологической группы связка идёт через уровень группировки по ATC (Anatomical Therapeutic Chemical) и по производителя товара.
Ключевые элементы модели данных:
- Факт SalesFact: показывает объёмы продаж и сумму продаж по товарам.
- DimDate: хранит дату продаж, обеспечивает агрегацию по месяцам, кварталам и годам.
- DimStore: описывает региональный и канальный контекст продаж.
- DimProduct: содержит информацию о товаре, связанном ATC-группе, и ключах к бренду/производителю.
- DimManufacturer: справочник производителей, включая идентификатор, наименование, страну происхождения и прочие характеристики.
- DimATCGroup: справочник по фармакологическим группам, уровни ATC (например, L3/L2) и соответствующие коды.
Схема связей наглядно демонстрирует, что связь между продажами и производителем проводится через DimProduct. Это обеспечивает корректность расчётов даже в случае смены производителя по продукту, а также позволяет учитывать случаи переименований или ребрендингов без потери исторических данных.
- Важное для интеграции: поддерживать версию мастер-данных по производителю и ATC-группе через механизм SCD (Slowly Changing Dimension), чтобы сохранять историю изменений, влияющих на расчёты доли.
- Для удобства анализа рекомендуется иметь отдельную агрегированную таблицу MV_ProductManufacturerShare, которая хранит предвычисленные значения доли по группам, временным интервалам и географическому разрезу.
Техническое решение в виде набора компонентов:
- Источники данных: ERP/WM-системы аптечной сети, поставщики и каталоги брендов, POS-терминалы, онлайн-канал и промо-балансы.
- Интеграция и обработка: ELT-пайплайны на базе Spark или подобного движка, оркестрация через Apache Airflow или аналог; обмен данными через промежуточные слои для обеспечения консистентности.
- Хранилище: облачный или локальный DWH со слоем фактів и размерных таблиц; использование столбцовых СУБД (например, ClickHouse) для OLAP-запросов и скорости агрегаций.
- BI/аналитика: дашборды в Tableau/Power BI или аналогах, поддерживающие фильтры по группе, региону, времени и производителю.
Справочник по измерениям (помощь в понимании термина)
| Измерение | Ключ | Комментарий |
|---|---|---|
| ATC-группа | atc_group_id | Номер группы по ATC, выбран для анализа внутреннего ассортимента |
| Производитель | manufacturer_id | Уникальный идентификатор поставщика |
| Товар | product_id | Уникальный идентификатор товара; связан с ATC-группой и производителем |
| Магазин | store_id | Физический или онлайн-канал магазина |
| Период | date_id | Отражение даты продажи (день/месяц/год) |
Подход к интеграции и качеству данных требует строгого управления мастер-данными: поддерживают единый справочник производителей, соответствие кодов товаров и их категорий, а также контроль валидности записей по ATC-коду. В качестве ближайшей практики рекомендуется внедрить процесс MDM, который обеспечивает единый источник достоверной информации по товарам, производителям и ATC-группам, а также детальные истории изменений.
Математика расчета доли и алгоритмы
Ключевая метрика - доля производителя в рамках конкретной фармакологической группы за заданный период. В базовом виде она определяется как отношение продаж производителя в группе к суммарным продажам всех производителей внутри той же группы за период, возможно с учётом регионов и магазинов.
Определение доли может зависеть от единицы измерения:
- Доля по выручке: сумма продаж в денежном выражении по товарам конкретного производителя, относящимся к группе.
- Доля по объёмам: количество проданных единиц товара.
Расчёт требует аккуратной фильтрации по ATC-группе и согласованности дат. В случае промо-акций и возвратов следует использовать очищенные данные продаж (net_sales) или сделать альтернативный показатель (например, валовую выручку без учёта скидок) в зависимости от бизнес-целей.
Алгоритм расчета, который обеспечивает корректность и воспроизводимость, может быть реализован следующим образом:
- Фильтрация данных по выбранной ATC-группе и по временным интервалам (например, за месяц или за квартал).
- Расчёт group_total_sales - сумма продаж по всем товарам в группе за период.
- Расчёт manufacturer_sales - сумма продаж по товарам конкретного производителя в той же группе и период.
- Вычисление market_share = manufacturer_sales / group_total_sales для каждого производителя.
- Опционально: расчёт market_share по магазинам/регионам и агрегация к уровню группы.
Особое внимание уделяется агрегациям с учётом масштаба данных: необходимо поддерживать параллелизм при больших объёмах продаж, предусмотреть индексирование по date_id, atc_group_id и manufacturer_id.
- При необходимости можно выполнять расчёты с использованием оконных функций для создания временных рядов или скользящих средних по доле в группе за несколько периодов.
- Для устойчивого поведения в условиях неполной или неравной базы данных целесообразно использовать диапазоны дат и периодические дефляторы, чтобы сравнивать периоды с разной базой поставщиков и ассортимента.
Примерная схема расчета в виде SQL-запроса (упрощённый) приведена ниже для иллюстрации концепции. Реальная реализация будет зависеть от конкретной СУБД и структуры данных.
WITH group_sales AS (
SELECT
dp.atc_group_id,
fs.date_id,
SUM(fs.sales_amount) AS group_sales
## FROM FactSales fs
JOIN DimProduct dp ON fs.product_id = dp.product_id
## WHERE dp.atc_group_id = :atc_group_id
AND fs.date_id BETWEEN :start_date AND :end_date
GROUP BY dp.atc_group_id, fs.date_id
),
manufacturer_sales AS (
SELECT
dp.atc_group_id,
dp.manufacturer_id,
fs.date_id,
SUM(fs.sales_amount) AS manufacturer_sales
## FROM FactSales fs
JOIN DimProduct dp ON fs.product_id = dp.product_id
## WHERE dp.atc_group_id = :atc_group_id
AND fs.date_id BETWEEN :start_date AND :end_date
GROUP BY dp.atc_group_id, dp.manufacturer_id, fs.date_id
)
SELECT
ms.atc_group_id,
ms.manufacturer_id,
ms.date_id,
ms.manufacturer_sales,
gs.group_sales,
(ms.manufacturer_sales / NULLIF(gs.group_sales, 0)) AS market_share
FROM manufacturer_sales ms
JOIN group_sales gs
ON ms.atc_group_id = gs.atc_group_id
## AND ms.date_id = gs.date_id
ORDER BY ms.atc_group_id, ms.date_id, ms.manufacturer_id;
- Альтернативой может служить создание предвычисленного агрегированного слоя, например, MV_ProductManufacturerShare, который хранит долю по сочетанию (atc_group_id, manufacturer_id, date_id) и использует нормализацию и инкрементальные обновления для поддержания производительности.
- В зависимости от целей анализа может потребоваться расчёт по различным уровням агрегации: по группе, по подгруппе ATC, по региону, по сетевому каналу с учётом фильтров.
Оптимизация вычислений:
- Использование агрегатных таблиц для заранее рассчитанных долей по месяцам и по конкретной ATC-группе сокращает время отклика дашбордов.
- Разделение потоков загрузки для фактов продаж и справочников минимизирует блокировки и повышает параллелизм.
- Индексация по date_id и atc_group_id в FactSales и DimProduct ускоряет выборку.
- Периодические проверки консистентности между DimProduct и DimATCGroup, DimManufacturer предотвращают рассогласование в данных.
Интеграция данных и качество данных
Ключ к достоверности анализа - устойчивость контура источников данных и аккуратная работа с мастер-данными. В контексте анализа доли производителей внутри фармакологической группы критичны:
- единая идентификация производителей и ATC-групп;
- корректная связь товара с производителем;
- согласованность справочников по времени изменений.
Часть процесса посвящена настройке процесса загрузки данных:
- Инкрементальные загрузки: обновление фактов продаж за период, добавление новых товаров и производителей.
- Управление изменениями MAster Data: SCD-2 по DimManufacturer и DimATCGroup для сохранения истории и предотвращения артефактов при ребрендингах.
- Калибровка данных: проверки уникальности ключей, соответствие кодов ATC и производителей справочникам, валидация сумм продаж.
Ключевые шаги в управлении качеством данных:
- Валидность связей: каждый product_id must map to an existing atc_group_id и manufacturer_id.
- Контроль дубликатов: выявление и устранение дубликатов по фактам продаж.
- Нормализация валют: если продажи учитываются в разных валютах, обеспечение единицы расчетов (например, конвертация в базовую валюту).
- Верификация значений: отрицательные продажи, нулевые показатели и отсутствующие значения - подсветка для ручной проверки.
- Мониторинг изменений: отслеживание изменений в DimManufacturer и DimATCGroup и регистр изменений для аудита.
Интеграция с процессами закупок и планирования:
- Аналитика доли производителей внутри группы informs переговорные раунды с поставщиками, позволяет оценить концентрацию поставок и риски зависимости.
- Встраиваемая в планирование ассортиментной политики функция: на основе динамических долей определить, какие товары и какие бренды требуют промо-акций или перераспределения полок.
На практике следует рассмотреть использование Open-source инструментов (например, Apache Spark для ETL, ClickHouse для OLAP) и/или облачных решений (Snowflake, BigQuery) в зависимости от инфраструктуры. В рамках российского контекста допустима интеграция с локальными системами на базе PostgreSQL или 1С-решений, однако стоит держать в фокусе производительность и масштабируемость.
Хранение и производительность
Стратегия хранения ориентирована на скорость доступа к агрегированным данным и возможность детализации до уровня магазина и периода. Основные принципы:
- Разделение слоя фактов и размерностей: факт продажи - в FactSales; размерности - DimDate, DimStore, DimProduct, DimManufacturer, DimATCGroup.
- Материализованные представления и агрегаты: MV_ProductManufacturerShare для текущей выборки по группе и периодам; MV_GroupSalesForATC для быстрых сверок общей продажи по ATC-группе.
- Индексация и партиционирование: по дате и ATC-группе, чтобы ускорять запросы; использование колоночных форматов для ускорения сканирования.
- Инкрементальные загрузки: обновление агрегатов по мере поступления данных продаж; периодическое обновление справочников.
- Контроль версий и аудит: хранение информации об изменениях в DimManufacturer и DimATCGroup и их влиянии на расчёты доли.
Особенности реализации в рамках многосквозной архитектуры:
- В крупных сетях с большим количеством магазинов и периодов необходимо обеспечить горизонтальное масштабирование хранилища и скорости выполнения запросов.
- Встроенная кэш-логика BI-платформы под запросы долей по группе и по производителю c учётом фильтров по времени и регионах.
- Проектирование схем агрегаций с учётом того, что бизнес часто запрашивает не только средние доли, но и распределения по магазинам и регионам.
Реализация аналитического сценария
Практический сценарий: запуск ежемесячного анализа доли производителей внутри ATC-группы для подготовки управленческих дашбордов по закупкам и ассортименту. Этапы реализации:
- Подготовка мастер-данных:
- Включить DimManufacturer и DimATCGroup в систему MDM, обеспечить SCD2 и синхронизацию с внешними каталогами.
- Обеспечить единый код ATC для группы и корректное связывание с товарами.
- Построение фактов продаж и связанных измерений:
- Собрать продажи по фактам (FactSales) с привязкой к DimProduct и DimDate.
- Обеспечить связь DimProduct с DimManufacturer для последующих агрегаций.
- Расчёт доли:
- Реализовать SQL-выражения для расчёта market_share по группе и по производителю на уровне нужной агрегации (месяц, регион, магазин). Предусмотреть хранение в MV-таблицах.
- Выводы для бизнес-пользователей:
- Дашборды показывают лидерство производителей в рамках ATC-групп, динамику за период, региональные различия.
- Включение контекстной информации: ценовая политика, промо-акции, сезонность.
- Архитектура обновления:
- Ежемесячная загрузка продаж и ежедневные обновления справочников.
- Обновление MV-агрегатов по расписанию или по триггерам данных.
Приведённый ниже пример демонстрирует схему загрузки и расчётов. В коде отражены ключевые элементы: подготовка и агрегация, загрузка и расчёт доли. Код приведён в виде
для удобства восприятия.
-- Примерный поток расчётов доли производителя внутри ATC-группы
-- 1) Обновление мастер-данных (SCD2 для DimManufacturer и DimATCGroup)
-- 2) Загрузка фактов продаж (FactSales) с присоединением DimProduct
-- 3) Расчёт доли через промежуточные таблицы
-- Пример 1: агрегаты по группе за период
CREATE MATERIALIZED VIEW MV_GroupSales AS
SELECT
dp.atc_group_id,
DATE_TRUNC('month', dd.date) AS month_start,
SUM(fs.sales_amount) AS group_sales
## FROM FactSales fs
JOIN DimProduct dp ON fs.product_id = dp.product_id
JOIN DimDate dd ON fs.date_id = dd.date_id
GROUP BY dp.atc_group_id, DATE_TRUNC('month', dd.date);
-- Пример 2: агрегаты по группе и производителю
CREATE MATERIALIZED VIEW MV_GroupManufacturerShare AS
SELECT
dp.atc_group_id,
dp.manufacturer_id,
## DATE_TRUNC('month', dd.date) AS month_start,
SUM(fs.sales_amount) AS manufacturer_sales
## FROM FactSales fs
JOIN DimProduct dp ON fs.product_id = dp.product_id
JOIN DimDate dd ON fs.date_id = dd.date_id
GROUP BY dp.atc_group_id, dp.manufacturer_id, DATE_TRUNC('month', dd.date);
-- Пример 3: расчёт доли на уровне месяца
SELECT
g.atc_group_id,
g.manufacturer_id,
g.month_start,
g.manufacturer_sales,
m.group_sales,
g.manufacturer_sales / NULLIF(m.group_sales, 0) AS market_share
FROM MV_GroupManufacturerShare g
JOIN MV_GroupSales m
ON g.atc_group_id = m.atc_group_id
## AND g.month_start = m.month_start
ORDER BY g.atc_group_id, g.month_start, g.manufacturer_id;
Внедряемые сценарии визуализации:
- Динамика доли производителей внутри ATC-группы по регионам.
- Сравнение лидирующих производителей внутри группы за текущий период и аналогичный прошлый период.
- Аналитика зависимости доли от промо-акций и сезонных факторов.
Оценка рисков и управление изменениями:
- Риск несоответствия между справочниками производителей и товарами, что приводит к искажению долей. Рекомендуется внедрить периодическую сверку связей и автоматизированные уведомления об расхождениях.
- Риск потери истории при изменениях в DimManufacturer. Обеспечить SCD2 и хранение исторических записей.
- Риск некорректной агрегации из-за промо-цен или возвратов. Включить отдельные измерения для чистых продаж и промо-подпунктов.
Визуализация и сценарии внедрения
Дашборды должны представлять:
- Рыночную долю по ATC-группе и по производителю с возможностью детализации по магазинам и регионам.
- Тренды за заданные периоды и сезонные паттерны.
- Сопоставление доли с плановыми целями по ассортименту и условиями поставок.
Пользовательские сценарии:
- Аналитик по закупкам может определить производителей, которые доминируют внутри группы и нуждаются в специальных переговорных условиях.
- Менеджер ассортимента оценивает необходимость включения нового бренда или перераспределения полок в зависимости от доли и динамики.
- Руководство видит общую картину концентрации поставщиков и принимает решения по стратегическому развитию ассортимента.
Советы по интеграции с BI-инструментами:
- Используйте преднастроенные меры market_share в виде вычисляемых полей, чтобы обеспечить согласованность расчётов по всем дашбордам.
- Придерживайтесь единых временных осей и контекстов (мгновенная доля vs. скользящая доля) для поддержания сопоставимости.
Key takeaways
- Архитектура данных должна обеспечить связь продаж с конкретными производителями через DimProduct и DimATCGroup, с учётом истории изменений мастер-данных.
- Расчёт доли производителя внутри ATC-группы следует выполнять на основе валовой продажи или выручки по группе, с учётом периода и региона.
- Эффективность аналитики достигается через предвычисленные агрегаты и материализованные представления, что обеспечивает быструю подачу данных на BI-платформы.
- Качество данных критично: контроль целостности связей между товарами, группами и производителями, управление изменениями мастер-данных.
- Взаимосвязанная оптимизация ассортимента на основе анализа доли производителей внутри группы позволяет улучшить переговоры с поставщиками, скорректировать политику промо и повысить общую рентабельность сети.
FAQ
- Что такое “доля производителя внутри фармакологической группы” и зачем она нужна?
- Это отношение продаж конкретного производителя к суммарным продажам всех производителей внутри заданной фармакологической группы за определённый период. Она позволяет понять концентрацию поставщиков, оценить риск зависимости от одного бренда и информировать решения по ассортименту и переговорам с поставщиками. В сочетании с региональным разрезом даёт полноту картины по сети аптек.
- Какие уровни ATC-группы лучше использовать для анализа?
- В рамках практики рекомендуется использовать уровни ATC, соответствующие аналитическим целям. Часто применяются уровни L3/L2 для баланса детализации и устойчивости данных. Уровень следует зафиксировать в рамках модели и держать единообразным в течение времени.
- Как учитывать промо-акции и возвраты в расчётах доли?
- Промо-акции и возвраты должны учитываться в чистых продажах (net_sales) или в отдельно выделяемых измерениях. Это позволит избежать искажений, когда продажи повышаются за счёт скидок, но фактический вклад производителя в базовом объёме продаж остается низким. Вариант с использованием чистых продаж предпочтителен для мониторинга реальной доли рынка.
- Какие данные необходимы для корректного расчета?
- Продавец по товарам: факт продаж (amount, quantity), дата продажи, магазин.
- Справочник товара: product_id, atc_group_id (ATC), manufacturer_id.
- Справочники производителя и ATC-группы: уникальные идентификаторы и актуальные наименования.
- Временная размерность по датам и географическая размерность по магазинам.
- Какие технологии лучше выбрать для реализации?
- Выбор зависит от инфраструктуры: для ETL и подготовки данных - Apache Spark или аналогичные движки; для OLAP-аналитики - ClickHouse или PostgreSQL с расширениями; для облачных вариантов - Snowflake, BigQuery. Российские решения допускаются в рамках локальной инфраструктуры, но спрос на производительность и масштабируемость должен быть учтён.
- Как организовать процесс обновления мастер-данных?
- Рекомендуется использовать SCD-2 для DimManufacturer и DimATCGroup, чтобы сохранять историю изменений и обеспечивать корректность долговременной аналитики. Внедрить автоматическую сверку связей и уведомления об расхождениях между справочниками.
- Как обеспечить производительность при больших объёмах данных?
- Внедрить MV-агрегаты для наиболее частых запросов (например, MV_GroupSales и MV_GroupManufacturerShare), использовать партиционирование по дате, индексирование по atc_group_id и manufacturer_id, а также предусмотреть кэширование результатов в BI-инструментах.
- Как транслировать расчёты в управленческие решения?
- Предоставлять не только цифры доли, но и контекст: лидерство по группам, динамику, региональные различия, критерии эффективности поставщиков, влияющие на ассортимент и ценообразование. Включать рекомендации по корректировке ассортимента и промо-политике.
- Какие риски должны быть управляемы в процессе внедрения?
- Рассогласование между справочниками, неточности в данных по продажам, задержки в обновлениях мастер-данных, неэффективная архитектура агрегаций, недостаточный уровень автоматизации QA. Решения: автоматическая валидация данных, мониторинг изменений, тестирование на выборке и внедрение CI/CD для схем DWH.
- Как синхронизировать аналитику с операционными процессами?
- Настроить ежемесячный цикл обновления данных продаж, еженедельное обновление справочников, автоматизированные проверки качества, и оперативные дашборды для закупок и ассортимента. Взаимодействие между аналитиками и закупщиками должно быть организовано через регулярные обзоры и планирования на основе расчётов доли производителей внутри ATC-групп.
