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

Анализ покупательской корзины (market basket analysis) отвечает на простой вопрос: какие товары люди покупают вместе? Зная это, можно:

• располагать связанные товары рядом, это как раз пример выше, он же кросс-мерчандайзинг;

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

• рекомендовать товары в мобильном приложении (при добавлении в корзину пельменей у покупателя появляется рекомендация: с этим товаром также покупают майонез).

В этой статье мы разберем одну из типичных задач вида «Анализ покупательской корзины» на учебных данных, с кодом SQL, со всей необходимой математикой и с примером выводов. Задача не боевая, а лишь учебный пример.

1. Исходные данные

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

Таблица transactions (справочник со всеми чеками):

Таблица transaction_items (состав всех чеков из справочника):

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

2. Постановка задачи

Категорийный менеджер просит ответить на вопросы:

1. Какие пары товаров чаще всего встречаются в одном чеке?

2. Для каждого товара, что еще кладут в корзину вместе с ним?

3. Какие сочетания товаров приносят больше всего выручки?

Будем решать задачу на чистом SQL.

3. Простая статистика: частотность пар

Для начала найдем все пары товаров, которые встречаются в одном чеке.

3.1. Джойн таблицы на саму себя (self-join) для поиска пар

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

SELECT 
    ti1.transaction_id,
    ti1.product_id AS product_a,
    ti1.product_name AS product_a_name,
    ti2.product_id AS product_b,
    ti2.product_name AS product_b_name
FROM transaction_items ti1
JOIN transaction_items ti2 
    ON ti1.transaction_id = ti2.transaction_id
    AND ti1.product_id < ti2.product_id  -- убираем дубли и самосоединения
WHERE ti1.transaction_id IN (1001, 1002, 1003)  -- для примера
ORDER BY ti1.transaction_id;

Результат:

Что мы сделали:

Ограничение ti1.product_id < ti2.product_id гарантирует, что пара (хлеб, молоко) попадет в результат только один раз, а не дважды как (хлеб, молоко) и (молоко, хлеб).

Условие WHERE ti1.transaction_id IN (1001, 1002, 1003) - выбираем три чека для примера.

В чеке 1001 с тремя товарами получилось 3 пары (формула сочетаний: C(3,2) = 3).

3.2. Подсчет частоты пар

Теперь сгруппируем и посчитаем, сколько раз каждая пара встретилась:

SELECT 
    ti1.product_id AS product_a_id,
    MAX(ti1.product_name) AS product_a_name,  -- берем максимум, он все равно один
    ti2.product_id AS product_b_id,
    MAX(ti2.product_name) AS product_b_name,
    COUNT(*) AS times_together -- считаем строки 
FROM transaction_items ti1
JOIN transaction_items ti2 
    ON ti1.transaction_id = ti2.transaction_id
    AND ti1.product_id < ti2.product_id
GROUP BY ti1.product_id, ti2.product_id
ORDER BY times_together DESC;

Результат:

Инсайт: пельмени и молоко встречаются вместе в 2 чеках из 3, а хлеб с молоком - только в одном, на таком малом наборе выводы делать рано, но думаю, что базовая логика понятна.

4. Метрики качества ассоциативных правил

Простая частота встречаемости – это не единственная метрика, в анализе корзины используют три ключевых показателя, рассмотрим каждый из них.

4.1. Поддержка (Support)

Support – это доля транзакций, содержащих данный набор товаров. Для набора (X, Y):

Support(X, Y) = (число транзакций с X и Y) / (общее число транзакций)

На наших данных (3 транзакции):

Support(пельмени, молоко) = 2/3 = 0,67
Support(хлеб, молоко) = 1/3 = 0,33

4.2. Достоверность (Confidence)

Confidence – это условная вероятность: если купили X, то купят и Y .

Confidence(X → Y) = Support(X, Y) / Support(X)

Сначала нужно посчитать частоту каждого товара отдельно:

SELECT 
    product_id,
    product_name,
    COUNT(DISTINCT transaction_id) AS transaction_count
FROM transaction_items
GROUP BY product_id, product_name;

Результат:

Теперь считаем Confidence для правила пельмени + молоко:

Support(пельмени, молоко) = 2
Support(пельмени) = 2
Confidence = 2/2 = 1,0 (100%)

