Cohort analysis template for early-stage SaaS startups

Still loading...

	B	C	D	F	G	H	I	J	K	L	M	N	O	Q	R	S	T	U	V	W	X	Y	Z	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AO
1
2																																		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.
3	Customer Churn
4	Conversion	New
5	month	customers		# of retained customers in month									A1	# of retained customers in lifetime month									A2	# of churned customers in lifetime month									A3
6				Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	0	1	2	3	4	5	6	7	8	9	0	1	2	3	4	5	6	7	8	9
7	Jan-13	80		78	75	72	70	69	67	66	66	65	64	78	75	72	70	69	67	66	66	65	64	2	3	3	2	1	2	1	0	1	1
8	Feb-13	88			88	86	82	78	77	76	73	72	70	88	86	82	78	77	76	73	72	70		0	2	4	4	1	1	3	1	2
9	Mar-13	105				103	103	98	94	92	90	86	82	103	103	98	94	92	90	86	82			2	0	5	4	2	2	4	4
10	Apr-13	110					107	106	102	99	97	92	90	107	106	102	99	97	92	90				3	1	4	3	2	5	2
11	May-13	115						114	112	105	98	97	96	114	112	105	98	97	96					1	2	7	7	1	1
12	Jun-13	128							128	122	119	115	110	128	122	119	115	110						0	6	3	4	5
13	Jul-13	137								136	129	122	118	136	129	122	118							1	7	7	4
14	Aug-13	151									149	145	135	149	145	135								2	4	10
15	Sep-13	161										158	154	158	154									3	4
16	Oct-13	168											167	167										1
17				78	163	261	362	465	580	696	821	952	1,086
18
19				% of retained customers in lifetime month									B1	% of churned customers in lifetime month (relative to base number)									B2	% of churned customers in lifetime month (relative to previous month)									B3
20				0	1	2	3	4	5	6	7	8	9	0	1	2	3	4	5	6	7	8	9	0	1	2	3	4	5	6	7	8	9
21	Jan-13	80		97.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%
22	Feb-13	88		100.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%
23	Mar-13	105		98.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%
24	Apr-13	110		97.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%
25	May-13	115		99.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%
26	Jun-13	128		100.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%
27	Jul-13	137		99.27%	94.16%	89.05%	86.13%							0.73%	5.11%	5.11%	2.92%							0.73%	5.15%	5.43%	3.28%
28	Aug-13	151		98.68%	96.03%	89.40%								1.32%	2.65%	6.62%								1.32%	2.68%	6.90%
29	Sep-13	161		98.14%	95.65%									1.86%	2.48%									1.86%	2.53%
30	Oct-13	168		99.40%										0.60%										0.60%
31				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%
32
33
34
35	MRR Churn
36	Conversion	New																																If you have any questions or comments:
37	month	MRR		Cohort MRR in month									C1	Retained MRR in lifetime month									C2	MRR churn in lifetime month									C3	christoph@pointninecap.com
38				Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	0	1	2	3	4	5	6	7	8	9	0	1	2	3	4	5	6	7	8	9
39	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
40	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
41	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	$164			Created by Christoph Janz
42	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	-$220				at Point Nine Capital
43	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
44	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
45	Jul-13	$ 13,212								$12,987	$12,756	$13,345	$13,456	$12,987	$12,756	$13,345	$13,456							$225	$231	-$589	-$111
46	Aug-13	$ 14,789									$14,786	$14,201	$14,897	$14,786	$14,201	$14,897								$3	$585	-$696
47	Sep-13	$ 15,786										$15,676	$14,988	$15,676	$14,988									$110	$688
48	Oct-13	$ 16,119											$15,897	$15,897										$222
49				$7,650	$15,524	$24,067	$33,099	$43,887	$55,883	$67,654	$82,452	$98,039	$114,559
50
51				% of retained MRR in lifetime month									D1	% MRR churn in lifetime month									D2	% MRR churn in lifetime month (relative to previous month)									D3
52				0	1	2	3	4	5	6	7	8	9	0	1	2	3	4	5	6	7	8	9	0	1	2	3	4	5	6	7	8	9
53	Jan-13	$7,851		97.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%
54	Feb-13	$7,988		100.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%
55	Mar-13	$9,234		99.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%
56	Apr-13	$10,123		98.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%
57	May-13	$11,788		99.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%
58	Jun-13	$12,876		100.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%
59	Jul-13	$13,212		98.30%	96.55%	101.01%	101.85%							1.70%	1.75%	-4.46%	-0.84%							1.70%	1.78%	-4.62%	-0.83%
60	Aug-13	$14,789		99.98%	96.02%	100.73%								0.02%	3.96%	-4.71%								0.02%	3.96%	-4.90%
61	Sep-13	$15,786		99.30%	94.94%									0.70%	4.36%									0.70%	4.39%
62	Oct-13	$16,119		98.62%										1.38%										1.38%
63				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%
64
65
66
67	Customer acquisition profitability
68	Conversion	New
69	month	MRR	CACs	Cumulated revenue in lifetime month vs. CACs									E1										E2
70				0	1	2	3	4	5	6	7	8	9	Cohort is profitable since lifetime month
71	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,099	7
72	Feb-13	$7,988	$50,000	$7,988	$15,676	$22,908	$29,906	$36,796	$43,585	$50,264	$56,853	$63,312		6
73	Mar-13	$9,234	$60,000	$9,145	$18,042	$26,720	$35,289	$43,721	$52,073	$60,225	$68,213			6
74	Apr-13	$10,123	$60,000	$9,988	$19,645	$28,867	$37,768	$46,969	$56,437	$66,125				6
75	May-13	$11,788	$70,000	$11,678	$23,224	$34,545	$46,223	$57,978	$69,846					Not yet profitable
76	Jun-13	$12,876	$70,000	$12,876	$25,422	$37,855	$50,254	$63,230						Not yet profitable
77	Jul-13	$13,212	$80,000	$12,987	$25,743	$39,088	$52,544							Not yet profitable
78	Aug-13	$14,789	$90,000	$14,786	$28,987	$43,884								Not yet profitable
79	Sep-13	$15,786	$90,000	$15,676	$30,664									Not yet profitable
80	Oct-13	$16,119	$90,000	$15,897										Not yet profitable
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100