Forum Discussion
Calculating Recency with Auto-Updating Sales Data
Hi everyone!
I need some assistance with a Power BI project related to calculating "Recency" from our sales data. Recency is a metric that measures the number of days since a customer's last purchase.
🔲 Here's the context:
I have a dataset named "Sales" which contains customer transaction records. Each row represents a single transaction with the following columns:
- date: The date when the purchase occurred
- customer_id: A unique identifier for the customer
- total_sales: The amount spent by the customer in that transaction
🔲 Important Notes:
- The date can repeat if multiple transactions occurred on the same day.
- The customer_id can repeat if the same customer made multiple purchases over time.
- The Sales database updates monthly.
🔲 Objective:
I want to calculate the "Recency" for each customer for each month of the year (or years) automatically, ensuring the calculation adapts as the Sales database updates. This should be a running recency, which is the number of days since the last purchase for each customer.
🔲 Specific Requirements:
- The recency calculation should include all customers up to the month being analyzed, regardless of whether they made a purchase in that specific month.
- I need to visualize this Recency data in a chart. For example, the chart could show the percentage or count of customers with a specific recency: how many customers have a recency of X days, how many have a recency of Y days, etc.
🔲 Example: If looking at the data for February 2024, I want to know the recency for all customers up to that point, including those who did not make any purchases in February 2024, either as a count or as a percentage of the total customers up to that month.
👉 You can find a PBIX and a Sales data sample here.
Thank you for your help!
16 Replies
I want to calculate the "Recency" for each customer for each month of the year (or years)This is generally considered a fallacy, as you have not specified where in the current month you have a transaction. A better approach will be to say "number of days between first transaction in current period and the penultimate transaction".
Hi lbendlin ,
Thank you for your input. I understand your point, but I believe there might be a misunderstanding.
In this context, "Recency" refers to the number of days since the last purchase made by each customer, not the number of days between the first and penultimate transactions. This metric is commonly used in customer behavior analysis to determine how recently a customer has engaged with the business.
▶️My goal is to calculate this "Recency" for each customer for each month, considering all their transactions up to that month.
⚫This means if a customer made their last purchase on January 15, 2024, the recency for this customer when looking at the data at the end of January 2024 will be 16 days; if looking at the data at the end of February 2024, the recency will be 46 days.Could you please help me with a way to calculate this in Power BI?
Thank you!
I didn't say "first". I said "first in selected period".
The period aggregation is the problem. Instead, treat each customer individually.
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.