То есть если купили пельмени, то в 100% случаев купили и молоко (на примере наших трех чеков).

4.3. Лифт (Lift)

Lift показывает, насколько вероятнее покупка Y при покупке X по сравнению со случайной покупкой Y.

Lift(X → Y) = Confidence(X → Y) / Support(Y)

Для правила пельмени + молоко:
Confidence = 1,0
Support(молоко) = 2/3 = 0,67
Lift = 1,0 / 0,67 = 1,5

Интерпретация метрики:
Lift = 1 — независимы (покупка X не влияет на покупку Y)
Lift > 1 — положительная связь (X увеличивает вероятность Y)
Lift < 1 — отрицательная связь (X уменьшает вероятность Y)

Lift = 1,5 означает, что при покупке пельменей вероятность купить молоко в 1,5 раза выше, чем просто случайная вероятность покупки молока.

4.4. Финальный запрос со всеми метриками

Соберем все метрики в одном запросе:

WITH 
product_freq AS (
  
-- частотность каждого товара
    SELECT 
        product_id,
        COUNT(DISTINCT transaction_id) AS product_freq
    FROM transaction_items
    GROUP BY product_id
),
total_transactions AS (
    
-- общее число чеков
    SELECT COUNT(DISTINCT transaction_id) AS total FROM transaction_items
),
pair_counts AS (
  
-- частотность пар
    SELECT 
        ti1.product_id AS a_id,
        ti2.product_id AS b_id,
        COUNT(*) AS pair_freq
    FROM transaction_items ti1
    JOIN transaction_items ti2 
        ON ti1.transaction_id = ti2.transaction_id
        AND ti1.product_id < ti2.product_id
    GROUP BY ti1.product_id, ti2.product_id
)

SELECT 
    p1.product_name AS product_a,
    p2.product_name AS product_b,
    pc.pair_freq,
    ROUND(100.0 * pc.pair_freq / tt.total, 2) AS support_percent,
    ROUND(100.0 * pc.pair_freq / pf_a.product_freq, 2) AS confidence_percent,
    ROUND((pc.pair_freq * tt.total) / (pf_a.product_freq * pf_b.product_freq), 2) AS lift
FROM pair_counts pc
CROSS JOIN total_transactions tt
JOIN products p1 ON pc.a_id = p1.product_id
JOIN products p2 ON pc.b_id = p2.product_id
JOIN product_freq pf_a ON pc.a_id = pf_a.product_id
JOIN product_freq pf_b ON pc.b_id = pf_b.product_id
ORDER BY lift DESC;

5. Продвинутый анализ: когда больше двух товаров

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

-- тройки товаров
SELECT 
    ti1.product_id AS a_id,
    ti2.product_id AS b_id,
    ti3.product_id AS c_id,
    COUNT(*) AS triple_freq
FROM transaction_items ti1
JOIN transaction_items ti2 
    ON ti1.transaction_id = ti2.transaction_id AND ti1.product_id < ti2.product_id
JOIN transaction_items ti3 
    ON ti1.transaction_id = ti3.transaction_id 
    AND ti2.product_id < ti3.product_id
GROUP BY ti1.product_id, ti2.product_id, ti3.product_id;

Условия ti1.product_id < ti2.product_id AND ti2.product_id < ti3.product_id также, как в предыдущем примере с парами обеспечивают уникальность комбинации (избегаем перестановок).

6. Анализ с учетом выручки

Менеджеру важно не только что с чем, но и сколько денег нам все это приносит, поэтому добавим стоимость.

6.1. Ищем товары с максимальной выручкой в парах

WITH pair_revenue AS (
    SELECT 
        ti1.transaction_id,
        ti1.product_id AS a_id,
        ti2.product_id AS b_id,
        ti1.price * ti1.quantity + ti2.price * ti2.quantity AS pair_sum
    FROM transaction_items ti1
    JOIN transaction_items ti2 
        ON ti1.transaction_id = ti2.transaction_id
        AND ti1.product_id < ti2.product_id
)
SELECT 
    p1.product_name AS product_a,
    p2.product_name AS product_b,
    COUNT(*) AS times_together,
    SUM(pr.pair_sum) AS total_revenue_from_pairs,
    AVG(pr.pair_sum) AS avg_check_with_this_pair
