Points for any useful design suggestions on how to pull off a 'Cohort Analysis'.
, used to measure customer lifetime and used to make spending decisions on attracting new customers vs. retaining existing ones.
Assuming a single table with subscription_start_dt and subscription_end_dt...
For example in the below image the current day is somewhere between 10/31/13 and end of November 2013.
There were 168 new customers in October 2013.
Of those 168, 99.4% (167) were still customers as of 10/31/13
There were 161 new customers in September 2013.
Of those 161, 98.14% (158) were still customers as of 9/30/13.
Of those 161, 95.65% (154) were still customers as of 10/31/13.
So the %age numbers will only go down across the grid, as the 161 number doesn’t change, as people leave. (assuming that people coming back will be represented as a new customer again on the line they ‘came back’.
January 2013 has ten columns as there are ten months between January 2013 and current end of month October 2013.
Note: Googling 'Cohort Analysis' and posting links here with no other commentary will be mercilessly mocked with child-like glee.