Customer Attrition

Question

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.

Customer_Data.xlsx

Vapour02 · Answer

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

Qiu · Answer

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

Pilsner · Answer

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

Attrition.yxzp