如何用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来计算用户的生命周期价值。

本文的版权归 ArrayZoneYour 所有,如需转载请联系作者。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户3246163的专栏

[脑书笔记]《整体性学习》3-拓展观点技术和记忆随意信息技术

这篇脑书继续讲整体性学习的第二部分整体性学习的技术,在《整体性学习》1里面在谈到信息进入大脑的顺序是,获取,理解,拓展,纠错和应用。这篇脑书笔记主要针对这5个步...

11920
来自专栏AI研习社

DeepMind提出增强想象智能体 这次能帮你赢游戏 | 2分钟读论文

来源 / Two Minute Papers 翻译 / 张丹婷 校对 / 囧囧 整理 / 雷锋字幕组 本期论文 "Imagination-Augmented ...

39970
来自专栏新智元

【前沿】CMU研发算法透明化系统,点亮机器学习黑箱

【新智元导读】现在的机器学习系统并不是完全安全可靠的,这不仅是因为我们无从得知黑盒子里发生的事情,还因为用人类生成的数据训练机器,难免会把人类的偏见和固有缺陷也...

26350
来自专栏AI科技评论

产业报告 |“Deep Learning”再次无缘Gartner技术成熟度曲线

雷锋网按:Gartner技术成熟度曲线(Hyper Cycle)是Gartner著名并受到市场广泛认可的一项市场评估手段。它通过量化一个技术从诞生到进行成熟的产...

39950
来自专栏Python中文社区

用Python分析股市指数

專 欄 ❈本文作者:王勇,目前感兴趣项目商业分析、Python、机器学习、Kaggle。17年项目管理,通信业干了11年项目经理管合同交付,制造业干了6年项目...

326100
来自专栏机器人网

这个机器人太牛了,陌生物体抓取识别成功率高达100%

给杂货拆包是一件简单但乏味的工作:手伸进包里,摸索着找到一件东西,然后把它拿出来。简单瞄一眼之后,你会了解这是什么东西,它应该存放在哪里 如今,麻省理工学院和普...

43880
来自专栏大数据文摘

全方位搜集汪星人行为数据,让AI学做一只狗 | 华盛顿大学最新研究

22350
来自专栏PaddlePaddle

前沿|PaddlePaddle开源项目DeepNav“无人船”炼成记(一)

前言:本系列将集中展示PaddlePaddle的开源项目,即PaddlePaddle研发团队在深度学习领域的前沿研究成果。首先展示DeepNav自动驾驶船项目,...

11910
来自专栏大数据文摘

百度为人工智能测试违规道歉

307100
来自专栏数据科学与人工智能

【应用】信用评分卡:高级分析

当一位年轻的商业分析师向我们讲述他最近回家的事件时,充满分析师的房间爆发出一阵响亮的笑声。 一位遥远的阿姨询问了他的新职业。 他的回答 - 我正在进行建模。 她...

14720

扫码关注云+社区

领取腾讯云代金券