RFM analysis is the customer-side equivalent of ABC analysis. The pattern is: classify customers by behaviour, drive different marketing and sales actions for each segment, and watch the campaign ROI improve. The seven steps below are the standard pattern we ship on every e-commerce, D2C, and B2B SaaS engagement.
The seven steps
01
Pull 12 months of customer orders
Export the orders table for the last 12 months. Each row is an order with customer ID, order date, and order value. The data lands in a Table (Ctrl+T). The senior uses Power Query to merge with the customer master to get the customer name, region, and signup date.
02
Calculate Recency per customer
Days since the last order. =TODAY()-MAXIFS(orders[Date], orders[CustomerID], A1). Lower is better. The senior creates a Customer Summary table on a hidden tab with one row per customer, then calculates the three RFM measures with MAXIFS, COUNTIFS, and SUMIFS.
03
Calculate Frequency per customer
Number of orders in the last 12 months. =COUNTIFS(orders[CustomerID], A1, orders[Date], ">="&DATE(YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY()))). Higher is better. The senior wraps the date calculation in DATE to make it locale-independent.
04
Calculate Monetary per customer
Sum of revenue in the last 12 months. =SUMIFS(orders[Revenue], orders[CustomerID], A1, orders[Date], ">="&DATE(YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY()))). Higher is better. The senior uses revenue by default; the right answer is gross margin if the data is available.
05
Score 1-5 on each dimension
Use PERCENTRANK or QUARTILE to assign scores. =PERCENTRANK($B$2:$B$N, B2, 4) returns 0-1; multiply by 4 and round up to get 1-5. Higher R score for lower Recency (recent). Higher F score for higher Frequency. Higher M score for higher Monetary. The senior uses the same scoring across all three dimensions for consistency.
06
Combine into an RFM code
Concatenate the three scores into a 3-digit code: =R & F & M. 555 is the best customer (recent, frequent, high value). 111 is the worst (long ago, once, low value). The senior uses the RFM code as the segment key in the marketing automation.
07
Map codes to segments and pivot
The senior uses a lookup table on a hidden tab: RFM code range → segment name (Champions, Loyal, At Risk, Hibernating, etc.). XLOOKUP at the call site. Pivot the segments to get the count, revenue, and margin per segment. The pivot is the input to the marketing and sales action.
The segment map
The RFM code is the key; the segment name is the action. The standard map below is the one we ship on most engagements. The exact mapping depends on the business, the margins, and the marketing channels.
Champions
555-554Action: Reward. Early access to new products. Loyalty programme.
Loyal Customers
551-552Action: Upsell. Cross-sell. Cross-channel engagement.
Potential Loyalists
513-533Action: Onboarding. Education. Free trial of higher tier.
At Risk
411-452Action: Win back. Personal outreach. Special offer.
Hibernating
111-211Action: Ignore. Reactivation campaign at quarter end. Do not over-invest.
Lost
111-112Action: Ignore. Reactivation only if cost per send is below LTV/customer count.
What the operational impact looks like
The typical RFM engagement improves email campaign revenue by 30-50% in the first quarter. The mechanism is: the right offer goes to the right customer at the right time. Champions get loyalty rewards, not generic promotions. At Risk customers get win-back offers, not new product announcements. Hibernating customers are excluded from the heavy campaigns. The senior ships the segments in two weeks, integrates with the marketing automation in another week, and the campaign revenue shows up in the next quarter's reporting.
