[UPDATE: canning this for now, its a big project to do it the way I want – if you want something basic go here, scroll to “Some examples of CAC calculations” and check the xls, or go to the bottom of this post to see some SAAS modeling examples – or a more complex full SAAS model go here and click a link]
I have done a lot of research on SAAS metrics over the years and have not really found a calculator that lets you input your metrics for a lead source and tell you how you’ll go long term.
Currently I use a Google Sheet spreadsheet for this data but I think it would be nice if it was a little prettier and have more organisation of data fields.
I feel the exercise s important to me personally as it not only helps understand the data flow, but how to work with the data and to optimise my funnel thinking.
I may eventually turn this in to a full api driven solution using external data (CRM etc), but for now it will be a viability calc.
Some other interesting use cases for this in the future would be to determine how much investment you may need to start a SAAS product based on lead source cost saturation:
For example if you wanted to start a landing page building tool in the US, you could pick a popular competitor, say: Instapage.com
Examine their top keywords both in Organic search and paid (if they do), look at the CPCs and start to work backwards.
Profiling company data (Linkedin to see their sales rep numbers, retention)
Lookup some global averages and plumb them in to the tool and see what pops out and how much you may have to spend.
I am going to use WordPress press, Toolset, Chart.js and custom functions for the calculations.
The data will be housed in a number of related custom post types.
SAAS Company Data – for the parent CPT and will display averages
Lead Source data – for the child CPT of above (so you can use more channels)
Its likely there will be a few other child related CPT in the future if this works out ok so i can expand on the tool.
Custom Field Groups
There will be custom field groups to organise data fields that are relevant to the calculations – this will also allow me to expand on this later.
Sales People Data
- Number of sales people (this is to average total salary and for predicting future growth)
- Total salary(inc commission)
- Other sales expenses necessary to do the job, e.g. entertainment, travel,tools etc
- Average salary – calculation on above
- Average expenses (for future streamlining)
- Average Sales Department Cost – both the last averages total
- Average # Leads per SDR (sales person)
- Average Sales Target # per SDR
- Average Conversion to sale
- Average PPR – productivity per rep in $value
- Max PPR
Other data that will be likely added later:
Custom post type for the “Sales People” that will allow for more granular control on SDR performance and average at the top
Start date, leads pm, conversion rate, country served etc, type of sales (inside, new business), average sales, actual sales, SDR Cost to business (Full OTE), break out account vs user sales, ad hoc revenue.
Marketing People Data
- Number of Marketing People
- Total Monthly Salaries
- Total Extra Marketing Expenses (tools, etc)
- Total Marketing Budget
- Average Salary
- Total Marketing Department Cost
- Average LTV (months)
- Users/Account (we use user based and account based pricing)
- if = ‘Users’ – Average Number
- If = ‘Account – Average $Value
- Average Single User Price (Month)
- Average # of User Deal Site
- Average Account Price (Month)
* I may ad a conditional to select if this funnel has a ‘Free Trial’ component in the future and include PQL stats
Lead Source Data
- Medium (PPC)
- Channel Source
- LP Average Conversion Rate %
- Traffic to Channel
- Average Ad Conversion Rate
- # Account Leads
- # User leads
- # Total Leads
- Channel Spend (budget)
- Per day spend
- Daily Clicks – Average From Ads
- Monthly Clicks From Ads
- Monthly Leads
- #Sales in Month
- Total Sales Marketing Expenses
- Total SME + Channel Spend
- Total SEM + Budget
- Cost of Acquiring Demo Request
- Cost of PPC – Lead to Sale
- Ideal situation: Is LTV > 3 CAC?
- Marketing break even: Is LTV > CAC?
From what I can see here its going to be a challenge to build and I already see there is scope creep in my original idea 🙂
This post will need an update as I get a better handle on the data architecture and make changes
UPDATE: this is complex to create, I will attempt to create a version at some point but if you are looking for other SAAS modeling tools check out these:
- The Definitive SAAS Financial Modeling Template by Alexander Jarvis – very good… hard to beat.
- SaaS Financial Model by Ben Murray
- SaaS Financial Model Baremetrics by Jaakko Piipponen
- Startup Financial Model Template by Slidebean
- Forentrepreneurs SAAS Metrics 2 – David Skok – love his stuff
- SaaS Financial Plan 2.0 by Christoph Janz @ Point Nine Capital