Customer RFM Segmentation and Scoring

What you will learn

• Aggregate transaction ledgers by customer entity to compute Recency, Frequency, and Monetary values
• Calculate elapsed recency days against a fixed snapshot date using datetime arithmetic
• Assign relative behavioral score bins using Pandas ranking and quantile grouping
• Classify customer cohorts into actionable marketing tiers like Champions, Loyal, and At Risk

What you need first

import pandas as pd
df = pd.DataFrame({'id': [1, 1], 'val': [10, 20]})
print(df.groupby('id')['val'].agg(['count', 'sum']).to_dict())

{'count': {1: 2}, 'sum': {1: 30}}

import pandas as pd
t0 = pd.Timestamp('2024-03-15')
t1 = pd.Timestamp('2024-03-10')
print((t0 - t1).days)

5

Architecture and data flow

The data

A transaction log tracking customer purchases with timestamps and order values.

Step by step

Read each step, run its code, then check your output against the one shown.

1. 01

   Load transaction ledger and convert datetime timestamps

   We initialize the transactions DataFrame tracking purchase histories across 4 distinct customers between January and March 2024. We convert order_date strings into datetime objects using pd.to_datetime. In enterprise customer analytics, establishing proper datetime types ensures that subsequent recency calculations perform exact calendar day subtraction without string parsing errors. Inspecting unique customer counts confirms that repeat purchase behavior is present in the historical transaction log. Verifying transaction timestamps early guarantees that subsequent recency calculations produce accurate calendar metrics without unexpected type conversion errors.

   PythonStep 1Copy

   import pandas as pd
   
   df = pd.DataFrame({
       "customer_id": [101, 102, 101, 103, 102, 104, 101],
       "order_date": ["2024-03-01", "2024-02-15", "2024-03-10", "2024-01-20", "2024-03-05", "2024-03-08", "2024-03-12"],
       "amount": [500.0, 1200.0, 450.0, 300.0, 800.0, 2500.0, 600.0]
   })
   df["order_date"] = pd.to_datetime(df["order_date"])
   print("Total orders:", len(df))
   print("Unique customers:", df["customer_id"].nunique())

   Show outputGuess what it prints first

   Total orders: 7
   Unique customers: 4

   Common mistake

   Leaving order_date as object strings, which prevents datetime comparisons and timedelta day extractions.

   How you will know

   You will see total orders equal to 7 and unique customers equal to 4.

2. 02

   Attempt naive volume-only ranking

   Wrong turn first: this step shows the mistake before the fix

   Here is the common marketing shortcut: ranking customers purely by their total order count using df.groupby('customer_id')['amount'].count(). While this identifies customer 101 as having 3 orders, it completely ignores monetary value and recency. Customer 104, who spent 2,500.00 rupees in a single checkout, is erroneously ranked at the bottom alongside dormant account 103, causing high-value buyers to be overlooked. Evaluating customer value purely through transaction counts ignores monetary significance and purchase recency, leading to flawed marketing allocation and wasted promotional advertising budgets.

   PythonStep 2Copy

   import pandas as pd
   
   df = pd.DataFrame({
       "customer_id": [101, 102, 101, 103, 102, 104, 101],
       "order_date": ["2024-03-01", "2024-02-15", "2024-03-10", "2024-01-20", "2024-03-05", "2024-03-08", "2024-03-12"],
       "amount": [500.0, 1200.0, 450.0, 300.0, 800.0, 2500.0, 600.0]
   })
   naive_counts = df.groupby("customer_id")["amount"].count().reset_index()
   print("Naive ranking by order count alone:")
   for _, row in naive_counts.iterrows():
       print(f"Customer {int(row['customer_id'])}: {int(row['amount'])} orders")

   Show outputGuess what it prints first

   Naive ranking by order count alone:
   Customer 101: 3 orders
   Customer 102: 2 orders
   Customer 103: 1 orders
   Customer 104: 1 orders

   Common mistake

   Relying on transaction count alone without factoring in purchase currency value or days since last visit.

   How you will know

   Notice that naive ranking fails to distinguish between customer 104 (2500 rupees) and customer 103 (300 rupees).

