May 2, 2019
How to Derive Business Insights Using SQL, BigQuery, and Google Data Studio
Note: This is Part 2 of Days of Our Liftetime (Value), Metric Theory’s three-part series exploring lifetime value which will look at:
If you have stumbled upon this article about calculating lifetime value (LTV), but are still thinking to yourself: “Wait, why should I be using lifetime value in the first place?” then stop right there. Read this first. We’ll still be here when you’re finished.
Now that you understand why it’s important to use lifetime value for your business, let’s get down to the nuts and bolts. How do we even approach calculating lifetime value?
The short answer: it’s complicated. People have literally written textbooks on this stuff.
We don’t have a textbook for you, but we do have some guidance for how to calculate your customer’s LTV that can be applied to both ecommerce and B2B companies.
Do this with me: search Google Images for “lifetime value formula.” Are you overwhelmed by the results? So is nearly everyone. Unless you have an advanced degree in mathematics, there’s a good chance you aren’t making much sense of this:
So let’s walk through a simpler formula. One that everyone can understand, and can still be useful for your business.
A note before we get started: some other folks’ LTV calculations don’t take into account margins; we view those calculations as customer lifetime revenue, and a separate metric from lifetime value.
Lifetime Value = (Average Order Value) x (Purchase Frequency) x (Margin) x (Average Length of Customer Retention)
Average Order Value – This one should be simple. What is the average order value for all of your customers? In other words, how much revenue does an average checkout bring? Look at your last year of data to determine this number. For a quick example, let’s say our average sale is $100.
Purchase Frequency – How often does your average customer purchase from you (including the first purchase) in a given year? For some businesses (e.g., prescription medicine) this will be a frequent occurrence. For others, (e.g., doors) this will occur much less frequently. To continue with our example, let’s pretend the purchase frequency is 2 times per year.
Margin – What is your average profit margin on a sale? The item(s) that you just sold were not free for you to acquire or make. When taking into account that cost, what percentage of your overall revenue from a sale is (on average) profit for you? That’s your profit margin. For our example, let’s say our margin is 30%.
Average Length of Customer Retention – How long does a customer stay with your company? This will have a lot to do with how satisfied they are with your product. Will they still be purchasing from you in 1 year? 5 years? 10 years? To complete our example, let’s say our average customer purchases from us for 3 years.
Lifetime Value = ($100) x (2) x (.30) x (3)
So what does this mean? With an average order value of $100, an average of 2 purchases per year, a profit margin of 30%, and a three year returning customer – our liftetime value of one customer averages out to be $180.
Liftetime Value = [(Initial Contract Value) x (Initial Margin)] + [(Average Number of Continuing Contracts x (Average Continuing Contract Value) x (Continuing Margin)]
Initial Contract Value – This one is pretty straightforward. How much is the initial signed contract worth over the duration of the contract? For an example, let’s say our initial contract is worth $50,000.
Initial Margin – Similar to what we talked about on the ecommerce side, you did not gain your initial contract for free. How much will it cost you in sales commissions to close that deal? Are there any other costs involved in the deal closing or in servicing the contract? The percentage of the overall initial contract value that does not go to covering these costs is your profit margin. To continue our example, let’s say our profit margin on that first contract is 60%.
Average Number of Continuing Contracts – How long does a customer stay with your company? Obviously, the longer a customer stays with you, the more continuing contract agreements they will sign. What is the average number of continuing contracts, or continuing subscriptions, your average customer completes? In our case, let’s say our average customer stays with us for a total of 3 continuing subscriptions.
Average Continuing Contract Value – We know your customers are continuing with your company as part of your subscription model – but how much is that worth? Does renewing a subscription cost as much as the initial contract? Does it come at a discount? In our example, we’ll say that continuing our contract is $45,000 per year.
Continuing Margin – Is the margin on a 2nd contract the same as the 1st contract? With no set-up costs and possibly no sales commission, let’s say the margin is higher at 75%.
Lifetime Value = [($50,000) x (.60)] + [(3) x ($45,000) x (.75)]
So what does this mean? For a customer who signs a $50,000 initial first year contract at 60% margin, and then continues with 3 subscription cycles at $45,000 per year at 75% margin, we observe a lifetime value of $131,250.
Everything presented above should give you a good start for calculating lifetime value for your customers. However, remember that all the numbers I discussed were averages.
You know that some of your customers are going to have better lifetime values than others. The next level of this analysis would be to identify who are your most valuable lifetime customers – and focus your marketing strategy around acquiring more of those valuable customers. In addition, you might want to shift focus away from one-time purchasers (ecommerce) or clients who will not renew after the initial contract (B2B).
If any of these thoughts have piqued your interest, feel free to reach out to our team at Metric Theory and talk about how we can incorporate lifetime value analysis into a successful paid search strategy. And if your business has lifetime value numbers that are even trickier to calculate – we’d love to hear about them!