SQL: 3 задачи по анализу торгового пространства для ритейла

В ритейле каждый сантиметр полки – это деньги (буквально) и от того, насколько эффективно используется торговое пространство, зависит:
– Выручка – товары на видных местах продаются лучше;
– Прибыль – маржинальные позиции должны занимать лучшие места;
– Удовлетворенность клиентов – если товар не на своем месте или отсутствует, покупатель уходит;
– Отношения с поставщиками – они платят за определенное количество "лиц" товара.

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

Задача 1. Эффективность использования полочного пространства (ROSP)

Представьте, что вы управляете супермаркетом и у вас есть 100 метров полок, и на них нужно разместить тысячи товаров.
Вопрос: как понять, какие товары достойны лучшего места, а какие стоит ужать или убрать совсем?

Метрика ROSP (Return on Shelf Space) отвечает на этот вопрос, она показывает, сколько выручки приносит каждый метр полки, это аналог более известной метрик ROI, но для торгового пространства.

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

Математика метрики

ROSP=Выручка за период/Занимаемое место на полке (в метрах)
Занимаемое место считается как: Ширина товара (см) * Количество единиц товара /100

Пример кода на SQL:

WITH product_shelf_metrics AS (
    SELECT 
        p.product_id,
        p.product_name,
        p.category,
        (p.width * p.facing_count / 100.0) AS shelf_meters,
        SUM(s.amount) AS monthly_revenue
    FROM products p
    JOIN sales s ON p.product_id = s.product_id
    WHERE s.sale_date BETWEEN '2026-02-01' AND '2026-02-28'
    GROUP BY p.product_id, p.product_name, p.category, p.width, p.facing_count
)
SELECT 
    product_name,
    category,
    shelf_meters,
    monthly_revenue,
    ROUND(monthly_revenue / shelf_meters, 2) AS revenue_per_meter,
    ROUND(AVG(monthly_revenue / shelf_meters) OVER(PARTITION BY category), 2) AS avg_category_rosp,
    ROUND(100.0 * (monthly_revenue / shelf_meters) / AVG(monthly_revenue / shelf_meters) OVER(PARTITION BY category) - 100, 2) AS vs_category_avg_percent
FROM product_shelf_metrics
ORDER BY revenue_per_meter DESC;

Результат выполенния запроса:

product_name	category	shelf_meters	monthly_revenue	revenue_per_meter	avg_category_rosp	vs_category_avg
Шоколад Аленка	Кондитерка	0.20	810.00	4050.00	4050.00	0.00
Сок Добрый 1л	Напитки	0.21	1680.00	8000.00	8000.00	0.00
Молоко Простоквашино 1л	Молочка	0.20	2700.00	13500.00	13500.00	0.00
Пельмени Цезарь 500г	Заморозка	0.60	1620.00	2700.00	2700.00	0.00
Майонез Слобода 400г	Соусы	0.16	770.00	4812.50	4812.50	0.00
Хлеб Бородинский	Хлеб	0.15	810.00	5400.00	5400.00	0.00

Пример вывода по полученным данным: Молоко показывает самую высокую выручку на метр полки 13 500 руб, пельмени при всей популярности занимают много места (0,6 м) и дают только 2700 руб/м. Молоку можно выделить больше места на полке (если спрос позволяет), пельмени – требуют дополнительного анализа: может, дело в цене? В сезонности? Или действительно стоит ужать? Вообще, в идеале для пельменей нужно считать не только выручку, но и прибыль (возможно, маржа выше).

Задача 2. Анализ доступности товаров на полке (OSA)

Одна из главных проблем ритейла – это out-of-shelf (OOS), когда товар есть на складе, числится в системе, но на полке его нет и клиент не находит товар и уходит к конкуренту, возникают потери.

Метрика OSA (On-Shelf Availability) показывает, насколько товар доступен покупателю и в идеальном мире она должна быть 100%, но в реальности часто проседает, важно знать насколько.

Почему товар может отсутствовать на полке:
– Не успели выложить из подсобки
– Поставили не на то место
– Сбой в планограмме
– Кража (да, такое тоже бывает)

Как диагностировать OSA по данным

