Customer Segmentation for Data Analysts

Why Customer Segmentation Matters

Not all customers are equal. Sending the same message to a customer who bought yesterday and one who hasn't purchased in two years wastes budget and reduces effectiveness. Segmentation lets teams personalise messaging, prioritise retention effort, and allocate acquisition spend where it converts best.

RFM Analysis in SQL

RFM scores each customer on three dimensions: Recency (days since last purchase — lower is better), Frequency (number of orders), and Monetary (total spend). Score each dimension 1–5 using NTILE(5), then combine scores into named segments.

-- Step 1: Calculate raw RFM values per customer
WITH rfm_raw AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date)         AS recency_days,
    COUNT(DISTINCT order_id)               AS frequency,
    SUM(order_value)                       AS monetary
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '365 days'
  GROUP BY customer_id
),
-- Step 2: Score each dimension 1–5 (5 = best)
rfm_scores AS (
  SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    -- Recency: fewer days = higher score (use DESC for NTILE)
    NTILE(5) OVER (ORDER BY recency_days DESC)  AS r_score,
    NTILE(5) OVER (ORDER BY frequency)           AS f_score,
    NTILE(5) OVER (ORDER BY monetary)            AS m_score
  FROM rfm_raw
),
-- Step 3: Combine scores into a segment label
rfm_segments AS (
  SELECT
    customer_id,
    r_score, f_score, m_score,
    (r_score + f_score + m_score) AS rfm_total,
    CASE
      WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
      WHEN r_score >= 4 AND f_score >= 3                  THEN 'Loyal'
      WHEN r_score >= 4 AND f_score <= 2                  THEN 'New Customers'
      WHEN r_score = 3  AND f_score >= 3                  THEN 'Potential Loyalists'
      WHEN r_score = 3  AND f_score <= 2                  THEN 'Promising'
      WHEN r_score = 2  AND f_score >= 3                  THEN 'At Risk'
      WHEN r_score = 2  AND f_score <= 2                  THEN 'Needs Attention'
      WHEN r_score = 1  AND f_score >= 3                  THEN 'Cant Lose Them'
      WHEN r_score = 1  AND f_score = 2                   THEN 'Hibernating'
      ELSE                                                      'Lost'
    END AS segment
  FROM rfm_scores
)
-- Step 4: Aggregate segment metrics for the stakeholder report
SELECT
  segment,
  COUNT(*)                              AS customers,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_base,
  ROUND(AVG(rfm_raw.recency_days), 0)  AS avg_recency_days,
  ROUND(AVG(rfm_raw.frequency), 1)     AS avg_orders,
  ROUND(AVG(rfm_raw.monetary), 0)      AS avg_ltv
FROM rfm_segments
JOIN rfm_raw USING (customer_id)
GROUP BY segment
ORDER BY AVG(rfm_total) DESC;

Decile Analysis — Revenue Concentration

Decile analysis answers a simple but powerful question: how concentrated is your revenue? In most businesses the top 10% of customers generate 50–60% of revenue. Knowing exactly which customers are in that top decile is critical for retention prioritisation.

