Customer Attrition

[Vapour02]

Hi all , I have a list of customer name , customer code, billing date and sale value. I need to identify which customers were active for all months and which got inactive in which month. Also need to highlight the new customers who got added in the months ahead. To check the customer attrition. I tried generating rows used summarize tool on min and max month but unable to get a desired results. Kindly help with a better solution.

Attached is the same data and expected output for reference.

[Qiu]

Sample data please

[Vapour02]

Hey @Qiu please find attached the sample data and expected output.

[OTrieger]

@Vapour02 
When working with dates you have to ensure that the data has Date data type, in Alteryx the date need to have this format YYYY-MM-DD (2025-08-19). Only when you will have your data in that format you will be able to use Min and Max and get the desired results. So ensure that the date column is in the correct format and data type.

[Pilsner]

Hello @Vapour02 

I've not matched your sample output exactly as I was unsure how to determine which people to calculate subsequent "inactive" rows for and which people to just drop from the data set, but I have got a solution that matches closely. Here are my steps:

1) Clean the data. In this step I use regex to make sure all the dates are properly formatted and then I use a formula tool to create a standardised "first of month" column.

2) Next I find the minimum date per person, and generate one row of data per month between their minimum row, and todays date. 

3) After joining this generated list back to the original data we have both the inactive and active records on separate output anchors of the join tool. I use the formula tool to label them and the date time tool to fin the month number. 

4) Finally, the union tool can be used to combine all the records.

I have attached and annotated the workflow below to try and assist further. 

Please let me know how you get on or if you have any questions.

Regards - Pilsner

[Qiu]

@Vapour02 
We are not allowed to sort the data first?
For Reeva, with same code, appears twice as new customer.

[Vapour02]

@Qiu My bad it should be new customer for 06 and Active for 07 we can sort data