Анализ ассортимента - Анализ дублирующих препаратов с одинаковым действующим веществом для оптимизации товарной матрицы
В рамках данного раздела рассматривается методология анализа ассортимента сети аптек с акцентом на идентификацию дублирующих препаратов, которые имеют одинаковое активное вещество. В условиях конкурентного рынка, регуляторных требований и ограничений по SKU, задача сводится к нормализации номенклатуры, унификации единиц измерения и форм, а затем к принятию управленческих решений по консолидации и рационализации товарной матрицы. Разбор опирается на архитектуру BI DWH, процессы ETL, качество данных и конкретные алгоритмы для автоматизации идентификации дублей и поддержки принятия решений менеджментом.
Данная глава ориентирована на технический профиль: описание архитектуры данных, схемы, протоколы интеграции, примеры кода SQL для идентификации дублей и рекомендации по внедрению в инфраструктуру аптечной сети. В тексте приведены принципы нормализации, варианты реализации в реальном DWH и типовые сценарии эксплуатации.
- Цели анализа дублей и ожидаемые результаты
- Архитектура данных и модель данных
- Алгоритмы идентификации дублей и их эмпирическая валидизация
- Интеграция данных, ETL-процессы и качество данных
- Практические сценарии внедрения и операционная устойчивость
Концепции и требования к данным
Идентификация дублирующих препаратов строится на сопоставлении записей о товарах по одному активному веществу. В этом контексте дубликаты понимаются как набор SKU приводящих к одному базовому активному веществу, но различающихся по форме выпуска, солям, упаковке или торговому бренду. Основная цель - выделить группы SKU, которые можно консолидацией или унификацией привести к минимальному набору позиций без потери ассортимента и покупательской привлекательности.
Ключевые концепции:
- Активное вещество и его базовая идентификация. В идеале - INN (International Nonproprietary Name). Однако в реальных каталогах присутствуют сольные формы, гидраты, экстракты и т. п. В рамках анализа важно определить базовую форму вещества и отдельныеSalt/Prodrug формы в контексте дальнейшей нормализации.
- Номенклатура и синонимы. Производители используют различные названия одного и того же вещества: торговые названия, региональные синонимы, упаковочные идентификаторы. Необходимо создать карту синонимов к базовому INN.
- Форма выпуска, содержание и дозировка. Совпадение активного вещества само по себе недостаточно - нужна сопоставимая форма выпуска (таблетка, капсула, суспензия), концентрация/сила действия, путь введения (per os, topical и т. д.).
- Соление и соль/кислотность. Препараты с тем же INN и разной солью могут иметь различные ценовые и доступ к рынку параметры. В некоторых случаях их следует агрегировать на уровне базового вещества, в других - сохранять различия для точного конкурентного анализа.
- Контекст продаж и маржинальности. Дублирование SKU может влиять на управляемость запасами, ценообразование и промоактивности. Нужна методика оценки эффекта консолидации на прибыльность и доступность.
Практическим следствием является переход к ядру справочника активных веществ (dim_active_substance) и к нормализованной номенклатуре SKU. В качестве основы рекомендуется реализовать:
- единый базовый INN-событийный ключ;
- таблицу синонимов и соответствий к INN;
- таблицу солей и форм, связанных с INN, с возможностью агрегации к базовой форме;
- единый канал времени и версии справочников, чтобы отслеживать изменения в кодификациях и их влияние на анализ.
Ниже приведена примерная модель данных, которая будет использована в оркестрации и последующем анализе.
| Поле | Описание | Примечания |
|---|---|---|
| inn_id | Внутренний идентификатор базовой формы активного вещества | Уникальный ключ INN |
| active_substance_name | Название базового активного вещества | Стандартизированное поле |
| salt_form_id | Идентификатор соли или солевой формы | При отсутствии - NULL |
| salt_form_name | Название соли/солей | Пример: "Фосфат натрия" |
| dosage_form_id | Идентификатор формы выпуска | Таблетка, капсула, суспензия и пр. |
| dosage_form_name | Название формы выпуска | |
| strength | Сила действия (например, 500 мг) | Число с единицей измерения |
| unit | Единица измерения | мг, мл и т. д. |
| sku_id | Идентификатор конкретного торгового наименования SKU | |
| sku_code | Код SKU | |
| brand_name | Торговое название бренда | |
| pack_size | Размер упаковки | Количество единиц в упаковке |
| packaging_unit | Единица упаковки | шт., пляшка и т. д. |
| vendor_id | Идентификатор поставщика | |
| vendor_name | Название поставщика | |
| category_id | Категория товара | Фарм. группа или сегмент |
| category_name | Название категории | |
| sales_amount | Объем продаж за период | Для расчета методик оптимизации |
| gross_margin | Валовая маржа за период | |
| last_seen_date | Дата последнего появления SKU в системе |
Концептуальная схема такого подхода позволяет производить агрегацию по inn_id (базовое вещество) и парам дифференцированной формы выпуска и соли. После нормализации можно перейти к идентификации дубликатов на уровне inn_id + dosage_form_id + strength + salt_form_id.
Архитектура и модель данных
Архитектура BI DWH для задачи анализа дублей по одному активному веществу должна обеспечивать четкое разделение этапов: инклузивная загрузка данных, их гармонизация, хранение справочных данных и оперативную аналитику. Предлагаемая архитектура включает три слоя: ODS (оперативно-аналитическая система), ядро MDM/домены справочников и слой аналитических витрин (хранилище фактов и измерений).
- Источники данных. Для корректного анализа важны данные из ERP/продаж, PIM/каталогов, ERP-логистики, а также внешние каталоги поставщиков и регуляторные справочники. Эти потоки интегрируются через ETL/ELT-процессы и подстраиваются под версионность справочников.
- Справочники и мастер-данные. Важна единая версия справочников активных веществ, форм выпуска, солей и брендов. Все изменения фиксируются в MDM, чтобы история изменений не разрушала консистентность анализа.
- Модель данных. Используется звёздная схема: размерность dim_product (SKU), dim_active_substance (INN), dim_dosage, dim_salt, dim_brand, измерение времени (dim_time), факт продаж (fact_sales) и факт ассортимента (fact_product_profile). Связи между размерностями поддерживают агрегацию на уровне inn_id и определённых комбинаций параметров SKU.
- Интеграция и обмен данными. Интеграцию можно реализовать через современные оркестраторы (например, Apache Airflow, Dagster) для планирования ETL-цепочек, с поддержкой контроля версий справочников и дедупликацией на этапе загрузки.
Пример целей архитектуры:
- гибко добавлять новые источники данных без разрыва схемы;
- поддерживать версионность и аудит изменений справочников;
- обеспечивать консистентную агрегацию по inn_id для дилерского анализа;
- предоставлять быстрые витрины для дашбордов по различным ролям: планировщикам, закупщикам, аналитикам по ассортименту.
Чтобы наглядно структурировать модель данных, приведём краткую схему соответствий:
- dim_active_substance - базовый INN, с уникальным inn_id
- dim_salt - соль и/или соль-подформа, связанная через salt_form_id
- dim_dosage - форма выпуска и сила; связь через dosage_form_id и strength
- dim_product - SKU-уровень с привязкой к inn_id, salt_form_id, dosage_form_id
- fact_product_profile - факты по наличию и составе позиций (набор SKU в ассортименте)
- fact_sales - продажи SKU, привязка к time_id и sku_id
Имеется возможность представить небольшую табличку структурой на уровне dimensions, чтобы показать связи и шагающие ступени нормализации.
Алгоритмы идентификации дублей и их реализация
Основной алгоритм сводится к группировке SKU по базовому активному веществу и сопутствующим параметрам, с последующим выделением «многочисленных» SKU в рамках одной группы. Этапы включают:
- нормализация названий и синонимов активных веществ в INN-идентификатор (inn_id);
- агрегация соль/форма и сила (salt_form_id, dosage_form_id, strength);
- группировка по inn_id + salt_form_id + dosage_form_id + strength;
- поиск групп, где число SKU > 1;
- расчет индикаторов риска и приоритетов консолидации (например, доля продаж и маржа по группе, доля запасов).
Эти этапы позволяют не просто найти дубликаты, но и ранжировать их по экономической эффективности и операционным рискам. В случае отсутствия одной из составляющих (например, соль отсутствует), группы формируются по inn_id + dosage_form_id + strength, с дальнейшей возможной агрегацией на уровень inn_id.
-- Пример 1: поиск групп дубликатов по inn и формам SELECT inn_id, salt_form_id, dosage_form_id, strength, COUNT(DISTINCT sku_id) AS sku_count, SUM(sales_amount) AS total_sales, AVG(gross_margin) AS avg_margin ## FROM dim_product dp JOIN dim_active_substance das ON dp.inn_id = das.inn_id LEFT JOIN fact_sales fs ON dp.sku_id = fs.sku_id GROUP BY inn_id, salt_form_id, dosage_form_id, strength HAVING COUNT(DISTINCT sku_id) > 1 ORDER BY total_sales DESC;
-- Пример 2: инициирование процесса консолидации — выбор лидера SKU по группе
WITH duplicates AS (
SELECT
inn_id,
salt_form_id,
dosage_form_id,
strength,
MIN(sku_id) AS leader_sku_id
## FROM dim_product
GROUP BY inn_id, salt_form_id, dosage_form_id, strength
HAVING COUNT(*) > 1
)
SELECT d.inn_id, d.salt_form_id, d.dosage_form_id, d.strength, d.leader_sku_id
FROM duplicates d;
Для повышения точности можно внедрить дополнительные правила:
- учет региона и цепочки поставок. Разные регионы могут использовать разные соль/формы, их объединение по inn_id возможно только после верификации экономических параметров;
- учет временных изменений. Ввод новых упаковок или форм может существенно повлиять на анализ, поэтому важны версионные слои справочников и датавалидность;
- учет специфики регуляторики. В отдельных странах требования по уникальности бренда и форм выпуска свободно влияют на принятие решений.
Процесс идентификации дублей может быть дополнен правилами на базе простого ранжирования: оставить один SKU на группу (lead SKU) по критериям: более высокий объем продаж, вышее среднее значение валовой маржи, лучшее наличие на складе, более широкая доступность по регионам. В дальнейшем можно использовать эти правила в ETL-процессах и в витринах аналитики.
Эталонные процессы ETL и качество данных
Для устойчивого анализа дублей требуется повторяемый и управляемый процесс ETL. Рекомендуется разделить загрузку на четыре слоя:
- Staging. Временная зона, куда поступают сырые данные из ERP, PIM и внешних каталогов. Выполнение очистки имени, устранение дубликатов на входном этапе и первичная нормализация INN-имен.
- Core MDM. Мастер-данные для активных веществ, соль/форма и форма выпуска. Версионирование и разрешение конфликтов между источниками. Привязка к срокам и аудиту.
- Warehouse. Хранилище фактов продаж и ассортимента, связанное с dimension-моделями. Здесь осуществляется агрегация по inn_id и другим параметрам для анализа дублей.
- Semantic layer и витрины. Оптимизированные структуры для BI-инструментов (дашбордов и отчеты, доступные для закупщиков и руководителей).
Качество данных - краеугольный камень. Важны:
- полнота и консистентность ключевых полей (inn_id, dosage_form_id, salt_form_id, strength);
- корректность сопоставления синонимов активных веществ и их версий;
- мониторинг изменений справочников и согласование версий в всех слоях;
- контроль изменений SKU и их музей в течение времени (versioning/auditing).
Рекомендованный набор качественных метрик:
- доля SKU без привязки к inn_id в формате normalized;
- доля SKU, у которых есть противоречивые salt_form_id между источниками;
- доля дубликатных групп, где Lead SKU отличается от других по критериям продаж или маржи;
- частота обновления справочников и задержки синхронизации между источниками.
Практическая реализация и кейсы внедрения
Для внедрения в реальную сеть аптек требуется поэтапный подход, учитывающий организационные аспекты, технологическую готовность и регуляторный фон.
Этапы внедрения:
- формирование команды и ролей: владельцы продукта, data steward, аналитики ассортимента, IT-инженеры данных и закупщики;
- стартовый пилот на одной географической зоне или на ограниченном наборе категорий. Цель - проверить корректность нормализации INN, точность выявления дублей и влияние на модель ассортимента;
- расширение пилота на весь регион/сеть с внедрением ETL-цепочек и мониторинга качества;
- интеграция с процессами закупок и планирования ассортимента: для каждого выявленного дубля - решение об сохранении, консолидации или замене SKU;
- управление изменениями. Ввод новых правил в справочники, ежедневные или еженедельные обновления, журнал изменений и коммуникация с бизнес-подразделениями.
Выбор инструментов и технологий. В техническом профиле можно упомянуть:
- оркестраторы и ETL-платформы: Apache Airflow, Dagster;
- базы данных и хранилища: облачные или локальные решения, поддерживающие коллаборативную работу с справочниками и фактами;
- BI-инструменты. Для мониторинга дублей и их влияния на ассортимент можно использовать платформы типа Metabase или Tableau, с доступом к слою DW.
Потенциальные кейсы внедрения:
- консолидация линейки одинаковых активных веществ в рамках регионального рынка; снижение числа SKU на 15-25% без потери доступности;
- рационализация упаковок по группе INN; переход к единому lead SKU для каждого INN в конкретной форме выпуска; сокращение числа лейблов и промо-опций, сохранение ассортиментной полноты;
- интеграция с цепочкой поставок. Уменьшение числа вариаций поставщиков по одному INN за счет унифицированной формы и соли, что упрощает договоры и закупки.
Ключевые вызовы и риски:
- риск регуляторной несовместимости при аггрегации соль/form: требуют аккуратного анализа по регионам;
- риск потери доли продаж из-за чрезмерной консолидации; необходимы сценарии «бережной» оптимизации;
- сложности в поддержке справочников и версионности при частых изменениях в поставках и составе активных веществ.
Key takeaways
- Дублирующиеся SKU по одному активному веществу называются группами inn_id с различными salt_form и dosage_form; задача - их идентификация и разумная консолидация без снижения доступности.
- Архитектура DWH должна включать единый мастер-данных справочник INN и связи с солью, формой выпуска и силой. Это обеспечивает единый контекст для анализа дублей.
- Алгоритм идентификации дублей строится на группировке SKU по inn_id + salt_form_id + dosage_form_id + strength, с последующей оценкой экономического эффекта и операционных рисков.
- Этап ETL и качество данных критично: контроль версий справочников, аудит изменений и мониторинг целостности ключевых полей.
- Внедрение требует пилота, управления изменениями и тесной интеграции с закупками и планированием ассортимента.
- Непременным элементом являются показатели эффективности - доля консолидации, рост маржинальности, улучшение доступности и снижение издержек на хранение.
- Инструментальная база: применение современного оркестратора и инфраструктуры для поддержки версионности справочников и скорости анализа.
FAQ
- Что считается дубликатом препарата в рамках анализа?
дубликатом считается SKU, который относится к одному базовому активному веществу (inn_id) и имеет сопоставимую форму выпуска (dosage_form_id) и силу (strength). Если у SKU есть соль или соль-форма, они объединяются на уровне inn_id и salt_form_id, но в некоторых случаях требуется отдельная агрегация по salt_form_id, если соль влияет на регуляторику, цену или доступность. Главная цель - определить группы SKU, которые можно консолидировать без потери ассортимента.
- Зачем нужна нормализация к INN и синонимам?
реальность каталога насыщена синонимами, торговыми названиями и региональными обозначениями. Нормализация к INN обеспечивает единый контекст и позволяет корректно аггрегировать характеристики по активному веществу, избегая ошибок из-за различий в названиях.
- Какие данные считаются критичными для идентификации дублей?
inn_id, salt_form_id, dosage_form_id, strength, sku_id, brand_name и торговые данные; дополнительно важны данные по продажам и марже для оценки последствий консолидации. Наличие аудита версий справочников и времени загрузки снижает риск ошибок.
- Как влияет соль на анализ дублей?
соль может влиять на регуляторные признаки, цену и доступность. В рамках анализа можно агрегировать по inn_id + salt_form_id для выявления дублей, а затем оценивать, требует ли конкретная соль отдельной консолидации. В регионах с регуляторной необходимостью соль может сохраняться как отдельная единица анализа.
- Как определить порядок консолидации SKU?
применяются правила на основе экономической эффективности: доля продаж, валовая маржа, покрытие по регионам, доступность запасов и стабильность спроса. В качестве лидера SKU выбирается запись с наилучшими значениями по этим критериям, а остальные SKU подвергаются миграции или замене.
- Какие данные и процессы требуют мониторинга после внедрения?
контроль изменений в INN-справочнике, частоты обновления форм и соли, показатели консолидации по регионам, влияние на доступность и запасы, а также стабильность цен и промо-эффективности. Важно поддерживать отчеты и оповещения об отклонениях.
- Какие источники данных наиболее важны для точного анализа дублей?
ERP/прайс-листы, PIM-каталоги, внешние каталоги поставщиков и регуляторные справочники. Важна совместимость по времени и версии данных, чтобы не допустить ложных дублей из-за рассогласованных обновлений.
- Какую роль играет MDM в рамках проекта?
MDM обеспечивает единый источник истины по INN, соль/форма, форма выпуска и брендам, а также версионность справочников. Без MDM задача будет подвержена рассинхронизации между источниками данных и усложнит последующую агрегацию дублей.
- Какие метрики полезно показывать руководству?
доля консолидации SKU в группе INN, изменение числа SKU на уровне INN, чистая экономическая выгода (рост маржи, снижение затрат на склад), показатель доступности ассортимента по регионам и динамика по времени.
- Какие риски существуют при внедрении?
регуляторные требования к формам выпуска и сольям, неправильная агрегация соль/форма, потери продаж при чрезмерной консолидации, задержки в обновлении справочников и невозможность оперативно реагировать на изменения рынка. Управление рисками включает пилоты, регламент изменений и постоянную коммуникацию между бизнес-ролями.
Завершая главу, следует подчеркнуть: рационализация ассортимента с опорой на идентификацию дублей по одному активному веществу позволяет снизить операционные издержки, улучшить управляемость запасами и повысить маржинальность на уровне сети аптек, сохранив при этом доступность наиболее востребованных форм выпуска.