У нас нет прямых данных "есть товар на полке / нет товара". Но мы можем косвенно определить проблемы через такие маркеры, как, например:
– Резкое падение продаж при стабильном среднем
– Нулевые продажи несколько дней подряд при том, что раньше продажи были
– Аномалии в динамике

Пример кода на SQL:

WITH daily_sales_pattern AS (
    -- Собираем ежедневную статистику по продажам
    SELECT 
        store_id,
        product_id,
        sale_date,
        SUM(quantity) AS daily_qty,
        CASE WHEN SUM(quantity) > 0 THEN 1 ELSE 0 END AS was_sold
    FROM sales
    GROUP BY store_id, product_id, sale_date
),
stockout_candidates AS (
    -- Считаем скользящие метрики для поиска аномалий
    SELECT 
        sp.store_id,
        sp.product_id,
        sp.sale_date,
        sp.daily_qty,
        -- Были ли продажи в последние 7 дней (исключая сегодня)
        SUM(sp.was_sold) OVER(PARTITION BY sp.store_id, sp.product_id 
                               ORDER BY sp.sale_date 
                               ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS sales_last_7_days,
        -- Среднее количество за последние 30 дней (для сравнения)
        AVG(sp.daily_qty) OVER(PARTITION BY sp.store_id, sp.product_id 
                                ORDER BY sp.sale_date 
                                ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS avg_qty_last_30_days
    FROM daily_sales_pattern sp
)
SELECT 
    s.store_name,
    p.product_name,
    sc.sale_date,
    sc.daily_qty,
    sc.sales_last_7_days,
    ROUND(sc.avg_qty_last_30_days, 2) AS avg_daily_qty,
    CASE 
        WHEN sc.sales_last_7_days = 0 AND sc.avg_qty_last_30_days > 0 THEN '🔴 Вероятный out-of-shelf'
        WHEN sc.sales_last_7_days = 0 AND sc.avg_qty_last_30_days = 0 THEN '⚫ Мертвый товар'
        WHEN sc.daily_qty < 0.3 * sc.avg_qty_last_30_days AND sc.avg_qty_last_30_days > 2 THEN '🟡 Аномальное падение'
        ELSE '🟢 Норма'
    END AS shelf_status
FROM stockout_candidates sc
JOIN stores s ON sc.store_id = s.store_id
JOIN products p ON sc.product_id = p.product_id
WHERE sc.sale_date = '2026-02-03'  -- проверяем конкретный день
  AND sc.shelf_status IN ('🔴 Вероятный out-of-shelf', '🟡 Аномальное падение')
ORDER BY s.store_name, p.category;

Результат выполнения ��апроса:

store_name	product_name	sale_date	daily_qty	sales_last_7	avg_qty	shelf_status
Магнит Столичный	Майонез Слобода	2026-02-03	5	2	5.50	🟡 Аномальное падение

Пример вывода по полученным данным:

Показатель	Значение	Что означает
Среднее за 30 дней	5,5 шт/день	Обычно продается 5-6 пачек майонеза в день
Продажи 3 февраля	5 шт	Вроде нормально, но...
Продажи за 7 дней до 3 февраля	2 дня из 7	Это аномалия, значит, 5 февраля продажи восстановились после провала

Что произошло на самом деле:
Последние 7 дней майонез продавался только 2 дня, вероятно, его не выкладывали, а 3 февраля выложили и продажи сразу вернулись к норме. Это пример классического out-of-shelf, который мы поймали по косвенным признакам.

Что нам делать дальше?
– Направить запрос в магазин: Что случилось с майонезом в конце января?
– Проверить, не было ли проблем с поставкой;
– Если товар был на складе — разобраться с выкладкой.

Задача 3. Анализ планограмм и соблюдения выкладки

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

Проблема: фактические фейсы часто не соответствуют запланированным:
– Меньше, чем в договоре и мы теряем деньги поставщика (и бонусы);
– Больше, чем в договоре и мы неэффективно используем место (можно было поставить что-то более маржинальное).

Зачем это контролировать

Штрафы/бонусы поставщикам – если мы не выполняем планограмму, поставщик может требовать компенсацию;
Эффективность – лишние фейсы на слабых товарах съедают место сильных;
Контроль исполнения – в больших сетях без автоматизации сложно уследить.

Пример кода на SQL:

WITH planogram_compliance AS (
    SELECT 
        sp.store_id,
        sp.product_id,
        sp.facing_actual,
        p.facing_count AS facing_planned,
        p.category,
        -- Считаем отклонение
        (sp.facing_actual - p.facing_count) AS facing_diff,
        -- Средняя цена товара (для оценки потерь)
        (SELECT AVG(amount/quantity) FROM sales s2 
         WHERE s2.product_id = sp.product_id AND s2.sale_date >= '2026-02-01') AS avg_price
    FROM shelf_placement sp
    JOIN products p ON sp.product_id = p.product_id
    WHERE sp.is_active = TRUE
)
SELECT 
    s.store_name,
    p.product_name,
    p.category,
    pc.facing_planned,
    pc.facing_actual,
    pc.facing_diff,
    ROUND(pc.avg_price, 2) AS avg_price,
    -- Оценка потерь: если фейсов меньше, чем надо, считаем упущенную выручку
    ROUND(ABS(pc.facing_diff) * pc.avg_price * 30, 2) AS estimated_monthly_loss
FROM planogram_compliance pc
JOIN stores s ON pc.store_id = s.store_id
JOIN products p ON pc.product_id = p.product_id
WHERE pc.facing_diff != 0
ORDER BY estimated_monthly_loss DESC;

Результат выполнения запроса:

store_name	product_name	planned	actual	diff	avg_price	monthly_loss
Магнит Столичный	Пельмени Цезарь 500г	3	4	+1	180.00	0
Магнит Центральный	Шоколад Аленка	4	3	-1	30.00	900.00

Пример вывода по полученным данным:

Кейс 1. Шоколад (Магазин Центральный)
По плану: 4 фейса
Факт: 3 фейса
Потеря: 1 фейс × 30 руб. (цена) × 30 дней = 900 руб/мес

Поставщик платил за 4 фейса, а получил 3, это либо возврат части оплаты, либо недовольство партнера.

Кейс 2. Пельмени (Магазин Столичный)
По плану: 3 фейса
Факт: 4 фейса
Потеря: не денежная, а пространственная

Мы поставили лишний фейс пельменей (которые и так плохо продаются) и вместо них можно было поставить, например, еще молока.

Итог:
По шоколаду: либо вернуть 4-е фейс, либо объяснить поставщику, почему так (и, возможно, снизить оплату)
По пельменям: убрать лишний фейс, освободить место под более эффективный товар

Данные для всех задач

Если есть необходимость повторить решение из примеров, ниже код по созданию таблиц и код по заполнению данными.

-- Таблица магазинов
CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(100),
    city VARCHAR(50),
    total_shelf_area DECIMAL(10,2),
    shelf_length DECIMAL(10,2)
);

-- Таблица товаров
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200),
    category VARCHAR(100),
    subcategory VARCHAR(100),
    width DECIMAL(10,2),
    depth DECIMAL(10,2),
    height DECIMAL(10,2),
    facing_count INT DEFAULT 1
);

