How to Calculate Customer Lifetime Value (CLV) in Google Sheets using AirOps

What is Customer Lifetime Value (CLV)?

Customer Lifetime Value (CLV) is a metric that measures how much revenue a company can expect to earn from a single customer over the course of their relationship with the company. It’s also sometimes referred to as Lifetime Value and abbreviated as LTV.

The longer a customer remains a customer, the greater their lifetime value becomes.

Because it’s an outcome metric that measures growth, CLV is an important metric for several functions in an organization, including customer success, sales, and executive leadership teams. It’s also relevant for product-led growth (PLG) and sales-led growth (SLG) companies alike.

For example, any organization can use CLV to identify which customer segments are most valuable to the company. For PLG companies, CLV is an important metric to measure alongside Customer Acquisition Cost (CAC). If your CLV is higher than your CAC, those acquisition costs represent money well spent.

How to calculate Customer Lifetime Value

There are a few different ways to calculate your business’s CLV. Let’s take a look at two of the most common.

For the first CLV calculation, you’ll need two pieces of information:

The company’s Annual Recurring Revenue (ARR)
The average lifespan of your customers

Once you have that information, the formula for calculating CLV is:‍

Customer Lifetime Value = Annual Recurring Revenue x Average Customer Lifespan (in years)

You can also use the following formula, which requires Average Purchase Value, Average Purchase Frequency, and Average Customer Lifespan:

Customer Lifetime Value = Average Purchase Value x Average Purchase Frequency x Average Customer Lifespan (in years)

However, if you want to know the Net Customer Lifetime Value for your CLV calculation, you must also consider what it would cost to lose a customer.

As you can see, CLV can be a fairly complicated metric with a lot of moving parts (which we’ll explain in greater detail in the Frequently asked questions about Customer Lifetime Value section). That’s why it’s so important to reach an organization-wide consensus on the definition of this metric, including the exact inputs you’ll use to calculate it.

Track Customer Lifetime Value alongside other important metrics in Google Sheets

On its own, CLV doesn't provide a complete picture of your organization's performance. That's why you should always track it alongside other metrics.

Tracking and analyzing multiple metrics isn't always easy, though – most companies have important data scattered across different SaaS tools and other systems. In our experience, most teams prefer to analyze data in operating documents they already know and love, like Google Sheets ❤️.

Getting high-quality data into an operating document like a GSheet isn't necessarily easy, as you probably know all too well. Especially if it requires hours of manual CSV downloads from different sources, followed by copying and pasting the data into a rickety, VLOOKUP-filled spreadsheet 👎.

While you can use the basic calculation we shared earlier, there's a much more efficient way to calculate your team's CLV (and other essential product-led growth metrics): the AirOps PLG Scorecard.

This template simplifies how you measure growth and overall performance. In addition to CLV, use it to easily track metrics such as:

… and more. Get in touch with our team to learn more and get started!

Frequently asked questions about Customer Lifetime Value

Should my company track Customer Lifetime Value?

In general, the answer to “Should we track X metric?” is almost always, “It depends.”

Early-stage companies that don’t yet have long-term customers will have a tough time tracking CLV, since (in theory) the majority of their customer base is only recently acquired. It’s tough to measure the lifetime value of customers who have only been around for a few weeks or months.

Still, CLV is one of those rare metrics with nearly universal value. If your company has customers, calculating the lifetime value of those customers is a good call.

Why is Customer Lifetime Value an important metric to track?

CLV is an important metric to track for several reasons:

Increases in CLV lead to increased revenue… but you can’t improve a metric unless you track it and take the time to understand the root causes behind the numbers.
Tracking CLV helps you identify your most profitable customers. Once you know which customers bring in the most revenue, you can develop new campaigns, new features, and new ways of delivering more value to your ideal audience.
It helps you answer important questions, such as “Which customers or segments are worth investing more in?”, “Do any existing customer segments have a negative impact on revenue?”, and “Are there any troubling customer retention trends we need to get ahead of?”

Additionally, because CLV is an outcome metric that speaks to the financial performance of the company, it’s a data point that executive leadership will want to see in board reports.

What are some common mistakes companies make when calculating Customer Lifetime Value?

When calculating CLV, there are a few common mistakes that you should keep in mind.

