[Note: This post first appeared as a guest post on the blog of Totango. In case you don't know Totango, it is a powerful analytics product which gives online services the information they need to increase user engagement, conversion and retention. If you're a SaaS company you should check it out. Thanks to Guy Nirpaz and his team for publishing my post, which I am republishing here.]
In talking to a pretty large number of SaaS entrepreneurs in the last few years I've observed that there's a considerable amount of uncertainty around metrics: Which KPIs are the most important ones, what's the right way to calculate churn, CACs, MRR and other key metrics, how can I estimate customer lifetime value – these and other questions come up all the time, and the answers aren’t always obvious.
I've tried to address some of these questions in a couple of blog posts:
- 9 Worst Practices in SaaS Metrics
- Excel template for cohort analyses in SaaS
- The 8th DO for SaaS startups - Stay on top of your KPIs
I also put together a template which I thought SaaS startups could find useful and which also makes it easier for us as a VC to communicate what KPIs we're looking for when we talk to SaaS entrepreneurs. Needless to say a template can only be a starting point, as every SaaS startup is different and needs to build its own, customized dashboard. Nonetheless it seems like the template, first published in April of this year, struck a chord with many SaaS founders and investors: The blog post got more than 60,000 page views (which I assume is quite a lot for a niche topic on a VC blog, at least if you’re not Fred Wilson :-) ) and I get requests for the Excel file every day.
In the meantime I’ve put some more work into the dashboard. You can download it here. (And if you like it, tweet it!)
Here’s how the charts look like with some sample data in it:
 |
