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

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

Краткое содержание главы

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

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

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

Факт продаж (fact_sales): ежедневные или еженедельные продажи по SKU и магазину, цены, скидки, промо-метки.
Факт запасов (fact_inventory): запасы на складах и в магазинах, Beginning Inventory, Receiving, Adjustments.
Размерная часть (dim_product, dim_store, dim_date, dim_category, dim_supplier): атрибуты товара (категория, бренды, срок годности), структура сети аптек, календарь продаж.
Методы обеспечения качества данных: валидация уникальности ключей, дефектные записи, тесты на полноту данных, lineage и регламент обновления SCD (Slowly Changing Dimension) для атрибутов товара.
Интеграционные точки: источники POS-систем, ERP/МСФО-учет, данные поставщиков, промо-истории и данные о сроках годности. Архитектура должна поддерживать ELT-пайплайны и параллельную обработку больших объемов данных.
Контроль качества и безопасность: режимы доступа, аудит изменений, соответствие регуляторным требованиям по хранению данных и анонимизации персональных данных.

Для реализации конкретной среды рассматриваемые решения могут выглядеть так:

Хранение в data lakehouse: структурированные данные в формате Parquet/ORC и метаданные в каталоге.
База данных DWH уровня warehouse (например, PostgreSQL, ClickHouse) для быстрых агрегаций и операционных дэшбордов.
Оркестрация процессов: запуск ETL/ELT-пайплайнов, мониторинг качества данных и алертинг через DAG-менеджеры (Airflow или эквивалент).
Модели обработки: SQL-ориентированные вычисления, преобразования в рамках dbt, а для сложной аналитики - Spark или аналогичный движок.

Ниже приведен упрощенный пример архитектурной схемы, иллюстрирующий связи между источниками данных и слоем BI DWH:

Источник POS → технический слой интеграции → staging → телескопическое преобразование → факт и размерные таблицы → BI/аналитика.
Источник ERP/продажа и поставки → интеграция запасов → обновление dim_inventory и факт_inventory.

Источник промо-данных → связь с фактами продаж через промо-метки и календарь.

-- Пример пайплайна (абстрактно) 
-- 1) извлечение данных
SELECT * FROM pos_orders WHERE order_date >= date '2024-01-01';

-- 2) очистка и обогащение
SELECT o.order_id, o.product_id, o.store_id, o.qty, o.price, d.date, p.category
FROM staging.pos_orders o
JOIN dim_date d ON o.order_date = d.date
JOIN dim_product p ON o.product_id = p.product_id;

-- 3) загрузка в факт/размерные таблицы
INSERT INTO fact_sales (date_id, product_id, store_id, sold_qty, revenue)
SELECT date_id, product_id, store_id, SUM(qty), SUM(qty * price)
FROM cleaned_sales
GROUP BY date_id, product_id, store_id;

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

Метрики неликвидности и оборачиваемости

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

Оборачиваемость (turnover) товара рассчитывается как отношение объема продаж к среднему запасу за период. В простейшей форме: оборачиваемость = проданные единицы за период / средний запас за период. Это позволяет сравнивать позиции с разной ценой и разной длительностью хранения.
Продажная доля за период (sell-through rate) характеризует долю фактических продаж от доступного запаса: sell_through = sold_qty / (beginning_inventory + received_during_period - ending_inventory).
Среднее количество дней на складе (DOS, days of stock): DOS = средний запас / средние дневные продажи. Чем выше DOS, тем выше риск неликвидности.
Временная устойчивость спроса: анализ трендов (растущий/падающий спрос), сезонные паттерны и эффекты промо-акций, чтобы отделить долгосрочную неликвидность от сезонной просадки.
Коэффициент регрессии оборачиваемости по категориям и линеям товаров с учетом срока годности и промоций.
Индекс риска устаревания: учитывает срок годности по каждому SKU и вероятность потери ликвидности по причине истечения срока годности или появления аналогов на рынке.

Чтобы иллюстрировать практическую реализацию, можно использовать следующий подход: нормализация показателей по каждому SKU к диапазону [0,1], взвешенная сумма метрик образует итоговый неликвидный балл. Веса в сумме 1 отражают приоритеты бизнеса - например, более высокий вес можно дать промо-эффекту и сроку годности для скоропортящихся категорий.

Важно учитывать сезонность и промо-эффекты. Для корректной оценки неликвидности следует:

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

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

