如何用SQL计算客户生命周期价值

本文将提供一个简单的方案来帮助你计算用户的生命周期价值（LTV）

Statsbot团队针对不同的客户和不同的商业模型预估了592次LTV（用户生命周期价值，Custom lifetime value）。我们在本文以及免费的电子书中分享了我们如何使用不涉及复杂的统计模型的SQL语句来计算客户生命周期价值。

用户生命周期价值，简写为LTV，是客户在他们的“生命”当中将会在我们的产品上花费的钱或者间接为我们带来的收益的数值。这是衡量你可以花费多少钱来获得新客户的重要指标。举例来说，如果你的获客成本（CAC，custom acquisition cost）为$150，LTV为\$600，那么你可以考虑增大预算来获得更多的客户来发展你的业务。CAC和LTV平衡与否可以帮助你检验任何企业在市场中的生存现状。

LTV是基于过去的模式来预测未来收入的预测性指标，它可以帮助你了解当前企业当前的风险程度以及你可以投入多少钱来获取新的客户。单独分析LTV也可以帮助你发现对于你来说最有价值的客户是怎样的——他们不仅仅是当前也是将来最有价值的客户。

为了理解如何计算LTV，我们需要考虑在用户与我们产品的生命周期结束时产生的总价值。那我们从一个简单的情况开始，假设一个客户使用了我们的产品12个月，并且每个月花费了$50。

显然，这个用户在他的生命周期中花费了$50*12 = \$600。从这个场景来看，我们可以将LTV定义为指定用户付款的总和。

这个定义可以扩展至某个用户群组。如果我们想要得到某个用户群的平均LTV，可以从来源于该用户群整体的收益以及该用户群的人数出发进行计算。当我们预估某个用户群的LTV时，我们需要知道用户会在我们的产品上停留多久。而为了得到这个指标，我们需要回头审视我们在过去的时间中损失了多少用户，以及用户的流失率。

如何计算软件服务的LTV？

在群组的层级上计算LTV的基本公式如下：

ARPU为每个月每个用户的平均获利，流失率（Churn rate）是我们的用户流失的速率（留存率则相反）。

上面的公式是基于下面的假设得到的：

Next Month Revenue = (Current Month Revenue) * (1 - Churn Rate)

注意：在我们计算软件服务的LTV时，我们没有考虑毛利率，这是因为软件服务的成本较低，对结果的准确率影响不大。但是在我们计算电子商务场景下的LTV时，我们的公式中会考虑这一点（COGS，具体内容可以在电子书的PREDICTIVE LTV FOR ECOMMERCE一节中查阅）

上面提到的LTV公式的局限性在于我们假设用户的流失随时间线性分布，即我们的一个客户在第一个月流失的概率和在第二个月流失的概率是相等的。如果你理解LTV这一概念的话，应当知道它是一个几何级数的求和，线性的流失率对应的图像并不是一条直线（很多文章中展示的图像也佐证了这一点）

当然，我们知道线性流失率并不能概括所有的场景。

在相对灵活的订阅模型当中，用户很容易在“体验服务”这一流程中流失，但是一旦用户使用一段时间之后，他们就不太可能会离开了。

最终我们可以看到，LTV取决于用户和产品之间的消费模型：比方说，按年度的订阅模式得到的LTV会更贴合我们上述公式。

而没有直接消费的产品在用户很容易在最初的时候流失，但是之后用户流失的速率可能会减缓。

我们可以通过图形直观地思考这一概念：

如果用户群的LTV为曲线下的面积，我们可以很清楚地看出，两种情况下客户的流失率对LTV估计值影响的程度相差很大。所以我们在计算的时候需要考虑到这一点，但是在最初估算的时候，使用最简单的公式也是合理的，在此之后我们可以逐步提高模型的复杂程度。

使用SQL提取ARPU和用户流失率

为了对LTV进行最基本的估计，我们需要处理一下我们的交易记录。从交易记录当中我们可以确定每个客户的平均收入以及我们观察的时间段内用户的流失率。为了简单起见我只查看了去年的记录。

我们可以分两步计算ARPU：

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)

得到的结果如下：

从上面的结果我们可以进一步计算得到该用户群每月的平均收益为$987.33。

计算用户流失率会复杂一点，因为我们需要计算上一个月留存的用户当月流失的百分比，我们需要获取该用户首次访问的月份并依次检测下个月他们是否流失。

这里的问题在于传统的数据库当中用户的访问记录为一个个单独的行而不是位于同一行。

解决这个问题的方法是使用自连接，这样我们就可以在一行中看到某用户的全部行为。

为了避免波动的用户的影响，我们将从第一个月开始查询，利用left join获得下一个月的访问id，如果下个月没有访问则为null。

WITH monthly_visits AS 
(SELECT
     DISTINCT
     Datediff(month, ‘2010-01-01’, transaction_date) AS visit_month, 
     cust_id 
FROM            transactions 
WHERE
transaction_date > dateadd(‘year’, -1, current_date)), 
(SELECT
avg(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)
)

为了简单，我们假设我们得到的结果为0.1。

现在我们已经得到了ARPU和用户流失率，只需要再完成一个简单的除法我们就可以得到预估的LTV了！

$987.33/0.1 = $9873.3

正如前面所说，这个公式是有局限性的，因为它的假设在真实世界当中经常是不成立的。其中最主要的一点就是它假设了在不同群组和当前留存时间不同的用户的流失率是相等的。

不同群组的流失率相同暗示了最近加入的用户流失的概率和之前获得的用户流失的概率可能是接近的，而当前已经留存时间不同的用户的流失率相同意味着用户在开始的时候流失的概率和后来流失的概率是一样的（比方说两年以后）。根据这些假设得到的结果与事实的接近程度，你可以对LTV的估计值进行进一步的修正。

如果你想了解如何在电子商务场景下为每个群组估算LTV，请下载我们的免费电子书并参考其中的SQL来计算用户的生命周期价值。