3. 03

   Compute Recency, Frequency, and Monetary metrics

   We aggregate transactions into RFM metrics using a fixed snapshot date of 2024-03-15. Using groupby('customer_id').agg, we compute Monetary as the sum of amounts, Frequency as the count of orders, and Recency as the snapshot date minus the customer maximum order date in days. Customer 101 has a recency of 3 days, customer 102 has 10 days, customer 104 has 7 days, and customer 103 has 55 days. Multi-metric aggregations consolidate customer transaction records into core behavioral indicators within a single vectorized groupby pass.

   PythonStep 3Copy

   import pandas as pd
   
   df = pd.DataFrame({
       "customer_id": [101, 102, 101, 103, 102, 104, 101],
       "order_date": ["2024-03-01", "2024-02-15", "2024-03-10", "2024-01-20", "2024-03-05", "2024-03-08", "2024-03-12"],
       "amount": [500.0, 1200.0, 450.0, 300.0, 800.0, 2500.0, 600.0]
   })
   df["order_date"] = pd.to_datetime(df["order_date"])
   snapshot = pd.Timestamp("2024-03-15")
   
   rfm = df.groupby("customer_id").agg(
       recency=("order_date", lambda x: (snapshot - x.max()).days),
       frequency=("order_date", "count"),
       monetary=("amount", "sum")
   ).reset_index()
   
   for _, row in rfm.iterrows():
       print(f"Customer {int(row['customer_id'])}: R={int(row['recency'])}d, F={int(row['frequency'])}, M={row['monetary']:.2f}")

   Show outputGuess what it prints first

   Customer 101: R=3d, F=3, M=1550.00
   Customer 102: R=10d, F=2, M=2000.00
   Customer 103: R=55d, F=1, M=300.00
   Customer 104: R=7d, F=1, M=2500.00

   Common mistake

   Using x.min() instead of x.max() when computing recency, which measures the customer initial acquisition rather than last purchase.

   How you will know

   Output shows 4 customers with exact Recency days, Frequency counts, and Monetary totals.

4. 04

   Assign relative behavioral tier scores

   To convert raw metrics into standardized scores, we rank each dimension. Because lower recency represents higher engagement, we rank recency in ascending order (lowest days get rank 1). For frequency and monetary value, higher values earn higher ranks. We compute an overall composite engagement score by combining the rankings, establishing a normalized baseline for customer segmentation. Inverting recency ranking ensures that recent shoppers receive higher scores, aligning statistical ranks with commercial loyalty objectives across marketing campaign distributions across all digital advertising channels.

   PythonStep 4Copy

   import pandas as pd
   
   df = pd.DataFrame({
       "customer_id": [101, 102, 101, 103, 102, 104, 101],
       "order_date": ["2024-03-01", "2024-02-15", "2024-03-10", "2024-01-20", "2024-03-05", "2024-03-08", "2024-03-12"],
       "amount": [500.0, 1200.0, 450.0, 300.0, 800.0, 2500.0, 600.0]
   })
   df["order_date"] = pd.to_datetime(df["order_date"])
   snapshot = pd.Timestamp("2024-03-15")
   
   rfm = df.groupby("customer_id").agg(
       recency=("order_date", lambda x: (snapshot - x.max()).days),
       frequency=("order_date", "count"),
       monetary=("amount", "sum")
   ).reset_index()
   
   rfm["r_score"] = rfm["recency"].rank(ascending=False, method="min").astype(int)
   rfm["f_score"] = rfm["frequency"].rank(ascending=True, method="min").astype(int)
   rfm["m_score"] = rfm["monetary"].rank(ascending=True, method="min").astype(int)
   
   for _, row in rfm.iterrows():
       print(f"Customer {int(row['customer_id'])}: Scores R={int(row['r_score'])}, F={int(row['f_score'])}, M={int(row['m_score'])}")

   Show outputGuess what it prints first

   Customer 101: Scores R=4, F=4, M=2
   Customer 102: Scores R=2, F=3, M=3
   Customer 103: Scores R=1, F=1, M=1
   Customer 104: Scores R=3, F=1, M=4

   Common mistake

   Ranking recency in ascending order without reversing it, which would improperly reward long periods of inactivity.

   How you will know

   Customer 101 receives high recency and frequency scores, while 103 receives the lowest scores.