-- Таблица размещения товаров
CREATE TABLE shelf_placement (
    placement_id INT PRIMARY KEY,
    store_id INT,
    product_id INT,
    shelf_number INT,
    facing_actual INT,
    start_date DATE,
    end_date DATE,
    is_active BOOLEAN DEFAULT TRUE
);

-- Таблица продаж
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    store_id INT,
    product_id INT,
    sale_date DATE,
    quantity INT,
    amount DECIMAL(10,2),
    profit DECIMAL(10,2)
);

Заполнение созданных ранее таблиц данными

-- Магазины
INSERT INTO stores VALUES
(1, 'Магнит Столичный', 'Москва', 2500.0, 450.0),
(2, 'Магнит Центральный', 'Москва', 1800.0, 320.0),
(3, 'Магнит Северный', 'СПб', 2200.0, 400.0),
(4, 'Магнит Южный', 'Краснодар', 1500.0, 280.0),
(5, 'Магнит Западный', 'Казань', 2000.0, 360.0);

-- Товары
INSERT INTO products VALUES
(101, 'Молоко Простоквашино 1л', 'Молочка', 'Молоко', 10.0, 10.0, 20.0, 2),
(102, 'Хлеб Бородинский', 'Хлеб', 'Хлеб', 15.0, 25.0, 10.0, 1),
(103, 'Пельмени Цезарь 500г', 'Заморозка', 'Пельмени', 20.0, 15.0, 8.0, 3),
(104, 'Майонез Слобода 400г', 'Соусы', 'Майонез', 8.0, 8.0, 15.0, 2),
(105, 'Колбаса Докторская', 'Мясная гастрономия', 'Колб��са', 12.0, 12.0, 12.0, 2),
(106, 'Сок Добрый 1л', 'Напитки', 'Сок', 7.0, 7.0, 20.0, 3),
(107, 'Шоколад Аленка', 'Кондитерка', 'Шоколад', 5.0, 10.0, 1.0, 4),
(108, 'Чай Greenfield 25пак', 'Бакалея', 'Чай', 8.0, 8.0, 15.0, 2),
(109, 'Кофе Jardin', 'Бакалея', 'Кофе', 10.0, 10.0, 20.0, 2),
(110, 'Сахар песок 1кг', 'Бакалея', 'Сахар', 12.0, 8.0, 18.0, 2);

