Customer Segmentation with RFM Analysis -- Complete Guide to Behavioral Segmentation

WITH customer_rfm AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(order_total) AS monetary
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '365 days'
  GROUP BY customer_id
),

rfm_scores AS (
  SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    NTILE(5) OVER (ORDER BY recency_days DESC) AS recency_score,
    NTILE(5) OVER (ORDER BY frequency ASC) AS frequency_score,
    NTILE(5) OVER (ORDER BY monetary ASC) AS monetary_score
  FROM customer_rfm
),

segment_scores AS (
  SELECT
    customer_id,
    recency_score,
    frequency_score,
    monetary_score,
    CONCAT(
      recency_score::TEXT,
      frequency_score::TEXT,
      monetary_score::TEXT
    ) AS rfm_cell
  FROM rfm_scores
)

SELECT
  rfm_cell,
  COUNT(*) AS customer_count,
  CASE
    WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4 THEN 'Champions'
    WHEN recency_score >= 4 AND frequency_score >= 3 AND monetary_score >= 3 THEN 'Loyal Customers'
    WHEN recency_score >= 4 AND frequency_score >= 1 AND monetary_score >= 1 THEN 'Recent Customers'
    WHEN recency_score >= 3 AND frequency_score >= 3 AND monetary_score >= 3 THEN 'Potential Loyalists'
    WHEN recency_score >= 3 AND frequency_score >= 2 THEN 'Promising'
    WHEN recency_score >= 2 AND frequency_score >= 2 THEN 'Needs Attention'
    WHEN recency_score >= 1 AND frequency_score >= 1 AND monetary_score >= 2 THEN 'At Risk'
    WHEN recency_score >= 1 AND frequency_score >= 1 THEN 'Hibernating'
    ELSE 'Lost'
  END AS segment
FROM segment_scores
ORDER BY customer_count DESC;

import pandas as pd
import numpy as np

def calculate_rfm(orders_df, reference_date=None):
    if reference_date is None:
        reference_date = orders_df["order_date"].max()

    df = orders_df.copy()
    df["order_date"] = pd.to_datetime(df["order_date"])

    rfm = df.groupby("customer_id").agg(
        recency=("order_date", lambda x: (reference_date - x.max()).days),
        frequency=("order_id", "nunique"),
        monetary=("order_total", "sum"),
    ).reset_index()

    rfm["r_quartile"] = pd.qcut(rfm["recency"], 5, labels=[5, 4, 3, 2, 1])
    rfm["f_quartile"] = pd.qcut(rfm["frequency"].rank(method="first"), 5,
                                  labels=[1, 2, 3, 4, 5])
    rfm["m_quartile"] = pd.qcut(rfm["monetary"].rank(method="first"), 5,
                                  labels=[1, 2, 3, 4, 5])

    rfm["rfm_score"] = (
        rfm["r_quartile"].astype(int)
        + rfm["f_quartile"].astype(int)
        + rfm["m_quartile"].astype(int)
    )

    def segment_label(row):
        r, f, m = row["r_quartile"], row["f_quartile"], row["m_quartile"]
        if r >= 4 and f >= 4 and m >= 4:
            return "Champions"
        elif r >= 4 and f >= 3 and m >= 3:
            return "Loyal Customers"
        elif r >= 4 and f <= 2:
            return "New Customers"
        elif 2 <= r <= 3 and f >= 3 and m >= 3:
            return "Potential Loyalists"
        elif r >= 3 and f <= 2 and m <= 2:
            return "Promising"
        elif r <= 2 and f >= 3 and m >= 3:
            return "At Risk"
        elif r <= 2 and f >= 1 and m >= 1:
            return "Hibernating"
        else:
            return "Lost"

    rfm["segment"] = rfm.apply(segment_label, axis=1)
    return rfm

np.random.seed(42)
n = 5000
orders = pd.DataFrame({
    "customer_id": np.random.choice(range(1000), n),
    "order_date": pd.date_range("2025-06-23", "2026-06-23", periods=n),
    "order_id": range(1, n + 1),
    "order_total": np.random.exponential(50, n) + 10,
})

