ChristophJanz_SaaSCohortAnalysis
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

Comment only
 
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1
Please note that this spreadsheet has been created by Christoph Janz of Point Nine Capital. You can find out more about the article here: http://andrewchen.co/the-easiest-spreadsheet-for-churn-mrr-and-cohort-analysis-guest-post/
2
3

Notes

1) The blue values need to be entered. Everything else is calculated automatically.

2) MRR increases of a cohort in spite of customer churn are the result of "account expansions" – customers paying you more by adding seats, upgrading to a higher plan, buying premium features etc.

3) If you want to calculate your CLTV, "costs of good sold" should be subtracted from revenues (payment, hosting, customer service).

4) CACs should be "all in", including sales and marketing expenses and salaries and all costs associated with customer acquisition.

5) If you want to know if you're acquiring customers profitably using your sales & marketing initiatives, include only customers won via those initiatives in the analysis and exclude all others.

6) For simplicity purposes, this template contains only 10 months of data. If you have older cohorts, please extend the sheet accordingly.

Disclaimer: Please be aware that his model may be inaccurate, incorrect or misleading, use it at your own risk, yada yada yada.

Feel free to distribute. If you distribute a modified version, please make it clear that you've made changes.
4
Customer Churn
5
ConversionNew
6
monthcustomers# of retained customers in monthA1# of retained customers in lifetime monthA2# of churned customers in lifetime monthA3
7
Jan-13Feb-13Mar-13Apr-13May-13Jun-13Jul-13Aug-13Sep-13Oct-1301234567890123456789
8
Jan-138078757270696766666564787572706967666665642332121011
9
Feb-1388888682787776737270888682787776737270024411312
10
Mar-1310510310398949290868210310398949290868220542244
11
Apr-1311010710610299979290107106102999792903143252
12
May-13115114112105989796114112105989796127711
13
Jun-1312812812211911511012812211911511006345
14
Jul-131371361291221181361291221181774
15
Aug-131511491451351491451352410
16
Sep-1316115815415815434
17
Oct-131681671671
18
781632613624655806968219521,086
19
20
% of retained customers in lifetime monthB1% of churned customers in lifetime month (relative to base number)B2% of churned customers in lifetime month (relative to previous month)B3
21
012345678901234567890123456789
22
Jan-138097.50%93.75%90.00%87.50%86.25%83.75%82.50%82.50%81.25%80.00%2.50%3.75%3.75%2.50%1.25%2.50%1.25%0.00%1.25%1.25%2.50%3.85%4.00%2.78%1.43%2.90%1.49%0.00%1.52%1.54%
23
Feb-1388100.00%97.73%93.18%88.64%87.50%86.36%82.95%81.82%79.55%0.00%2.27%4.55%4.55%1.14%1.14%3.41%1.14%2.27%0.00%2.27%4.65%4.88%1.28%1.30%3.95%1.37%2.78%
24
Mar-1310598.10%98.10%93.33%89.52%87.62%85.71%81.90%78.10%1.90%0.00%4.76%3.81%1.90%1.90%3.81%3.81%1.90%0.00%4.85%4.08%2.13%2.17%4.44%4.65%
25
Apr-1311097.27%96.36%92.73%90.00%88.18%83.64%81.82%2.73%0.91%3.64%2.73%1.82%4.55%1.82%2.73%0.93%3.77%2.94%2.02%5.15%2.17%
26
May-1311599.13%97.39%91.30%85.22%84.35%83.48%0.87%1.74%6.09%6.09%0.87%0.87%0.87%1.75%6.25%6.67%1.02%1.03%
27
Jun-13128100.00%95.31%92.97%89.84%85.94%0.00%4.69%2.34%3.13%3.91%0.00%4.69%2.46%3.36%4.35%
28
Jul-1313799.27%94.16%89.05%86.13%0.73%5.11%5.11%2.92%0.73%5.15%5.43%3.28%
29
Aug-1315198.68%96.03%89.40%1.32%2.65%6.62%1.32%2.68%6.90%
30
Sep-1316198.14%95.65%1.86%2.48%1.86%2.53%
31
Oct-1316899.40%0.60%0.60%
32
98.79%96.00%91.36%88.07%86.58%84.54%82.25%80.59%80.36%80.00%1.21%2.70%4.70%3.67%1.92%2.21%2.61%1.83%1.79%1.25%1.21%2.73%4.90%4.00%2.17%2.55%3.08%2.22%2.17%1.54%
33
34
35
36
MRR Churn
37
ConversionNewIf you have any questions or comments:
38
monthMRRCohort MRR in monthC1Retained MRR in lifetime monthC2MRR churn in lifetime monthC3christoph@pointninecap.com
39
Jan-13Feb-13Mar-13Apr-13
May-13
Jun-13Jul-13Aug-13Sep-13Oct-1301234567890123456789
40
Jan-13 $ 7,851 $7,650$7,536$7,234$6,982$6,876$6,780$6,678$6,567$6,454$6,342$7,650$7,536$7,234$6,982$6,876$6,780$6,678$6,567$6,454$6,342$201$114$302$252$106$96$102$111$113$112
41
Feb-13 $ 7,988 $7,988$7,688$7,232$6,998$6,890$6,789$6,679$6,589$6,459$7,988$7,688$7,232$6,998$6,890$6,789$6,679$6,589$6,459$0$300$456$234$108$101$110$90$130
42
Mar-13 $ 9,234 $9,145$8,897$8,678$8,569$8,432$8,352$8,152$7,988$9,145$8,897$8,678$8,569$8,432$8,352$8,152$7,988$89$248$219$109$137$80$200$164Created by Christoph Janz
43
Apr-13 $ 10,123 $9,988$9,657$9,222$8,901$9,201$9,468$9,688$9,988$9,657$9,222$8,901$9,201$9,468$9,688$135$331$435$321-$300-$267-$220at Point Nine Capital
44
May-13 $ 11,788 $11,678$11,546$11,321$11,678$11,755$11,868$11,678$11,546$11,321$11,678$11,755$11,868$110$132$225-$357-$77-$113
45
Jun-13 $ 12,876 $12,876$12,546$12,433$12,399$12,976$12,876$12,546$12,433$12,399$12,976$0$330$113$34-$577
46
Jul-13 $ 13,212 $12,987$12,756$13,345$13,456$12,987$12,756$13,345$13,456$225$231-$589-$111
47
Aug-13 $ 14,789 $14,786$14,201$14,897$14,786$14,201$14,897$3$585-$696
48
Sep-13 $ 15,786 $15,676$14,988$15,676$14,988$110$688
49
Oct-13 $ 16,119 $15,897$15,897$222
50
$7,650$15,524$24,067$33,099$43,887$55,883$67,654$82,452$98,039$114,559
51
52
% of retained MRR in lifetime monthD1% MRR churn in lifetime monthD2% MRR churn in lifetime month (relative to previous month)D3
53
012345678901234567890123456789
54
Jan-13$7,85197.44%95.99%92.14%88.93%87.58%86.36%85.06%83.65%82.21%80.78%2.56%1.45%3.85%3.21%1.35%1.22%1.30%1.41%1.44%1.43%2.56%1.49%4.01%3.48%1.52%1.40%1.50%1.66%1.72%1.74%
55
Feb-13$7,988100.00%96.24%90.54%87.61%86.25%84.99%83.61%82.49%80.86%0.00%3.76%5.71%2.93%1.35%1.26%1.38%1.13%1.63%0.00%3.76%5.93%3.24%1.54%1.47%1.62%1.35%1.97%
56
Mar-13$9,23499.04%96.35%93.98%92.80%91.31%90.45%88.28%86.51%0.96%2.69%2.37%1.18%1.48%0.87%2.17%1.78%0.96%2.71%2.46%1.26%1.60%0.95%2.39%2.01%
57
Apr-13$10,12398.67%95.40%91.10%87.93%90.89%93.53%95.70%1.33%3.27%4.30%3.17%-2.96%-2.64%-2.17%1.33%3.31%4.50%3.48%-3.37%-2.90%-2.32%
58
May-13$11,78899.07%97.95%96.04%99.07%99.72%100.68%0.93%1.12%1.91%-3.03%-0.65%-0.96%0.93%1.13%1.95%-3.15%-0.66%-0.96%
59
Jun-13$12,876100.00%97.44%96.56%96.30%100.78%0.00%2.56%0.88%0.26%-4.48%0.00%2.56%0.90%0.27%-4.65%
60
Jul-13$13,21298.30%96.55%101.01%101.85%1.70%1.75%-4.46%-0.84%1.70%1.78%-4.62%-0.83%
61
Aug-13$14,78999.98%96.02%100.73%0.02%3.96%-4.71%0.02%3.96%-4.90%
62
Sep-13$15,78699.30%94.94%0.70%4.36%0.70%4.39%
63
Oct-13$16,11998.62%1.38%1.38%
64
99.09%96.30%96.02%94.40%93.77%92.07%88.64%84.33%81.53%80.78%0.91%2.85%0.53%0.66%-1.01%-0.22%0.55%1.46%1.53%1.43%0.91%2.88%0.55%0.69%-1.09%-0.24%0.61%1.70%1.85%1.74%
65
66
67
68
Customer acquisition profitability
69
ConversionNew
70
monthMRRCACsCumulated revenue in lifetime month vs. CACsE1E2
71
0123456789 Cohort is profitable since lifetime month
72
Jan-13$7,851$50,000$7,650$15,186$22,420$29,402$36,278$43,058$49,736$56,303$62,757$69,0997
73
Feb-13$7,988$50,000$7,988$15,676$22,908$29,906$36,796$43,585$50,264$56,853$63,3126
74
Mar-13$9,234$60,000$9,145$18,042$26,720$35,289$43,721$52,073$60,225$68,2136
75
Apr-13$10,123$60,000$9,988$19,645$28,867$37,768$46,969$56,437$66,1256
76
May-13$11,788$70,000$11,678$23,224$34,545$46,223$57,978$69,846Not yet profitable
77
Jun-13$12,876$70,000$12,876$25,422$37,855$50,254$63,230Not yet profitable
78
Jul-13$13,212$80,000$12,987$25,743$39,088$52,544Not yet profitable
79
Aug-13$14,789$90,000$14,786$28,987$43,884Not yet profitable
80
Sep-13$15,786$90,000$15,676$30,664Not yet profitable
81
Oct-13$16,119$90,000$15,897Not yet profitable
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Loading...