Forum Discussion
Calculating Recency with Auto-Updating Sales Data
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 ,
I'm not sure I understand what you are saying.
To clarify, my objective is to calculate the "Recency" for each customer individually, considering all their transactions up to the end of the selected month. For each customer, I want to determine the number of days since their last purchase as of the end of each month.
My ultimate goal is to understand if my marketing strategies are working.
For instance, if 80% of my customers have a recency of 90 days this month (meaning 80% last purchased 3 months ago) and I implement a marketing strategy to reduce recency, I want to see if the percentage of customers with a recency of 90 days has decreased after 2 months.
This would help me determine if the strategy was effective.
Therefore, I need to compare the previous period with the current period to see if there has been an improvement. Ideally, I would like to understand if there has been an improvement over multiple months.
Could you please explain more clearly what you mean by "period aggregation" and how it relates to my objective?
Thank you for your help!Let's assume you are looking at May data.
Customer A had their first transaction on May 1st
Customer B had their first transaction on May 20th
Both customers had their penultimate transaction on April 30th.
What's the recency for either customer?
Hi lbendlin ,
Thank you for the example. I calculate recency as the time from the last transaction to the last date of analysis (in this case, May 31).
So, for Customer A:If the transaction on May 1st is the last one in May, the recency is 31−1=30 days.
For Customer B:Similarly, if the transaction on May 20th is the last one in May, the recency is 31−20=11 days.
From what I understand, you are suggesting calculating the recency as the number of days between the penultimate purchase and the first purchase of the selected period. However, this would not be a correct calculation of recency and could lead to misleading results.
Example:Customer C:
First purchase of the period: May 15th
Penultimate purchase: January 1st
Using your suggested method, the recency would be 135 days, whereas calculating it as I suggest, it would be 16 days.With the first calculation method, I would think that this is a dormant customer who needs to be reactivated, while with the second calculation method, this is a customer I consider active, which they actually are.
Could you please confirm if my interpretation of your suggestion is correct?Thank you for your help!