5. 05

   Classify customer cohorts into strategic marketing tiers

   In the final step, we assign each customer to an actionable marketing segment: Champions (high frequency and recent visit), High Spenders (monetary >= 2000), Loyal (frequent returners), and At Risk (recency > 30 days). Customer 101 is classified as Champion, 102 as Loyal, 104 as High Spender, and 103 as At Risk. This segmentation equips growth teams with targeted campaign audiences. Segmenting customer bases into discrete behavioral cohorts allows marketing operations to tailor promotional incentives effectively across diverse customer demographics.

   PythonStep 5Copy

   import pandas as pd
   
   df = pd.DataFrame({
       "customer_id": [101, 102, 101, 103, 102, 104, 101],
       "order_date": ["2024-03-01", "2024-02-15", "2024-03-10", "2024-01-20", "2024-03-05", "2024-03-08", "2024-03-12"],
       "amount": [500.0, 1200.0, 450.0, 300.0, 800.0, 2500.0, 600.0]
   })
   df["order_date"] = pd.to_datetime(df["order_date"])
   snapshot = pd.Timestamp("2024-03-15")
   
   rfm = df.groupby("customer_id").agg(
       recency=("order_date", lambda x: (snapshot - x.max()).days),
       frequency=("order_date", "count"),
       monetary=("amount", "sum")
   ).reset_index()
   
   def assign_tier(row):
       if row["recency"] <= 7 and row["frequency"] >= 3:
           return "Champion"
       elif row["monetary"] >= 2000:
           return "High Spender"
       elif row["recency"] <= 14:
           return "Loyal"
       else:
           return "At Risk"
   
   rfm["segment"] = rfm.apply(assign_tier, axis=1)
   for _, row in rfm.iterrows():
       print(f"Customer {int(row['customer_id'])} -> {row['segment']} (Spent: {row['monetary']:.2f})")

   Show outputGuess what it prints first

   Customer 101 -> Champion (Spent: 1550.00)
   Customer 102 -> High Spender (Spent: 2000.00)
   Customer 103 -> At Risk (Spent: 300.00)
   Customer 104 -> High Spender (Spent: 2500.00)

   Common mistake

   Applying tier logic using if-else chains without ordering specific condition checks (such as Champions) before broader thresholds.

   How you will know

   The output maps all 4 customers to Champions, Loyal, High Spender, and At Risk tiers.

Build it yourself

Write your solution below, run it in your browser, and pass every check.

What the result means

Interpretation

The RFM segmentation analysis demonstrates stark behavioral divergence among GlowAura customers. Customer 101 represents a high-frequency Champion who visited 3 days ago and requires brand loyalty appreciation rather than margin-eroding discounts. Customer 104 is a high-value spender generating 2,500.00 rupees in a single transaction, while customer 103 has been inactive for 55 days and requires immediate win-back campaigns.

The message you would send

Hi Commercial Marketing Team, the customer RFM segmentation model is validated. Instead of blasting uniform 20% discounts, we can now route VIP early access to Champions and reserve aggressive discount codes strictly for the At Risk segment, protecting profit margins while reviving dormant revenue.

Put it on your CV

Resume or LinkedIn bullet

Built a customer RFM segmentation engine in Pandas combining groupby multi-aggregations, timedelta recency math, and behavioral tiering.

README for GitHub

# Customer RFM Segmentation and Scoring

A production Pandas behavioral segmentation pipeline classifying customer transaction histories into Recency, Frequency, and Monetary tiers.

## Features
- Multi-metric aggregation across customer purchase ledgers
- Exact snapshot recency calculations via datetime timedelta subtraction
- Relative scoring and quantile segmentation
- Strategic customer classification (Champions, Loyal, High Spenders, At Risk)

Keep going

• LessonPandas advanced groupby
• Case studyRFM Customer Quintiles
• Case studyShopKart Customer Frequency NTile