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
Why Firmafon uses R and Google Spreadsheets, not Excel. #growthhacking #firmafon
Connect your data in the cloud
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:
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.
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