The goal of any business it to have repeat customers who are happy, but how can you tell if they aren’t? Well, one way to tell is their buying habits.
This came up recently, so I jumped into Super Store to try and figure it out… nothing ground-breaking here, but I’m writing about it here as a reminder to myself 🙂
Time Between Purchases
High volume retailers probably look at things at a day or week level… seeing repeat customers screams Customer Loyalty. Super Store is a little more slow moving, so I wanted to see months between Purchases.
First, we need to find the Most Recent Order, and we can do that with a simple Fixed LOD.
Next, we need to figure out the Previous Purchase Date. Another LOG, with some nested If logic referencing the Most Recent Order calc from above
Next, we figure out the time frame between the two dates. Again, I’m doing months, so the calc looks something list this:
Months Between Purchase
DATEDIFF(‘month’,[Previous Order],[Most Recent Order])
Next, we need a parameter to allow us to change how we define “Loyalty”
Lastly, we need a filter to bring all the parts together, so a simple Boolean calculation will do the trick
Time Between Orders
[Months Between Purchase] >= [Months Between]
Now, we can put it all together!
Time Since Last Purchase
But what about lost customers? Those that came in and purchased stuff and were never seen again? Using the Most Recent Order calc, we can follow some very similar steps…
In the real world, you’d probably user TODAY(), but since we have a static data set, I had to use an anchor date. I created a parameter for that, and then created the following calculation.
Months Since List Purchase
DATEDIFF(‘month’,[Most Recent Order],[Anchor Date])
Create a parameter to control how long you want to look back..
Next, we create another Boolean calculation..
Time Since Last Order
[Month Since Last Purchase] >= [Months Since]
…then put it all together!