-- Пример расчета ключевых метрик для SKU за период P
## WITH daily AS (
  SELECT date_id, product_id, store_id, SUM(sold_qty) AS sold_qty,
         SUM(beginning_inventory) AS beg_inv, SUM(received) AS received, SUM(ending_inventory) AS end_inv
## FROM fact_sales_inventory
  WHERE date_id BETWEEN :start_date AND :end_date
  GROUP BY date_id, product_id, store_id
)
SELECT product_id, store_id,
       SUM(sold_qty) AS total_sold,
       SUM(beg_inv) AS total_beg_inv,
       SUM(received) AS total_received,
## SUM(end_inv) AS total_end_inv,
       (SUM(sold_qty) / NULLIF(AVG((beg_inv + end_inv) / 2), 0)) AS turn_over,
       (SUM(sold_qty) / NULLIF(SUM(beg_inv), 0)) AS sell_through
FROM daily
GROUP BY product_id, store_id;

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

Источники данных и интеграции

Эффективный анализ неликвидности требует консолидации разнообразных источников данных и качественной синхронизации. Основные источники включают:

POS-системы аптечной сети: продажи по SKU, цена, скидки и промо-метки. Это основа для расчетов продаж и оборачиваемости.
ERP/логистика: запасы по складам и магазинам, поступления, списания, годные к реализации запасы и просрочка.
Промо-истории: параметры промо-акций, календарь, эффекты на спрос, эластичность цены.
Данные о сроке годности и свойствах товаров: категория, бренд, размер, упаковка, регуляторные ограничения.
Внутренние регистры и внешние источники: сезонные эффекты, конкурентное окружение, регуляторные уведомления.

Ключевые принципы интеграции:

единый ключ SKU и единица измерения во всех источниках, согласование справочников (dim_product, dim_store);
хранение временных рядов с достаточной историей для анализа трендов и сезонности;
обеспечение качества данных через валидации на полноту, уникальность, целостность и непротиворечивость между источниками;
прозрачность источников и lineage: можно отслеживать, как именно данные попали в факт-sales и dim_product;
обеспечение безопасности и соответствия: контроль доступа к данным, анонимизация там, где это необходимо, и хранение регуляторного журнала изменений.

Рассматривая инструменты, можно опираться на практические решения:

dbt для управления трансформациями и тестирования моделей;
Apache Airflow или аналог для оркестрации и мониторинга пайплайнов;
движок анализа - выбор между традиционным DWH (PostgreSQL, Snowflake) и быстрыми аналитическими базами (ClickHouse, Redshift);
инструменты визуализации и дашбордов: Power BI, Tableau или открытые решения; при необходимости - интеграция с локальными витринами для оперативной аналитики.

Алгоритмы выявления неликвидных товаров

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

Пороговый подход: задаются пороги по совокупности метрик (например, sell-through < 20% в течение 8 недель и DOS > 120 дней). Порог может зависеть от категории и срока годности.
ABC/XYZ-анализ: классификация по доли продаж и устойчивости спроса. Категории A - высокие продажи и оборачиваемость, C - низкие продажи и непредсказуемость спроса.
Временные тренды: использование скользящих средних, сезонных индексов и анализа трендов для идентификации устойчивых неликвидных позиций, отличающихся от сезонных вариаций.
Мультифакторное скорирование: взвешенная сумма факторов** - продажа, запас, срок годности, влияние промоций, вариативность спроса и регуляторные ограничения. Формула может выглядеть как:

Score = w1 normalized_sell_through + w2 normalized_DOS + w3 obsolescence_potential + w4 promo_sensitivity + w5 * category_risk

где веса отражают бизнес-приоритеты для конкретной категории и региона.
Кластеризация: методы неупорядочного анализа (K-средних, иерархическая кластеризация) для выявления групп SKU с похожими паттернами спроса и запасов. Это помогает определить подобные стратегии для разных подмножеств ассортимента.
Анализ совместимости и зависимости: исследование принципа «заменяемых» SKU, чтобы понять, какие позиции можно заменить аналогами или объединить в ассорти для сокращения числа артикулов без снижения удовлетворенности клиентов.
Проверка чувствительности: моделирование сценариев «что если» - например, сокращение ассортимента на X% по определенным категориям и оценка влияния на маржу, доступность и удовлетворенность клиентов.

Практический подход к внедрению алгоритмов:

