Sepet analizi: birlikte satın alınan ürünler

“Bunu alanlar şunu da aldı”

Sepet analizi (market basket analysis), aynı siparişte birlikte görünen ürünleri bulur. Öneri sistemlerinin, raf düzeninin ve çapraz satış kampanyalarının temelidir. SQL'deki karşılığı, order_items tablosunu kendisiyle sipariş numarası üzerinden birleştirmektir.

SELECT p1.name AS urun1,
       p2.name AS urun2,
       COUNT(DISTINCT a.order_id) AS birlikte
FROM order_items a
JOIN order_items b ON b.order_id = a.order_id
                  AND a.product_id < b.product_id
JOIN products p1 ON p1.product_id = a.product_id
JOIN products p2 ON p2.product_id = b.product_id
GROUP BY p1.name, p2.name
ORDER BY birlikte DESC, urun1, urun2
LIMIT 5;

Üç ölçü

Derinleş: Destek, güven ve kaldıraç: hesap ve yorum

“Birlikte” sayısı tek başına yanıltıcıdır, çünkü çok satan iki ürün tesadüfen de sık sık aynı sepete düşer. Asıl soru şudur: A'yı alanlar B'yi, herkesin aldığından daha sık mı alıyor? Bunu kaldıraç (lift) ölçer.

lift(A, B) = destek(A ve B) ÷ (destek(A) × destek(B)) = ortak × N ÷ (adet_A × adet_B). Lift = 1 bağımsızlık, 1'den büyük çekim, 1'den küçük itme demektir. Lift simetriktir, güven ise yönlüdür: A→B ile B→A farklıdır.

Üç ölçüyü tek sorguda hesaplamak

Önce sepeti tekilleştir: aynı siparişte aynı kategoriden üç ürün varsa kategori bir kez sayılmalı. Sonra her öğenin kaç siparişte geçtiğini, çiftlerin ortak sayısını ve toplam sipariş sayısını ayrı CTE'lerde hazırla. Her CTE'nin taneciği ayrı olsun ki JOIN satırları çoğaltmasın.

WITH s AS (
  SELECT DISTINCT oi.order_id, p.category
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status = 'delivered'
),
n AS (SELECT COUNT(DISTINCT order_id) AS toplam FROM s),
u AS (SELECT category, COUNT(*) AS adet FROM s GROUP BY category),
c AS (
  SELECT a.category AS k1, b.category AS k2, COUNT(*) AS ortak
  FROM s a JOIN s b ON b.order_id = a.order_id AND a.category < b.category
  GROUP BY a.category, b.category
)
SELECT c.k1, c.k2, c.ortak,
       ROUND(100.0 * c.ortak / n.toplam, 1) AS destek,
       ROUND(100.0 * c.ortak / u1.adet, 1) AS guven_1_2,
       ROUND(1.0 * c.ortak * n.toplam / (u1.adet * u2.adet), 2) AS lift
FROM c
JOIN u u1 ON u1.category = c.k1
JOIN u u2 ON u2.category = c.k2
CROSS JOIN n
ORDER BY lift DESC, c.k1, c.k2;

Sonuç şaşırtıcı: tüm kategori çiftlerinde lift 1'in altında. En yüksek değer Ev & Yaşam–Kitap çiftinde (0,89). Bunun sebebi sepetlerin küçük olmasıdır: siparişlerin büyük kısmı bir ya da iki kalemden oluşur. Bir kategoriyi seçen sepette başka bir kategoriye az yer kalır. Bu veride kategori düzeyinde “çapraz satış fırsatı” yoktur. Ürün düzeyinde ise bazı çiftler öne çıkar.

Küçük sayı tuzağı

Ürün düzeyinde lift'i en yüksek çift Suç ve Ceza + Bisiklet Kaskı (lift ≈ 3), ama yalnızca 5 ortak siparişe dayanıyor. Az görülen ürünlerde birkaç tesadüfi sepet lift'i uçurur. Bu yüzden her zaman bir en az destek eşiği uygula (ör. en az 5–10 ortak sipariş) ve lift'i ortak sayısıyla birlikte raporla.