Multi-Row Formula all the way down

[JordyMicheal]

Hey all,

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:

HE	Orders more than five times in 90 days
E	Orders four times in 90 days
I2	Orders three times in 90 Days
I1	Orders twice in 90 Days
RC	Buys every 90 Days
D1	Misses a 90 Day Period
D2	Misses a 180 Day Period
D3	Misses a 270 Day Period
DO	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:

Anything I left out? Just ask please

[JordyMicheal]

gentle bump

[Luke_C]

@JordyMicheal 

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?

[Qiu]

@JordyMicheal 
Agree with @Luke_C , a bit clear logic is necesary for me also😎

[JordyMicheal]

Not an issue at all.
I have trouble reading it back myself hahaha.

So here a customer would be starting in 2018

They made a first purchase, and did not for another 277 days.

I want to use an append fields to generate each customer, with each MONTH between 2018-01 and 2020-12 and their "Status" for that month.

In this example = 2018-08 would be the new customer flag. In 90 days from there, they would decline a level into decline 1 (since they did not make a purchase).

So in 2018-11 I would expect to see a "Decline 1" as a flag and that would carry for another 90 days if that don't take an action, they decline again.

So I want to fill in these blanks if you will with rolling actions

Hope that makes it a bit more clear