rfm_results = calculate_rfm(orders)
segment_summary = rfm_results.groupby("segment").agg(
    count=("customer_id", "count"),
    avg_recency=("recency", "mean"),
    avg_frequency=("frequency", "mean"),
    avg_monetary=("monetary", "mean"),
    total_revenue=("monetary", "sum"),
).round(2)

segment_summary["pct_customers"] = (
    segment_summary["count"] / segment_summary["count"].sum() * 100
).round(1)
segment_summary["pct_revenue"] = (
    segment_summary["total_revenue"] / segment_summary["total_revenue"].sum() * 100
).round(1)

print(segment_summary.sort_values("total_revenue", ascending=False))

import matplotlib.pyplot as plt
import numpy as np
from math import pi

def segment_radar_chart(rfm_df, segments_to_plot=None):
    if segments_to_plot is None:
        segments_to_plot = ["Champions", "Loyal Customers", "At Risk", "Lost"]

    segment_means = rfm_df.groupby("segment")[
        ["recency", "frequency", "monetary"]
    ].mean()

    normalized = segment_means.copy()
    for col in ["frequency", "monetary"]:
        normalized[col] = (normalized[col] - normalized[col].min()) / (
            normalized[col].max() - normalized[col].min()
        )
    normalized["recency"] = 1 - (
        (normalized["recency"] - normalized["recency"].min())
        / (normalized["recency"].max() - normalized["recency"].min())
    )

    categories = ["Recency (high=good)", "Frequency (high=good)", "Monetary (high=good)"]
    num_vars = len(categories)
    angles = [n / float(num_vars) * 2 * pi for n in range(num_vars)]
    angles += angles[:1]

    fig, ax = plt.subplots(figsize=(8, 8), subplot_kw={"projection": "polar"})
    colors = ["#2563eb", "#22c55e", "#f59e0b", "#ef4444"]

    for i, segment in enumerate(segments_to_plot):
        if segment not in normalized.index:
            continue
        values = normalized.loc[segment].tolist()
        values += values[:1]
        ax.plot(angles, values, color=colors[i], linewidth=2, label=segment)
        ax.fill(angles, values, color=colors[i], alpha=0.1)

    ax.set_xticks(angles[:-1])
    ax.set_xticklabels(categories, fontsize=10)
    ax.set_ylim(0, 1)
    ax.legend(loc="upper right", bbox_to_anchor=(1.3, 1.0))
    ax.set_title("Customer Segment Profiles", fontsize=14, fontweight="bold")
    plt.tight_layout()
    return fig

fig = segment_radar_chart(rfm_results)

import pandas as pd
import numpy as np
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split
from sklearn.metrics import classification_report

def predict_segment_migration(rfm_df, historical_orders_df):
    rfm_df["is_champion"] = (rfm_df["segment"] == "Champions").astype(int)
    rfm_df["is_at_risk"] = (rfm_df["segment"] == "At Risk").astype(int)
    rfm_df["is_lost"] = (rfm_df["segment"] == "Lost").astype(int)

    features = ["recency", "frequency", "monetary"]
    X = rfm_df[features]
    y_at_risk = rfm_df["is_at_risk"]

    X_train, X_test, y_train, y_test = train_test_split(
        X, y_at_risk, test_size=0.3, random_state=42
    )

    model = RandomForestClassifier(
        n_estimators=100,
        max_depth=5,
        min_samples_leaf=20,
        random_state=42,
        class_weight="balanced",
    )
    model.fit(X_train, y_train)

    y_pred = model.predict(X_test)
    importance = pd.DataFrame({
        "feature": features,
        "importance": model.feature_importances_,
    }).sort_values("importance", ascending=False)

    return model, importance, classification_report(y_test, y_pred, output_dict=True)

model, importance, report = predict_segment_migration(rfm_results, orders)
print("Feature importance:")
print(importance)
print(f"\nPrecision (At Risk): {report['1']['precision']:.3f}")
print(f"Recall (At Risk): {report['1']['recall']:.3f}")