Build an RFM Customer Segmentation from Excel Data

RFM segmentation groups customers by three questions: how recently did they buy (recency), how often (frequency), and how much have they spent (monetary value). It needs nothing but an order history, it is easy to explain to a marketing team, and it produces lists people can act on: thank the best customers, win back the ones drifting away, stop discounting to people who would buy anyway. This page builds it from an Excel export with pandas and writes the segments back to Excel. It is part of Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl xlsxwriter

orders_24m.xlsx holds two years of orders, one row per order: order ID, customer ID, order date and amount. The analysis date is Monday 28 September 2026, the day after the last full week of data. Returns and cancelled orders should be removed before this point; they distort both frequency and value.

Step 1 — Recency, frequency and monetary per customer

One groupby produces all three. Recency is measured from the analysis date, not from today, so the analysis can be rerun as of any date:

import pandas as pd

orders = pd.read_excel("orders_24m.xlsx", parse_dates=["OrderDate"])
AS_OF = pd.Timestamp("2026-09-28")

rfm = orders.groupby("CustomerID").agg(
    LastOrder=("OrderDate", "max"),
    Frequency=("OrderID", "nunique"),
    Monetary=("Amount", "sum"),
)
rfm["Recency"] = (AS_OF - rfm["LastOrder"]).dt.days
print(f"{len(rfm)} customers")
print(rfm[["Recency", "Frequency", "Monetary"]].describe().round(1).loc[["min", "25%", "50%", "75%", "max"]].to_string())

Frequency counts distinct orders, not rows: if the export has one row per order line, nunique on the order ID stops a five-line order counting as five purchases.

Step 2 — Score each measure from 1 to 5

Scores put the three measures on the same scale: each customer gets 1 to 5 by quintile, with 5 the best. Recency is reversed, since fewer days is better. The trap is frequency: with many customers on one or two orders, the quintile boundaries coincide and pd.qcut raises an error about duplicate bin edges:

try:
    pd.qcut(rfm["Frequency"], 5, labels=[1, 2, 3, 4, 5])
except ValueError as e:
    print("qcut failed:", e)

Ranking first gives every customer a distinct position, so the bins are always equal-sized. Customers with the same frequency can land in adjacent scores; that is the price of equal groups, and the usual choice:

def score(series, higher_is_better=True):
    ranked = series.rank(method="first", ascending=higher_is_better)
    return pd.qcut(ranked, 5, labels=[1, 2, 3, 4, 5]).astype(int)

rfm["R"] = score(rfm["Recency"], higher_is_better=False)
rfm["F"] = score(rfm["Frequency"])
rfm["M"] = score(rfm["Monetary"])
rfm["RFM"] = rfm["R"].astype(str) + rfm["F"].astype(str) + rfm["M"].astype(str)
print(rfm.groupby("R")["Recency"].agg(["min", "max"]).to_string())

Look at what ranking does to frequency, though. 188 customers placed exactly one order, more than the 120 that fit in the bottom fifth, so 68 of them receive a frequency score of 2 alongside customers with two orders, purely because of the order they happened to be ranked in. For monetary value, which is continuous, that never matters. For a count with a few very common values it can, and some teams prefer fixed thresholds for frequency instead of quantiles:

bins = [0, 1, 2, 4, 9, float("inf")]           # 1, 2, 3–4, 5–9, 10+ orders
rfm["F_fixed"] = pd.cut(rfm["Frequency"], bins=bins, labels=[1, 2, 3, 4, 5]).astype(int)
print(pd.crosstab(rfm["F_fixed"], rfm["F"]).to_string())

Fixed thresholds have a second advantage: a score means the same thing next month. The quintile version is relative, so as the customer base grows a customer can drop a score without changing their behaviour. The rest of this page uses the quintile scores, since that is the classic method; switching is a one-line change.

Step 3 — Name the segments

125 score combinations are too many to act on. Map them to a handful of named segments with rules based mostly on recency and frequency, which are the strongest signals of future buying. Rules are checked in order, and the first match wins:

import numpy as np

R, F = rfm["R"], rfm["F"]
rules = [
    ("Champions",       (R >= 4) & (F >= 4)),
    ("Loyal",           (R >= 3) & (F >= 4)),
    ("New",             (R >= 4) & (F <= 1)),
    ("Promising",       (R >= 4) & (F <= 3)),
    ("Needs attention", (R == 3) & (F <= 3)),
    ("At risk",         (R <= 2) & (F >= 3)),
    ("Hibernating",     (R <= 2) & (F <= 2)),
]
rfm["Segment"] = np.select([cond for _, cond in rules], [name for name, _ in rules], default="Other")
print(rfm["Segment"].value_counts().to_string())

Rules are a business decision; write them where marketing can read them and adjust. The segments here follow a widely used scheme, but "At risk" for a subscription business and for a furniture shop mean different things.

Step 4 — Summarise the segments

The summary is what gets presented: how many customers in each segment, what share of revenue they bring, and their typical recency and order count:

summary = rfm.groupby("Segment").agg(
    Customers=("R", "size"),
    Revenue=("Monetary", "sum"),
    MedianRecency=("Recency", "median"),
    MedianOrders=("Frequency", "median"),
    AvgSpend=("Monetary", "mean"),
)
summary["Customer %"] = summary["Customers"] / summary["Customers"].sum() * 100
summary["Revenue %"] = summary["Revenue"] / summary["Revenue"].sum() * 100
summary = summary.sort_values("Revenue", ascending=False)
print(summary.round(1).to_string())

Here the Champions are 19.5% of customers and bring 55.3% of revenue, with a median of nine orders and a last order 25 days ago. The At risk segment is the largest, 146 customers: a median of five orders, but the last one about ten months ago. Together with Loyal they account for another 35% of revenue.

The typical result, and the one here, is that a small share of customers brings a large share of revenue, and that "At risk" holds customers who used to be valuable and have stopped buying — the best target for a win-back campaign, because they have already shown what they are worth.

Step 5 — The recency-by-frequency grid

A pivot of customer counts by R and F score is the picture marketing teams recognise; with a colour scale it shows where the customer base sits at a glance:

grid = rfm.pivot_table(index="R", columns="F", values="Monetary", aggfunc="size", fill_value=0)
grid = grid.sort_index(ascending=False)
print(grid.to_string())

Step 6 — Write the workbook

A summary sheet with the grid and segment table, and one sheet listing every customer with scores and segment, ready to filter into campaign lists:

with pd.ExcelWriter("rfm_segments_2026-09-28.xlsx", engine="xlsxwriter") as xw:
    book = xw.book
    summary.round(1).to_excel(xw, sheet_name="Summary")
    ws = xw.sheets["Summary"]
    ws.set_column(0, 0, 16)
    ws.set_column(1, 7, 13, book.add_format({"num_format": "#,##0.0"}))
    top = len(summary) + 3
    ws.write(top, 0, "Customers by recency (rows) and frequency (columns) score", book.add_format({"bold": True}))
    grid.to_excel(xw, sheet_name="Summary", startrow=top + 1)
    ws.conditional_format(top + 2, 1, top + 6, 5, {"type": "3_color_scale",
                          "min_color": "#F8FAFC", "mid_color": "#A5B4FC", "max_color": "#4338CA"})

    out = rfm.reset_index()[["CustomerID", "Segment", "R", "F", "M", "RFM", "Recency", "Frequency", "Monetary", "LastOrder"]]
    out.sort_values(["Segment", "Monetary"], ascending=[True, False]).to_excel(xw, sheet_name="Customers", index=False)
    cs = xw.sheets["Customers"]
    cs.set_column(0, 1, 16)
    cs.set_column(8, 8, 12, book.add_format({"num_format": "#,##0.00"}))
    cs.set_column(9, 9, 12, book.add_format({"num_format": "yyyy-mm-dd"}))
    cs.autofilter(0, 0, len(out), 9)
    cs.freeze_panes(1, 1)
print("written")

Turn a Pivot Table into a Heatmap in Excel covers colour scales on pivots in more detail.

Common pitfalls

• Counting rows as orders. Use the number of distinct order IDs for frequency.
• qcut on skewed counts. Duplicate bin edges raise an error; rank with method="first" before cutting.
• Forgetting to reverse recency. Fewer days since the last order is better, so it scores 5.
• Using today as the analysis date. Fix the date so results can be reproduced and compared month to month.
• Scores across different runs. Quintiles are relative to the current customer base; a score of 5 this month is not the same threshold as last month. Compare segments, not raw scores, over time.
• Returns and test orders. Remove them first, or refund-heavy customers look valuable.

Conclusion

Group orders by customer to get days since the last order, the number of distinct orders and the total spent, then score each measure 1 to 5 by quintile, ranking first so ties do not break qcut and reversing recency. Map score combinations to a few named segments with ordered rules, summarise customers and revenue per segment, and write the summary, a recency-by-frequency grid with a colour scale and the scored customer list to Excel.

Frequently asked questions

What is RFM analysis? A way of segmenting customers by recency (days since the last purchase), frequency (number of purchases) and monetary value (total spent). Each measure is scored, usually 1 to 5, and combinations of scores are grouped into segments.

Why does pd.qcut fail with duplicate bin edges? Because many customers share the same value, such as one order, so several quantile boundaries are equal. Rank the values with method="first" and cut the ranks instead.

How many RFM segments should I use? Few enough to act on: five to eight named segments is typical. The 125 raw score combinations are useful for analysis but not for campaigns.

How often should RFM segments be recalculated? Monthly is common for retail. Keep the analysis date fixed per run and track how many customers move between segments from one run to the next.

Related

• Up one level: Creating Pivot Tables from Excel Data — summarising Excel data by group.
• Build a Cohort Retention Table from Excel Data — retention by first-purchase month.
• Turn a Pivot Table into a Heatmap in Excel — colouring the grid.
• Calculate Percent of Total per Group in pandas — revenue shares by segment.
• Build a Weekly Sales Report — the same orders, by week.