Customer RFM Segmentation in Power BI
The report scores every customer on how recently they bought, how often they order and how much they spend, then splits your base into 10 segments, from "Champions" to "Lost". Each segment comes with a ready-made action. You see how many customers and what share of revenue are at risk right now — and whom to call first.
How RFM analysis works
Each customer gets a score from 1 to 5 on three parameters. Together they make up an RFM code: for example, 555 means the customer bought recently, often and for large amounts. Based on the code, the customer is automatically placed into one of 10 segments.
R — Recency
How many days have passed since the last purchase. The strongest churn signal: a customer who used to buy every month and has been silent for six months is already looking at a competitor.
F — Frequency
How many orders the customer placed during the period. It separates a regular partner from a one-off buyer with the same total spend.
M — Monetary
How much the customer has spent in total. It shows how much value you lose if the customer leaves and how much is worth investing in retaining them.
What's inside the report
Two pages. The first, "Customer RFM segmentation", shows the structure of the base and the action plan. The second, "Customers: details", is a working tool for sales managers, with filters and a list of specific customers.
KPIs: how much revenue is at risk
Revenue, number of customers, average order value, plus two figures you won't find in standard sales reports: how many customers sit in churn-risk segments and what share of revenue they generate.
Customers and revenue by segment
A treemap shows the number of customers in each of the 10 segments: Champions, Loyal, New, Promising, Need attention, About to sleep, At risk, Can't lose them, Hibernating, Lost. Next to it is the revenue share of each segment.
R × FM matrix
A heatmap of customer counts by recency (R) and a combined frequency-and-monetary score (FM). You can see whether the zone of recent, active customers is growing or whether the base is drifting towards long-inactive ones.
Action plan by segment
A table with the number of customers, a description of their behaviour and a recommended action for each segment. Reward Champions and ask them for reviews, guide New customers through onboarding with a discount on their second order, send a reactivation campaign to those About to sleep.
Customer map and monthly revenue
A "frequency × recency" scatter chart: point size shows total spend, colour shows the segment. A column chart shows each segment's contribution to revenue by month over several years. Average R (days), F and M for the filtered selection are shown as KPIs.
⚠ Customer action list
A customer table with region, sales manager, segment, RFM code, recency in days, number of orders, total spend and last purchase date. Filters by segment, region, manager and customer type turn it into today's call list.
Who gets the most value from the RFM report
Three roles for whom behavioural customer segmentation is an everyday working tool.
Marketing director
Instead of identical mailings for everyone, gets 10 audiences with ready-made scenarios. Justifies the retention budget with numbers: how many customers are in the risk zone and how much revenue they carry.
Head of sales and sales managers
Filters the details page by their region or manager and sees which customers haven't ordered for a while, how much they used to buy and when their last purchase was. Gets a ready-made call plan.
Commercial director and CEO
Sees who revenue depends on and how each segment's contribution changes month by month. Can judge whether retention is working before a decline shows up in the P&L.
Where the report gets its data
RFM needs only sales history: customer, order date and amount. Region, sales manager and customer type add filters.
Microsoft Dynamics 365
Orders, invoices, accounts and account managers straight from Dataverse. RFM scores and segments are recalculated with every data refresh.
ERP and accounting systems
Sales and payments from BAS, 1C, Business Central or another ERP. For an accurate monetary (M) score, we use actual shipments net of returns.
Excel and SharePoint
If sales history is kept in spreadsheets, we connect them via Power Query. The report refreshes automatically when the files change in SharePoint or OneDrive.
Sending segments to email and CRM
Optionally, we set up Power Automate: a customer who moves into "At risk" automatically gets a task for their manager in the CRM or is added to a reactivation mailing audience.
Frequently asked questions
Which segments does the report include?
What does an RFM code such as 555 or 155 mean?
Which RFM parameters can be configured?
Can segments be sent to an email platform or CRM?
How is RFM different from ABC analysis?
Ready to launch RFM segmentation?
Turn your customer base into a managed asset
Instead of manual segmentation in Excel, you get a report that recalculates segments with every data refresh. Instead of the same mailings for everyone, your team works with specific actions for each segment and knows whom to call today.