Customer Churn Model - Calculating Churn with some rules

[Amadeu_gustavo]

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 Product	Distribuition Branche	Client Name	Client ID	Churn 17-18	Churn 18-19	Churn 19-20
Line D	South Dist	John Mark	22222222222	Regular Customer	Out-of-date Customer	null
Line C	South Dist	John Mark	22222222222	Out-of-date Customer	Loss Customer	null
Line A	South Dist	John Mark	22222222222	New Customer	Out-of-date Customer	Regular Customer
Line A	North Dist	Darik Miller	11111111	New Customer	Loss Customer	null
Line B	North Dist	Darik Miller	11111111	Regular Customer	Out-of-date Customer	Loss Customer
Line D	North Dist	Darik Miller	11111111	Regular Customer	Out-of-date Customer	Out-of-date Customer

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

Can anyone help me with this?

Thanks since now!

[ggruccio · Accepted solution]

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.

For instance the output of this is going to be: