Маркетинговая аналитика - Анализ доли продаж товаров, участвующих в акциях
Маркетинговая аналитика в рамках BI DWH сети аптек нацелена на измерение эффективности промо-кампаний и оптимизацию ассортимента. В данной главе рассмотрены подходы к анализу доли продаж товаров, участвующих в акциях, с фокусом на архитектуру данных, модели данных, методики расчета и интеграции источников. Особое внимание уделено требованиям к качеству данных, скорости получения ответов и воспроизводимости расчетов в условиях большой ротации промо-акций и множества торговых точек.
Маркетинговые промо-акции влияют на поведение покупателя и структуру заказов, но реальная ценность анализа доли продаж акционных товаров проявляется в сравнении с безакционной частью ассортимента, в управлении маржей и в планировании акций на ближайшие периоды. В рамках BI DWH для сети аптек такие данные собираются из нескольких источников: POS-системы, система управления промо-кампаниями, календарь торговых точек, база товаров и ассортимент. Надежная связка этих источников в единой модели данных позволяет автоматически рассчитывать долю продаж акционных товаров, оценивать сезонные колебания, сравнивать результаты между регионами и сетями, а также проводить сценарный анализ влияния изменений условий промо.
Краткое содержание главы
- Архитектура данных и модели данных для анализа доли продаж акционных товаров.
- Метрики, расчеты и алгоритмы определения доли продаж по акции и по категориям.
- Интеграции источников данных, качество данных и управление данными в рамках промо-аналитики.
- Реализация пайплайна данных: ETL/ELT, инструменты, автоматизация и контроль качества.
- Визуализация результатов и примеры сценариев внедрения в процессы маркетинга и торговли.
Архитектура данных для анализа акций
Базовая архитектура строится вокруг понятного и устойчивого витка обработки данных: от сбора данных до расчетов и представления результатов конечному пользователю. В рамках анализа доли продаж акционных товаров целесообразно применять концепцию «звездной схемы» (star schema) с четко выделенными измерениями (dimensions) и фактом (fact). Это обеспечивает простую и быструю агрегацию по времени, магазину, товарной позиции и промо-акции.
Основные слои архитектуры:
- raw (сырые данные) - данные из источников без трансформаций;
- staging (временный слой) - никаких сложных вычислений, только нормализация ключей и базовая очистка;
- integration (интеграционный слой) - формирование согласованных размерностей и фактов;
- curated/semantic (семантический слой) - готовые бизнес-определения и агрегаты;
- mart/analytic (маркеты) - облегчают доступ аналитическим инструментам и BI-пользователям.
Ключевые размерности и факторная таблица:
- dim_product: product_id, sku, category, brand;
- dim_store: store_id, region, city, chain;
- dim_calendar: date_key, year, quarter, month, week, day_of_week;
- dim_promo: promo_id, promo_name, promo_type, start_date, end_date, discount_pct;
- fact_sales: date_key, store_id, product_id, promo_id, revenue, units_sold, promoted_units, promoted_revenue.
В данной модели важно определить место фокуса на промо-данных в факте продаж. Если в продажах присутствуют не только промо-товары, имеет смысл хранить значения promo_id (NULL, когда товар не участвует в акции) и при необходимости хранить отдельно поля promoted_units и promoted_revenue для ускорения вычислений промо-показателей. Это позволяет быстро рассчитывать долю продаж акционных товаров без повторной фильтрации по полю promo_id.
%%PRE_BLOCK0%%В контексте архитектуры целесообразно внедрить согласованный процесс загрузки и проверки ключей. Ключи dim и fact_ должны совпадать по стандарту (например, surrogate keys для dim, датируемые date_key в формате YYYYMMDD для календаря). Эффективность операций достигается за счет денормализации кросс-табличных зависимостей и использования агрегированных предикатов на уровне presentation-марета.
Модели данных и схемы
В практике BI DWH для розничной сети аптек выбор между звездной и снежной схемой зависит от объема данных и требований к скорости запросов. Звездная схема обеспечивает простые SQL-запросы и быстроту агрегаций, что критично для анализа маркетинговых кампаний с частыми итерациями. Снежная схема применяется, когда требуется экономия пространства и повышение нормализации, например для детального отслеживания иерархий в товарной группе или промо-типах.
Чтобы обеспечить полноту анализа, полезно иметь следующие агрегаты и представления:
- промо-витрина по магазинам и периодам: суммарная выручка и количество продаж по каждому promo_id;
- морфология акций: распределение по promo_type и по category;
- совместные акции: комбинации promo_id и category, если промо распространяется на несколько категорий;
- нормализованные показатели: промо-доля продаж по общей выручке, доляUnits, маржа по промо-сегментам.
Ниже приведён пример запроса, который демонстрирует типичную агрегацию для анализа промо-эффекта на уровне магазина и периода.
SELECT c.year, c.month, p.promo_type, d.category, ## SUM(f.revenue) AS promo_revenue, SUM(CASE WHEN f.promo_id IS NOT NULL THEN f.units_sold ELSE 0 END) AS promo_units, SUM(f.revenue) AS total_revenue ## FROM fact_sales f JOIN dim_calendar c ON f.date_key = c.date_key JOIN dim_promo p ON f.promo_id = p.promo_id JOIN dim_product d ON f.product_id = d.product_id GROUP BY c.year, c.month, p.promo_type, d.category ORDER BY c.year, c.month, p.promo_type, d.category;
Особое значение имеет корректная трактовка grain-уровня. Для анализа доли продаж акционных товаров достаточно выбирать зерно по date_key, store_id, product_id, promo_id; это позволяет разделять продажи по конкретному промо-объекту и сверять их с общим объёмом продаж.
Расчёт доли продаж акционных товаров
Основной KPI - доля продаж акционных товаров в общей выручке (Promo Revenue Share, PRS). В простейшей форме PRS для магазина и периода равна отношению выручки по промо-товарам к общей выручке магазина за период.
Дополнительно полезны:
- доля промо-единиц: promo_units / total_units;
- доля по категориям: promo_revenue_by_category / total_revenue_by_category;
- динамика по времени: PRS по неделям/месяцам с трендами.
Расчеты следует проводить с учётом качества данных и дисциплины обработки свечей продаж. В реальном мире часто встречаются пропуски в promo_id (товары не участвуют в акции) и задержки загрузки источников. Использование безопасных выражений (NULLIF, COALESCE) и проверок на валидность ключей снижает риск ошибок.
Ниже пример запросов, иллюстрирующих расчёты.
-- 1) Доля продаж акционных товаров по магазинам за период
WITH period AS (
SELECT
s.store_id,
DATE_TRUNC('month', c.date_key) AS period_start,
## SUM(f.revenue) AS total_revenue,
SUM(CASE WHEN f.promo_id IS NOT NULL THEN f.revenue ELSE 0 END) AS promo_revenue
## FROM fact_sales f
JOIN dim_calendar c ON f.date_key = c.date_key
WHERE c.date_key >= DATE '2025-01-01' AND c.date_key
-- 2) Распределение доли по категориям для определенного периода
WITH by_cat AS (
SELECT
d.category,
## SUM(f.revenue) AS total_rev,
SUM(CASE WHEN f.promo_id IS NOT NULL THEN f.revenue END) AS promo_rev
## FROM fact_sales f
JOIN dim_product d ON f.product_id = d.product_id
JOIN dim_calendar c ON f.date_key = c.date_key
WHERE c.date_key BETWEEN DATE '2025-03-01' AND DATE '2025-03-31'
GROUP BY d.category
)
SELECT
category,
promo_rev / NULLIF(total_rev, 0) AS promo_share_by_category
FROM by_cat
ORDER BY category;
Важно помнить, что при расчете коэффициентов следует обрабатывать нулевые значения и пропуски. Пример с использованием NULLIF и COALESCE помогает избежать деления на ноль и обеспечивает более надёжные результаты в случаях отсутствия промо-данных. Также полезно строить «погрешности» доверительных интервалов для сравнения эффективности промо между магазинами и временными окнами, если данные достаточно стабильны по объему.
Интеграции источников данных и качество данных
Глубокая интеграция внешних и внутренних источников данных требует согласованности моделей и процессов.
- Источники: POS-системы (продажи и возвраты), маркетинговые системы (информация об акциях, условия промо), управляющие системы товарной базой (категории, бренды), календарь торговых точек (гибкость графика рабочих дней, праздники), данные о скидках и ценах в рамках акций.
- Процессы загрузки: этапы ETL/ELT должны быть повторяемыми и документированными. Важно обеспечить согласование идентификаторов (product_id, store_id, promo_id, date_key) между системами и хранение истории изменений.
- Контроль качества: проверки целостности ключей (кросс-референс между dim и fact), контроль дубликатов, проверка суммарной выручки по источникам, сравнение периодических агрегатов с внешними отчётами.
- Линейность данных: необходимо фиксировать источник и время загрузки, чтобы иметь реплики «как было» в каждый момент времени, особенно когда дополнительные промо-атрибуты (типы промо, условия) меняются в рамках кампаний.
- Метаданные и трассируемость: метаданные моделей, версии схемы и датчики качества должны быть доступны бизнес-пользователю через репозитории метаданных и документацию.
Примеры задач контроля качества:
- сравнение суммарной выручки по периоду между фактом продаж и источниками ERP/финансов;
- проверка соответствия числа промо-вызовов в dim_promo и фактовых записей promo_id;
- мониторинг задержек загрузки: сколько времени прошло с момента окончания акции до загрузки фактов продаж.
Реализация в пайплайне BI DWH
Реализация данной аналитики требует устойчивого пайплайна: сбор данных, очистка, согласование ключей, вычисления и публикация результатов. В типовом стеке для российских и глобальных решений часто встречаются:
- источники: ERP/POS-экспорт, промо-системы;
- инструмент трансформации: dbt (для трансформаций и декларативного описания моделей), SQL-агрегации;
- оркестрация: Apache Airflow, Dagster или аналогичные решения;
- хранилище: Snowflake, BigQuery, Redshift, ClickHouse (в зависимости от инфраструктуры);
- визуализация: Power BI, Tableau, Looker.
Ключевые практики:
- держать бизнес-определения в dbt-моделях и обеспечивать совместную работу аналитиков и инженеров над единой версией бизнес-логики;
- использовать incremental-модели там, где грубая базовая агрегация становится дорогой по времени;
- строить повторяемые тесты (модельные тесты dbt, внешние тесты на качество данных) и регламентировать частоту обновления данных.
Пример упрощённого dbt-подхода для подготовки фактов и размерностей:
-- dbt model: dim_product.sql
select
product_id,
sku,
category,
brand
from {{ ref('stg_product') }}
where product_id is not null;
-- dbt model: dim_store.sql
select
store_id,
region,
city,
chain
from {{ ref('stg_store') }}
where store_id is not null;
-- dbt model: dim_calendar.sql
select
date_key,
year,
quarter,
month,
week
from {{ ref('stg_calendar') }}
where date_key is not null;
-- dbt model: dim_promo.sql
select
promo_id,
promo_name,
promo_type,
start_date,
end_date,
discount_pct
from {{ ref('stg_promo') }}
where promo_id is not null;
-- dbt model: fact_sales.sql
select
s.date_key,
s.store_id,
s.product_id,
s.promo_id,
s.revenue,
s.units_sold
from {{ ref('stg_sales') }} s
where s.date_key is not null;
Пример инкрементальной модели фактSales для промо-аналитики:
-- dbt model: facts_sales_incremental.sql
{{ config(
materialized = 'incremental',
unique_key = 'date_key || store_id || product_id || promo_id'
) }}
select
date_key,
store_id,
product_id,
promo_id,
revenue,
units_sold
from {{ ref('stg_sales') }}
where date_key > (select max(date_key) from {{ this }});
Эти примеры демонстрируют, как структурировать трансформации так, чтобы обеспечивать воспроизводимость и управляемость анализа. Внедрение графа зависимостей dbt упрощает диагностику ошибок и ускоряет внедрение изменений в модели данных в случае обновления источников промо-данных.
Метрики и визуализация
После расчета доли продаж акционных товаров рекомендуется представить данные через несколько видов визуализации:
- тепловые карты по магазинам и месяцам с долей промо-продаж;
- линейные графики по времени для PRS и promo_units;
- столбчатые графики по категориям для анализа влияния промо на конкретные группы товаров;
- дашборды по региону и сети, для сравнения эффективности промо-кампаний.
Необходимо обеспечить, что визуализации работают на обновленных данных и позволяют бизнесу оперативно реагировать на изменения в промо-политике.
Key takeaways
- Эффективный анализ доли продаж акционных товаров требует четкой архитектуры данных и согласованных ключей между источниками и фактом.
- Звездная схема упрощает доступ к данным и ускоряет агрегации для промо-аналитики, но следует соблюдать баланс нормализации там, где это критично.
- Основной KPI - доля продаж акционных товаров (promo_revenue_share) измеряет влияние промо на общую выручку и должен сопровождаться метриками по единицам и категориям.
- Важна интеграция источников данных и обеспечение качества данных: целостность ключей, согласование периодов и прозрачность изменений в промо-атрибутике.
- Пайплайн должен быть воспроизводимым: использование dbt для трансформаций, Airflow/ Dagster для оркестрации и детальная документация бизнес-логики.
- Примеры SQL-метрик следует строить так, чтобы учитывать нулевые и пропущенные значения, применяя NULLIF и COALESCE.
- Визуализация должна предоставлять не только текущие значения, но и динамику по времени и региональные различия, поддерживая сценарный анализ.
FAQ
- Какие основные показатели учитывать при анализе доли продаж акционных товаров?
- Включайте долю promo_revenue (выручка по промо-товарам) от общей выручки, долю promo_units (единиц товара в акциях) от общего объема продаж, а также распределение по категориям и по магазинам. Дополнительно полезны показатели маржинальности и влияния промо на повторные покупки.
- Как учесть возвраты и скидки в расчете доли?
- Возвраты и скидки следует учитывать в соответствующих полях фактов продаж (revenue с учетом возвратов) или отдельно в полях, отражающих влияние акции. Если применимо, можно хранить is_return и корректировать выручку. В промо-аналитике важно явно разграничить продажи по акции и обычные продажи, чтобы не искажать долю.
- Как обеспечить сопоставимость данных между источниками?
- Требуется единая модель размерностей (dim_product, dim_store, dim_calendar, dim_promo) и единый ключ date_key. Рекомендуются регламентированные процедуры сопоставления идентификаторов и версионирование схем.
- Какие инструменты выбрать для внедрения пайплайна?
- Типично: dbt для трансформаций, Apache Airflow или Dagster для оркестрации, Snowflake/BigQuery/Redshift в качестве хранилища. В бюджете и инфраструктуре можно рассмотреть локальные аналоги или гибридные решения.
- Как ускорить расчеты на больших объемах данных?
- Используйте инкрементальные модели в dbt, материализованные представления, агрегации на уровне marts и параллелизацию задач в оркестраторе. Разделение по регионам и магазинам позволяет распараллеливать вычисления.
- Какие риски существуют при промо-аналитике и как их минимизировать?
- Риск несогласованных данных между источниками, задержки загрузки и изменение структуры источников. Минимизируйте риск через детальные тесты качества данных, контракт между системами и автоматизированные уведомления об отклонениях.
- Как интерпретировать результаты анализа для оперативной маркетинговой деятельности?
- Результаты следует связывать с бизнес-терминами: какая доля продаж приходится на акции в каждом регионе и магазине, какие категории эффективнее в промо, как промо влияет на общую маржу и повторные покупки. Рекомендации могут включать корректировки ассортимента и параметров акций на ближайшие периоды.
- Какие ограничения следует учесть при расчете доли?
- Ограничения связаны с задержками в данных, пропусками по promo_id, различиями в локализации акций и временными окнами. Важно поддерживать консистентность измерений и ясно обозначать период и географию анализа.
- Как можно расширить анализ за долю продаж акционных товаров?
- Можно добавить анализ влияния промо на среднюю стоимость продажи, маржу по акции, динамику запасов при акциях, а также сравнительный анализ эффективности разных форматов акций (скидка на агрегированную сумму vs. скидка на единицу).
- Какие шаги предпринять после внедрения аналитики?
- Автоматизировать обновление дашбордов, включить триггеры на изменение промо-правил в маркетинговой системе, проводить ежемесячные и ежеквартальные ревизии моделей данных, поддерживать документированную версию бизнес-логики и регулярно обновлять планы по качеству данных и тестам.
