ABSTRACT

Have you ever wondered why an investor gave money to another startup and not yours? This article summarizes how to increase your investor’s (and your own) awareness of your business by helping you build a SaaS startup analytics platform in a spreadsheet. Our step-by-step process results in an automated startup SaaS Analytics platform that helps you track, measure, and react to the key insights for your business. Follow the steps discussed below to build the only spreadsheet a SaaS entrepreneur could ever need. Use this spreadsheet template to help you run your SaaS startup and impress all of your current and future investors by providing a transparent (and comprehensive) understanding of all of your revenue KPIs, advertising KPIs, marketing KPIs within your business model.

The Only Spreadsheet a SaaS Entrepreneur Will Ever Need: Why build it?

I’ve met many entrepreneurs who seem to struggle devoting enough time to building and understanding their own KPI and financial models. We all know how important it is to track our data yet, due to time constraints, we rarely put in enough effort to do so. As a consequence, we miss key data points along the way that have the potential to make or break our businesses. As a fellow entrepreneur, I am well aware of the level of personal effort required to build or update that new KPI model, Pirate Metrics Model, Financial Model, even an Ad campaign report at 2am.

In an effort to stop wasting time building spreadsheets and spend more time on data analysis and strategy execution, I asked a few key figures, including some prominent venture capitalists like David Gardner and David Skok, if there are any tools out in the market that would help me aggregate my KPI data into my business model. I also mentioned to them how important it was that I could also share such information with my investors and team members 24×7. The responses I received were a little deflating. For the most part, it was suggested that:

1. There are plenty of dashboard tools out there, like Baremetrics or ChartMogul, that will let you visualize your data but you can’t combine them with your own financial models.
2. You’re probably better off sticking with spreadsheets since KPIs are constantly evolving and require a level of constant customization that only a spreadsheet can give you.
3. To really get what you want, you must be able to fetch data from different systems and then be able to frame the data to your own needs. You can invest in a consultant who can write a number queries to fetch the data but it could get expensive to maintain or modify.
4. Since the data resides in several system silos, you have to use a platform that already has connectors to the systems you use to collect the data. To this day, I am not aware of any spreadsheet templates that will let you do that. 

Every single time someone gave me one of these responses, I honestly couldn’t believe it. With so much innovation, how has someone not yet solved this problem.

This article was created to show other SaaS entrepreneurs how to create their own spreadsheet analytics system to help better run their companies by spending more time analyzing and reacting to key data instead of aggregating it.  I hope that by the end of this tutorial your spreadsheet template can resemble our very own “The Best Spreadsheet Template for SaaS Startups“.

While I attempt to provide a comprehensive look into the key metrics that we believe are commonly needed to run and optimize a SaaS business, keep in mind that I will not cover every single metric related to your particular business. If you fall under that category, use this how-to guide as a stepping stone to: 1) help you build a data-centric culture, and 2) build a better thought-out KPI strategy that you can track as you continue to grow.

Check out the video below to see our spreadsheet template in action:

(Note: although I focus on SaaS specifically, this article can be applicable to other subscription businesses and spreadsheet templates.)

Learning About SaaS Analytics – 

There are plenty of articles on the nature of a SaaS business and the importance of customer acquisition and retention metrics. This article assumes you already know the meaning behind the SaaS Metrics we discuss. If you need help understanding SaaS metrics, I recommend you read our own SaaS Metrics 3.0 article or search SaaS metrics online.

Getting Your Business Ready for SaaS Analytics Business – 

There are hundreds of analytics tools that can give you insights on your website, marketing campaigns, revenue, and even your own app’s performance. While I can’t recommend one over the other, I decided to take a practical approach and talk about the most commonly used (and most cost effective) analytics tools known to entrepreneurs (aside from Factivate). For SaaS startups (and our spreadsheet template), we boiled these tools down to: Stripe (revenue data), Google Analytics and AdWords (website data), and Facebook Insights and Ads (Social Media data). I then combined the data from these tools in an analytics spreadsheet template in order to give SaaS startups:

1. An Investor Dashboard
2. A Cap Table example
3. A Month-over-Month Dashboard
4. A Week-over-Week Dashboard
5. A Marketing KPI Report
6. A Content Calendar 
7. An Advertising KPI Report
8. A Sales KPI Report
9. A Financial Model (Realized)
10. A Projected Financial Model

My hope is that building this template will not only help you WOW your investors (or future investors) but that this automated spreadsheet will be the only spreadsheet your startup will ever need to build. If you don’t want to go through the process of building this system, you can use the Factivate automated Saas Analytics spreadsheet template titled “The Best Spreadsheet Template for Saas Startups”.

