Hello All,
I am trying to create a specific visual for survivorship of customers. I have read through various articles on customer churn etc. and they have led to some vary robust visuals. However, I still need to replicate a specific visualization that I describe below.
My data source is a single table that contains MTD sales records for customers, the table was created by appending monthly extracts of customer's that transacted during the month. Therefore, a customer would not have a record within the monthly extract if they didnt have any sales. There are three key columns: Customer ID, MTD Sales (never zero otherwise customer would not appear as a record), Processing Date of the extract (the key to knowing which month MTD sales is referring to).
The visual I would like to create is as follows: Count the # of customers as of a particular processing date (Time = 0) and index to 1 or 100. The count the # of Customers in each next month that were also active at Time=0 and then index based on the # of Customer at Time=0. In other words, 100 surviving of 200 starting at Time=0 would be an index of 0.5. This is easy enough to do for a single Processing Date. However, I would like to automatically do this same analysis for each Processing Date as a Time=0 and overlay them onto a visual such as a line graph. The X-Axis would be the Time component 0 to N The legend would be the Processing Date at Time=0, in other words the line will be all customers that were active as of that processing date as Time=0. Lastly, the Y-Axis would just be the Index.
Probably not the best visual since it doesnt address seasonality and customers will overlap. Eitherway, any pointers over which calculated columns and measures I should be looking at would be very helpful.
Thanks!