-- Revenue decile analysis
WITH customer_revenue AS (
  SELECT
    customer_id,
    SUM(order_value) AS total_revenue,
    COUNT(order_id)  AS total_orders
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '365 days'
  GROUP BY customer_id
),
with_decile AS (
  SELECT
    customer_id,
    total_revenue,
    total_orders,
    NTILE(10) OVER (ORDER BY total_revenue DESC) AS revenue_decile
  FROM customer_revenue
)
SELECT
  revenue_decile,
  COUNT(*)                                              AS customers,
  ROUND(SUM(total_revenue), 0)                         AS segment_revenue,
  ROUND(100.0 * SUM(total_revenue)
        / SUM(SUM(total_revenue)) OVER (), 1)          AS pct_of_total_revenue,
  ROUND(SUM(SUM(total_revenue)) OVER (
        ORDER BY revenue_decile
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        / SUM(SUM(total_revenue)) OVER () * 100, 1)    AS cumulative_pct,
  ROUND(AVG(total_revenue), 0)                         AS avg_customer_revenue
FROM with_decile
GROUP BY revenue_decile
ORDER BY revenue_decile;

Behavioural Segmentation in SQL

For SaaS and product analytics, RFM is often less useful than behavioural segments based on feature usage. Define segments using explicit rules that map to your product's value moments.

-- SaaS behavioural segmentation
WITH usage_30d AS (
  SELECT
    user_id,
    COUNT(DISTINCT session_date)              AS active_days,
    COUNT(DISTINCT feature_used)              AS features_used,
    SUM(CASE WHEN feature_used = 'export'
             THEN 1 ELSE 0 END)               AS exports,
    SUM(CASE WHEN feature_used = 'share'
             THEN 1 ELSE 0 END)               AS shares,
    MAX(event_date)                           AS last_active
  FROM product_events
  WHERE event_date >= CURRENT_DATE - 30
  GROUP BY user_id
)
SELECT
  user_id,
  active_days,
  features_used,
  CASE
    WHEN active_days >= 20 AND features_used >= 5  THEN 'Power User'
    WHEN active_days >= 10 AND features_used >= 3  THEN 'Regular'
    WHEN active_days >= 3  AND features_used >= 1  THEN 'Casual'
    WHEN active_days = 0   OR last_active < CURRENT_DATE - 7 THEN 'Churned'
    ELSE 'New / Onboarding'
  END AS user_segment
FROM usage_30d;

K-Means Clustering in Python

When you have more than 3 features and want data-driven groupings rather than rule-based ones, K-means clustering is the standard starting point. The key steps: scale your features, choose k with the elbow method, fit the model, then interpret each cluster.

import pandas as pd
import numpy as np
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
import matplotlib.pyplot as plt

# --- Load and prepare features ---
df = pd.read_sql("""
    SELECT customer_id,
           EXTRACT(DAY FROM NOW() - MAX(order_date)) AS recency,
           COUNT(DISTINCT order_id)                  AS frequency,
           SUM(order_value)                          AS monetary,
           AVG(order_value)                          AS avg_order_value,
           COUNT(DISTINCT product_category)          AS category_breadth
    FROM orders
    GROUP BY customer_id
""", conn)

features = ['recency', 'frequency', 'monetary', 'avg_order_value', 'category_breadth']
X = df[features].fillna(0)

# --- Scale features (K-means is distance-based, scale matters) ---
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)

# --- Elbow method: find optimal k ---
inertias = []
k_range = range(2, 11)
for k in k_range:
    km = KMeans(n_clusters=k, random_state=42, n_init='auto')
    km.fit(X_scaled)
    inertias.append(km.inertia_)

plt.plot(k_range, inertias, marker='o')
plt.xlabel('Number of clusters (k)')
plt.ylabel('Inertia')
plt.title('Elbow method — choose k where curve flattens')
plt.savefig('elbow.png', dpi=150)
plt.close()

# --- Fit final model (choose k=5 from elbow) ---
km = KMeans(n_clusters=5, random_state=42, n_init='auto')
df['cluster'] = km.fit_predict(X_scaled)

# --- Interpret clusters via centroid profiles ---
profile = df.groupby('cluster')[features].mean().round(1)
profile['size'] = df.groupby('cluster').size()
print(profile.sort_values('monetary', ascending=False))

Making Segments Actionable

Segmentation only creates value when it changes what teams do. For each segment, define the action, the channel, the frequency, and the success metric before handing it to marketing.

Monitoring Segment Health Over Time

Segmentation is not a one-off analysis. Run it monthly and track segment migration — how many customers moved from Champions to At-Risk, how many Lost customers were reactivated. Segment migration is often a leading indicator of overall business health.

-- Month-over-month segment migration
WITH this_month AS (
  SELECT customer_id, segment FROM rfm_segments
  WHERE analysis_month = '2026-10-01'
),
last_month AS (
  SELECT customer_id, segment FROM rfm_segments
  WHERE analysis_month = '2026-09-01'
)
SELECT
  lm.segment   AS from_segment,
  tm.segment   AS to_segment,
  COUNT(*)     AS customers
FROM last_month lm
JOIN this_month tm USING (customer_id)
WHERE lm.segment != tm.segment   -- only show customers who moved
GROUP BY 1, 2
ORDER BY customers DESC;

Frequently Asked Questions