-- Размещение товаров
INSERT INTO shelf_placement VALUES
(1, 1, 101, 2, 2, '2026-01-01', NULL, TRUE),
(2, 1, 102, 1, 1, '2026-01-01', NULL, TRUE),
(3, 1, 103, 3, 4, '2026-01-01', NULL, TRUE),
(4, 1, 104, 3, 2, '2026-01-01', NULL, TRUE),
(5, 1, 105, 1, 1, '2026-01-01', NULL, TRUE),
(6, 2, 101, 2, 2, '2026-01-01', NULL, TRUE),
(7, 2, 103, 3, 3, '2026-01-01', NULL, TRUE),
(8, 2, 107, 4, 3, '2026-01-01', NULL, TRUE),
(9, 3, 101, 2, 2, '2026-01-01', NULL, TRUE),
(10, 3, 106, 4, 3, '2026-01-01', NULL, TRUE);

-- Продажи (февраль 2026)
INSERT INTO sales VALUES
(1, 1, 101, '2026-02-01', 5, 500.00, 100.00),
(2, 1, 101, '2026-02-02', 3, 300.00, 60.00),
(3, 1, 101, '2026-02-03', 4, 400.00, 80.00),
(4, 1, 102, '2026-02-01', 10, 450.00, 90.00),
(5, 1, 102, '2026-02-02', 8, 360.00, 72.00),
(6, 1, 103, '2026-02-01', 3, 540.00, 108.00),
(7, 1, 103, '2026-02-02', 4, 720.00, 144.00),
(8, 1, 104, '2026-02-01', 6, 420.00, 84.00),
(9, 1, 104, '2026-02-03', 5, 350.00, 70.00),
(10, 2, 101, '2026-02-01', 4, 400.00, 80.00),
(11, 2, 101, '2026-02-02', 6, 600.00, 120.00),
(12, 2, 103, '2026-02-01', 2, 360.00, 72.00),
(13, 2, 107, '2026-02-01', 15, 450.00, 90.00),
(14, 2, 107, '2026-02-02', 12, 360.00, 72.00),
(15, 3, 101, '2026-02-01', 7, 700.00, 140.00),
(16, 3, 106, '2026-02-01', 8, 560.00, 112.00),
(17, 3, 106, '2026-02-02', 10, 700.00, 140.00),
(18, 3, 106, '2026-02-03', 6, 420.00, 84.00),
(19, 4, 101, '2026-02-01', 3, 300.00, 60.00),
(20, 4, 105, '2026-02-01', 4, 720.00, 144.00),
(21, 5, 108, '2026-02-01', 5, 400.00, 80.00),
(22, 5, 109, '2026-02-01', 3, 600.00, 120.00),
(23, 5, 110, '2026-02-02', 8, 320.00, 64.00);

Предыдущие статьи-разборы задач на аналитику ассортимента:
https://habr.com/ru/articles/1005262/
https://habr.com/ru/articles/1005284/

💚 Больше про будни и задачи аналитика данных в моем тг 🌸Таня и Данные📊