ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Multi-Row Formula all the way down

JordyMicheal
11 - Bolide

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 days
EOrders four times in 90 days
I2Orders three times in 90 Days
I1Orders twice in 90 Days
RCBuys every 90 Days
D1Misses a 90 Day Period
D2Misses a 180 Day Period
D3Misses a 270 Day Period
DOA 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:

JordyMicheal_0-1617900540110.png

 


Anything I left out? Just ask please

JordyMicheal
11 - Bolide

gentle bump

Luke_C
12 - Quasar

@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
17 - Castor

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

JordyMicheal
11 - Bolide

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

 

So here a customer would be starting in 2018

JordyMicheal_0-1618240995037.png

 

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

 

JordyMicheal_0-1618241466396.png

 



Hope that makes it a bit more clear

 

Labels