Multi-Row Formula all the way down

Question

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:

HEOrders more than five times in 90 daysEOrders four times in 90 daysI2Orders three times in 90 DaysI1Orders twice in 90 DaysRCBuys every 90 DaysD1Misses a 90 Day PeriodD2Misses a 180 Day PeriodD3Misses a 270 Day PeriodDOA 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

sampledata.yxdb

JordyMicheal · Answer

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

Qiu · Answer

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

Luke_C · Answer

@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?