Pivottable - Subscriber Attachment Rate % (Cohort Analysis)

[davidlim]

hi guys, i'm been scratching my head to figure this out.


basically, i have a long table of subscriber info (Account ID, Activation Date, Termination Date, etc.)


i'm trying do a simple cohort analysis of subscriber tenure (by month).


a) I can determine the tenure by month using =DATEDIF(activation date, termination date, "m"). I'll get 0, 1, 2, 3, etc. of each subscriber's tenure.


b) I can plot this table into PivotTable. For Row Label, i put the Activation Date (grouped in year+month). For Column Label, i put the Tenure Month. For Values, i put in the count of Account IDs.


Now here's the conundrum: by nature of pivottable, each values are exclusive to column-vs-label. It does not overlap.


For tenure's cohort analysis, i'm trying to count how many subs were active for each activation month by tenure (attachment rate).


example (activation month Jan'13):


Tenure 0 (Jan'13): 10,000 of subs were active. it will always be 100% because all subs activated on same month.


Tenure 1 (Feb'13): 9,000 of subs were active who has tenure of 2 months (Jan+Feb). This also means 1,000 subs has churned on month 2. Note: this 9,000 is part of tenure 0 becuase these subs were active on previous month too (Jan).


Tenure 2 (Mar'13): 8,000 of subs were active with tenure of 3 months (Jan+Feb+Mar). So, on month 3, 2000 subs has churned (since Jan). Note: 8,000 subs were part of tenure 1 and tenure 0 (because there were active previously on Jan+Feb)).


this the distribution/cohort across tenure months will be lower and lower (as more subs churned).


it looks like a rolling/cumulative sub across tenure months, but I still couldnt wrap my head how to manipulate the table data/pivottable so that it can pivot the data required.


how this clearly explains the problem


thanks!

 

[davidlim]

best example (actually a direct example of what i planned) is here http://insideintercom.io/retention-cohorts-and-visualisations/


refer 1st diagram.

 

[keymaster]

@David... interesting question. I tried this using Pivot Tables. I could not get the exact result as what you wanted. But something very close. I was able to visualize % of accounts leaving by each month (opposite of retention). See this image.


http://img.chandoo.org/playground/cohort-analysis-david.png


If you want to do this,

[list type=decimal]
[*]Create a pivot from your data
[*]Put start month in rows, duration in columns
[*]Drag account id to values
Right click on value field > choose "Show values as" > % Running Total in option
Select base field as Duration
You are done!
[/list type=decimal]

Let me know if you come across a way to do this for retention using Pivots alone. I would be very keen to learn that.


PS: You can easily do this and more using Power Pivot

 

[davidlim]

thanks Chandoo


for retention, i found 2 ways:


1) pivot: ugly, in a sense that i have to create individual cohort month. e.g. Month 1, i'd create a formula to determine if the tenure >=1. Month 2, count if tenure >=2. It'd be impractical if the months are >10 or so.


2) simple SUMPRODUCT array. the formula =SUMPRODUCT((activation yearmonth list=activation ym)*(tenure list>=tenure)) should take care of it i'm using this on a 300K rows, and it's still fairly fast.