This is a guest post written by Auston Bunsen. Auston is the organizer behind SuperConf, an awesome conference for web entrepreneurs which I had the privilege to attend in February. If you’re in the South Florida area, make sure you go next year!
I watched a Mixergy interview with Jason Fried [link] and at one point he talked about how they (37Signals) hired a data guy to generate metrics, more specifically Customer LTV. Andrew asked how LTV is calculated & if there was software available to help figure it out.
This is my answer to that question. It’s how I’ve done Customer LTV calculations & segmenting at companies I’ve worked with/at. Hope it’s useful!
Disclaimer: Code may not be 100% working & some of this stuff is very lego-like. Take this with a grain of salt, it’s just how I do my LTV stuff.
The Customer Lifetime Value Formula
For a SaaS startup today, the formula in it’s simplest form is as follows.
Customer LTV = Revenue Per Month * Number of Months being a customer
Getting The Necessary Data
If you’re lucky, you will have a record (in a table or datastore) of all payments (& hopefully declines) that a given customer has made.
If you don’t have this you may be able to get it from your payment gateway via their API. If you can, do yourself a favor & do a retroactive import of this data & store it on your servers. It’s important!
Once you have this data, you can just create a script to run through all customers & generate an average LTV. Here is an example in SQL:
For more meaningful metrics, you’ll need more data than just your LTV. You need to be able to properly separate customers with High LTV’s from customers with Low LTV’s. To do this, simply add a “group by” & “order by” to the above SQL statement:
That covers how to get a granular look at your customers & their lifetime value. But now that you’ve got that, you’re probably going to want to see where the bad customers came from. Conversely, you might have some intuition that certain traffic has a lower or higher LTV than others. Basically, you’ll want to go deeper.
If you’d like to segment your customers by traffic source/medium/campaign, you need to get that data somehow & analytics like Google/Clicky don’t have granular data export (each visitors ip/browser/os) in their API. So, you will need to collect that data & somehow pin it to customers record at the time of signup. You might have to update your database/store to accomplish this. It’s actually really simple to do, just grab the url params & pass them through to the sign up page. Here are a couple of examples…
in Python (django):
Once you do that, things get fun; you can:
1. Mash up data from google analytics with your own data by using their API [link].
2. Segment your customers by sex, age group, education, marital status, etc using the Rapleaf API [link]
3. See if customers in certain cities or states have higher LTV (using SQL)
With the above information, you can create dashboards for realtime campaign tracking (or just dashboard widgets, like one for geckoboard [link]), daily emails with metrics, create upsells to your customers based on LTV (in order to raise low LTV or increase revenue in high LTV customers), re-focus your marketing/pr efforts where they will make the most impact based on historic data & if you’re really nerdy (& smart) run ML/Pattern recognition on your data to find patterns that may be interesting.
If you’ve enjoyed reading this, you can follow the author on Twitter @Bunsen.
For more startup news, follow us on Twitter @startupfoundry.