How to Use SQL to Calculate Customer Lifetime Value (LTV)

Working with data is an essential skill for marketers in today's data-driven world. As a marketer myself, I decided to create a series of articles about calculating key performance indicator (KPI) metrics for SaaS (Software as a Service) companies. We’ll start by calculating customer lifetime value (LTV) using SQL.

Data is one of today’s most valuable resources. If you work in a SaaS company, you are flooded with a huge amount of data every day. Without the support of a full Business Intelligence department, it is often difficult for a marketer to profit from such data.

Even if you are lucky enough to be able to rely on your IT colleagues, it is liberating to be able to analyze key metrics on your own. Excel and Google Sheets are great analytical tools, but you’ll quickly come to the point where you need to dig deeper and look for your data directly in the database. And that's when you need to be able to write SQL queries.

As a marketer without a degree in computer science, I know that gaining this kind of knowledge without a formal technical education may seem difficult. But don't worry – you don’t have to know IT to learn SQL. Thanks to the online courses on LearnSQL.com, all you’ll need to do is find some time in your busy calendar and enroll in the SQL Fundamentals course. You can learn SQL for sure, even if you haven't had any previous experience with databases.

Since you are reading this article, you probably already know that this blog is worth following. It has plenty of articles to help you broaden your skills and achieve your goals.

I did exactly that. A few weeks ago, I started to learn SQL; now not only do I use it on a daily basis as a marketer, I also wrote this article. I didn't think it would all go so quickly, but I am an example that it can be done. So no excuses – let's get to work!

Computing LTV and Related SaaS KPI Metrics Using SQL

My initial goal was to compute the LTV of our customers based on GEO segments. I ended up with a pretty complex query that also computed some other interesting KPIs you may find useful for your SaaS business.

Although you’re welcome to adapt my approach to your needs, it’s usually not possible to provide a copy/paste query for every SaaS business. Each business has different data architecture and limitations. However, I will explain how to use SQL to calculate each metric and bring them all together to get the LTV value. Specifically, I will describe how we compute these six SaaS KPIs at UptimeRobot:

1. Total Current Customers
2. Current Average Monthly Recurring Revenue (AVG MRR)
3. Customer Lifetime value (LTV)
4. Current Monthly Recurring Revenue (MRR)
5. All Time Average Revenue per User (ARPU)
6. Average Customer Lifetime

Why Calculate Customer Lifetime Value?

I am a really big fan of Stephen Covey’s end goal mindset, so I will start from the end. Knowing the lifetime value of your customers is the key to success for most of your marketing efforts and decisions. You’ll want to see LTV for segments like:

• Source of customer acquisition
• The day, month, or year of customer acquisition
• Products (lifetime value of customers using different products)
• Payment type (PayPal, credit card, invoices)
• Payment period (monthly vs. annual)
• Geographical location

As a new CMO, I was wondering if there are significant differences in revenue made by our customers across countries. By answering this question, we could better prioritize our activities and hopefully identify new opportunities for harvesting low-hanging fruits.

It would be also great to know the LTV based on the source of customer acquisition, but – due to current technical limitations in web analytics – I don't see any method of developing this report based on reliable data.

No matter which segment you choose, first you will need to compute the overall LTV of your customer base. To get that, you’ll first need to compute some partial KPIs. Then you can segment. However, you will need to know where you are heading in the beginning so you will know how to prepare your data.

Customer Lifetime Value (LTV) Formula

There are several methods to compute customer lifetime value. This one suits my needs best:

LTV = ARPU * Avg. Lifetime

I will be using data we store in the payments table. I will compute the LTV with the data from this one table, but you might need to work with multiple tables. For that, you’ll need to understand SQL JOINs. If you’re not familiar with them, I recommend looking at the SQL JOINs course.

I will be using JOIN in my computation of LTV. This Illustrated Guide to the SQL Self Join helped me understand this powerful concept. If you want more, you can also read SQL JOIN Types Explained.

We store records about every payment any user makes in the payments table. To find the LTV, I’ll be using these fields:

