Lifetime value, churn and cohort analysis? You need tools to analyze increasing amounts of data when growth hacking. Here is a quick comparison of Excel, Google Spreadsheets and R along with examples of how we use data at Firmafon, including links to our source code.

To do good growth hacks you need a general capability to analyze data. We use two tools at Firmafon: R and Google Spreadsheets.

Excel comes short in a serious startup

Let’s start with Excel, which many people use for number crunching and statistical analysis. You quickly hit Excel’s limitations if you manage large amounts of data, keep track of versions and if you want to update data automatically. That’s why we started using Google Spreadsheets early on at Firmafon.

Three reasons to use Google Spreadsheets

A Google Spreadsheet is a URL: It’s in the cloud so there’s always one version of the numbers, version tracking doesn’t get lost in emails. That’s advantage number one. Number two is, you have great scripting in Google Spreadsheet. Google Apps Script is JavaScript. It has a great execution engine that runs in the cloud as well, e.g. to run a scheduled import every midnight. Your computer (or server) doesn’t have to run as is the case with Excel. Here’s the central gateway to Google Apps Script resources: https://developers.google.com/apps-script/
GoogleSpreadheetExample-web

Why Firmafon uses R and Google Spreadsheets, not Excel. #growthhacking #firmafon

Connect your data in the cloud

Advantage number three: Google Spreadsheets are truly connected because they all live in the cloud. If you change data in one spreadsheet, it is reflected immediately in other sheets. This is very practical. Examples of formulas we use at Firmafon: “ImportRange” in Google Spreadsheet reads ranges from one spreadsheet to another. “UrlFetchApp” in JavaScript lets you fetch data from other sources like REST APIs. You can also let external systems read from Google Spreadsheets, for example many dashboards (Klipfolio, Geckoboard, Ducksboard etc.) can read from your live data in Google Spreadsheet. Think this through and you will probably come up with a number of practical implementations of Google Spreadsheets in your business.

Try Firmafon’s script for exporting data to Google

At Firmafon we developed the Ruby script “to_google_spreadsheet” to push data into Google Spreadsheets from our servers. We have seen tremendous benefits from that and I’m happy to share it with the NGH community. Download “to_google_spreadsheet” here: https://github.com/firmafon/to_google_spreadsheet When you have your data in Google Spreadsheets you will benefit from its analysis capabilities which are, I think, many steps ahead of Excel. E.g. the two commands FILTER and QUERY are like VLOOKUP on speed. If you want to start segmenting thousands of customers and base business critical decisions on that, you need the power of Google Spreadsheets.

Example #1: Analyzing Customer Lifetime Value

At Firmafon we analyze lifetime value and churn in great detail. We have our backend database push data about revenues and users into one Google Spreadsheet; then we have another spreadsheet that our finance team enters sales and marketing expenses into. This gives us an updated lifetime value analysis that we consult all the time.

We hit Google’s limits when analyzing churn

Churn is a critical metric for Firmafon as a business phone subscription company. When analyzing churn with thousands of customers we ran into the limits of Google Spreadsheets. When you start exporting 100s of 1000s or millions of values into Google Spreadsheet it becomes clear that it was never intended to be a Big Data tool.

The ultimate analysis tool: R

The next step for us was “R”. R is a programming language. As programming languages go it’s fairly simple and is certainly approachable for non-experts. Instead of entering cells you create script files which is advantageous when your data becomes increasingly large and complex.. You can easily share code so you can collaborate with your team and it will basically take data from almost any format and output it to anything.

What makes “R” superior to spreadsheets

Again I have three reasons R is better than Google Spreadsheet – or any other spreadsheet. First of all, the analysis you can do with R is almost unlimited. If you want to do a neural network that studies your growth, you can do it. Any kind of statistical analysis is available in R in prewritten packages. There are more than 5,000 packages of solutions for various statistical problems.

Unified logic and version control

Second: In R your logic is unified. In a spreadsheet it’s spread out in many formulas, each with a little bit of your logic, which – when they get very complex – makes them impossible to troubleshoot. In R you have all your logic together in simple source files. You can troubleshoot easily and everything is reproducable: If you run the same analysis, you get the same result every time. Third reason: R has version control of your code, so you keep track of the changes you made.

The essential tool: RStudio IDE

R is an enormous ecosystem, but a good starting point is RStudio IDE. I’ve tried many tools but I prefer RStudio IDE. http://www.rstudio.com/ For growth hacking I find these R packages essential:

  • RMySQL
  •  dplyr
  •  ggplot2
  •  lubridate
  •  httr
  •  data.table

RsetupDetails-web

Our setup is: We have production data which is mirrored into another database instance and R reads directly from there.

Example #2: Cohort analysis is key for a SaaS business

With R we do cohort analysis, see the graph on the slide below. For every month in 2014, for all the customers that came in that month, it shows how much revenue we got from that cohort of customers. The longest one is the oldest, here it’s January. They start low in the first month – customers are getting on board, typically buying a partial month the first month of their subscription. Then they decide to stay with Firmafon and buy some more services, add users in their organisation etc.CohortAnalysis-web

What’s in your growth hacking tool box? #growthhacking #firmafon

If I just looked at my revenue in August I would see all cohorts aggregated and one month’s cohort could be falling but be hidden by others that were growing. Here is the code for R that we use to perform our cohort analysis: https://gist.github.com/pengelbrecht/9763dfec14ba31d33075 Cohort analysis is an invaluable tool in analyzing lifetime behavior and value of your customer and you typically get important insight into your channels, sales and marketing strategies.

Negative churn means healthy business

We focus a lot on churn and we can see from the cohort analysis that we actually have total negative churn: The base keeps growing because the upsell to existing customers is higher than the revenue loss from customers that are leaving. If we didn’t win one new customer in October our revenue would still be higher in November than it was in October, so it’s negative churn. If you can get to negative churn, you really have a great growth engine. I highly recommend cohort analysis for any subscription business as a key tool for growth hacking. It gave us an enormous amount of practical things we could change.

Bonus info: Trustpilot score history

Here is an example of neat things you can do with Google Spreadsheets and Google Apps Script: We have developed our own Trustpilot score analysis at Firmafon, read about it here (in Danish): http://blog.firmafon.dk/post/78432013267/sadan-bruger-du-din-trustpilot-score-som-kpi

About Peter Engelbrecht and Firmafon

Peter Engelbrecht is founder and CEO of Firmafon, a phone subscription service company with 2500+ companies as customers in Denmark alone. http://www.firmafon.com

Peter Engelbrecht

Founder and CEO of Firmafon
CategoryExperiments