Customer analytics · B2B
RFM analysis
B2B hospitality distributor · 400 customers · customer segmentation in Power BI
scroll ↓
The problem
Sales were falling, but it wasn’t clear why.
The customer base was growing, but revenue wasn’t keeping up. The sales team had the data, but no simple way to know which customers were still active, which were buying less often and which might be about to disappear.
Before thinking about winning more customers, we had to look at the ones already there.
The goal
Understanding how each customer was buying.
I analysed the 400 customers starting from three very simple questions:
When did they last buy? How often do they buy? How much do they spend?
With those three variables I built an RFM model to spot different buying behaviours and, above all, find where revenue might be at risk.
customers analysed
behaviour segments
revenue at risk identified
My approach
Three variables to understand a customer base.
The RFM model starts from a fairly simple idea: a customer’s recent behaviour says much more than their historical value on its own.
For each customer I calculated:
Recency
Days since their last purchase.
Frequency
Number of orders placed.
Monetary
Total amount bought.
The three values are turned into scores using dynamic percentiles instead of fixed cut-offs. That way, the segmentation adapts to how customers are really distributed and doesn’t depend on thresholds set by hand.
The result?
7 behaviour segments
Champions
The most active, highest-value customers.
Loyal
Customers with a solid relationship who are starting to show signs of losing activity.
Potential
Customers with room to grow their relationship with the company.
At Risk
Customers whose activity is starting to deteriorate.
Needs Attention
Customers who need follow-up before their activity keeps falling.
Average
Customers with middle-of-the-road behaviour.
Lost
Customers who have practically stopped buying.
Data model
RFM lives inside the model, not apart from it.
I built a star schema with Fact_Pedidos as the fact table and five dimensions: Dim_Cliente · Dim_Fecha · Dim_Producto · Dim_Comercial · Dim_TipoEstablecimiento.
The RFM scores and the final segment live in Dim_Cliente. That way, the segmentation can be crossed with the rest of the model to analyse it by area, sales rep, venue type, product or period.
The dashboard was built around four questions:
How is the business doing?
How are customers distributed?
How is revenue evolving?
What should the sales team do with each segment?
That last part became a table of sales actions generated directly from DAX using HTML, with no external visual.
Schema
Visualisation in Power BI
One page to go from data to action.
A signal that didn’t show up in the total.
111 Loyal customers — €198,000 at risk.
This group accounted for a large share of revenue, but its recency had been getting worse for months.
They weren’t customers who had suddenly disappeared. They still had a valuable buying history, but they were buying less and less.
That’s where segmentation changes the reading: the problem stops being «sales are falling» and becomes «these customers need attention».
Key findings
Two segments. Two completely different situations.
Loyal
Customers with a significant buying history who were losing activity.
Their recency had increased over recent months while their historical spend level was still significant. It was a particularly interesting group for a reactivation strategy.
Champions
The most active and profitable customers in the base.
Their average recency was just 14 days, a clear sign they kept up a frequent buying relationship.
The same dashboard that spots customers at risk also shows who is holding the business up.
Tools
Tech stack
Power BI
Modelling, visualisation and dashboard interaction.
DAX
Calculating Recency, Frequency and Monetary, percentiles, segmentation and generating the sales action table.
Power Query / M
Data preparation and transformation.
Star schema
Separating facts and dimensions to keep the model flexible and easy to analyse.
HTML in DAX
A sales action table generated inside a measure, with no external visual.
Dynamic percentiles
Segmentation cut-offs adapted to how customers are really distributed.
What I take away from the project
The hardest part wasn’t calculating RFM.
The formula is well known. The interesting part was deciding how to turn those calculations into a segmentation that made sense for the business.
A fixed cut-off can work on one dataset and stop making sense when the customer distribution changes. That’s why I chose dynamic percentiles: the model adapts to the data instead of forcing the data to adapt to the model.
I also ran into the limits of generating HTML from DAX. It works in some cases, but it doesn’t replace a native visual once interaction or maintenance start to get complicated.
In the end, the project left me with a fairly simple idea:
A good analysis doesn’t just have to say what’s happening. It has to help you see where to look next.
From «sales are falling» to knowing exactly where to look.
Interactive dashboard published in the Power BI Service
Full Power BI portfolio