How to Calculate CLV for your Shopify Store

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.

What is CLV (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 CLV important for Shopify merchants?

With CLV analysis, you can quantify the value of your customers. And with net margins for ecommerce averaging just 4.6%, CLV 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 CLV 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 CLV works (the basics)

The basic formula for finding CLV is incredibly simple:

CLV = 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 CLV at:

$2 per cup x 52 weeks x 5 days a week x 5 year lifespan = $2600 CLV

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 CLV 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 CLV analysis is the same: CLV lets you identify 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 CLV 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:

  1. We’re focusing on net_sales as 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.
  2. We’ve used the where clause 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:

email net_sales total_orders aov
Charlie@angels.com $3,854.00 12 $321.17
Justin@case.edu $6,213.00 20 $310.65
Avery@badman.org $4,623.00 15 $308.20
Ima@hogg.com $5,648.00 28 $201.71

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.

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 CLV, 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.

avg_customer_lifespan
38

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 CLV, 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;
country_code avg_customer_lifespan
AU 0.75
CA 1.87
GB 0.77
NZ 0.80
US 2.10

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 14 day 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;
email total_orders customer_lifespan avg_purchase_frequency
Charlie@angels.com 12 8 1.50
Ima@hogg.com 28 23 1.22
Avery@badman.org 15 21 0.71
Justin@case.edu 20 34 0.59

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 CLV

Here’s where things get weird (and awesome). Since we’re working with aggregate metrics, it doesn’t make sense to calculate CLV at the customer level. Here’s why:

  1. Individual CLV doesn’t help to create a model for an aggregate population.
  2. The result ends up being the same as sum(total_value).

What’s up with the second one? Here’s the deal. The most common formula for CLV is: CLV = 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 sum(total_orders) and average_customer_lifespan, which means they cancel each other out.

If you’re looking closely, you’ll notice that there are two versions of total_orders, sum and 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 CLV: Net sales / Total customers. Don’t believe us? Try running the query below, where we’ve included both ways of calculating CLV:

/* CUSTOMER LEVEL CLV */
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 clv,
-- mathematically the same
sum(net_sales) / count(email) easy_clv
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;
email aov avg_customer_lifespan purchase_frequency clv easy_clv
Charlie@angels.com $321.17 38 1.50 $10,357.63 $10,357.63
Ima@hogg.com $201.71 38 1.22 $5,402.43 $5,402.43
Avery@badman.org $308.20 38 0.71 $4,843.14 $4,843.14
Justin@case.edu $310.65 38 0.59 $4,020.18 $4,020.18

Looking at the results, it’s easy to see what makes CLV so useful: CLV 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 CLV 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 CLV */
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 clv,
-- mathematically the same
sum(net_sales) / count(email) easy_clv
FROM shopify_customer_analytics
WHERE total_orders > 1
AND date_diff('day', first_order_date, recent_order_date)::FLOAT / (365::FLOAT / 12) > 0;
aov avg_customer_lifespan purchase_frequency clv easy_clv
$302.12 38 1.3 $14,924.73 $14,942.73

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 CLV 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 CLV. Try it for yourself in Panoply. We give you 14 days free, but 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 CLV, 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.

total_orders distinct_customers aov customer_value
1 8720 $58.75 $58.75
2 323 $59.63 $119.25
3 22 $66.75 $200.24
4 5 $83.29 $333.14
5 2 $90.65 $453.25

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 a CLV query. 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 CLV */
SELECT
	sum(customer_value * distinct_customers) / sum(distinct_customers) weighted_clv
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
	);
weighted_clv
$61.48

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.