原文：
www.kdnuggets.com/2018/02/calculating-customer-lifetime-value-sql-example.html
评论
作者：Luba Belokon，Statsbot
1. Google 网络安全证书 - 快速开启网络安全职业之路。
2. Google 数据分析专业证书 - 提升您的数据分析技能
3. Google IT 支持专业证书 - 支持您的组织 IT
Statsbot团队为不同客户和业务模型估算了 LTV 592 次。我们在这篇文章和一个*免费电子书*中分享了我们的经验，介绍了如何在没有复杂统计模型的情况下使用 SQL 计算客户生命周期价值。
客户生命周期价值（LTV）是指客户在其“生命周期”内，或者至少在他们与您关系中的一段时间里，会花费在您业务上的金额。这是一个重要的指标，能够帮助您了解可以花费多少资金来获取新客户。**例如，如果您的客户获取成本（CAC）为$150，而 LTV 为$600。**您将能够增加预算以吸引更多人，推动业务增长。CAC 与 LTV 之间的平衡使您能够检查任何业务的市场生存能力。
估算 LTV 是一个预测指标，它依赖于基于过去模式的未来购买情况，并使您能够看到作为企业您面临的风险以及您可以花费多少来获取新客户。在个体层面，它还使您能够确定谁是您未来可能的高价值客户。
为了理解如何估算 LTV，首先可以考虑在客户与我们关系结束时评估其生命周期价值。假设一个客户与我们保持 12 个月的关系，每月消费$50。
他们在整个生命周期内为我们的业务带来的收入为$5012 = $600。简单！我们可以将LTV 的基本定义*视为来自特定用户的支付总和。
同样的原则适用于群体。如果我们想要查看群体的平均 LTV，我们可以查看总支出除以客户数量。当我们谈论估算 LTV或预测 LTV 时，我们需要考虑客户与我们的停留时间。为了得到这一点，我们实际上是“倒退”来看：我们查看随着时间的推移我们失去多少客户，或流失率。
在群体层面，估算 LTV 的基本公式是这样的：
其中 ARPU 是每个用户的平均每月经常性收入，流失率是我们失去客户的速率（即留存率的倒数）。
这个基本公式可以通过假设得到：
下个月收入 = (当前月收入) * (1 – 流失率)
注意：当我们为 SaaS 估算客户生命周期价值时，可以忽略毛利率，因为成本很小，不会影响结果的准确性。但当我们稍后在本文中为电商计算预测 LTV 时，我们会将 COGS 包括在公式中。
上述 LTV 公式的主要限制是它假设流失是线性的，即：我们在会员服务的第一个月和第二个月之间失去客户的可能性与我们在之后很久失去客户的可能性是一样的。深入到预测 LTV 的本质，我们可以说它是一个几何级数的和，线性流失看起来不像是一条直线（如许多关于 LTV 的文章所示）。
实际上，我们知道线性流失通常不是情况。
在一个灵活的订阅模式中，我们在一开始就会失去很多人，当他们在“试用”一个服务时，但是一旦他们和我们待了很长时间，他们就不太可能离开。
最终，这取决于客户和业务之间存在的合同类型：例如，年度续订，其中流失更线性，将导致 LTV 非常接近上述公式。
没有合同的服务可能会失去很高比例的新客户，但流失可能会减缓。
我们可以图形化地考虑这个概念：
如果群体的 LTV 是线下的面积，我们可以很清楚地看到我们失去客户的速度将非常显著地影响我们的 LTV 估算。因此，我们在进行计算时需要考虑这一点。然而，对于 LTV 的初步估算，使用最简单的公式是有意义的。在此之后，我们将增加复杂性。
为了做出最基本的 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**。
计算流失率则复杂一些，因为我们需要计算从一个月到下一个月未返回的人的百分比，按他们第一次访问的月份将客户分组，然后检查他们在下个月是否返回。
问题在于，在事务性数据库中，我们的客户访问记录是分开行的，而不是全部在同一行上。
解决这个问题的方法是将事务性数据库与自身连接，以便我们可以在一行中看到客户的行为。
为了隔离那些流失的客户，我们将第 1 个月的访问数据与第 2 个月的访问数据按照 cust_id 左连接。第 2 个月访问数据中 cust_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 的结果，仅为简单起见。
因此，估算 LTV 是一个简单的计算：我们有每月 ARPU 和每月流失率，所以我们只需将一者除以另一者！
$987.33/0.1 = $9873.3
如前所述，这个公式有其局限性，主要是因为它做了一系列在现实中可能不成立的假设。主要的假设是留存率和流失率在群体间和时间间都是稳定的。
跨群体的稳定性意味着早期采用者的行为与晚期采用者类似，而跨时间的稳定性意味着客户在与你关系的开始时流失的可能性与例如两年后是一样的。根据这些假设的真实性，你可能需要下调 LTV 估算值。
如果你想了解如何估算电子商务的 LTV，针对群体和每个个体客户，请下载我们免费的电子书，内容涵盖了如何用 SQL 计算客户生命周期价值。
个人简介：Luba Belokon 是 Statsbot 的内容专家。
原文。经许可转载。
相关：
-
SQL 客户留存分析指南
-
机器学习算法：选择哪一种解决你的问题
-
集成学习提升机器学习结果