How to Calculate LTV (Lifetime Value) in SQL

Intro

According to the Pareto Principle or the 80:20 rule, 80 percent of all your revenue comes from just 20 percent of your current customers. That means that retaining your existing customers is far more important than acquiring new ones. The question is, how do you measure the current value of your customers?

What is LTV?

No matter what business model you fall under, you probably have some type of annual recurring revenue (ARR). However, optimizing around your existing customers can be challenging, which is exactly why knowing your customers' lifetime value (LTV) is so important.

LTV is the total revenue you can reasonably expect from a customer or account in a single business relationship. LTV looks at the current revenue your generating from a given customer/account and compares that against the predicted lifespan. A longer lifespan translates into a higher LTV.

How to Calculate LTV?

Calculating LTV is a bit complicated because you need to calculate several other metrics before defining your LTV. Here’s a list of the most important ones:

Average Purchase Value (APV): You can calculate this number by dividing your total revenue by your total number of orders. For subscriptions, you could easily adjust so that you divide your total revenue by the total number of months subscribed.

Average Purchase Frequency Rate (APF): You can calculate this by taking your total number of purchases and dividing it by your total number of customers. Depending on your business model, you can also swap out purchases for contracts.

Average Customer Value (ACV): You can calculate this number by taking your APV and multiplying it by your APF. You can also calculate your average contract value by taking your total contract value and dividing it by your total contract length (e.g., the total number of years your contracts are valid).

Average Customer Lifespan (ACL): You can calculate this number by adding up the total number of years of your customers and dividing that by your total number of customers.

Customer Acquisition Costs (CAC): You can calculate this by adding up the total costs of your sales and marketing teams over a set time period and dividing that by the number of new customers you’ve acquired.

To calculate your LTV, you must multiply your ACV by your ACL. For example, if your ACV was $10,000 and your ACL was five, your LTV would be $50,000. However, it’s important to note that you can use predictive and historical modeling to calculate your number. With historical modeling, you leverage your past data, and with predictive modeling, you analyze the behavior of your new and existing customers.

For the most part, predictive models tend to be more accurate because they enable you to identify your most valuable customers and highlight which products and services contribute to the most revenue. There’s a lot to account for when calculating your LTV. Still, it’s important to remember that you can easily edit these formulas to fit your specific use case and business model.

Why Does LTV Matter?

Aside from your north star metric (e.g., the core metric unique to your business), LTV is probably your company’s most critical data point. Every major B2B SaaS and subscription-based company monitors LTV extremely closely because it’s the most significant indicator of your overall business health. Knowing your LTV can help you reliably forecast, predict costs, optimize spending, and even raise money.

Your LTV determines your growth and the rate at which you can scale. However, the largest benefit of LTV is that it enables you to measure your return on investment (ROI) effectively.

Specifically, you can compare your LTV directly against your customer acquisition costs (CAC) to measure your profitability and break-even point. Once you know your LTV, you calculate your LTV to CAC ratio. You can calculate your LTV/CAC ratio by dividing your LTV by your CAC. A good LTV/CAC ratio is in the 3:1 range. For example, if your LTV for a given customer is $1 million, you shouldn’t spend more than $300,00 acquiring that customer.

How to Grow Your LTV

When you understand your LTV, you can look at what features or products are most common among your highest-value customers and incentivize them to increase product usage and order frequency. Knowing who your highest value customers is super valuable when optimizing your marketing campaigns and sales processes.

Knowing your LTV can also give you valuable insight into which customers are using your products and services so your marketing teams can target lookalike audiences across your ad platforms like Google and Facebook.

You can also analyze your LTV to improve your margins and customer retention rate and see exactly what factors are causing customers to churn and what offerings are driving the most revenue. For example, you might want to analyze the average value of your contracts/orders.

In addition, you identify accounts with a low LTV and look for ways to increase adoption and expand to grow your LTV. Maybe certain users are getting stuck in onboarding, or you want to give your free tier customers access to a particular feature. LTV allows you to identify trends and ask questions that weren’t previously possible.

Calculating LTV Using SQL

