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

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

Краткое введение

Вторичные продажи представляют собой часть оборота, которая уже произошла на уровне розничной реализации, в отличие от первичной дистрибуции через торговые каналы. Анализ по SKU позволяет увидеть, какие товары наиболее часто покупаются конечным потребителем, какие товарные позиции быстро «оборачиваются», а какие требуют корректировок в ассортименте, ценообразовании или маркетинговых акциях. Эффективная аналитика требует тесной интеграции данных из ERP, POS и систем управления запасами, согласования понятий «продажа», «остаток» и «потребление» и целостной модели данных, позволяющей сравнивать период за периодом и проводить прогнозирование.

Далее приводится подробное раскрытие темы: архитектура и данные, метрики и алгоритмы, реализация в BI DWH, особенности масштабирования и интеграции, практики внедрения и устойчивости решения.

Краткое содержание главы
Архитектура данных и модель данных для анализа SKU
Метрики оборачиваемости, лидеры и риски
Алгоритмы идентификации лидеров и необорачиваемых SKU
Реализация ETL/ELT, инструменты и рекомендации по производительности
Внедрение, governance и управление качеством данных
Примеры сценариев внедрения и практические выводы

Архитектура данных и модель данных для анализа SKU

Для анализа вторичных продаж по SKU в рамках BI DWH целесообразно применять звездную архитектуру данных. Фактовая часть хранит измерения и показатели, связанные с продажами по SKU за конкретный период в разрезе магазинов, каналов сбыта и дат. Измерения (dims) поддерживают контекст: идентификатор SKU, дата продажи, магазин, канал продаж, категория товара и т. д. Такая структура обеспечивает гибкость отчетности, возможность агрегаций на разных уровнях и ускорение аналитических запросов.

Типовая схема:

Факты:
- fact_sales: quantity_sold, revenue, discount_amount, cost_of_goods_sold, units_returns, date_key, sku_id, store_id, channel_id
- fact_inventory_daily: date_key, sku_id, store_id, on_hand_units, on_hand_value
Измерения (Dims):
- dim_date: date_key, date, year, quarter, month, week_of_year
- dim_sku: sku_id, sku_code, product_id, brand, category_id, assortment_flag, price
- dim_store: store_id, store_code, region_id, store_type
- dim_channel: channel_id, channel_name
- dim_product_category: category_id, category_name
- dim_brand: brand_id, brand_name

Привязка к источникам: данные поступают из ERP (первичная поставка), POS-систем (фактические продажи), WMS/OMS (остатки, движение запасов) и, при необходимости, онлайн-торговая платформа. Необходимо обеспечить согласование ключей и справочных данных через мастер-данные (MDM), а также единые концепции дат и валют.

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

-- Пример упрощенной DDL-структуры звездной схемы

CREATE TABLE dim_date (
  date_key INT PRIMARY KEY,
  date DATE,
  year INT,
  quarter INT,
  month INT,
  week INT
);

CREATE TABLE dim_sku (
  sku_id INT PRIMARY KEY,
  sku_code VARCHAR(50),
  product_id INT,
  brand VARCHAR(100),
  category_id INT,
  assortment_flag BOOLEAN
);

CREATE TABLE dim_store (
  store_id INT PRIMARY KEY,
  store_code VARCHAR(50),
  region_id INT,
  store_type VARCHAR(50)
);

CREATE TABLE dim_channel (
  channel_id INT PRIMARY KEY,
  channel_name VARCHAR(100)
);

CREATE TABLE fact_sales (
  sale_id BIGINT PRIMARY KEY,
  date_key INT,
  sku_id INT,
  store_id INT,
  channel_id INT,
  quantity_sold INT,
  revenue DECIMAL(18,2),
  cost_of_goods_sold DECIMAL(18,2),
  discount_amount DECIMAL(18,2),
## FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
## FOREIGN KEY (sku_id) REFERENCES dim_sku(sku_id),
## FOREIGN KEY (store_id) REFERENCES dim_store(store_id),
  FOREIGN KEY (channel_id) REFERENCES dim_channel(channel_id)
);

CREATE TABLE fact_inventory_daily (
  record_id BIGINT PRIMARY KEY,
  date_key INT,
  sku_id INT,
  store_id INT,
  on_hand_units INT,
  on_hand_value DECIMAL(18,2),
## FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
## FOREIGN KEY (sku_id) REFERENCES dim_sku(sku_id),
  FOREIGN KEY (store_id) REFERENCES dim_store(store_id)
);

Эта базовая схема обеспечивает:

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

Стратегия интеграции данных должна учитывать:

согласование ключей между системами (SKU коду, store_id, date_key);
обработку поздно поступающих данных (late arriving data);
нормализацию единиц измерения (валюта, масштаб продаж);
качество справочников (категории, бренды, каналы).

Для обеспечения качества данных полезно внедрить слои обработки данных: staging, cleansing, conformity и mart, с автоматическими проверками целостности и консистентности на каждом слое.

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

Ключевые показатели для анализа SKU в контексте вторичных продаж включают следующие элементы:

Оборачиваемость SKU (inventory turnover):
- варианты расчета:
  - по объему продаж: units_sold в период / среднее количество единиц в запасе за период
  - по стоимости запасов: COGS в период / средняя стоимость запасов за период
Средние запасы (average inventory) за период:
- может рассчитываться как среднее арифметическое на начало и конец периода в розничном канале, либо как агрегат по дневной инвентаризации.
DIO (days inventory outstanding) или days of inventory:
- DIO = 365 / turnover_rate
Sell-through rate по SKU:
- sell_through = units_sold / (units_sold + units_remaining_end_period)
- отражает долю проданного объема относительно доступного на период запасов
Выручка и маржинальность по SKU:
- revenue_per_sku, margin_per_sku
Рейтинг лидеров:
- топ-N SKU по выручке или по объему продаж за период
- устойчивость лидерства: рейтинг по нескольким периодам и скорректированные по сезонности
Риск низкой оборачиваемости:
- доля SKU с turnover ниже порога
- рост складских запасов по данным SKU
- временная корреляция с сезонностью, промо-акциями и изменениями спроса

Формулы для расчета на уровне SKU в рамках периода (пример):

turnover_units = SUM(quantity_sold)
average_inventory_units = AVG(on_hand_units)
turnover_rate_units = turnover_units / NULLIF(average_inventory_units, 0)
DIO_units = 365.0 / NULLIF(turnover_rate_units, 0)
turnover_revenue = SUM(revenue)
average_inventory_value = AVG(on_hand_value)
turnover_rate_value = turnover_revenue / NULLIF(average_inventory_value, 0)
sell_through = turnover_units / NULLIF((turnover_units + SUM(on_hand_units) - SUM(quantity_sold)), 0)

Пояснения по интерпретации:

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

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

-- Пример SQL-запроса на расчёт основных метрик по SKU за выбранный период
WITH period_sales AS (
  SELECT
    s.sku_id,
    SUM(fs.quantity_sold) AS units_sold,
    SUM(fs.revenue) AS revenue
## FROM fact_sales fs
  JOIN dim_date d ON fs.date_key = d.date_key
  WHERE d.date BETWEEN '2025-01-01' AND '2025-01-31'
  GROUP BY s.sku_id
),
period_inventory AS (
  SELECT
    si.sku_id,
## AVG(si.on_hand_units) AS avg_inventory_units,
    AVG(si.on_hand_value) AS avg_inventory_value
## FROM fact_inventory_daily si
  JOIN dim_date d ON si.date_key = d.date_key
  WHERE d.date BETWEEN '2025-01-01' AND '2025-01-31'
  GROUP BY si.sku_id
)
SELECT
  ps.sku_id,
  ps.units_sold,
  ps.revenue,
  pi.avg_inventory_units,
  pi.avg_inventory_value,
  (ps.units_sold / NULLIF(pi.avg_inventory_units, 0)) AS turnover_rate_units,
  (ps.revenue / NULLIF(pi.avg_inventory_value, 0)) AS turnover_rate_value,
  365.0 / NULLIF((ps.units_sold / NULLIF(pi.avg_inventory_units, 0)), 0) AS days_inventory
## FROM period_sales ps
LEFT JOIN period_inventory pi ON ps.sku_id = pi.sku_id
ORDER BY ps.revenue DESC
LIMIT 100;

Алгоритмы расчета и отбора лидеров:

ранжирование SKU по нескольким критериям (например, выручка, объем продаж, маржа) с последующим агрегированием по фильтрам (канал, регион, категория);
использование скользящих средних и экспоненциального сглаживания для устранения кратковременных шумов;
учет сезонности через сравнение с аналогичным периодом прошлого года (YoY) и сезонные индексы;
кластеризация SKU по поведенческим признакам (оборачиваемость, маржа, чувствительность к акциям) для планирования ассортиментной политики;
пороговые правила для уведомлений: например, если turnover_rate_units падает ниже заданного порога в течение двух последовательных периодов, создаётся предупреждение.

Алгоритмы идентификации лидеров и необорачиваемых SKU

Идентификация лидеров и SKU с низкой оборачиваемостью требует сочетания точности и устойчивости к сезонности. Приведенные подходы можно реализовать в рамках ETL/ELT слоёв и на стадии презентации данных в BI-слое.

