How Leadfeeder automates KPI reporting with Supermetrics

9-MINUTE READ · By Pekka Koskinen on March 7 2017.

Running a SaaS startup is like flying an airplane in the dark. By looking out of the window and using your senses you get some feedback on what’s happening, but the real trustworthy information comes from your instruments. We need to be always aware of how our business is doing and where we are heading.

When steering the company to a new direction, developing new features for our product or testing different marketing campaigns, we need to have a reliable feedback loop to know if we are doing the things right. Without a proper analytics feedback loop we would not know what is working and what should be done next. KPIs are a vital part of our business development and they give the whole team and investors a real-time view on how we are progressing. That’s why a big part of my job as a CEO is to make sure we have the metrics in place and people have access to the information they need.

The old faithful Excel

I started out developing business analytics tools 10 years ago when I was running my first SaaS startup. Back then there weren’t any good business analytics tools available so the only way to do this was to make ad hoc queries to our database with SQL. With a bunch of joins I was typically able to answer any business question I had.

This approach was fine for occasional one-time queries but not convenient for regular KPI reporting. I wanted to have my KPIs automatically updated in Excel spreadsheets. As a solution, we created a read-only replica of our production database which I then connected to from Excel using an ODBC driver. With this method, I was able to refresh the data with one click and use pivot tables to analyze and draw conclusions from it.

Nowadays, there are tools like Kissmetrics, Mixpanel, Google Analytics and many other business analytics tools which give you many important metrics off the shelf. I’ve used these but they cannot answer all business questions. A big part of the data I want to analyze isn’t sent to any third party analytics systems, but instead is stored in our own database. Since it’s not possible to send all data from our database to some other system, there’s still a need for direct queries to the database.

The main challenge with using Excel was that it was very difficult to share the reports. If I sent the spreadsheet to someone else, they couldn’t refresh the report with newest data without first installing an ODBC driver on their own computer. I would have also needed to give them credentials to the database.

Google Sheets to the rescue

When I started Leadfeeder a few years ago, I began looking for a better solution that catered to the modern cloud-based world. And since Google Sheets had replaced the use of Excel, I also focused on looking for a way to connect Google Sheets to our database with SQL. This is how I discovered Supermetrics.

I basically just searched for Supermetrics from Google Sheets Add-ons, added it and gave permission to access my Google resources. This added Supermetrics to my Google user so the add-on was then available in all my Google sheets. The entire process was very easy.

I then added a database connection, entered database credentials and edited a couple of firewall rules to enable access from Google server IPs to our MySQL business database. We run our services in AWS and created a separate read-only business database which is a daily copy of the production database. We could have used a straight replica which is easy to make in AWS, but we wanted to be able to select the tables and columns that are sent to the business database to prevent any sensitive information from being sent, like password hashes.

Creating the automatic revenue report

Our most important KPI report shows how the amount of users, accounts, purchases, churns and MRR (Monthly Recurring Revenue) are developing. For this, you need to store in the database daily changes for users, accounts and paid subscriptions. From this data we can retrospectively calculate how many users, accounts and paid subscriptions we had on any given day.

Calculating these metrics for all days in the last two years gives us a nice view on how the amount of users, accounts, paid subscriptions and MRR has evolved. In order to get this done, you have to do some SQL magic with probably lots of joins and subqueries. MySQL workbench and someone who knows SQL are your best friends here.

From these metrics we can draw nice graphs and calculate growth and churn rates for each month and week. In the Supermetrics settings, I added a trigger to automatically refresh the data every night. For the most important reports I’ve also configured a weekly email delivery for the team. The spreadsheet is accessible online for the whole team as well as investors, making our work transparent to everyone. When talking with new potential investors, they have been very impressed with how we have our business metrics updated and available at all times.

In the revenue KPI report we have two tabs for raw data from our database and four tabs for different pivot table analyses. More specifically, the tabs we have are:

• Daily KPIs. Daily amount of new users, trials, subscriptions, churns and MRR.
• Monthly KPIs. Same information as in daily KPI tab, but grouped on a monthly level using pivot.
• Subscription changes. A log of changes to our subscription base, including daily information about who subscribed, downgraded/upgraded, and churned and what the monetary effect was on MRR. New subscribers are divided into three categories, new sales (customer bought their first subscription), expansion sales (existing customer bought for another website) and reactivations (churned customer came back).
• Monthly subscription changes. All subscription changes aggregated on a monthly level using a pivot analysis.
• MRR change by country. This is a pivot table where we have the MRR change for each country for each month. From this report we can see how we are progressing in each market region.
• Gross and net churn. A pivot table about how much MRR we lose each month. The difference with gross and net churn is that for gross churn we only look at cancellations and for net churn we take into account reactivations, upgrades, downgrades and other expansion sales.

In addition to the revenue KPI report, we’ve created tens of other reports with Supermetrics for analyzing how the usage of different features correlate with conversion from trial to paid and how traffic from different marketing channels convert into trials.

Just give it a go

Working with Supermetrics is easy, but in order to implement smart reports you need to have some SQL skills and know how to use pivot in Google Sheets. There might be a learning curve in the beginning, but once you learn this it’s super quick to create powerful new reports.

Typically it takes me about 30 minutes to create a new report and have it automatically updated. We’ve learned a lot from our business and we’ve been able to develop our sales, marketing and product development based on facts. Investors have been very pleased about having such a transparent and real-time reporting in place and new potential investors have been impressed on the level of business analytics we are having. I would recommend having this kind of reporting in place for every SaaS startup founder.

About Leadfeeder

Leadfeeder is an online service that tells who have been on your website and what they have done there. It uses data from Google Analytics, so you don’t need make any changes to your website. It also connects to your CRM to get updates on CRM on website visits from interesting companies and to Mailchimp to identify email addresses of website visitors. Learn more about Leadfeeder.