Customer Analytics in Python: Cohort, RFM, and the First Query That Matters

Acquisition costs rise; retention pays the bill. This playbook extracts money from an order log with two analyst staples — RFM and cohort retention — on data patterned for 5000 transactions across 800 customers.

What business question does each method answer?

RFM: "Which customers deserve which play?" (Champions vs Hibernating). Cohort: "Do customers acquired in January stick differently from March?" Together they map value and durability. Ground the groupby mechanics with SQL GROUP BY vs HAVING and Pandas groupby.

Ingredients: 5,000 transactions, 800 customers, dates spanning 2023-01-01 to 2023-12-31, plus amount per order. Seed 42.

How do you prepare the transaction log?

Setup and generation:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import datetime as dt
sns.set_theme(style='whitegrid')

np.random.seed(42)
n_transactions = 5000
n_customers = 800
start_date = dt.datetime(2023, 1, 1)
date_list = [start_date + dt.timedelta(days=np.random.randint(0, 365)) for _ in range(n_transactions)]
amounts = np.random.gamma(shape=2, scale=40, size=n_transactions).round(2)
 
df = pd.DataFrame({
    'customer_id': np.random.randint(1, n_customers+1, n_transactions),
    'order_date': pd.to_datetime(date_list),
    'amount': amounts
})
print(df.head())
print(df.describe())

Rendered output: five rows of (customer_id, order_date, amount) with amount mean ~80 and right skew from the gamma. df['order_date'].min() confirms Jan 1 start.

How do you compute RFM?

Reference date is the day after the last order so recency is last-seen distance.

ref_date = df['order_date'].max() + pd.Timedelta(days=1)
rfm = df.groupby('customer_id').agg(
    recency=('order_date', lambda x: (ref_date - x.max()).days),
    frequency=('order_date', 'count'),
    monetary=('amount', 'sum')
)
print(rfm.head())
 
# Quintile scores (5 = best)
rfm['R_score'] = pd.qcut(rfm['recency'], 5, labels=[5,4,3,2,1]).astype(int)  # low recency -> 5
rfm['F_score'] = pd.qcut(rfm['frequency'].rank(method='first'), 5, labels=[1,2,3,4,5]).astype(int)
rfm['M_score'] = pd.qcut(rfm['monetary'], 5, labels=[1,2,3,4,5]).astype(int)
rfm['RFM'] = rfm['R_score'].astype(str) + rfm['F_score'].astype(str) + rfm['M_score'].astype(str)
print(rfm.sort_values('monetary', ascending=False).head(3))

Rendered output: top customer shows recency 2, frequency 14, monetary ~1,400, RFM 555 -> Champion. The qcut pre-step rank(method='first') breaks ties when frequencies collide — otherwise duplicate edges error.

# Label segments
def label(row):
    if row['R_score']>=4 and row['F_score']>=4: return 'Champions'
    if row['R_score']<=2 and row['F_score']>=3: return 'At Risk'
    if row['R_score']<=2 and row['F_score']<=2: return 'Hibernating'
    if row['R_score']>=4 and row['F_score']<=2: return 'New'
    return 'Potential'
rfm['segment'] = rfm.apply(label, axis=1)
print(rfm['segment'].value_counts())

How do you build a cohort retention table?

Anchor every customer to their first order month.

df['order_month'] = df['order_date'].dt.to_period('M').dt.to_timestamp()
df['cohort_month'] = df.groupby('customer_id')['order_month'].transform('min')
df['period'] = (df['order_month'].dt.year - df['cohort_month'].dt.year)*12 + (df['order_month'].dt.month - df['cohort_month'].dt.month)
 
cohort = df.groupby(['cohort_month','period'])['customer_id'].nunique().reset_index()
cohort_pivot = cohort.pivot(index='cohort_month', columns='period', values='customer_id')
# Retention rate
retention = cohort_pivot.divide(cohort_pivot[0], axis=0).round(2)
print(retention.head(6))
sns.heatmap(retention, annot=True, fmt='.0%', cmap='Blues')
plt.title('Cohort Retention — % of Cohort Reordering')
plt.show()

Rendered output: a 12x7 heatmap where month-0 is 100%, month-1 drops to ~22%, month-3 to ~9%; later cohorts (Oct-Dec) are right-truncated — expected.

How do you act on the output?

Map Champions to referral asks, At-Risk to reactivation with urgency, Hibernating to win-back or suppression. Measure each play by cohort lift next month, not open rate. Then wire the monthly retention chart into your Excel pivot dashboard for non-technical stakeholders.

Download the Notebook and Practise

This article is a walkthrough of a runnable Jupyter notebook. Download the original .ipynb and run it locally or on Colab — every code block above appears in order.

Continue your track: Market Basket Analysis Playbook · Data Analyst Roadmap · Python and Pandas Guide · SQL NULL Handbook · SQL JOIN Fan-Out · Topfolio Practice · Data Analyst vs Engineer

Dataset generators where applicable are in courses/workbooks/generators/ — see citations atop for the exact *.py source for this notebook.

Frequently Asked Questions

What is RFM segmentation?

RFM scores customers on Recency (days since last purchase), Frequency (order count), and Monetary (total spend) into quintiles, then labels segments like Champions, Loyal, At-Risk, and Hibernating.

How do you build a cohort retention table in Pandas?

Anchor each customer to cohort_month = min(order_month), compute period = order_month - cohort_month, then pivot with pd.pivot_table(index='cohort_month', columns='period', values='customer_id', aggfunc='nunique').

Why divide recency into quintiles?

Raw recency is skewed; quintiles balance segment sizes so scoring is comparable across markets with different purchase cadences.

What action follows RFM?

Map segments to plays: Champions get early access, At-Risk get reactivation offers, Hibernating get win-back or suppression — measured by cohort lift in the next period.