Whether you call it LTV or CLV, customer lifetime value is an incredibly valuable metric for ecommerce retailers. By knowing how much your customers are likely to spend, you can quantify customer value, figure out how to allocate precious time and money, and more accurately forecast your store’s financial performance.
This tutorial features SQL queries written in Redshift SQL syntax. If you’re on a Google BigQuery data warehouse, work with one of our data architects to adjust these queries to work in standard SQL syntax.
What is LTV (customer lifetime value)?
Customer lifetime value is a metric used to calculate the long term value of a customer. Rather than focusing narrowly on individual orders or looking broadly at total revenue, it pushes analysis one step further by factoring in customer lifespan.
What’s the difference between LTV and CLV? It’s entirely semantic. In fact, LTV and CLV—and CLTV (customer lifetime value) and LCV (lifetime customer value)—all ultimately measure the same thing: how much a customer spends with your business over the period of time they buy from you.
Why is LTV important for Shopify merchants?
With LTV analysis, you can quantify the value of your customers. And with net margins for ecommerce averaging just 4.6%, LTV is a make-or-break metric that Shopify merchants can’t afford to ignore.
Understanding your customer lifetime value enables decisions around:
- How to manage customer relationships, in particular time-intensive customer support or costly returns
- Which customers to target when launching loyalty or “VIP shopper” programs designed to increase customer retention
- How to maximize marketing ROI by increasing spend for high-value customers and cutting back on acquisition channels or tactics that bring in lower-value customers
How powerful is LTV? It’s so important that even massive companies like Amazon, Netflix, and Starbucks have all used it to identify their most important customer segments, optimize their products, and weigh customer acquisition costs against potential revenue.
Ready to get to the SQL? The wait is over →
How LTV works (the basics)
The basic formula for finding LTV is incredibly simple:
LTV = Average purchase value x Number of purchases per year x Number of years the customer will purchase
To get a sense of what that means in practice, let’s take a look at your coffee habit. Let’s say you stop by your corner coffee shop to pick up a large drip for $2 on your way to work or while walking the dog. Assuming you only stop in on weekdays, you spend upwards of $500 on coffee every year:
$2 per cup x 52 weeks x 5 days a week = $520 spent on coffee per year
If that’s not making you rethink your caffeine consumption, let’s up the ante by factoring in how long you’re likely to be a customer. If you’re a regular, religiously visiting that same shop for 5 years, that puts your LTV at:
$2 per cup x 52 weeks x 5 days a week x 5 year lifespan = $2600 LTV
A simple cup of coffee really adds up, right? Now imagine that you treat yourself to a $5 scone every Friday. That would only raise your average purchase value by a dollar, but over a 5 year lifespan your total LTV would increase more than $1k for a total of $3640.
While ecommerce merchants are probably selling less frequently and at higher price points than your local coffee shop, the value of LTV analysis is the same: LTV identifies which customers are likely to be the most valuable over the long haul, which can then inform all sorts of business decisions about how best to retain and incentivize them.
Itching to give it a try? Just add your data and start a free 14 day trial of Panoply to find your shop’s LTV today. There’s no commitment and no credit card required. Try it now!
How to calculate LTV for your Shopify store using SQL
Calculating LTV is pretty easy, especially when you start out with a solid Shopify customer analytics view. We’ll query against the
shopify_customer_analytics view that we created for customer analytics for all the queries below. But before we get into LTV, let’s look at the metrics it’s built upon.
For clarity’s sake, we’ll walk through the components that go into CLV, but you can also skip straight to the query →
Step 1. Find your customers’ AOV
Average order value (AOV) gives you a sense of how much customers spend each time they purchase. AOV is a common KPI (key performance indicator) for ecommerce companies because it helps you better understand your customers’ buying habits.
AOV is a particularly powerful metric for retailers that need to keep a tight rein on their acquisition costs because increasing AOV requires increasing the order size of existing customers, which can often be achieved through low-cost tactics like recommendation emails. In contrast, acquiring new customers often requires higher-cost tactics like paid marketing campaigns.
The formula for AOV is simple:
Average order value (AOV) = Net sales / Count of orders
/* AOV */ SELECT email, net_sales, total_orders, net_sales / total_orders aov FROM shopify_customer_analytics WHERE total_orders > 0 ORDER BY 4 DESC
There are a few things you should note about the way we’ve built this query:
- We’re focusing on
net_salesas our revenue metric. That’s because it factors in discounts and returns, providing a more realistic picture of our customers’ value than we’d get from gross sales.
- We’ve used the
whereclause to only include customers with one or more purchases. We’re not interested in outliers like customers who returned the only order they ever made. Your mileage may vary, though, so feel free to eliminate or adjust that clause based on your context.
When you run the query, you’ll get a table like this:
This tells us that, while Ima made the most orders from your Shopify store, she tends to buy at lower price points than your other customers. In contrast, Charlie orders the least but buys higher priced items, resulting in higher AOV.
If all that SQL is making your head spin, we’ve got just the thing! Watch our on-demand webinar with Trevor.io to see how to get important insights without a single spreadsheet and 0 lines of SQL. View it now →
Step 2. Calculate average customer lifespan
Customer lifespan is how long, on average, customers purchase from you before going dormant.
The concept of and (general) formula for customer lifespan is pretty simple:
Customer lifespan = (Most recent order date - First order date).
But there’s a twist we need to be mindful of: When caulculating LTV, we need to move away from individual customers to focus instead on our average customer. That means we’ll need to calculate customer lifespan—and the rest of the metrics that follow—at the aggregate level. That will allow us to shift our attention away from the historical performance of specific customers and toward a model of what we can expect when we acquire a new one.
To find that aggregate customer lifespan, we use the formula above, but instead of looking at individual results, we need the overall average. That will give us one metric (average customer lifespan) for all our customers.
/* AVERAGE CUSTOMER LIFESPAN */ SELECT avg(date_diff('day', first_order_date, recent_order_date)::FLOAT / (365::FLOAT / 12)) avg_customer_lifespan FROM shopify_customer_analytics -- Add or remove following line depending on how you want to define the metric WHERE total_orders > 1
In Step 1, we used a
where clause to include only customers with 1+ orders. For this query, we’re only including customers with 2+ orders. Why the change? Simple: The concept of a lifespan really only makes sense for customers with multiple purchases. We’ll talk more about the implications of this decision later, but for now it’s just something to be mindful of both in the example here and in your own queries.
Because we’re working in SQL, the query doesn’t look quite as simple as the formula, but it’s doing the same thing by using
date_diff to get the number of days between the first and most recent order dates.
This tells us that our average customer lifespan is 38 months. That’s a little longer than the default lifespan for ecommerce customers, which is 3 years.
Though average customer lifespan is one of the core building blocks for LTV, it’s not a terribly actionable metric at the customer level. But if you calculate it by segment, things get way more interesting:
/* AVERAGE CUSTOMER LIFESPAN BY SEGMENT */ SELECT country_code, -- Substitute your chosen segment here avg(date_diff('day', first_order_date, recent_order_date)::FLOAT / (365::FLOAT / 12)) avg_customer_lifespan FROM shopify_customer_analytics -- Add or remove following line depending on how you want to define the metric WHERE total_orders > 1 GROUP BY 1;
Looking at our data, it’s clear that our Shopify store does better in North America than in other English-speaking markets. Understanding why that’s the case requires a lot more business context, though. Perhaps we’ve been advertising to Canada and the US longer than other markets, or maybe our shipping policies aren’t as clear as they could be. There’s more investigation to do, but that segmented analysis is a great starting point.
And, of course, because you can look at any segment you’d like, you can find out which marketing channels bring in long-term customers and which attract short-term buyers who come for a promotion but don’t become regular purchasers. To find out if that’s the case, let’s take a look at purchase frequency.
Curious which of your customer segments stick with you the longest? Find out with a free trial of Panoply. Setup takes minutes and our ready-made SQL makes analysis copy-and-paste simple. Try it now!
Step 3. Calculate purchase frequency
Purchase frequency tells you how often your customers buy from you. Ecommerce customers probably won’t match your daily coffee habit, but if they’re truly addicted to your shop, they may purchase multiple times a month.
You may also find that purchase frequency varies wildly based on seasonality, whether that’s because your customers’ minds turn to swimsuits in May or because of gifts purchased on Black Friday and Cyber Monday.
Subscriptions make calculating purchase frequency really tricky. Shopify defaults to creating separate orders for each shipment—so, one order per quarter for quarterly subscriptions—but you should give some thought to how you want to handle them. There’s no right answer, but it’s critical to keep that business logic consistent.
The formula for purchase frequency is:
Average purchase frequency = Total orders / Customer lifespan
/* PURCHASE FREQUENCY */ SELECT email, total_orders, date_diff('day', first_order_date, recent_order_date)::FLOAT / (365::FLOAT / 12) customer_lifespan, total_orders::FLOAT / customer_lifespan purchase_frequency FROM shopify_customer_analytics WHERE total_orders > 1 AND customer_lifespan > 0;
Looking at purchase frequency, we can see that Charlie is a big fan of your shop, ordering from you 1.5 times per month over the eight months they’ve been a customer. In contrast, Avery and Justin buy from you less than once a month. All of their lifespans are lower than your Shopify store’s average customer lifespan of 38 months, so you stand to continue earning from these folks a while longer—just a few months in Justin’s case and for years in Ima’s.
Step 4. Calculate LTV
Here’s where things get weird (and awesome). Since we’re working with aggregate metrics, it doesn’t make sense to calculate LTV at the customer level. Here’s why:
- Individual LTV doesn’t help to create a model for an aggregate population.
- The result ends up being the same as
What’s up with the second one? Here’s the deal. The most common formula for LTV is:
LTV = AOV x Average customer lifespan x Average purchase frequency. But if you break those metrics into their component parts, you end up multiplying and dividing by both
average_customer_lifespan, which means they cancel each other out.
If you’re looking closely, you’ll notice that there are two versions of
average. But since you’re calculating it at the individual level, the outcome is the same.
Realizing that, we arrive at this alternative formula for LTV:
Net sales / Total customers. Don’t believe us? Try running the query below, where we’ve included both ways of calculating CLV:
/* CUSTOMER LEVEL LTV */ SELECT email, sum(net_sales) / sum(total_orders) aov, avg(date_diff('day', first_order_date, recent_order_date)::FLOAT / (365::FLOAT / 12)) avg_customer_lifespan, sum(total_orders::FLOAT) / avg_customer_lifespan purchase_frequency, aov * avg_customer_lifespan * purchase_frequency ltv, -- mathematically the same sum(net_sales) / count(email) easy_ltv FROM shopify_customer_analytics WHERE total_orders > 1 AND date_diff('day', first_order_date, recent_order_date)::FLOAT / (365::FLOAT / 12) > 0 GROUP BY 1;
Looking at the results, it’s easy to see what makes kind of analysis so useful: LTV quantifies the value of your customers, making their potential value super stinkin’ clear. For example, the above tells you that newcomer Charlie is on track to become your best customer, while long-time fan Justin may not be as valuable as he seems.
But again, while customer-level LTV is interesting, it’s not actionable because it only gives us a historical view of customers’ buying tendencies, not a way to estimate how newly acquired customers are likely to perform. So instead, let’s take a look at the aggregate numbers:
/* AGGREGATE LTV */ SELECT sum(net_sales) / sum(total_orders) aov, avg(date_diff('day', first_order_date, recent_order_date)::FLOAT / (365 / 12)) avg_customer_lifespan, avg(total_orders::FLOAT) / avg_customer_lifespan purchase_frequency, aov * avg_customer_lifespan * purchase_frequency ltv, -- mathematically the same sum(net_sales) / count(email) easy_ltv FROM shopify_customer_analytics WHERE total_orders > 1 AND date_diff('day', first_order_date, recent_order_date)::FLOAT / (365::FLOAT / 12) > 0;
Bam! Now you’ve got model that allows you to estimate what newly acquired customers will be worth. What’s more, you can compare the LTV of different customer segments—just group by different dimensions—to get a sense of which customers bring in the highest ROI. The best part is that since you’ve calculated the metrics in SQL, you have full visibility into the logic and calculations that go into the metric. Plus, and this is no small thing, you did it all without downloading a single spreadsheet!
You know you want that LTV. Try it for yourself in Panoply. You can set up an account in minutes and, once your Shopify data is synced, can simply paste the queries in this guide into the workbench to find out who your most valuable customers really are. Try it now!
Going deeper with customer value
What, you thought we were done? Puh-lease. Now that we’ve gone through the basics of LTV, let’s loop back around to an assumption that’s baked into it: lifespan.
As we mentioned above, from Step 2 onward, our queries include a
where clause to include only customers with more than one order. That’s because focusing on lifespan inherently means focusing on repeat customers. But that business logic doesn’t work for every ecommerce business model. For example, a subscription service for natural cleaning products may have lots of long-term customers, while a wedding ring shop may rely on one-time purchasers (we hope).
If most of your customers only buy once or twice, messing around with lifespan may not make sense. Take a look:
/* CUSTOMER VALUE BY SEGMENT */ SELECT total_orders, count(DISTINCT id) distinct_customers, avg(net_sales::FLOAT / total_orders) aov, sum(net_sales::FLOAT) / distinct_customers customer_value FROM shopify_customer_analytics WHERE total_orders > 0 GROUP BY 1 ORDER BY 1 LIMIT 5
We’ve limited the results to up to 5 total orders because segments of 6+ orders are negligible for our data. When querying your data, try it without the
limit to get a sense of how large your segments are and choose a
limit that makes sense given your how your customers shake out.
This data tells you that while 5-time purchasers have the highest AOV, the vast majority of your customers are one-time purchasers. And sure, you could go figure out lifespan and purchase frequency and run an LTV analysis. But in cases where the bulk of your customers purchase just a few times, you’ll get just as much value from a simple weighted average. The best part is, you can build on the Customer Value By Segment query to make it happen. Check it out:
/* WEIGHTED LTV */ SELECT sum(customer_value * distinct_customers) / sum(distinct_customers) weighted_ltv FROM ( SELECT total_orders, count(DISTINCT id) distinct_customers, avg(net_sales::FLOAT / total_orders) aov, sum(net_sales::FLOAT) / distinct_customers customer_value FROM shopify_customer_analytics WHERE total_orders > 0 GROUP BY 1 ORDER BY 1 LIMIT 5 );
And there you have it: the weighted customer value for your Shopify store. As long as your customer value is greater than your customer acquisition costs, you should be in good shape. But if you find that costs are creeping up and customer value isn’t increasing, it may be time to reevaluate your marketing spend.
Ready to find out whether your CAC > LTV? Try Panoply free today! There’s no credit card required and no strings attached.