• userID – Unique identifier for every user.
• paymentDateTime
• paymentStatus – Where 0 = failed, 1 = success, and 2 = refunded.
• paymentPeriod – In months: 1 for monthly payments, 12 for annual payments.
• paymentAmount
• paymentBillingCountry

 Take a look at some example records showing the latest payments:

If I order this data by userID, you can see that one user can have multiple records. Each row represents a unique payment the user made. Some of the payments have a paymentStatus = 0, which means the payment failed.

Some records have a NULL value in paymentBillingCountry. To be honest, this should not happen; I found it out as I was writing this article and I have to ask our developers to fix it. That's a real-life example for you. You always find something unpredictable.

Computing Monthly Recurring Revenue and Average Revenue Per User

Let's start computing the LTV part by part. First, we find the ARPU (average revenue per user) by computing the MRR (monthly recurring revenue) of our current customers and dividing it by the number of total current customers by country.

Here is the query for this job:

/*Total Current Customers, Current MRR by Country calculation*/
(SELECT paymentBillingCountry, count(DISTINCT userID) Total_Current_Customers, ROUND(SUM(paymentAmount / paymentPeriod)) AS CurrentMRR
FROM payments
WHERE paymentDateTime < CURDATE() AND DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH) >= CURDATE() AND paymentStatus = 1 AND paymentPeriod > 0 AND userID IS NOT NULL
GROUP BY paymentBillingCountry
ORDER BY CurrentMRR DESC) AS arpu

We select paymentBillingCountry since we want to group the data by country, then with count(DISTINCT userID) we count the number of Total_Current_Customers. The DISTINCT statement ensures that we count every customer just once, even if they’ve made multiple payments.

Lastly, we count the CurrentMRR generated by all users from the given country as the SUM of the payment amount divided by the payment period (to prevent including whole payments in case of annual payments).

ROUND(SUM(paymentAmount / paymentPeriod)

We use the ROUND() function to make the output easier to digest (nicer numbers).

All the magic happens in the WHERE clause. Here, we filter just the payments of current (active) customers. We use the DATE_ADD function for this. By adding the paymentPeriod to the paymentDateTime, we can check if the customer's expiration date is after the current date.

DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH) >= CURDATE() 

If this condition is TRUE, it means the customer is active and we want to include their last payment into the computation of CurrentMRR.

To get the desired result, we need to use GROUP BY paymentBillingCountry. If you are not familiar with GROUP BY, you can learn more about it here.

There are also some other conditions in the WHERE clause:

AND paymentStatus = 1 AND paymentPeriod > 0 AND userID IS NOT NULL

These are mostly specific to our business case. We want to include only the paymentAmount of successful payments (paymentStatus = 1) and exclude payments for specific non-recurring products (paymentPeriod > 0) as well as payments without an assigned userID (userID IS NOT NULL).

In the last line of code, we order the result based on the CurrentMRR (ORDER BY CurrentMRR DESC).

The whole query is in parentheses, followed by AS arpu. If we want to run this query by itself, we will need to remove this. It's there because we will need it later, when we will be joining it with another query.

Are you missing the ARPU? That's correct. We will compute it later for each country in the master query by dividing CurrentMRR by Total_Current_Customers. This gives us the average monthly recurring revenue (average MRR) generated by current (active) customers for each country.

When you get this result, you can just copy/paste it into the Google Sheets or Excel and make a nice visualization of this analysis. I am sure your stakeholders will love it. Even for you, it will be much more insightful.

Computing Average Customer Lifetime

Once we have the ARPU by country ready, we need to get the average customer lifetime. This metric is the average length of time a customer is active/paying. I will describe two solutions:

1. Computing the difference between the first and the last payment the customer made.
2. The average of all the payments the customer made during the payment period.

It's up to you (and the data you have available) which solution you choose. I began with the first one, but later I realized it is not 100% precise. It does not take into account the situations when users stop paying for a while and then later renew their subscription.

Since we store the number of months the customer has pre-paid the payment in the paymentPeriod field, I was able to come up with a second solution. Don't worry if you do not have this data available. I was comparing it and I can tell you that there were really minor differences. If you are analyzing a big enough data set, you should be safe.