Note: all of the spreadsheet screenshots will be zeroed out (for obvious reasons) but if you have any questions about the data itself, feel free to email us at [email protected] or add your questions in the comments section.

Step 1: How to Set up your Digital Marketing KPIs

1. Setting Google Analytics for Conversion Tracking – First things first, your website should be integrated with Google Analytics. This means that you have a Google Tracking ID and landing pages that you plan to continously check for user traction using AB tests, Micro-conversions, and Macro-conversions. (To learn how to set your Google Analytics structure, you can view our How to Track Marketing KPIs or Sales KPI conversions with Google Analytics Funnels. ) In our case, we break down the data that we want to track on a daily (or weekly) basis into three categories:

1. AB Tests – refers to the pages we’re actively comparing to each other to learn about message, design, and user responses. In our case, we’re consistently testing different variations of our consumer-facing website pages from the home screen to the pricing page.
2. Macro-Conversions – We call these Sales Qualified Leads or Sales Conversions. These are the landing pages we have added in Google Analytics as our Goals which result in a user being one to two steps away from buying our service. These include demo presentations and account creation thank you pages. 
3. Micro-Conversions – We call these our customer touch points that can lead to a macro conversion. Examples of these would be downloading white papers, playing how-to videos, subscribing to an email list, etc…

The end result in our marketing tab includes the three sections (like the image below):

The image displays part our marketing team’s spreadsheet tab. To get data in it, all we need to do is replace the columns with the “/URLs”, enter the landing page URLs, and then the rest of the data fills up automatically. We find that these three sections give us an indication about a data spike or a flat user response related to our website tests. The second we notice a dramatic change in the data, we then dig deeper into the data to make sure we understand the reason for the data fluctuation. Finally, we decide whether we can repeat the same actions that led to the data spike, stop execution on a flat campaign, or continue to closely monitor.

This leads me to my next point: understanding why, how, and where your conversions came from is critical to iterating on positive conversions. As a result, the data analysis mentioned above goes hand in hand with understanding where your conversions can come from:

The traffic source information can or cannot coincide with your page conversions; it’s up to you to figure that out as quickly as possible. This section can help quickly identify the “where did they come from” question(s). You can continue digging deeper by learning about demographics, location, etc… but the above data should help you get a quick head start and not get lost in the universe of too much data.

Do you feel you have a good handle on your website KPIs and have your structure set up correctly? The end result could look like the screenshot below:

Now it’s time to further increase your marketing efforts by combining them with a content and social media strategy.

2. Setting up your Content Calendar to Increase Brand Awareness

Just as we’re constantly testing AB pages and landing page conversions, we need to be testing content and digital interactions. This is probably the section that will fluctuate the most since most entrepreneurs are in a constant state of experimentation (aside from product experimentation). Either way, I’ve found that setting a monthly content calendar, based on a content goal, helps us focus and execute. The content calendar also gets thrown right out the window the second we identify a conversion spike in a particular channel with a particular message. When that happens, we rewrite the entire calendar to try to milk as many conversions as possible. Once we see it that content conversion strategy flatten, we move back to new content experiments. To set up a content calendar, we add the following columns:

• Date
• Categories
• Subcategories
• Target
• Call to Action
• Post / Content
• # of the same Post variations for that day (we use this mostly if we’re AB testing)
• Impressions
• Comments
• Likes
• Shares
• Clicks
• Total Engagement

So now that you’ve set up your content calendar, it’s time to see how your content is perceived in individual social networks!

3. Setting up a Social Media KPI marketing structure:

Social media is a great way to create brand awareness and community relations but it can also seem mystical if you’re not tracking the right data. I frequently run into entrepreneurs who are active in social media and when I start asking them about how their interactions lead to micro or macro conversions they never give me a good answer. Sometimes they flat out tell me that social media just doesn’t work for their company demographics so they don’t track it. This realization is often a symptom of their inability to tracking the right KPIs. I must admit that even I was in that same boat at one point. It wasn’t until I began focusing on the following data that I really started improving on social media:

We track these KPIs because they help us get to three key ratios that indicate how our social media strategies are having an impact: conversation rate, amplification rate, and applause rate. (To read more about these ratios click on our post on the top 3 facebook insights metrics: conversation, amplification, and applause rate.) Each ratio gives us an idea on how we should be executing and reacting based on our marketing strategy.

4. Putting your Digital Marketing KPIs together on a daily/weekly basis

