Ana içeriğe geç
Kesit SQL
İleri · Büyük Sorgular

Büyük sorgu 3: RFM puanlama ve müşteri segmentleri

Yakınlık, sıklık ve parasal değer için NTILE ile 1–5 puan, puan birleşimlerinden iş segmentleri ve segment raporu.

Uygulamada aç, 4 alıştırmayı çözÜcretsiz, hesap gerekmez, kurulum yok.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Soru

CRM ekibi kampanya listeleri hazırlayacak: kimler en değerli, kimler kaybedilmek üzere? Cevap RFM: Recency (son siparişten bu yana gün), Frequency (sipariş sayısı), Monetary (toplam harcama).

Sorgu

Büyük sorgu nasıl okunur?Yukarıdan aşağı, her CTE'yi ayrı bir tablo gibi düşün. Bir adımı anlamadıysan o CTE'nin adını en sondaki SELECT'e yazıp yalnızca onu çalıştır: SELECT * FROM adim_adi LIMIT 20;
WITH teslim AS (          -- 1) Yalnızca teslim edilmiş siparişler, sipariş tutarıyla
  SELECT o.customer_id, o.order_id, o.order_date, SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS tutar
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'delivered'
  GROUP BY o.customer_id, o.order_id, o.order_date
),
ham AS (                 -- 2) Müşteri başına R, F, M ham değerleri (referans: 2025-01-01)
  SELECT customer_id,
         julianday('2025-01-01') - julianday(MAX(order_date)) AS r_gun,
         COUNT(*) AS f_siparis,
         SUM(tutar) AS m_tutar
  FROM teslim
  GROUP BY customer_id
),
puan AS (                -- 3) Her boyut 1–5 puan (5 en iyi); NTILE eşit dilimler üretir
  SELECT customer_id, r_gun, f_siparis, m_tutar,
         NTILE(5) OVER (ORDER BY r_gun DESC, customer_id) AS r,
         NTILE(5) OVER (ORDER BY f_siparis, customer_id) AS f,
         NTILE(5) OVER (ORDER BY m_tutar, customer_id) AS m
  FROM ham
),
segment AS (             -- 4) Puan birleşimlerini iş segmentlerine eşle
  SELECT *,
         CASE WHEN r >= 4 AND f >= 4 THEN 'Şampiyon'
              WHEN r >= 3 AND f >= 3 THEN 'Sadık'
              WHEN r >= 4 AND f <= 2 THEN 'Yeni ve umut vadeden'
              WHEN r <= 2 AND f >= 3 THEN 'Kaybedilmek üzere'
              WHEN r <= 2 THEN 'Uykuda'
              ELSE 'Gelişmeye açık' END AS segment
  FROM puan
)
SELECT segment,              -- 5) Segment özeti
       COUNT(*) AS musteri,
       ROUND(AVG(r_gun)) AS ort_gun,
       ROUND(AVG(f_siparis), 1) AS ort_siparis,
       ROUND(SUM(m_tutar)) AS ciro,
       ROUND(100.0 * SUM(m_tutar) / SUM(SUM(m_tutar)) OVER (), 1) AS ciro_payi
FROM segment
GROUP BY segment
ORDER BY ciro DESC;

Adım adım

AdımNe yapar?Çıktının bir satırı
teslimSipariş düzeyinde net tutar (satır çoğalması burada çözülür)müşteri 5, sipariş 31, 2024-11-02, 1840.5
hamMüşteri başına gün, sipariş sayısı, toplam tutarmüşteri 5, 59 gün, 4 sipariş, 6120
puanHer boyutu NTILE(5) ile 1–5 arasında puanlarr 4, f 4, m 5
segmentPuan birleşimlerini kurallara göre adlandırırŞampiyon
son SELECTSegment başına sayı, ortalamalar ve ciro payıŞampiyon, 36 müşteri, …, %31,4

Tasarım kararları

Alıştırmalara geç (4)