Business Problem

Stripe's revenue operations team wants to move beyond guesswork when deciding which merchants to prioritize for retention outreach versus expansion calls. Build a classic RFM (Recency, Frequency, Monetary) segmentation: score every customer on how recently they transacted, how often, and how much they have spent in total, then bucket them into named segments like 'Champions', 'Loyal', 'At Risk', and 'Lost' so account managers can act on the list directly. Transaction history spans years and is not pre-aggregated, so recency and frequency need to be computed dynamically from raw transaction records.

Dataset & Schema

Customer data with customer_id, signup_date; transactions with customer_id, transaction_date, transaction_amount; need to calculate recency (days since last purchase), frequency (transaction count), monetary (total spent).

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  signup_date DATE
);
CREATE TABLE transactions (
  transaction_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  transaction_date DATE,
  transaction_amount DECIMAL(10,2),
  FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);

SQL Solution

WITH customer_metrics AS (
  SELECT 
    c.customer_id,
    c.customer_name,
    MAX(t.transaction_date) as last_transaction_date,
    ROUND((julianday('2024-03-31') - julianday(MAX(t.transaction_date))) / 1.0, 0) as recency_days,
    COUNT(DISTINCT t.transaction_id) as frequency_count,
    ROUND(SUM(t.transaction_amount), 2) as monetary_value
  FROM customers c
  LEFT JOIN transactions t ON c.customer_id = t.customer_id
  GROUP BY c.customer_id, c.customer_name
),
rfm_scores AS (
  SELECT 
    customer_id,
    customer_name,
    last_transaction_date,
    recency_days,
    frequency_count,
    monetary_value,
    CASE 
      WHEN recency_days <= 30 THEN 3
      WHEN recency_days <= 90 THEN 2
      ELSE 1
    END as recency_score,
    CASE 
      WHEN frequency_count >= 4 THEN 3
      WHEN frequency_count >= 2 THEN 2
      ELSE 1
    END as frequency_score,
    CASE 
      WHEN monetary_value >= 10000 THEN 3
      WHEN monetary_value >= 5000 THEN 2
      ELSE 1
    END as monetary_score
  FROM customer_metrics
)
SELECT 
  customer_id,
  customer_name,
  recency_days,
  frequency_count,
  monetary_value,
  recency_score,
  frequency_score,
  monetary_score,
  (recency_score + frequency_score + monetary_score) as combined_rfm_score,
  CASE 
    WHEN recency_score = 3 AND frequency_score = 3 AND monetary_score = 3 THEN 'Champions'
    WHEN recency_score >= 2 AND frequency_score >= 2 AND monetary_score >= 2 THEN 'Loyal-Customers'
    WHEN recency_score >= 2 AND frequency_score < 2 THEN 'Potential-Loyalists'
    WHEN recency_score = 1 AND frequency_score >= 2 THEN 'At-Risk'
    WHEN recency_score = 1 AND frequency_score = 1 THEN 'Lost'
    ELSE 'Need-Engagement'
  END as customer_segment
FROM rfm_scores
ORDER BY combined_rfm_score DESC, monetary_value DESC;

Explanation