One common mistake is failing to account for customer churn. Churn, or the rate at which customers stop using a product or service, can significantly impact CLV.. Businesses that don’t account for churn in their calculations are likely to overstate the lifetime value of their customers.

It’s also common for businesses to use overly simplified formulas that don’t consider all relevant factors, such as cost of acquisition, customer retention costs, and the lifetime value of a customer’s referrals.

Another common mistake is failing to segment customers, which we’ll explore in more detail below.

What are some best practices for calculating Customer Lifetime Value based on customer segments?

In all likelihood, your CLV will vary from one customer segment to another, sometimes dramatically.

If you want to calculate CLV by customer segment, the first step is to separate your customers into different groups. You can segment by behavior (e.g., average order value, number of purchases, or engagement) or demographics, but behavior-based segmentation is generally considered the gold standard. Some common behavior-based segments include labels like new customers, mid-market customers, premium customers, loyal customers, and non-loyal customers (note that every company’s definition of each will vary).

Once you’ve identified your segments, you can use one of the CLV formulas to calculate the CLV for each customer segment.

How can businesses increase their Customer Lifetime Value?

To increase CLV, you need to improve operational efficiency and/or the customer experience. Ideally, you’ll tackle both (some of the strategies we’re about to cover will influence both sides of the equation).

Here are four tactics you can try:

1. Optimize and automate customer onboarding.

Customer onboarding occurs after a customer makes their first purchase. If you want to avoid customer churn, getting onboarding right is critical.

The ideal onboarding process looks different for every company (and sometimes, it even differs amongst customer segments). Still, it typically involves education about the brand, the product, and how to use the product. We recommend studying the onboarding processes of your favorite brands to get inspiration and learn more about best practices.

Customer.io’s onboarding process stands out here – when a new customer signs up for the service, they automatically receive an 8-day email sequence that walks them through how to set up their first email campaign, complete with video walkthroughs for each step.

2. Focus on building long-term relationships with your customers.

When customers trust your brand, they’re more likely to stick around (and their CLV will increase as a result). Be sure to check in with your customers regularly to ensure they’re still receiving value from your product – Customer Satisfaction Score (CSAT) surveys are a great way to do this.

3. Increase average order value.

Average purchase value is an input variable in the CLV equation. If you can find ways to increase it, your CLV will increase, too. Upsells, cross-sells, and complementary add-on products are all great ways to increase average order value. You can see this tactic in action across industries, from SaaS to fast food and everything in between.

On the SaaS side, you can increase average order value by encouraging customers to switch to annual billing plans. A fast food company like McDonald’s employs this strategy by asking customers if they’d like to add a drink, fries, or other small purchases to their order. And ecommerce brands frequently display complementary products after a customer has added an item to their cart.

4. Reward loyal customers.

A loyalty program can incentivize customers to stick around and spend more. There’s no one-size-fits-all approach to loyalty programs, but two common methods include reward points and free or discounted products or features.

I know my company’s Customer Lifetime Value… now what?

Again, an increased CLV is only helpful if you use the data to inform decisions throughout the business. Here are some examples of how you can put CLV to work in your organization:

Use CLV to optimize marketing and sales: When you know which customers are most valuable, you can tailor your marketing and sales strategies more effectively. This information can help you avoid investing resources in customers who don't have high potential lifetime values or who may eventually leave the company.

Use CLV to improve customer retention strategies. A high CLV indicates that customers are more likely to stay with your company over time. This can result in decreased churn rates and higher revenue. A low CLV, on the other hand, suggests that your customer retention strategies could use some work. Dig into the data (both qualitative and quantitative) to determine where and why customers are churning.

Use CLV to forecast and plan. When you have visibility into the average lifetime values of your current customers, you can use that information to forecast sales and plan for the future.

What are the differences between Predictive and Historical Customer Lifetime Value models?

The CLV model we’ve explained so far is a historical CLV model; it uses past data to predict future behavior. But, it doesn’t consider whether a customer will continue being a customer.

The predictive CLV model analyzes customers’ historical behavior alongside predicted churn to estimate future customer lifetime and revenue. Machine learning makes this possible if you have enough customer data.

The formula for predictive CLV is the same as historical CLV, but you can expect a far more accurate prediction for expected lifespan thanks to churn modeling.

However, both options are entirely valid models for calculating CLV.