определить базовый набор метрик и порогов, валидируемых бизнес-подразделениям;
запускать пилотные расчеты на тестовой выборке SKU в ограниченном регионе;
формировать набор выводов и действий для управляющих ассортиментом с конкретными сроками;
внедрить обратную связь и обновлять весовые коэффициенты и пороги по мере накопления данных.
```
-- Пример псевдокода для вычисления Score по SKU
SELECT product_id, store_id,
       (0.3 * normalized_sell_through
        + 0.25 * normalized_DOS
        + 0.2 * obsolescence_potential
        + 0.15 * promo_sensitivity
        + 0.1 * category_risk) AS risk_score
## FROM sku_metrics_view
WHERE date_id BETWEEN :start_date AND :end_date;
```
Эти примеры ориентированы на интеграцию с существующими моделями оценки риска и позволяют строить управляемые процессы по сокращению ассортимента на основе данных.

Внедрение и контроль качества

Успешное внедрение требует систематического подхода к мониторингу, качеству данных и управлению изменениями. Важные элементы:

дашборды и сигналы: создание дашбордов по категориям и SKU, сигналы тревоги при резком росте DOS или падении sell-through, автоматические оповещения для ответственных менеджеров.
процедуры QA: регулярные проверки полноты и точности данных, тесты на консистентность между фактами продаж и запасами, мониторинг lineage и изменений в dim_product.
автоматизация процессов: планировщики обновлений, повторяемые регламенты по расчетам и публикации результатов, автоматический экспорт рекомендаций в системы планирования ассортимента.
тестирование изменений: A/B-тестирование изменений ассортимента в рамках пилотной зоны, сравнение ключевых показателей до и после внедрения.
управление рисками: контроль за воздействием сокращения ассортимента на доступность критических позиций, меры по снижению зависимости поставщиков и поддержанию регуляторных требований.

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

Key takeaways

Архитектура BI DWH для анализа ассортимента должна обеспечивать единое представление по SKU, магазинам и времени с четким lineage и качеством данных.
Метрики неликвидности и оборачиваемости требуют учета сезонности, промоций и срока годности, чтобы не ошибаться в оценках.
Алгоритмы выявления неликвидных товаров сочетают пороговые методы, кластеризацию и многокритериальные скоринговые модели, позволяющие получить управляемые рекомендации.
Внедрение должно сопровождаться пилотами, управлением изменениями и четкими индикаторами после изменений, чтобы минимизировать риски дефицита и регуляторных нарушений.
Интеграция источников данных и качество данных критичны: без надежной картины продаж и запасов любые рекомендации будут неустойчивыми.
Автоматизация, мониторинг и контроль качества обеспечивают устойчивость процесса и возможность масштабирования на всю сеть.
Взаимодействие между аналитикой и операционной командой ассортимента необходимо выстроить через прозрачные правила, регламенты и общие KPI.

FAQ

Что такое неликвидный товар в контексте аптек?

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

Какие период времени следует использовать для анализа?

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

Какие метрики наиболее эффективны при анализе неликвидности?

Ключевые метрики: sell-through, DOS (days of stock), turnover (оборачиваемость), obsolescence risk и category risk. Комбинация нормализованных метрик и взвешенного балла позволяет ранжировать SKU по риску неликвидности и определить целевые действия. Важно учитывать влияние промоций и срока годности, чтобы не искажать результаты.

Как учитывать сезонность и промо-акции?

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

Какую архитектуру выбрать для DWH и BI?

Подход зависит от объема данных и требуемой скорости аналитики. В типичной конфигурации можно использовать data lakehouse/ETL-слой с dbt, оркестрацию через Airflow, а для аналитики - DWH/аналитическую базу (PostgreSQL, ClickHouse, Snowflake). Важна прослеживаемость данных, масштабируемость и возможность интеграции с внешними источниками.

Какие алгоритмы наиболее полезны для выявления неликвидности?

Пороговые методы, ABC/XYZ-анализ, кластеризация SKU по паттернам спроса, анализ временных трендов и скоринговые модели. Комбинация методов позволяет не только выявлять неликвид, но и понять причины и определить корректирующие действия.

Как реализовать рекомендации по сокращению ассортимента?

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

Какие риски сопутствуют сокращению ассортимента и как их минимизировать?

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

Какую роль играет срок годности в анализе?

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

Как измерять эффект от изменений ассортимента?

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

Эта глава предлагает целостное решение: от архитектуры DWH и метрик до алгоритмов и организационных процессов, необходимых для рационализации ассортимента и повышения эффективности в сети аптек.

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

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

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

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