This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Been staring at a problem too long so I'm looking for some help out of it.
I have some sample data attached.
I want to follow customers and move them from groupings of regular, to incline/decline a few levels, to champion or dormant.
Basically it's a big frequency exercise. I've built a few different solutions and still haven't gotten it perfect.
Here are the groupings:
Orders more than five times in 90 days
Orders four times in 90 days
Orders three times in 90 Days
Orders twice in 90 Days
Buys every 90 Days
Misses a 90 Day Period
Misses a 180 Day Period
Misses a 270 Day Period
A full year without shopping
If a customerID orders every 90 days, they are within the average frequency. If they take actions above or below that, I want to move them up or down the levels. If they take multiple actions they could go up multiple levels.
So a customerID is going to move between groupings based on how often they order. I want to evaluate that on yearly calendar date, while the groupings are based on days since first purchase and time between purchase. Any help would be greatly appreciated. An end result might be structured like:
I'm not quite following the logic and it's tough to tell what the screenshot corresponds too. Could you provide example results for a few customer IDs that might illustrate the logic you're looking for a bit better?