| Click for a larger version |
The main improvement of this version is that it now includes different pricing tiers and annual plans. This makes the spreadsheet considerably larger, but I feel it's necessary if you have multiple pricing tiers and contract lengths, and you can collapse a lot of the rows to get a concise view of the top KPIs.
I hope you find it useful! If you have any questions, comments or suggestions, please feel free to email me at christoph@pointninecap.com.
Hi Christoph,
ReplyDeleteGreat post.
Just a quick note that two of the links above in the list of 3 blog posts don't link to anything.
Andrew
Oops! Thanks Andrew. Fixing it!
ReplyDeleteHi Christoph - can you comment on whether you would alter this for a SaaS firm focused in the Enterprise space vs B2C?
ReplyDeleteExcellent thanks.
ReplyDeleteHow can one include and calculate activation rate using the spreadsheet?
ReplyDeleteHi Christoph,
ReplyDeleteGreat post & thanks for sharing Excel template.
Hi Christoph,
ReplyDeleteI've been using the old version for a few months and was missing the multiple pricing tiers. Thanks a lot for sharing this and for the improvements!
I have actually been using the Google Docs version. Do you plan to release this improved version on Google Docs as well? That would be awesome!
I have a question about new customers:
If a new customer signs up at the beginning of the month and then cancels his plan before the end of the month, would you note this as 1 new customer and 1 churned customer? Or would you not count the customer at all?
This is no free trial, the monthly price is pro-rated on a daily basis.
Thanks!
Oh, one more thing: Our users can have multiple plans on one account, it's not limited to one. Do you have any suggestion on how to handle this in your spreadsheet?
ReplyDeleteSorry for the late reply, Bill. What I had in mind when I built the model was a SaaS startup with a low-touch sales model, which usually means a B2B SaaS company with a product that is geared towards SMBs.
ReplyDeleteYou can also use it for B2C, e.g. a B2C freemium subscription service, but it will likely require some adjustments. For example, you may want to add a few metrics for "activation" and "referral".
As for enterprise startups, if it's high-touch sales model I would put more focus on metrics like the productivity of the sales team. 80% of the template is still relevant but it will likely need some adjustments.
Thank you for your comments!
ReplyDeleteRe. a Google Docs version: I've just uploaded the Excel file to G-Drive and it seemed OK (except for the charts - some of them didn't survive the conversion). Before I publish it I'd like to verify that everything still works well, but until then please feel free to upload it to G-Drive yourself.
Re. your second question: I'd count it as 1 new customer and 1 churned customer.
Now it gets tricky. :) In the next version of the model I'm going to add support for "per seat" pricing. This might work for you as well if you substitute "seat" with "plan".
ReplyDeletePleasure!
ReplyDeleteJust add 2 additional rows below "signups" where you show the number and percentage of customers who activated.
ReplyDeleteCool, thanks!
ReplyDeleteThe problem w/seats is they are usually associated to the same plan. Example: Medium plan w/ 4 users
ReplyDeleteWe do database-as-a-service and charge at the end of the month based on the consumption, pro-rated by days.
An example for an edge case could be, for a specific month:
* 1 medium plan running all month
* 2 small plans running all month
* 1 small plan running 12 days
Thanks for updating the dashboard Cristoph!
ReplyDeleteVery cool tool for SaaS startups.
Just found this and it is fantastic. Setting it up to track 2014 now :-)
ReplyDeleteHallo Christoph,
ReplyDeleteschaue mir gerade dein neues Dashboard an, gefällt mir gut! Eine Frage: Was hältst du von der Diskussion MRR vs CMRR? Siehe z.B. http://www.bvp.com/sites/default/files/bvps_10_laws_of_cloud_saas_winter_2010_release.pdf
Ich führe in deinem Dashboard deshalb 2 Sheets, einmal IST und einmal PLAN. Würde es Sinn machen Nur 1 Sheet zu führen und mit CMRR zu rechnen?
freue mich auf deine Einschätzung!
Hi Christoph,
ReplyDeleteIm looking at your new dashboard, and it looks great! One question: Whats your take on the discussion MRR vs CMRR? e.g. http://www.bvp.com/sites/default/files/bvps_10_laws_of_cloud_saas_winter_2010_release.pdf law #2
Until now I am maintaining two versions of your dashboard: one with actual data and one with projected data. Wouldn't it make sense to use one sheet with CMRR instead?
I am very curious about your opinion! :-)
Pleasure! Glad you like it.
ReplyDeleteCool, thanks for the comment!
ReplyDeleteThank you for your comment Miro, and greetings to the countryside! :)
ReplyDelete1) MRR vs. CMRR
To make sure we're on the same page, I'm assuming the definition of CMRR is:
CMRR = MRR + purchase orders for future recurring revenues - revenues that is likely to churn within the year
(taken from http://cracking-the-code.blogspot.de/2007_07_01_archive.html)
In principle I agree with Philippe's statement that CMRR is an even better representation of a SaaS company's health than MRR. The reason why I didn't include it in the dashboard, at least not yet, is:
- CMRR is more relevant for enterprise-y SaaS companies with longer sales cycles and my dashboard is primarily geared towards SaaS startups with a low-touch sales model
- Based on the above definition there's an element of subjectivity ("revenue that is likely to churn") which I'm not too happy about
2) Plan vs. actual
Great idea to merge it into one sheet. How about you have 2 columns for each month, one for plan, one for actuals? If you want to take it even further you can add a 3rd one for the forecast (=updated forecasts based on actuals as they come in).
I am moving my data over to this updated xls file. What is the diff between marketing spend and sales spend? If I have a marketing manager then where would his/her salary go?
ReplyDeleteUsually the cut between marketing and sales is done at the "lead", "qualified lead" or "trial" level. So all costs associated with getting leads/trials should be considered marketing spend, and all costs associated with turning them into paying customers should be considered sales spend.
ReplyDeleteHi Christian,
ReplyDeletegreat feedback, thanks a lot! Its true, CMRR is best B2B SaaS with maybe >2 months cycles. I would argue that despite the implicit "churn forecast" it is still a better representation of how the company is doing and how sales dep is performing, since it accounts for all closed deals.
Using this, I think I can also ditch the sheet with planned data and think of all past months as actual data and all future months as planned data.
Christoph, That's an amazing contribution, and I look forward to giving it a try! Thanks so much. You had mentioned in the earlier post about one day hooking the spreadsheet up to a Geckoboard dashboard. Have you done that yet?
ReplyDeleteHi Christoph,
ReplyDeletewhere/how would you include non-recurring revenue in your model? Revenue coming from premium support or one-time setup fees for instance?
Thanks!
This is not included in the model at the moment. I would add an additional row for that.
ReplyDeleteI didn't go into that level of detail, i.e. think about periods less than one month, but your suggestion sounds good. (Although I don't understand why the example customer would pay only $50 if you charge monthly?)
ReplyDeleteOh, I should have explained that I guess.
ReplyDeleteWe pro-rate on a daily basis and charge at the beginning of every month for the used resources within the last month.
So if a customer signs up mid Jan for a plan which is $100, she will be charged $50 beginning of February, $100 beginning of March, etc.
This is typical in hosting, PaaS, DBaaS, etc.
Currently when filling out the cell for "New MRR from new customers" for a given month I will fill in the amount they have signed up for, not the actual charges we have made for that month (since those will be pro-rated).
Does it make sense? Do you have any suggestion on how to do this differently with your spreadsheet?
Thanks!
Got it, thanks!
ReplyDeleteYes, I think the way you're describing it it makes sense.
Thanks for sharing these graphs. It's very helpful especially for entrepreneurs with great startup ideas planning to pitch for angel investors. Angel group/investors, like iSeed , would be very interested for well laid out startup ideas!
ReplyDeleteHi Chris,
ReplyDeleteThanks so much for your spreadsheet. It's really helpful. I do have a question about one particular calculation.
The way you calculate signup-to-paying conversion is by having "new customer of the month/new signup(trial) of the month". What if there's a 2 week trial period. Say someone sign up for the trial on Jan 25th, and don't convert to pay (or churn) until Feb 10th, the calculation by this model will effectively use customers who sign up for trial in January to divide the new trials in February. That seems to be a data source inconsistency right? It would be great if you can tell me how you can resolve it. Thanks a lot!
David
I actually just saw that you assume a 30 day free trial. That make sense then. thanks Chris!
ReplyDeleteAll right! Thanks David! Let me know if any other questions come up.
ReplyDeleteThanks Chris. I actually do have another question about the funnel. It's along the same line - we have a freemium model + a free trial for the premium products. In this case, how do you suggest us do the calculation of monthly funnel conversion rate from free users to free trial users? Since users can use the free product forever, how can I avoid data source inconsistency when calculating the conversion from free to free trial on a monthly basis?
ReplyDeleteAgain, thanks so much for your response. I quite literally have been customizing your spreadsheet for building up our internal metrics dashboard. I learned so much from just working with it. So thank you so much for making this available!
Hi, this comes quite late but i think there is an error in the net MRR churn rate formula (row 161 and below). If you consider upgrades from e.g. basic to pro as a "positive" you shouldn't have the negative of them. If you consider them as a "negative" (which i think is correct) since this is MRR moving away from "basic" then you should add the "lost MRR" but you are actually detracting it (adding a negative number).
ReplyDeleteI think you should rewrite the formula in row 162 (and following) as: -(C129+C131-C138)/C112
Hi Stefano,
ReplyDeleteThank you for your comment. Great catch, I think you're right! I'm looking into it now...
Best regards
Christoph
Hi Stefano,
ReplyDeleteThanks again for spotting this issue.
After looking into it and almost getting a knot in my brain I think i can confirm that you're right. That said, I think there's even more wrong with the formula in row 162-167. ;-)
If you look at row 162, e.g. C162, in the numerator (which represents the net churned MRR), it includes:
- C129 (which is the MRR gained from the Basic > Pro upgrades)
- C131 (MRR gained from Basic > Enterprise upgrades)
- C138 (lost MRR from Basic customers who churned)
However, instead of C129 and C131 it should be the MRR lost for the Basic plan because of these upgrades (the price of Basic, not the difference between Pro/Enterprise and Basic):
- For the Basic => Pro Upgrade it's C63 x C84
- For the Basic => Enterprise Upgrade it's C65 x C84
...and MRR gained for the Basic plan due to downgrades from Pro and Enterprise needs to be considered, too.
Do you agree?
After thinking through this again, I think a) it's too complicated to be useful and b) it doesn't make sense to look at "net MRR churn" on a plan-by-plan basis because it produces misleading results, namely, that an MRR increase due to an upgrade from Basic to Pro is considered as churn for the Basic plan. I think I'll therefore just kill rows 162-167....
Hi,
ReplyDeletereally appreciate you spent the time looking into it again. I had reached the same conclusion and totally removed this section from the modified template I am working with. The entire up/downgrade issue makes it too confusing. I think the bottomline net churn is really what matters and everybody should just spend some time trying to understand if that comes from downgrades or simple churns. Again, your template is a fantastic starting point, we are using it already on 2 companies, a b2b saas product and a more consumer based subscription product. Great work for everybody.
Thanks Stefano, glad you've found it useful!
ReplyDeleteError (403)It seems you don't belong here! You should probably sign in. Check out our Help Center and forums for help, or head back to home.
ReplyDeleteIs this similar to GoodData? Either way, this seems like a great tool for startups. We are currently considering a dashboard and analytics page, but gathering feedback on it http://support.helprace.com/i131-does-helprace-have-reporting-features
ReplyDeleteI care about my karma. Glad there's a LinkedIn option - better audience to share with for me.
ReplyDelete