May 2, 2019
How to Derive Business Insights Using SQL, BigQuery, and Google Data Studio
Note: Days of Our Lifetime (Value) is Metric Theory’s three part series on exploring lifetime value, which will look at:
If you are like most people, calculating your customers’ lifetime value (LTV) is a lot like doing your taxes.
Taxes are important, and you know that. However, if you fail to file them, or file them poorly, you end up costing yourself a lot of money. Even knowing this, filing taxes claims a perpetual spot at the bottom of your to-do list.
Truth is, the same thing happens with lifetime value.
You know LTV is important. You also know that if you don’t calculate your customer’s LTV, or calculate it poorly, you will cost yourself a lot of money. Even knowing this, calculating LTV claims a perpetual spot at the bottom of your to-do list.
Not any more.
Let’s take a look at a quick example to see why calculating your customers’ LTV is something you cannot afford to put off.
Do this with me. Search for “socks” on Google. Do you get something like this?
Let’s count up the ads on this search. 7 just in this top half screenshot. Why is everyone bidding on the search “socks”? Socks are cheap products. Very cheap, actually. In fact, with all the advertisers bidding on this term – CPCs look quite expensive. There’s a chance the cost of a click just on this term is actually more than the singular product itself:
So what is the logic here? Let’s take a look at the situation from an advertiser’s perspective. For fun, let’s pretend we are Grippy Slippy Socks.
In our hypothetical world, Grippy Socks pays $1.50 for each click on the search terms “socks.” Let’s also say that the average checkout price for their socks orders is $20 (most people order their socks in bulk, right?), and we give them the average eCommerce conversion rate of 2.5%.
In a universe of 10,000 clicks – what does Grippy’s end result look like?
That’s bad, right? Wrong.
You see, Grippy isn’t playing the sock game for today. They’re playing the sock game for tomorrow.
Consider the following scenarios:
Our original ROI calculation is assuming everyone who buys socks from Grippy follows scenario #1. But what happens when we factor in the scenarios #2, #3, and #4? Let’s dive back into our hypothetical universe to see how much more revenue we can find over a customer’s lifetime.
We assume the scenarios occur with the following probabilities:
Scenario #1 (60%)
Scenario #2 (25%)
Scenario #3 (10%)
Scenario #4 (5%)
Out of our 250 original sock sales, 60% (150) of these people never return to buy socks from Grippy ever again. Well that’s no fun.
Additional Lifetime revenue gained: $0
Out of our 250 original sock sales, 25% (62.5) of these people return to buy socks from Grippy on a quarterly basis for the next year (4 times). Nice!
Additional Lifetime revenue gained: 4 more purchases x $20 x 62.5 people = $5,000
Out of our 250 original sock sales, 10% (25) of these people return to buy socks from Grippy on a quarterly basis for the next 10 years (40 times). Wow!
Additional Lifetime revenue gained: 40 more purchases x $20 x 25 people = $20,000
Out of our 250 original sock sales, 5% (12.5) of these people return to buy socks from Grippy on a quarterly basis for the next 40 years (160 times). YESSS!
Additional Lifetime revenue gained: 160 more purchases x $20 x 12.5 people = $40,000
So what does that all add up to?
A big difference from the surface-level 0.33 ROI, right? Now, Grippy paying $1.50 per click for a search on “socks” doesn’t seem so crazy. In fact, the crazy people are at the companies that choose not to bid on the search term because they are afraid of the initial 0.33 ROI.
It’s important to note that these calculations are hypothetical and based on many assumptions and examples, but they do mirror real-world trends. Many eCommerce products won’t have the lifetime purchase behavior of socks. Make sure that you use accurate and verified numbers specific to your customer base and their purchasing patterns when you apply this type of thinking to your business and products.