Knowing the formula for calculating LTV is helpful, but it’s not really actionable by your business teams (e.g., sales, marketing support, etc.). The good news is that all the data you need to calculate LTV already exists in your data warehouse. That means you can use standard SQL to calculate on both broad and individual account/customer levels. Here’s an example query we wrote.

As a reminder, to calculate your LTV, you must multiply your Average Customer Value (how much they spend) by your Average Customer Lifespan (how long they pay for). You first need to standardize on the time increment. For many recurring payment businesses like Subscriptions, customers pay either on a monthly or yearly basis. Let’s assume for our business that customers pay us monthly.

Then, we first need to define Average Customer Value (how much they spend) in SQL. Let’s assume we have a purchases table with the following schema:

Customer_id (int) | purchase_date (date) | purchase_amount (int)

Since my customers pay me monthly, I want to break down the total purchases by month by customer using the date_trunc function.

With purchases_by_month as (
  SELECT 
    customer_id, 
    date_trunc('month', purchase_date) as month, 
    SUM(purchase_amount) as total_purchases_that_month 
  FROM 
    purchases 
  GROUP BY 
    1, 
    2
)

Now that I have the amount they pay per month, I can take an average of that to get a sense of how much a customer typically pays us per month.

With ACV as (
  SELECT 
    AVG(total_purchases_that_month) as ACV 
  From 
    purchases_by_month
)

Then I need to calculate the Average Customer Lifespan (how long they pay for). I can do this by looking at the difference between the first purchase date and the most recent purchase date of a customer.

First, I’ll use the MAX and MIN functions to grab the first and most recent purchase.

With max_and_min_purchases as (
  SELECT 
    customer_id, 
    MAX(purchase_date) as most_recent_purchase, 
    MIN(purchase_date) as first_purchase 
  Group by 
    1
),

Then I’ll calculate the difference in time between them in days, divide by 30 to get the number of months in between.

SELECT 
  DATEDIFF(
    days, most_recent_purchase, first_purchase
  ) / 30.0 as months_in_between_purchases 
FROM 
  Lifespan_calculation
)

Finally, I can just take the average of that to get the average lifespan for a customer.

With average_lifespan as (
  SELECT 
    AVG(months_in_between_purchases) as ACL 
  FROM 
    Lifespan_calculation
)

Now that I have my ACV and ACL, I just need to divide them to get my overall LTV for my whole business!

SELECT 
  ACV / ACL as LTV 
From 
  average_lifespan 
  join ACV on 1 = 1

Combined, here is the entire SQL query:

With purchases_by_month as (
  SELECT 
    customer_id, 
    date_trunc('month', purchase_date) as month, 
    SUM(purchase_amount) as total_purchases_that_month 
  FROM 
    purchases 
  GROUP BY 
    1, 
    2
), 
ACV as (
  SELECT 
    AVG(total_purchases_that_month) as ACV 
  From 
    purchases_by_month
), 
max_and_min_purchases as (
  SELECT 
    customer_id, 
    MAX(purchase_date) as most_recent_purchase, 
    MIN(purchase_date) as first_purchase 
  Group by 
    1
), 
Lifespan_calculation as (
  SELECT 
    DATEDIFF(
      days, most_recent_purchase, first_purchase
    ) / 30.0 as months_in_between_purchases 
  FROM 
    Lifespan_calculation
), 
average_lifespan as (
  SELECT 
    AVG(months_in_between_purchases) as ACL 
  FROM 
    Lifespan_calculation
) 
SELECT 
  ACV / ACL as LTV 
From 
  average_lifespan 
  join ACV on 1 = 1

How to Implement LTV

Once you’ve built your LTV model in the warehouse, you simply need a way to sync that data to your downstream business applications, and your business teams can take action. Unfortunately, this often means forcing your data team to build and maintain a custom pipeline for every tool in your technology stack, which is simply not scalable.

As a Reverse ETL platform, Hightouch eliminates this problem and runs on top of your warehouse. With Hightouch, you can leverage your existing data models or write simple SQL to sync data from your warehouse to 100+ destinations. You don’t have to worry about manually uploading CSVs, integrating with 3rd party APIs, or writing custom scripts. You must define your data and map it to the appropriate fields in your end destination. You can get started with a free Hightouch workspace today!