Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Customer Churn Model - Calculating Churn with some rules

Amadeu_gustavo
7 - Meteor

Dear designer,

 

I have done a workflow to calculate the churn of final customers in a segment that buys in an annually cycle from certain Distribution Branches.

 

This was a simple rule:

If the client bought a line of product in 2018 and never bought it again, he's a Loss Customer 18-19

If the client bought a line of product in 2018 and bought it again in 2019, he's a Regular Customer 18-19

If the client didn't bought none of a line of product in 2018, but bought it in 2019, he's a New Customer 18-19

 

Now I need to apply the following rule:

  • If the client bought a line of product from a Branche in Mar/19, and also bought in the next year in a +/-1 month (Fev/20, Mar/20 or Apr/20) he's a Regular Customer in 18-19;
  • If the client bought a line of product from a Branche in Mar/19, and didn't bought in the next year in a +/-1 month (Fev/20, Mar/20 or Apr/20) he's a Out-of-Date Customer in 18-19;
  • If the client bought a line of product from a Branche in Mar/19, and never bought it again, he's a Loss Customer in 18-19;
  • If the client didn't bought none of a line of product in 2018, but bought it in 2019, he's a New Costumer 18-19.

The churn key is both client and line of product.

I tried to use the Generate Rows tool with:

 

DateTimeAdd([Month],1,"month") and DateTimeAdd([Month],-1,"month")

 

 before a Join, but I couldn't run it (even with the correct type of data).

 

I've attached some examples that should bring something like the following results:

 

Line of ProductDistribuition BrancheClient NameClient IDChurn 17-18Churn 18-19Churn 19-20
Line DSouth DistJohn Mark22222222222Regular CustomerOut-of-date Customernull
Line CSouth DistJohn Mark22222222222Out-of-date CustomerLoss Customernull
Line ASouth DistJohn Mark22222222222New CustomerOut-of-date CustomerRegular Customer
Line ANorth DistDarik Miller11111111New CustomerLoss Customernull
Line BNorth DistDarik Miller11111111Regular CustomerOut-of-date CustomerLoss Customer
Line DNorth DistDarik Miller11111111Regular CustomerOut-of-date CustomerOut-of-date Customer

 

After the Generate Rows tool I have no idea where to begin.

Can anyone help me with this?

 

Thanks since now!

1 REPLY 1
ggruccio
ACE Emeritus
ACE Emeritus

Hi @Amadeu_gustavo,

 

There may be a simpler way to resolve this - using the Multi-Row Formula tool you could check up and down within the file....

 

...for instance if Row 1 has an order date of May 22, 2019 and Row 2 an order date May 22, 2020 ...

 

You could apply the datetimediff formula to look at the next row as such...(just an example)...but you can also group by customer etc.

 

ggruccio_1-1590160571292.png

 

ggruccio_0-1590159793495.png

 

For instance the output of this is going to be:

ggruccio_3-1590160820806.png

 

 

Labels