Анализ ассортимента - Анализ дублирующих препаратов с одинаковым действующим веществом для оптимизации товарной матрицы

В рамках данного раздела рассматривается методология анализа ассортимента сети аптек с акцентом на идентификацию дублирующих препаратов, которые имеют одинаковое активное вещество. В условиях конкурентного рынка, регуляторных требований и ограничений по 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).

Практическая реализация и кейсы внедрения

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

Этапы внедрения:

формирование команды и ролей: владельцы продукта, 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, чистая экономическая выгода (рост маржи, снижение затрат на склад), показатель доступности ассортимента по регионам и динамика по времени.

Какие риски существуют при внедрении?

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

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

← Предыдущая статья

Анализ ассортимента - Анализ глубины ассортимента по фармакологическим группам для оценки полноты предложения для покупателей

Следующая статья →

Анализ ассортимента - Анализ маржинальности каждого препарата для выявления наиболее прибыльных товаров