Let's look at the first solution:

/*AVG LifeTime by Country based on MAX - MIN Payment date + Last PaymentPeriod)*/
(SELECT paymentBillingCountry, AVG(LT.LifeTime) AS LT
FROM

/*LifeTime last Last - First Payment Difference by User ID with PaymentBilling Country */
(SELECT MinP.userID, MinP.paymentBillingCountry, ((DATEDIFF(MAX(MinP.paymentDateTime), MIN(MinP.paymentDateTime))/ 30.5) + LP.paymentPeriod) AS LifeTime
    FROM payments MinP
    LEFT JOIN
 
/*last payment with Payment Period*/
(SELECT p1.userID, p1.paymentBillingCountry, DATE_FORMAT(p1.paymentDateTime, '%d/%m/%Y') AS paymentDateTime, p1.paymentPeriod
FROM payments p1
LEFT JOIN payments p2 ON (p1.userID = p2.userID AND p1.paymentDateTime < p2.paymentDateTime AND p2.paymentType = 2)
WHERE p2.paymentDateTime IS NULL AND p1.paymentDateTime IS NOT NULL AND p1.paymentPeriod > 0
GROUP BY p1.userID
ORDER BY p1.paymentID DESC) LP
 
ON MinP.userID = LP.userID
 
GROUP BY MinP.userID) LT
 
GROUP BY paymentBillingCountry
ORDER BY paymentBillingCountry) avgLT

I will start explaining from the /*last payment with Payment Period*/ part. The task seems to be pretty simple, but it is not a trivial task. I need to add the paymentPeriod of the last payment customer made to the difference between the last and first payment they made.

I was dealing with a similar problem and found the solution on Stack Overflow. It's a problem of getting records with max value for each group of grouped SQL results. This is the solution that worked for me:

Source: StackOverflow.com

To be honest, I did not understand it until I read the great explanation of self-JOINs that I mentioned earlier. Thanks to this article, I was able to visualize this problem and the solution.

I get the customer’s last payment date alongside the payment period for this payment with the following part of the code.

(SELECT p1.userID, p1.paymentBillingCountry, DATE_FORMAT(p1.paymentDateTime, '%d/%m/%Y') AS paymentDateTime, p1.paymentPeriod
FROM payments p1
LEFT JOIN payments p2 ON (p1.userID = p2.userID AND p1.paymentDateTime < p2.paymentDateTime AND p2.paymentType = 2)
WHERE p2.paymentDateTime IS NULL AND p1.paymentDateTime IS NOT NULL AND p1.paymentPeriod > 0
GROUP BY p1.userID
ORDER BY p1.paymentID DESC) LP

The LP at the end is the alias of this new temporary table and it means LastPayment. This new table is joined with the original payment table:

/*last payment with Payment Period*/
(SELECT p1.userID, p1.paymentBillingCountry, DATE_FORMAT(p1.paymentDateTime, '%d/%m/%Y') AS paymentDateTime, p1.paymentPeriod
FROM payments p1
LEFT JOIN payments p2 ON (p1.userID = p2.userID AND p1.paymentDateTime < p2.paymentDateTime AND p2.paymentType = 2)
WHERE p2.paymentDateTime IS NULL AND p1.paymentDateTime IS NOT NULL AND p1.paymentPeriod > 0
GROUP BY p1.userID
ORDER BY p1.paymentID DESC) LP
 
ON MinP.userID = LP.userID
 
GROUP BY MinP.userID) LT

In this part, I compute the difference between the last and first payment the customer made. I get results in the day format, so I divide it by the average number of days in a month (30.5) and add the paymentPeriod.

((DATEDIFF(MAX(MinP.paymentDateTime), MIN(MinP.paymentDateTime))/ 30.5) + LP.paymentPeriod)

This gives me the lifetime for each customer and their paymentBillingCountry:

But I need the average by country. I achieve this one level higher in my nested query by selecting this ...

