RFM müşteri segmentasyonu

Üç soru

• Recency: En son ne zaman aldı? (yakın = iyi)
• Frequency: Kaç kez aldı?
• Monetary: Ne kadar harcadı?

Her boyut NTILE ile 1–5 arası skora çevrilir, sonra skor kombinasyonlarından segment üretilir.

WITH m AS (
  SELECT o.customer_id,
         julianday('2025-01-01') - julianday(MAX(o.order_date)) AS recency_gun,
         COUNT(DISTINCT o.order_id) AS frequency,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS monetary
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'delivered'
  GROUP BY o.customer_id
),
s AS (
  SELECT customer_id, recency_gun, frequency, ROUND(monetary) AS monetary,
         NTILE(5) OVER (ORDER BY recency_gun DESC) AS r,
         NTILE(5) OVER (ORDER BY frequency) AS f,
         NTILE(5) OVER (ORDER BY monetary) AS m
  FROM m
)
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 / Umut vadeden'
            WHEN r <= 2 AND f >= 3 THEN 'Risk altında'
            ELSE 'Uykuda' END AS segment
FROM s
ORDER BY r DESC, f DESC
LIMIT 15;

Recency'de sıralama DESC: en uzun süredir gelmeyen 1, en yakın zamanda gelen 5 alır.

Derinleş: Puanlama eşikleri: NTILE mi, iş kuralı mı?

RFM'de her boyutu 1–5 puana çevirmenin iki yolu vardır. Seçim, segmentlerin ne kadar kararlı ve anlaşılır olacağını belirler.

NTILE'ın eşitlik sorunu

Frequency küçük tam sayılardan oluşur. Anadolu Market'te 27 müşterinin tam 2 teslim edilmiş siparişi var. NTILE(5) OVER (ORDER BY frequency, customer_id) bu 27 kişinin bir kısmına F = 1, bir kısmına F = 2 verir. Aynı davranışa farklı puan verilir ve aradaki tek fark müşteri numarasıdır. Frequency için çoğu zaman sabit eşik (1, 2–3, 4–5, 6–9, 10+) daha dürüsttür.

Diğer kararlar

• Referans tarihi: Recency “bugüne göre” değil, analiz tarihine göre sabitlenir (burada 2025-01-01). Aksi halde sorgu her gün başka sonuç verir.
• Monetary'de iadeler: Yalnızca teslim edilenleri saymak iadeleri dışarıda bırakır. İadeli siparişi saymak ise harcamayı şişirir. Tanımı yaz.
• JOIN çoğalması: Frequency'yi order_items JOIN'inden sonra COUNT(*) ile alırsan kalem sayarsın. COUNT(DISTINCT order_id) kullan.

Segmentin değerini görmenin en iyi yolu, müşteri sayısını ve ciro payını yan yana koymaktır. Aşağıda R için 90 gün, F için 5 sipariş eşiğiyle kurulmuş basit bir kural tabanlı segmentasyon var:

WITH m AS (
  SELECT o.customer_id,
         julianday('2025-01-01') - julianday(MAX(o.order_date)) AS gun,
         COUNT(DISTINCT o.order_id) AS n,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS para
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'delivered'
  GROUP BY o.customer_id
),
s AS (
  SELECT CASE WHEN gun <= 90 AND n >= 5 THEN 'Şampiyon'
              WHEN gun <= 90 THEN 'Aktif'
              WHEN n >= 5 THEN 'Risk altında'
              ELSE 'Uykuda' END AS segment, para
  FROM m
)
SELECT segment, COUNT(*) AS musteri, ROUND(SUM(para)) AS ciro,
       ROUND(100.0 * SUM(para) / (SELECT SUM(para) FROM s), 1) AS ciro_payi
FROM s
GROUP BY segment
ORDER BY ciro DESC;

135 müşterinin 41'i “Şampiyon” ve cironun %57,3'ünü getiriyor. 14 “Risk altında” müşteri ise az sayıda olmasına rağmen cironun yaklaşık %15'ini taşıyor. Geri kazanma kampanyasının ilk hedefi onlardır.