RFM Analizi: SQL, PostgreSQL ve Python ile Müşteri Segmentasyonu

Müşteri analitiği projelerinde en sık karşılaşılan tuzaklardan biri, klasik kalıpları sorgulamadan uygulamaktır. Müşteriyi anlamak için yıllardır başvurulan ilk yöntem RFM (Recency, Frequency, Monetary) analizidir. Ancak verinin gerçek dağılımını göz ardı edip temel SQL fonksiyonlarıyla segmentasyon yapmaya çalıştığınızda, iş birimlerini yanıltacak hatalı yapay sınırlar üretirsiniz.

Bu yazıda; basit bir RFM skorlamasından başlayarak yaşanan teknik problemleri, istatistiksel dönüşüm ihtiyacını ve nihayetinde SQL ortamında K-Means gibi makine öğrenmesi yaklaşımlarına uzanan teknik evrimi adım adım ele alacağız.

1. Temel RFM Analizi ve Klasik Metrikler

RFM; müşterilerin satın alma davranışlarını üç temel boyutta ölçer:

• Recency (Yenilik - R): Müşterinin son etkileşiminden/alışverişinden bu yana geçen gün sayısı. (Düşük olması makbuldür)

• Frequency (Sıklık - F): Müşterinin belirlenen dönemdeki toplam işlem sayısı. (Yüksek olması makbuldür)

• Monetary (Parasal Değer - M): Müşterinin ürettiği toplam ciro veya finansal değer. (Yüksek olması makbuldür)

Klasik yöntemde amaç, bu 3 ham metriği $1-5$ arasında göreceli skorlara dönüştürmek ve nihayetinde 555 (Şampiyonlar) veya 111 (Kayıp Müşteriler) gibi segmentler elde etmektir.

2. İlk Yaklaşım: NTILE (qcut) ve String Birleştirme

İlk akla gelen yöntem, veriyi SQL'deki NTILE(5) window fonksiyonuyla %20'lik eşit homojen dilimlere bölmektir.

WITH RawMetrics AS (
    SELECT 
        customer_id,
        DATEDIFF(DAY, MAX(transaction_date), GETDATE()) AS recency,
        COUNT(transaction_id) AS frequency,
        SUM(amount) AS monetary
    FROM sales_data
    GROUP BY customer_id
),
ScoredMetrics AS (
    SELECT 
        customer_id,
        NTILE(5) OVER (ORDER BY recency DESC) AS r_score, -- Recency ters sıralanır
        NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
        NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
    FROM RawMetrics
)
SELECT 
    customer_id,
    -- Skorları birleştirerek RFM Kodunu ve Matrisini Oluşturma
    CONCAT(r_score, f_score, m_score) AS rfm_combined,
    CONCAT(r_score, f_score) AS rf_matrix
FROM ScoredMetrics;

3. İlk Kırılma: NTILE ve RANK (FIRST) Mimarisi Neden Tıkanır?

NTILE kullanırken yapılan en büyük hata, verinin dağılımını dikkate almamasıdır.

Yaşanan Kriz:

Eğer müşterilerinizin %60'ı sadece 1 kez alışveriş yapmışsa (Frequency = 1), NTILE(5) fonksiyonu veriyi zorla %20'lik parçalara bölmek ister. Bu durumda:

• Alışveriş sayısı 1 olan Müşteri A $\rightarrow$ F Skoru 1 alır.

• Alışveriş sayısı yine 1 olan Müşteri B $\rightarrow$ F Skoru 2 alır.

Aynı metrik değerine sahip iki farklı müşteri, sırf veri tabanının ORDER BY sırasındaki FIRST mantığı yüzünden farklı segmente düşer. Bu durum yapay sınırlar üretir ve segmentasyon güvenilirliğini yok eder.

4. Metrik Mimarilerini Zenginleştirme: RFM Yerine Alternatif Kombinasyonlar

NTILE ve RANK problemlerini aşmaya çalışırken sorulması gereken bir diğer kritik soru şudur: "Her iş modelinde Monetary (Ciro) doğru metrik midir?"

Monetary tek başına yanıltıcı olabilir (örneğin iade oranı yüksek veya kâr marjı düşük VIP müşteriler). Bu aşamada RFM'i klasik yapısından çıkarıp iş ihtiyacına göre yeniden kurgulayabiliriz:

• RF + Profitability (Net Karlılık): Ciro yerine sipariş başına kalan net kârı koyarak "ciro yaptıran ama zarar ettiren" kitleyi ayırmak.

