How to Calculate LTV for E-commerce Shopify Store (Formula)

Whether you call it LTV or CLV, customer lifetime value is an incredibly valuable metric for e-commerce 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 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.

Why is LTV important for Shopify merchants?

With LTV analysis, you can quantify the value of your customers. And with net margins for e-commerce 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.

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 e-commerce 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.

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.

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 e-commerce 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:

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 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

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 e-commerce 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.

Step 3. Calculate purchase frequency

Purchase frequency tells you how often your customers buy from you. E-commerce 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.

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:

1. Individual LTV 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 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 sum(total_orders) and average_customer_lifespan, which means they cancel each other out.

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!

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 e-commerce 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

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.