Identify changes in customer status

[FilipViterra]

Hi guys,

My data is shaped like you see attached.

Is there a way to always check versus last year to see these 3 things:

1. Customers that are Active in current year and New or Active last year, flag them as recurring customers

2. Customer that are New this year stay New

3. Customer that were New or Active last year, but now they are inactive or just not visible anymore, flag them as Lost

Ultimately enabling me to make a waterfall chart with active base, lost, won customers and actually see which customers fall in which categories.

Anyone can guide a grasshopper in the art of Alteryx?

Thank you,

Filip

[DataNath]

Hey @FilipViterra, how does something like this look?

The two filters at the start leverage the DateTimeToday() function to fetch the current date, get the year from this and then split the data, giving us two streams of the current year and current year-1 for the previous year. We then conduct a join based on the client number so we get the two years alongside each other, and then use our if statement to check the 2 status' as you've mentioned above.

Now, for 'New' customers in the current year, these won't find a match in the previous year of course, and they come out of the L anchor of the join, which we then union back into the data set and the 'New' flag is automatically applied to them.

One thing to mention: Customers that became inactive (would be 'Lost') in the previous year fall out of the right side of the join. I haven't brought these back into the data as they would already be 'Lost' and therefore not form part of the behavioural analysis of the current year. However, if you do want them, let us know as the workflow will need amending slightly.

Hope this helps - any questions feel free to shout!

[FilipViterra]

Hey there,

Thanks a lot for the help, it's almost what I wanted to achieve.

Is there a way to do that dynamically for all years? We're trying to get a full history view, not only the last 2 years.

Thanks again,

Filip

[DataNath]

@FilipViterra was just having another think about this now and would cross-tabbing the data and having it like this be helpful? Each client has their own line that you can track their journey through:

If not, if you can provide full details of what the ideal outcome would look like then that'd be great.