(SELECT paymentBillingCountry, AVG(LT.LifeTime) AS LT

… and grouping it by country:

GROUP BY paymentBillingCountry

I also order it by country:

ORDER BY paymentBillingCountry

And when I run the whole query, I get the desired result: the average customer lifetime by country.

This method would be much simpler if we did not have to consider the last payment period in the computation. Fortunately, the second solution doesn’t:

/*AVG LifeTime by Country based on SUM of PaymentPeriod)*/
 
(SELECT ULT.paymentBillingCountry, AVG(ULT.UserLifeTime) AvgLifeTimeByCountry
FROM
    (SELECT userID, SUM(paymentPeriod) UserLifeTime, paymentBillingCountry
    FROM payments
    GROUP BY userID
    ORDER BY userID) ULT
GROUP BY paymentBillingCountry) ULT1

We start with counting the SUM of the payment period for all payments the customer made. I call this metric UserLifetime. To get the desired result, we need to group it by userID:

(SELECT userID, SUM(paymentPeriod) UserLifeTime, paymentBillingCountry
FROM payments
GROUP BY userID
ORDER BY userID) ULT

We use this new table in the FROM clause to compute the average lifetime of customers by country.

And here is the result:

You can see that there are not any big differences for most countries. But in Albania (ALB), the difference between the two methods is quite significant (17.38 vs. 28.5). This is caused by the small number of customers from this country (5). As I said, you should be safe if you have enough data. If we look at the USA, where we have most of our customers (8,483), the difference in lifetime between the two methods is almost nothing (21.63 vs. 21.16). It makes sense that the first method gives a slightly higher result, since it doesn’t account for customers that stop and then renew their subscription.

Putting It All Together: Calculating LTV with SQL

Now that we have prepared the following by country ...

• CurrentMRR
• Total_Current_Customers
• Average Customer Lifetime

... we just need to join everything in one master query:

/*Current Avg. MRR / LTV by Country Calculation*/
SELECT arpu.paymentBillingCountry, arpu.Total_Current_Customers, (arpu.CurrentMRR/arpu.Total_Current_Customers) AS CurrentAvgMRR_ARPU, ULT1.AvgLifeTimeByCountry AS LifeTime, (ULT1.AvgLifeTimeByCountry * (arpu.CurrentMRR/arpu.Total_Current_Customers)) AS LTV
FROM	
 
/*Total Current Customers, Current MRR by Country calculation*/
(SELECT paymentBillingCountry, COUNT(DISTINCT userID) Total_Current_Customers, ROUND(SUM(paymentAmount / paymentPeriod)) AS CurrentMRR
FROM payments
WHERE paymentDateTime < CURDATE() AND DATE_ADD(paymentDateTime, INTERVAL paymentPeriod MONTH) >= CURDATE() AND paymentStatus = 1 AND paymentPeriod > 0 AND userID IS NOT NULL
GROUP BY paymentBillingCountry
ORDER BY CurrentMRR DESC) AS arpu
 
LEFT JOIN
 
/*AVG LifeTime by Country based on SUM of PaymentPeriod)*/
(SELECT ULT.paymentBillingCountry, AVG(ULT.UserLifeTime) AvgLifeTimeByCountry
FROM
    (SELECT userID, SUM(paymentPeriod) UserLifeTime, paymentBillingCountry
    FROM payments
    GROUP BY userID
    ORDER BY userID) ULT
GROUP BY paymentBillingCountry) ULT1
 
ON ULT1.paymentBillingCountry = arpu.paymentBillingCountry

And when I run it, I get this result:

I can see where most of our customers come from and how the Current MRR, Lifetime and LTV values differ across countries.

Ready to Write Your Own KPI SQL Queries?

And here you have a recipe for computing customer lifetime value and some accompanying SaaS KPIs. I hope you’ve enjoyed my experiences and how I’ve used SQL in these queries. Or maybe you have an idea how to write these queries better? Let me know in the comments.

Stay tuned for the next article from our SaaS KPIs metrics series. I've already started working on it.

In the meantime, if you are interested in data analysis, I recommend LearnSQL.com’s SQL Reporting track. This is a great set of courses and interactive exercises. In it, you’ll learn to create SQL reports, to perform trend analysis using SQL, and how to analyze the customer lifecycle.

Remember, learning SQL and mastering databases just pays off!