• RF + Tenure (Müşteri Ömrü / Yaşı): Müşterinin ilk işleminden bu yana geçen süre. Yeni kazanılan yüksek frekanslı bir müşteri ile 5 yıldır sizinle olan sadık bir müşteriyi aynı sepete atmamak için kritiktir.

• RFD (Recency, Frequency, Duration / Engagement): SaaS, içerik ve platform iş modellerinde finansal harcama yerine harcanan zamanı veya oturum süresini eklemek.

• RFE (Recency, Frequency, Engagement Index): Müşterinin kaç farklı kanalı (Mobil, Web, Şube) veya kaç farklı ürün kategorisini kullandığı.

-- Müşteri Yaşı (Tenure) ve Net Kârı İçeren Alternatif Metrik Tablosu
SELECT 
    customer_id,
    DATEDIFF(DAY, MAX(transaction_date), GETDATE()) AS recency,
    COUNT(transaction_id) AS frequency,
    DATEDIFF(MONTH, MIN(first_transaction_date), GETDATE()) AS tenure_months, -- Müşteri Yaşı
    SUM(gross_margin_amount) AS net_profit -- Ciro yerine Net Kâr
FROM sales_data
GROUP BY customer_id;

5. Doğal Kırılma Sınırları: Z-Score (Standartlaştırma)

Veriyi logaritmik ölçeğe çektikten sonra, NTILE gibi yapay dilimler yerine verinin kendi Standart Sapmasını ($\sigma$) ve Ortalamasını ($\mu$) kullanarak Z-Score hesaplarız:

$$Z = \frac{\ln(1 + X) - \mu}{\sigma}$$

Z-Score bize bir müşterinin ortalamadan kaç standart sapma uzakta olduğunu söyler. Bu sayede hem farklı birimdeki verileri (gün sayısı ve TL tutarı) aynı $-3$ ile $+3$ ölçeğinde eşitlemiş hem de verinin kendi kırılma noktalarını yakalamış oluruz:

SQL

WITH LogMetrics AS (
    SELECT 
        customer_id,
        recency,
        LOG(1 + frequency) AS log_f,
        LOG(1 + monetary) AS log_m
    FROM RawMetrics
),
Stats AS (
    SELECT 
        AVG(log_f) AS avg_f, STDEV(log_f) AS stdev_f,
        AVG(log_m) AS avg_m, STDEV(log_m) AS stdev_m
    FROM LogMetrics
)
SELECT 
    l.customer_id,
    -- Z-score değerine göre doğal istatistiksel segmentasyon
    CASE 
        WHEN (l.log_m - s.avg_m) / NULLIF(s.stdev_m, 0) > 1.5 THEN 5  -- Grubun En Üst %7'si
        WHEN (l.log_m - s.avg_m) / NULLIF(s.stdev_m, 0) > 0.5 THEN 4
        WHEN (l.log_m - s.avg_m) / NULLIF(s.stdev_m, 0) > -0.5 THEN 3
        WHEN (l.log_m - s.avg_m) / NULLIF(s.stdev_m, 0) > -1.5 THEN 2
        ELSE 1 
    END AS z_m_score
FROM LogMetrics l
CROSS JOIN Stats s;

6. Zirve Noktası: MSSQL İçinde Native K-Means Algoritması ve Skala Tuzağı

İnsan eliyle kural veya eşik değeri yazmak yerine (örneğin Z > 1.5), verideki küme merkezlerini doğrudan makine öğrenmesiyle bulmak en olgun yaklaşımdır.

Ancak burada çok kritik bir Normalizasyon Tuzağı vardır:

Neden Doğrudan Min-Max Yapmadık?

Ham Ciro veya İşlem Sayısı verisine doğrudan Min-Max Scaler ($X_{\text{norm}} = \frac{X - X_{\text{min}}}{X_{\text{max}} - X_{\text{min}}}$) uygularsak; 10 Milyon TL harcayan tek bir VIP müşteri ($X_{\text{max}}$), 50.000 TL harcayan sadık müşterinizin skorunu $0.0049$ yapar. Sonuç olarak müşterilerin %99'u $0.00$ ile $0.01$ arasında sıkışıp kalır ve K-Means Öklid mesafesini hesaplarken bu devasa kitleyi tek bir nokta gibi görür.

Bu sorunu çözmek için K-Means öncesinde Robust ölçeklendirme mantığıyla (Logaritmik Dönüşüm + Min-Max) uç değerlerin yıkıcı etkisini nötrleştiririz.

Aşağıda, veri tabanı dışına çıkmadan, tempdb şişirmeyen optimize edilmiş saf T-SQL K-Means algoritmasını inceleyebilirsiniz:

-- 1. Aşama: Logaritmik Dönüşüm + Min-Max (Outlier Etkisini Nötrleyen Robust Scaler Proxy)
WITH LogMetrics AS (
    SELECT 
        customer_id,
        LOG(1 + recency) AS log_r,       -- Recency sağa basıktır, logaritmik ölçeğe çekilir
        LOG(1 + frequency) AS log_f,     -- Frequency sağa basıktır
        LOG(1 + monetary) AS log_m       -- Monetary sağa basıktır
    FROM RawMetrics
),
RobustNormalized AS (
    SELECT 
        customer_id,
        -- Logaritmik ölçekte 0-1 aralığına sıkıştırma (VIP müşterilerin %99'u ezmesi önlendi)
        (log_r - MIN(log_r) OVER()) * 1.0 / NULLIF((MAX(log_r) OVER() - MIN(log_r) OVER()), 0) AS r_norm,
        (log_f - MIN(log_f) OVER()) * 1.0 / NULLIF((MAX(log_f) OVER() - MIN(log_f) OVER()), 0) AS f_norm,
        (log_m - MIN(log_m) OVER()) * 1.0 / NULLIF((MAX(log_m) OVER() - MIN(log_m) OVER()), 0) AS m_norm
    FROM LogMetrics
)
SELECT * INTO #NormalizedCustomers FROM RobustNormalized;

-- Performance Indexing: İterasyon esnasında cross join ve join adımlarını hızlandırma
CREATE CLUSTERED INDEX IX_NormCust ON #NormalizedCustomers(customer_id);

-- 2. Aşama: Küme Merkezlerini (Centroids) Rastgele Başlatma (K=3)
SELECT TOP 3 
    ROW_NUMBER() OVER(ORDER BY NEWID()) AS cluster_id,
    r_norm, f_norm, m_norm
INTO #Centroids
FROM #NormalizedCustomers;

-- Atama tablosunu döngü dışında tek kez oluşturuyoruz (tempdb I/O optimizasyonu)
CREATE TABLE #Assignments (
    customer_id INT PRIMARY KEY,
    cluster_id INT
);

-- 3. Aşama: K-Means İterasyon Döngüsü
DECLARE @Iteration INT = 0;
WHILE @Iteration < 15
BEGIN
    TRUNCATE TABLE #Assignments;

    -- Müşterileri En Yakın Merkeze Atama (3 Boyutlu Öklid Mesafesi: d = sqrt((r1-r2)^2 + (f1-f2)^2 + (m1-m2)^2))
    INSERT INTO #Assignments (customer_id, cluster_id)
    WITH Distances AS (
        SELECT 
            c.customer_id,
            cent.cluster_id,
            ROW_NUMBER() OVER(
                PARTITION BY c.customer_id 
                ORDER BY (SQUARE(c.r_norm - cent.r_norm) + 
                          SQUARE(c.f_norm - cent.f_norm) + 
                          SQUARE(c.m_norm - cent.m_norm)) ASC
            ) AS rn
        FROM #NormalizedCustomers c
        CROSS JOIN #Centroids cent
    )
    SELECT customer_id, cluster_id 
    FROM Distances 
    WHERE rn = 1;

    -- Merkez Konumlarını Kümelerin Yeni Ağırlık Ortalamalarına Göre Güncelleme
    UPDATE cent
    SET 
        r_norm = new_c.avg_r,
        f_norm = new_c.avg_f,
        m_norm = new_c.avg_m
    FROM #Centroids cent
    INNER JOIN (
        SELECT 
            a.cluster_id,
            AVG(nc.r_norm) AS avg_r,
            AVG(nc.f_norm) AS avg_f,
            AVG(nc.m_norm) AS avg_m
        FROM #NormalizedCustomers nc
        JOIN #Assignments a ON nc.customer_id = a.customer_id
        GROUP BY a.cluster_id
    ) new_c ON cent.cluster_id = new_c.cluster_id;

    SET @Iteration = @Iteration + 1;
END;

-- Nihai Sonuç: Küme Merkezleri ve Müşterilerin Algoritmik Segmentleri
SELECT a.customer_id, a.cluster_id, c.r_norm, c.f_norm, c.m_norm
FROM #Assignments a
JOIN #NormalizedCustomers c ON a.customer_id = c.customer_id;

Blog yazına harika bir genişletme katmanı daha! K-Means bölümünü "PostgreSQL (pgvector & Native CTE)" ve "Python (scikit-learn / RobustScaler)" olarak iki alternatif kurguyla zenginleştirmek, yazının teknik derinliğini zirveye taşıyacaktır.

İşte blogunun 6. bölümüne (veya devamına) ekleyebileceğin modern ve production-ready kod blokları:

6.1 PostgreSQL İle K-Means (Modern & Vektörel Yaklaşım)