Многопериодное рейтинговое ранжирование:
1. Рассчитать по каждому SKU показатели за несколько периодов (например, 3-6 последних месяцев).
2. Привязать веса к каждому периоду в зависимости от сезонности.
3. Присвоить итоговый ранг по совокупности метрик (выручка, объем продаж, маржа, оборачиваемость).
Рейтинги по устойчивости лидеров:
- определить SKU, которые занимают топ-N в каждом из нескольких периодов подряд.
- идентифицировать SKU, сохраняющие лидерство в разных регионах/каналах.
Анти-оборачиваемость и риски устаревания:
- выделение SKU с низким turnover_rate и высоким запасом на складе.
- анализ влияния промо-акций и ценовых изменений на их оборот.
Кластеризация:
- применить k-средних или иерархическую кластеризацию на основании признаков: turnover_rate, маржа, конверсия в продажу, цена за единицу.
- формировать профили SKU и рецепты действий (например, для группы товаров с низкой оборачиваемостью - переработать ассортимент, пересмотреть полку, запланировать рекламную акцию).

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

Реализация: от данных до отчетов

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

Инфраструктура и архитектура:
- выбор OLAP-движка: по потребности - PostgreSQL для менее нагруженных сценариев, ClickHouse или Apache Pinot для больших объемов и реального времени.
- оркестрация ETL/ELT: Apache Airflow, Prefect или аналогичные решения для планирования загрузок и контроля зависимостей.
- хранение и агрегирование: база данных-«хранилище» для фактов продаж и инвентаря, отдельный слой mart для агрегатов по SKU.
Нормализация и качество данных:
- единые справочники SKU, магазинов, каналов;
- обработка пропусков и аномалий в источниках (например, пропуски продаж в праздничные дни).

Пример кода: расчеты и подготовка агрегатов

-- Пример сохранения итогов по SKU за период в mart для последующей аналитики
CREATE MATERIALIZED VIEW mv_sku_performance AS
SELECT
  fs.date_key,
  fs.sku_id,
  SUM(fs.quantity_sold) AS units_sold,
## SUM(fs.revenue) AS revenue,
## AVG(ii.on_hand_units) AS avg_inventory_units,
  AVG(ii.on_hand_value) AS avg_inventory_value
## FROM fact_sales fs
JOIN fact_inventory_daily ii ON fs.sku_id = ii.sku_id AND fs.store_id = ii.store_id AND fs.date_key = ii.date_key
GROUP BY fs.date_key, fs.sku_id;

Возможности аналитики и отчеты:
- дашборды в BI-системах (например, Power BI, Tableau, или отечественные решения) с интерактивными фильтрами по периоду, каналу, региону и категории;
- предиктивная аналитика: прогноз спроса по SKU на основе исторических трендов и сезонности;
- оповещения и автоматические рекомендации по ассортиментной политике.
Интеграции и протоколы:
- обеспечение согласованности ключей: SKU, Store, Date и Channel должны соответствовать внешним системам;
- протоколы обмена данными: ETL/ELT-процессы, поддержка incremental-load, обработка ошибок и повторные загрузки;
- данные об источниках и lineage: хранение метаданных о происхождении данных и изменениях схемы.
Производительность и масштабируемость:
- горизонтальное масштабирование хранилища; колоночные форматы для учета объемов;
- разбиение по времени (partitioning) в фактах и денормализация в мердж-слое;
- индексы по sku_id, date_key и store_id для ускорения запросов;
- кэширование часто используемых агрегатов и результатов вычислений.
Безопасность и соответствие:
- разграничение доступа на уровне ролей и проектов;
- аудит изменений и журналирование запросов к конфиденциальной информации;
- соблюдение требований локализации и приватности данных.

Сценарии внедрения и практические выводы

Внедрение в крупной розничной сети:
- создание единого источника правды по SKU, единых справочников и согласованных ключей;
- внедрение оперативной витрины, на которой бизнес видит топ-SKU в разрезе региона и канала;
- регулярное обновление данных и прозрачные SLA на загрузку.
Вендорский ритейл с omnichannel:
- интеграция оффлайн и онлайн продаж, учет разных источников спроса;
- применение единых метрик и порогов для определения лидеров и зон риска.
Примеры сценариев:
- запуск акции на топ-SKU в определенном регионе; мониторинг влияния акции на turnover и sell-through;
- перераспределение стоков по регионам на основе анализа оборачиваемости SKU;
- прорыв в ассортименте на основе комбинации лидеров и ростовых возможностей в категориях.

Производительность и устойчивость

Чтобы обеспечить устойчивую работу при больших объемах данных, необходимо:

правильно выбрать движок OLAP: ClickHouse для больших потоков и реального времени, PostgreSQL/Greenplum для традиционных решений;
обеспечить быстрые загрузки с минимальным временем задержки: incremental-load, change data capture (CDC);
защищать отчетность от перегрузок: кэширование, агрегации на уровне mart, ограничение по объему выборок в отчетах;
обеспечить мониторинг и алерты на качество данных и производительность запросов.

Безопасность и соответствие

Внедрение решений для анализа SKU должно сопровождаться:

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

Key takeaways

Анализ вторичных продаж по SKU требует интегрированной архитектуры и согласованных ключей между источниками данных.
Основные метрики: оборачиваемость по SKU (unit-level и value-level), sell-through, DIO, выручка и маржа по SKU; лидерство оценивается по устойчивым топ-позициям в периодах.
Эффективная реализация строится на звездной схеме, ELT-процессах, инкрементальных загрузках и продвинутых агрегированных слоях mart.
Алгоритмы идентификации лидеров и SKU с низкой оборачиваемостью должны учитывать сезонность, промо-эффекты и региональные различия.
Важны качество данных, мастер-данные и lineage, чтобы обеспечить доверие к аналитическим выводам и управлению ассортиментом.
Применение современных технологий OLAP-движков и инструментов визуализации позволяет быстро получать управляемые инсайты для принятия решений по ассортиментной политике.
Внедрение требует согласованности процессов, governance и контроля изменений на протяжении всего цикла жизненного цикла данных.

FAQ

Что такое вторичные продажи и почему они важны для анализа SKU?

Вторичные продажи - это реализация товаров через розничные каналы и в рамках существующей сети поставок. Анализ по SKU в этом контексте позволяет выявлять лидирующие позиции ассортимента, оценивать эффективность запасов и планировать маркетинговые активности. В развёрнутом DW-подходе эти данные позволяют управлять ассортиментом и запасами, снижать риск устаревших товаров и оптимизировать маркетинг.

Какие данные необходимы для анализа по SKU?

Необходимо собрать данные продаж (quantity_sold, revenue), данные об инвентаре (on_hand_units, on_hand_value), справочные данные по SKU (sku_id, category, brand), магазин/регион, канал продаж и календарь дат. Дополнительно полезны промо-данные и маржинальные показатели.

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

Полезны: turnover_rate_units, turnover_rate_value, sell_through, average_inventory, DIO, revenue_per_sku, margin_per_sku. Для лидеров полезно ранжирование по сумме выручки и объему продаж за период; для необорачиваемых - доля SKU с turnover below порог и рост запасов по SKU.

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

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

Какую архитектуру выбрать для DW и как связать источники данных?

Рекомендована звездная архитектура с fact_sales и fact_inventory_daily в качестве фактов и набором dimensions (dim_date, dim_sku, dim_store, dim_channel, dim_product_category). Необходимо обеспечить единые ключи и мастер-данные (MDM) для SKU, магазинов и каналов, а также процессы линейности данных (data lineage).

Какие примеры SQL-выражений полезно держать в арсенале?

Примеры расчета turnover и sell-through, агрегации по SKU за период, создание материаловиданных представлений (MV) для ускорения запросов. См. приведённые в главе примеры SQL-выражений и DDL.

Как организовать ETL/ELT-процессы для стабильности и скорости обновления?

Важно обеспечить инкрементальные загрузки, контроль ошибок, обработку поздно поступающих данных и повторные загрузки. Рекомендуются отдельные слои: staging, cleansing, conformity и mart, с мониторингом и SLA на обновления.

Какие инструменты подходят для реализации?

В качестве OLAP-движка можно рассмотреть ClickHouse или Pinot для больших объемов и быстрого анализа, PostgreSQL/Greenplum для менее нагруженных сценариев. Для оркестрации - Apache Airflow или аналог; для визуализации - бизнес-аналитические платформы (Tableau, Power BI) или локальные аналоги. open-source примеры: Yandex ClickHouse как популярный выбор в РФ и Apache Pinot для реального времени.

Какие риски стоит учитывать при внедрении анализа SKU?

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

Как обеспечить устойчивость и масштабируемость решения?

Внедрять гибкую схему данных, разделяемые слои mart и индексированные таблицы; использовать партитионирование по времени; выбирать подходящие движки для нужд реального времени и больших объемов; обеспечивать мониторинг производительности и качества данных; поддерживать документированность lineage и governance-процессов.

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

Анализ вторичных продаж - анализ продаж по форматам торговых точек для оценки эффективности различных форматов

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

Анализ вторичных продаж - анализ доли вторичных продаж в общем обороте компании