Forum Discussion
Calculating Recency with Auto-Updating Sales Data
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 ,
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!