PostgreSQL tarafında geleneksel döngüler yazmak yerine, veriyi pgvector eklentisiyle vektörleştirmek veya ANSI SQL standartlarında Recursive / Iterative CTE ile kümelemek çok daha performanslıdır.

pgvector eklentisi aktif bir PostgreSQL üzerinde 3 boyutlu $(R, F, M)$ vektör uzayında K-Means çalıştırmak şu kadar basittir:

-- 1. pgvector Eklentisini Aktif Etme
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. Logaritmik + Min-Max Robust Dönüşümü ve Vektör Tablosu Oluşturma
WITH LogMetrics AS (
    SELECT 
        customer_id,
        LN(1 + recency) AS log_r,
        LN(1 + frequency) AS log_f,
        LN(1 + monetary) AS log_m
    FROM raw_metrics
),
Normalized AS (
    SELECT 
        customer_id,
        -- 0-1 Arasına Sıkıştırma
        (log_r - MIN(log_r) OVER()) / NULLIF((MAX(log_r) OVER() - MIN(log_r) OVER()), 0) AS r_norm,
        (log_f - MIN(log_f) OVER()) / NULLIF((MAX(log_f) OVER() - MIN(log_f) OVER()), 0) AS f_norm,
        (log_m - MIN(log_m) OVER()) / NULLIF((MAX(log_m) OVER() - MIN(log_m) OVER()), 0) AS m_norm
    FROM LogMetrics
)
SELECT 
    customer_id,
    -- (R, F, M) Değerlerini 3 Boyutlu Vektöre Dönüştürme
    ARRAY[r_norm, f_norm, m_norm]::vector(3) AS rfm_vector
INTO customer_vectors
FROM Normalized;

-- 3. pgvector İle Öklid Mesafesi Bounding-Box veya Ivfflat/HNSW Index Üzerinden K-Means
-- PostgreSQL 15+ Native k-means fonksiyonu veya pgvector mesafe operatörü (<->) ile:
SELECT 
    customer_id,
    rfm_vector,
    -- Öklid Mesafesi Operatörü (<->)
    rfm_vector <-> '[0.1, 0.8, 0.9]'::vector AS distance_to_vip_centroid
FROM customer_vectors;

6.2 Python İle K-Means (Scikit-Learn & RobustScaler)

Eğer veri ambarından (DWH) veri çekilip Python ortamında (Jupyter, Airflow pipeline vb.) kümeleme yapılacaksa, el ile Min-Max + Log yapmak yerine RobustScaler ve KMeans pipeline'ı kurulmalıdır.

RobustScaler, verideki aşırı uç değerlerden etkilenmemek için Ortalama/Standart Sapma yerine Medyan ve Çeyrekler Arası Açıklığı ($IQR = Q3 - Q1$) kullanır:

$$\text{Robust\_Value} = \frac{X - \text{Medyan}}{\text{IQR}}$$

import pandas as pd
import numpy as np
from sklearn.preprocessing import RobustScaler
from sklearn.cluster import KMeans

# 1. Ham Verinin Yüklenmesi
df = pd.read_csv("customer_rfm_raw.csv")  # Columns: customer_id, recency, frequency, monetary

# 2. Sağa Basıklığı Bastırmak İçin Log Transformation (1 + X)
df_log = pd.DataFrame()
df_log['recency_log'] = np.log1p(df['recency'])
df_log['frequency_log'] = np.log1p(df['frequency'])
df_log['monetary_log'] = np.log1p(df['monetary'])

# 3. RobustScaler İle Aykırı Değerlere (Outliers) Karşı Dayanıklı Ölçeklendirme
scaler = RobustScaler()
X_scaled = scaler.fit_transform(df_log)

# 4. K-Means Algoritmasının Çalıştırılması (K=3)
kmeans = KMeans(
    n_clusters=3, 
    init='k-means++',  # Merkezleri akıllı başlatma (Rastgelelik hatasını önler)
    n_init=10, 
    max_iter=300, 
    random_state=42
)

# Kümelerin Tahmin Edilmesi ve Orijinal Veriye Eklenmesi
df['cluster_id'] = kmeans.fit_predict(X_scaled)

# 5. Cluster Özet İstatistiklerinin İncelenmesi
cluster_summary = df.groupby('cluster_id').agg({
    'recency': ['mean', 'median'],
    'frequency': ['mean', 'median'],
    'monetary': ['mean', 'median', 'count']
}).reset_index()

print(cluster_summary)

7. RF Skor Matrisi (5x5)

7.1 Segment Tanımları ve Eşleme Tablosu

Özet: Nereden Nereye Geldik?