Remember that the goal for any entrepreneur is to quickly identify the marketing strategy that will lead to the most conversions and reduce wasting time and effort on channels and messages that don’t work. When you’ve set up your spreadsheet, as this post hopes to show you, you can pull any amount of data together to calculate your Marketing ROI. To do so, you have to consider your marketing spend and conversions. Your marketing spend can be calculated by $ spent on marketing activities which include the salaries of your marketing employees or consultants. Having a conscious understanding of your Marketing ROI will quickly show you whether your marketing spend is working well (or not). The sooner you arrive at this conclusion, the better off you will be.

Step 2: How to Set up your Advertising KPIs

I started this article on Marketing KPIs because, as a company rule, we do not spend any money on advertising until we have some indication from our marketing efforts on social media, memes, content, or even landing pages of a good response/conversion rate. This little strategy has saved me thousands of dollars from advertising mistakes!

Let’s assume that you have now noticed the following from your marketing data: your landing page has a good conversion rate and low bounce rates, a marketing message in social media has a great conversation rate, and you’ve noticed a good micro (or macro) conversion rate from the message; now it’s time to amplify that message and (most importantly) track its performance through different channels. To do this, most startup entrepreneurs typically start with either an Adwords campaign or a Facebook Ads campaign.

1. Google Adwords Campaign (PPC) KPIs

Before you begin your Adwords campaign, be sure to link your Adwords account to your Google Analytics account (Click here if you don’t know how to do this). Once you have done so, you will be able to track your conversions just as you did with your marketing KPIs. In the end, you could end up following these Google Adwords KPIs per campaign:

From the data above, we pay particular attention to Ad Fatigue Rate, Quality Score, and Cost/Conversion. When we see that Ad Fatigue Rate is trending up and Quality Score begins to trend down, we monitor the ad closely to see when the cost/conversion gets close to the $ per new user conversion. In our case, if our ad’s ROI is only 20% more than the cost/conversion, or our ad fatigue rate has trended significantly upwards (within a certain number of days), we typically decide stop our ad and move on. To ensure that we never miss these important KPIs, we have our spreadsheet send us an SMS alert based on these formulas (ie, ROI is only 20% more than cost/conversion). That way, we’re always on top of our data even when we’re not looking at our spreadsheet. (Learn how to have your spreadsheet send an SMS when a cell value changes to never miss any important information). 

Since we run multiple campaigns (remember we continuously test), we set up our spreadsheet template to help us track them all in one place rather than going to each platform to fetch the data. Our marketing/advertising person (sometimes even the CEO) is responsible for updating the Campaign names, budgets, and creating alerts for campaign balances and the KPIs I mentioned above. This helps us keep a close eye on all of our campaigns even during nights and weekends. The end result of our template looks something like this:

Note: We track our campaign results on a daily basis (since we obsess about our data) but you don’t have to do that. Depending on the nature of your ad, you might not see results for weeks so just be sure to consider that not all ads are the same and some (which might lead to great conversion rates in due time) might take some time to grow. As a company that iterates continuously, we find that daily or weekly analysis works best for us since we’re not testing the effectiveness of the message (remember, we already saw some indication through our marketing efforts) but rather the effectiveness of the channel.

2. Social Media Advertising (Facebook Ads) KPIs 

While not all social media platforms give us comparable data to Adwords (see differences between Facebook Ads and Google), we track ratios that can indicate the effectiveness of the channel (and ad) as opposed to the typical impressions/visits/vanity metrics. This results in an Advertising spreadsheet tab that considers Ad Fatigue Rate, Cost/Conversion, and $ per new user conversion as the top level KPIs that we track (rather than clicks or CPC for example).

3. Putting your Advertising KPIs together

Tracking more than 20 campaigns at a time can be very difficult when you’re an entrepreneur that has to wear many hats. That’s why we created two summary views which we can quickly glance when our analysis time is limited.

The first section focuses on a per channel view and it includes an aggregate of our active campaigns on a week by week basis:

The second view relates to a high level overview of all of our advertising efforts in which we consider SaaS conversions as our primary objective:

Internally, whenever we’re running an ad campaign, this spreadsheet tab is always open in an independent monitor in our office. We do this to remind ourselves that every single dollar is important and if our KPIs are indicating waste or opportunity, we act on it immediately. So far, this strategy has worked really well for us.

Step 3. Setting up your Sales KPIs