FROM pair_revenue pr
JOIN products p1 ON pr.a_id = p1.product_id
JOIN products p2 ON pr.b_id = p2.product_id
GROUP BY p1.product_name, p2.product_name
ORDER BY total_revenue_from_pairs DESC;

6.2. Считаем долю кросс-продаж

Для каждого товара посчитаем, какую долю выручки он нам приносит в сочетании с другими товарами:

WITH product_total AS (
    SELECT 
        product_id,
        SUM(price * quantity) AS total_revenue
    FROM transaction_items
    GROUP BY product_id
),

pair_revenue_for_product AS (
    SELECT 
        ti1.product_id AS product_id,
        SUM(ti1.price * ti1.quantity + ti2.price * ti2.quantity) AS revenue_in_pairs
    FROM transaction_items ti1
    JOIN transaction_items ti2 
        ON ti1.transaction_id = ti2.transaction_id
        AND ti1.product_id < ti2.product_id
    GROUP BY ti1.product_id
)

SELECT 
    p.product_name,
    pt.total_revenue,
    COALESCE(prfp.revenue_in_pairs, 0) AS revenue_in_pairs,
    ROUND(100.0 * COALESCE(prfp.revenue_in_pairs, 0) / pt.total_revenue, 2) AS cross_sell_share_percent
FROM products p
JOIN product_total pt ON p.product_id = pt.product_id
LEFT JOIN pair_revenue_for_product prfp ON p.product_id = prfp.product_id
ORDER BY cross_sell_share_percent DESC;

7. Полный пайплайн задачи

Теперь на больших синтетических данных (20 чеков) посмотрим, как это работает в реальности.

Пример данных (транзакции):

Транзакции с 1 по 20, где:

Чек 1: молоко, хлеб, пельмени
Чек 2: молоко, хлеб, масло
Чек 3: молоко, пельмени, майонез, кетчуп
... и так далее

Результат анализа (топ-5 пар по лифту):

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

1. Пельмени и майонез – у нас самая сильная связка, лифт 2,67 означает, что при покупке пельменей вероятность купить майонез почти в 3 раза выше случайной, их нужно размещать рядом, это хорошая возможность для кросс-продажи.

2. Пельмени и кетчуп – это тоже достаточно сильная связка (лифт у пары 2,33), значит рядом с пельменями нам нужна зона соусов.

3. Хлеб и масло - казалось бы, классическая пара продуктов, но лифт ниже 2,0, поэтому возможно, масло покупают и просто так и наша интуиция нас подводит.

4. Молоко и хлеб – пара встречаются достаточно часто, но лифт близок к 1, что говорит нам о том, что зависимость слабая, значит это просто популярные товары, которые покупают независимо.

Пример рекомендация для менеджера:
Создать пельменный островок: пельмени (заморозка) + рядом или над холодильником стеллаж с майонезом, кетчупом, соусами, это увеличит кросс-продажи и средний чек на 1 клиента.

8. Еще один полезный запрос

Для каждого товара покажем топ-3 сопутствующих товара:

WITH pairs_with_rank AS (
--подзапрос с рейтингом
    SELECT 
        a_id,
        b_id,
        pair_freq,
        ROW_NUMBER() OVER(PARTITION BY a_id ORDER BY pair_freq DESC) AS rn
    FROM pair_counts
)

--итог с ограничением на топ 3
SELECT 
    p1.product_name AS product,
    p2.product_name AS frequently_bought_with,
    pair_freq
FROM pairs_with_rank pwr
JOIN products p1 ON pwr.a_id = p1.product_id
JOIN products p2 ON pwr.b_id = p2.product_id
WHERE rn <= 3
ORDER BY product, rn;

Вместо заключения

Мы разобрали один из множества вариантов анализа покупательской корзины, который можно провести на чистом SQL. Но важно помнить, что результат аналитики – это не просто цифры и это не просто понимание, что покупают наши клиенты, это возможность увеличения среднего чека, повышение эффективности выкладки товаров и возможности для персонализации рекомендаций клиентам.

?Статья с примерами задач из аналитики ассортимента https://habr.com/ru/articles/1005262/

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