Forum Discussion
Calculating Recency with Auto-Updating Sales Data
Hi lbendlin ,
The complete project is a bit more complex, and I tried to simplify it here.
What I need is to perform an RFM analysis to segment customers based on three parameters:
Recency (days since the last purchase),
Frequency (purchase frequency),
Monetary (spending).
Then, I segment customers based on the combinations of recency, frequency, and monetary. So far, I have no issues with this part.
Each RFM segment is then targeted with specific email marketing strategies, for example, aiming to increase a customer's spending, to encourage more frequent purchases, or to decrease their recency (or a combination of these goals).
👉What insights/actions should come out of it?
I need to understand if my email marketing strategies are working. To do this, I want to conduct a customer migration analysis to understand how many (or what percentage) of customers move from one segment to another.
For example, if the "TOP" customer category has not increased, it might indicate that my email marketing strategy is not working and needs to be adjusted.
Here are the specific details of the categories for a concrete example (I've no issues with this part):
Each customer is given a label based on their Recency, Frequency, and Monetary values.
Recency categories:
Months passed since the previous transaction (up to the current date) ≤ 1 month ago: "HOT"
Months passed since the previous transaction (up to the current date) from 1 to 6 months ago: "RECENT"
Months passed since the previous transaction (up to the current date) from 6 to 12 months ago: "INACTIVE"
Months passed since the previous transaction (up to the current date) > 12 months ago: "HIBERNATING"
Frequency categories:
Number of purchases up to the current date ≤ 1: "NEW"
Number of purchases up to the current date between 2 and 3: "OCCASIONAL"
Number of purchases up to the current date between 4 and 7: "FREQUENT"
Number of purchases up to the current date > 7: "LOYAL"
Monetary categories:
Total sales up to the current date ≤ 50 €, then: "COPPER"
Total sales up to the current date between 50 and 100 €, then: "BRONZE"
Total sales up to the current date between 100 and 200 €, then: "SILVER"
Total sales up to the current date > 200 €, then: "GOLD"
Then, there are RFM categories as combinations of recency, frequency, and monetary:
NEW:
Recency: < 6 months
AND Frequency: = 1 purchase
(Any Monetary)
SLEEPERS:
Recency: between 6 and 12 months ago
Any Frequency
Any Monetary
HABITUAL:
Recency: < 6 months
AND Frequency: > 2 purchases
AND Monetary: between 50 and 200 euros
TOP:
Recency: < 6 months
AND Frequency: > 2 purchases
AND Monetary: > 200 euros
So far, so good: I have no issues segmenting customers based on their current status; the real challenge arises when I want to conduct a customer migration analysis to find out HOW MANY customers move from one segment to another over time.
Thank you again for your help!
Thank you for the details, much appreciated. So i guess you have to accept the slight inconsistencies that come with the monthly bucketing. For Segment moves I would propose to use a ribbon chart, or a Sankey chart (that one requires additional data prep). To calculate the data you will have to materialize the RFM parameters for each customer and each month (either in a calculated table or through a measure), and then you can plot them accordingly. You will need to disconnect your Calendar table.
I'll have a look at your sample PBIX.
Here's how a sample approach for recency for a single customer.
Hi lbendlin ,
Thank you for your suggestions! I tried both the Ribbon chart and the Sankey chart while attempting to avoid creating a calculated table with every combination month-customer_id because I believe that in the long run, it could significantly slow down the system.
Since the Sales file updates over time, the combinations of month and customer_id could become extremely large.Is there a way to achieve the same goal without slowing down the system and ensuring everything is automatic?
- Is there a way to create the calendar with all month-customer_id combinations directly with a calculated measure and then use these data to create the visual?
- Is there a way to ensure that the table created with the measure and the visual update automatically over time?
Thank you again for your help!
Is there a way to create the calendar with all month-customer_id combinations directly with a calculated measure and then use these data to create the visual?Measures return scalar values. They cannot hold tables.
Is there a way to ensure that the table created with the measure and the visual update automatically over time?Not sure what you are asking. Assuming your data source is in Import mode this will be taken care of during the semantic model refresh.
Hi lbendlin,
I tried to replicate your approach below:
lbendlin wrote:Here's how a sample approach for recency for a single customer.
I created the measures to calculate Recency as you showed me and I also added the measures for Frequency and Monetary:
It works! However, I can't understand how from here I can visualize the customer migration, for example with a ribbon chart.
I suppose I should somehow assign each customer_id a category based on their current condition, and then count how many customer_ids are in that category, but I can't find a way to do this with the calculated measures.
I tried to create a calculated measure for the RFM segments, but it doesn't work.
Could you guide me?
I updated the PBIX file with these new measures.
Again, thank you!If you still want to avoid using a calculated table the next step is to prepare your independent dimensions (dates and customers) and then calculate the measures for each of the periods.
Without that the charts may not give you much