Survival Visual

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!