Now that you are driving some great inbound sales through your marketing and advertising funnels and you have a solid foundation to measure your customer acquisition KPIs, you need to consider your SaaS revenue metrics. From our experience, most of the entrepreneurs we speak to use Stripe to manage their online subscription payments so we connected our automated spreadsheet template to Stripe.

Note: if you use a different payment method, the spreadsheet structure will still work but note that if you use Factivate’s automated spreadsheet template, it will only fetch Stripe data for now.

There are a few spreadsheet templates that have helped entrepreneurs track their SaaS Metrics including: David Skok’s spreadsheet template on SaaS Metrics and Christopher Janz’s Financial Plan v2.0. My problem with these spreadsheets was not that they weren’t really useful. Rather, it related to the inability of the spreadsheet to automatically pull data from our payment processor, Stripe, in order to let us compare the KPIs we cared about (in context of the business and not just sales) in real time. As a result, we used those spreadsheet examples as a foundation for our own Sales KPI reports and improved on the foundations laid out by Skok and Janz by automating the spreadsheet to collect the data from Stripe. The Sales report is tracked on a monthly basis in this tab because our spreadsheet template also includes revenue data in the Week-over-Week tab and Month-over-Month tab.

The top part of spreadsheet tab looks at our Bookings and MRR Summary:

The bottom part of the spreadsheet tab focuses on Customer information by Plan:

Since the spreadsheet was built for SaaS startups, or subscription-based companies, there is a significant focus MRR and Churn KPIs. If these don’t work for you, feel free to customize your spreadsheet as you see fit.

Step 4: How to build your Startup Week-over-Week  and Month-over-Month KPI Dashboard(s)

Customer Aquisition KPIs, CHECK! Revenue KPIs, CHECK! Congratualtions, you’re quickly becoming a KPI superstar. So how do you stay sane with all of data that you need to consider when you’re already busy running a company? Dashboards give us a great top-level view of all of our activities (in context) so naturally, our spreadsheet template includes several dashboard views.

For Factivate, our dashboard uses the AARRR metrics foundation to monitor our company’s health. Conseqently, our dashboard is divided by the following sections:

• Acquisition KPIs
• Activation KPIs
• Retention KPIs
• Referral KPIs
• Revenue KPIs

If you’re interested in How to set up your own Pirate AARRR Metrics, simply click on that link.

Our tab essentially pulls all of the data that we collect (from Google, Social Networks, and even Stripe) into this pirate metrics dashboard. We are then able to add spreadsheet reactions to specific KPIs in order to be able to react to changes in data in an instant (whether we’re asleep, or even on vacation). Some of these examples include:

• Send me an Email when Marketing spend = (# of new users converted * $ per new Conversion)
• Send me an Email when Advertising spend = (# of new users converted * $ per new Conversion)
• Send my customer implementation team an Email when churn goes above 3%
• Send my customer implementation team an Email when there is a failed conv from trial
• Send me an Email when my Customer Acquisitoin cost exceeds ____
• and so on…

1. How to Set up your WoW Startup Dashboard

Think of this Week-over-Week Dashboard tab as your business’ management tab. It paints a picture of all of the business pieces working together on a weekly basis and, if you wish to dive deeper, all you need to do is go into the other individual spreadsheet tabs. 

Per company policy, my team typically makes notes on the top section of this spreadsheet tab (based on our data analysis & recommendations) before every team meeting so that we can discuss the important data stories and results when we plan our strategy. We also add a few charts to demonstrate our points.

2. How to Set up your MoM Startup Dashboard

Like the Week-over-Week Dashboard, our Month-over-Month Dashboard helps management (and even our board members) gain a good top-level view of our data. We use the same structure (Pirate Metrics) for our MoM Dashboard and end up with a spreadsheet tab like the one below:

Our spreadsheet tab also showcases the month over month growth from our macro conversions and micro conversions to help add context to some of our data.

Note the GOAL column. Before the beginning of each month, we specify our company goals here. These drive our entire business strategy for the upcoming month. It’s good practice for entrepreneurs to try to do the same as we have found it incredibly beneficial in keeping us focused.

The Month-over-Month Dashboard also includes a section for Analysis & Recommendations at the top. I find this extremely helpful as it forces our team to take a step back from our day to day (or week to week) analysis and consider the bigger picture.

Step 5: How to integrate your SaaS metrics KPIs within your business model

There are plenty of ways to build a business financial model to suit your startup. In our spreadsheet template (and article) we include a business model inspired by Cofounders Capital (a current investor in Factivate). The startup financial model provides an elegant way for entrepreneurs to structure their business assumptions and test against them. After spending years building complex models (with some very fancy S curves if I might add), I find this model is a great way to get started.

Your spreadsheet template should include at least two business model tabs. The first one is your projected model (as you see below) and portrays a basic Revenue/Costs picture of the business. You can edit the assumptions on the left hand column and see how it affects the rest of the business later on.

The second business model reflects the realized (actual) numbers from your business just like the image below. This tab is where you will go in and add your actual data at the end of the month.

We use these two tabs in order to learn how far off we typically are from our projections and goals. Sometimes we exceed these goals and we adjust our assumptions and sometimes we fall below our projections and have to adjust our cashflow. In the end, investors (and future investors) really care about the projected vs realized comparison so be sure to have some version of it in your spreadsheet system.

Step 6: How to set up an Investor Dashboard

If you really want to impress your investors (or future investors) you will need a way to integrate everything you have built (KPIs, business model, and strategy) into a dashboard that is specifically built for your current and future investors. With this spreadsheet tab, each investor will have a view into your business 24×7 without you ever needing to update the data manually. The investor dashboard should include three basic things:

1. An “Updates & Requests” Section – while some entrepreneurs will find sharing their needs and problems (with potential investors) as problematic, our typical experience is that the investors that were truly interested in our company, took us more seriously and, at the same time, appreciated how in tune and transparent we were with any potential problems related the business. We also had some outside investors offer us introductions because of what we posted in our Needs section.
2. A Current Month KPIs section – Each investor looks at different KPIs when they are working with your business. We find that simply placing those KPIs at the very forefront helps in our communication. The beauty in managing this information through a spreadsheet is that we have the flexibility to edit/manage all of their requests without spending any time coding or writing complex queries.
3. A Bookings Metrics Graphs overview – SaaS companies should provide a graphical (top-level) view of their data to their investors over a prolonged period of time. In our case, we provide a semi-annual view of our MRR and Churn metrics for our investors.
4. A MoM Metrics section – We added this section to show our investors (both current and future) how we were doing when compared to our monthly goals. If we want to add or remove a metric, it’s as simple as editing a spreadsheet formula. Notice that we stay away from vanity metrics and focus on ratios, conversions, and revenue-driving activities.

Here is what the entire Investor Dashboard spreadsheet tab looks like:

Factivate allows us to share individual tabs so we typically share this one tab with investors that we’re speaking with (including current investors of course). When we do so, we limit their read and write permissions to editing only dates on cells M1,M2 and N1,N2. As such, we make surethat they won’t mess with the model while still giving them the flexibility to view different MoM data.

Step 7: How to build your Cap Table

While this tab doesn’t have anything to do with your KPIs, I’ve met so many entrepreneurs that have a difficult time building one so I figured I would add it. I’m not an expert in building Cap Tables, but this tab reflects how we’ve managed our Cap Table throughout my various companies. Feel free to edit it as you see fit or to use the same structure I’ve used.

Using Factivate’s “Best Spreadsheet Template” for SaaS Entrepreneurs

Factivate uses a similar spreadsheet to manage our company. To use it effectively, we have had to be careful on the read/write permissions given to the team and investors. In our case, the marketing team only has access to the marketing tab and content calendar tab. The Advertising Manager only has access to the Ads tab and Content Calendar. Investors only have access to the Investor Dashboard, and so on… Take the permissions consideration carefully in order to prevent data corruption.

While I tried to showcase how SaaS entrepreneurs can build a robust data analytics spreadsheet that helps them run, measure, react, improve, and communicate important data, if you decided to build the spreadsheet from scratch rather than use Factivate’s template, take this into account: the spreadsheet is only as good as its ability to reflect the “pure data” pulled from the different cloud-based sources. You can spend hours building your own spreadsheet template but if you’re also spending hours updating it, you’re likely better off just emailing us and asking us for a deal in a subscription (hint hint, wink wink)

Disclaimer: There are thousands of data sources that we could have included to help companies gain better insight, but in an effort to focus on practicality, we decided to focus on what we believed were the core data feeds for a majority of entrepreneurs. We also understand that our KPIs might be different than yours. The beauty of the spreadsheet, however, is that you can customize it to fit your needs. If you have any insights or comments about this article, different KPIs, or the spreadsheet itself, please share them in the comments section so that we can improve this template and make it even better for all SaaS entrepreneurs! That being said, the spreadsheet’s purpose cannot be overstated, use it to build a culture that spends more time focussing on data to drive better business results instead of building reports.

I hope this guide helps you throughout your SaaS endeavors, and let me know if we can be of any further help ([email protected])!