How do you calculate LTV for SaaS?

At a group level, the basic formula for estimating LTV is this: Where ARPU is average monthly recurring revenue per user and the churn rate is the rate at which we are losing customers (so the inverse of retention). This basic formula can be obtained from assumption: Next Month Revenue = (Current Month Revenue) * (1 - Churn Rate) Note: When we’re estimating customer lifetime value for SaaS we can neglect Gross Margin, because costs are minor and don’t affect the accuracy of a result. But when we calculate predictive LTV for ecommerce later in this article, we’ll include COGS in our formula. The main limitation of the LTV formula above is that it assumes that churn is linear over time, as in: we are as likely to lose a customer between the first month of membership of our service and the second, as we are to lose them much later on. Going deeper into the nature of predictive LTV, we can say that it’s a sum of a geometric series , and linear churn doesn’t look like a straight line (as is shown in many articles about LTV).
In fact, we know that linear churn is usually not the case.
In a flexible subscription model, we lose many people at the very beginning, when they are “testing out” a service, but once they have been with us for a long time, they are less likely to leave. Ultimately, it depends on the type of contract that exists between customers and the business: for example, annual renewals, where churn is more linear, will result in LTV that is very close to the formula above. Services which do not have any contracts may lose a high percentage of their new customers, but then churn may slow down. We can think of this concept graphically: If the LTV of the group is the area under the line, we can very clearly see that the rate at which we lose customers will impact our LTV estimates very significantly. So we will need to take this into account when we are making our calculations. For a first estimate of LTV, however, it makes sense to go with the simplest formula. After that, we will add levels of complexity.

Extracting ARPU and churn using SQL

In order to make the most basic estimate of LTV, we need to look at our transaction history. Then, we can establish the average revenue per customer as well as the churn rate over the period that we are looking at. For simplicity, I’m going to look at the last year. You can calculate ARPU in 2 steps:
`month_ARPU AS(SELECT     visit_month,      Avg(revenue) AS ARPU FROM     (SELECT          Cust_id,          Datediff(MONTH, ‘2010-01-01’, transaction_date) AS visit_month,           Sum(transaction_size) AS revenue      FROM   transactions      WHERE  transaction_date > Dateadd(‘year’, -1, CURRENT_DATE)      GROUP BY           1,            2) GROUP BY 1)`
The results will look like this: In the case above, that would give us an average monthly spend of \$987.33. Calculating churn rate is a bit more complicated, as we need the percentage of people not returning from one month to the next, taking each group of customers according to the month of their first visit, and then checking if they came back or not in the following month.
The problem is always that, in a transactional database, we have customers’ visits on separate lines, rather than all on the same line.
The way to fix that problem is to join the transactional database to itself, so that we can see a customer’s behavior on one single line. In order to isolate those who churned, we take the visits from month 1, and left join the visits from month 2 on the cust_id. The lines where visits from month 2 have a cust_id that is null are the ones where the customer has not returned.
```WITH monthly_visits AS (SELECT     DISTINCT     Datediff(month, ‘2010-01-01’, transaction_date) AS visit_month,      cust_id FROM            transactions WHEREtransaction_date > dateadd(‘year’, -1, current_date)),
(SELECTavg(churn_rate) FROM     (SELECT          current_month,           Count(CASE                WHEN cust_type='churn' THEN 1                ELSE NULL      	  END)/count(cust_id) AS churn_rate      FROM          (SELECT               past_month.visit_month + interval ‘1 month’ AS current_month,                past_month.cust_id,                CASE                    WHEN this_month.cust_id IS NULL THEN 'churn'                     ELSE 'retained'                END AS cust_type           FROM               monthly_visits past_month   	       LEFT JOIN monthly_visits this_month ON                    this_month.cust_id=past_month.cust_id                    AND this_month.visit_month=past_month.visit_month + interval ‘1 month’          )data     GROUP BY 1))```
Say this gives us a result of 0.1, just for simplicity. It is a simple calculation, then, to estimate LTV: we have monthly ARPU and monthly churn, so we just divide one by the other! \$987.33/0.1 = \$9873.3 As stated earlier, there are limits to this formula, mostly because it makes a series of assumptions that may not hold in the real world. The main one is that retention and churn rates are stable both across cohorts and across time . Stability across cohorts implies that early adopters of your service act in similar ways to late adopters, while stability across time implies that customers’ likelihood of churning out is the same at the beginning of their relationship with you as it is, for example, 2 years in. Depending on how close to the truth these assumptions are, you may need to revise your LTV estimate downwards. If you want to lean how to estimate LTV for ecommerce, for cohorts and each individual customer, download our FREE ebook on calculating customer lifetime value with SQL.