如何使用 SQL 计算客户生命周期价值 (LTV)
了解如何在 Metabase 中使用 SQL 计算客户生命周期价值。
在我们的客户生命周期价值入门中，我们讨论了一些公司在该指标上犯的错误，并为如何使用 LTV 提供了一些指导。本指南采用更实用的方法：我们将精确地解决基于订阅的公司如何使用 Metabase 中的 SQL 查询来估计客户在其作为客户的生命周期内将花费的总金额。
我们将首先回顾用于确定 LTV 的公式以及您需要获得的指标，然后我们将提供一个示例 SQL 查询，您可以运行该查询以获取 LTV 数据。如果您只是在寻找该示例 SQL 查询，请随时跳至此处。
基本 LTV 公式
这个针对基于订阅的 SaaS 公司的简单公式是计算 LTV 的良好起点，它将每位客户的平均收入 (APRC) 除以订阅流失率
Customer LTV = ARPC / Churn rate
在整个计算过程中坚持使用单个间隔。如果您按季度收费，那么计算每月的订阅数量不会非常有用。在我们的示例中，我们将按月计算。
基于现有的 Metabase 问题构建
为您的 LTV 计算借鉴现有问题或模型可以节省大量精力，因此值得检查您的组织中是否有人自己完成了这些计算。您甚至可以直接从您使用的任何第三方支付处理商（例如 Stripe 的收入或流失数据）访问这些计算指标——如果是这种情况，建模 LTV 就会容易一些。
我们的目标：LTV 表
我们的目标是最终得到一个表格，其中每行代表一个结算周期，列对应于该结算周期的特定数据。生成的表将包含以下字段
- 结算周期月份
- 每月经常性收入 (MRR)
- 订阅数
- 每位客户的平均收入 (ARPC)
- 订阅流失率
- 客户生命周期价值 (LTV)
您的数据外观
为了简化我们的示例，我们将假设我们从三个表开始：Invoices、Subscriptions 和 Revenue changes
发票
| invoice_id | subscriber_id | month | amount_dollars |
| ---------- | ------------- | ------------- | -------------- |
| N001 | S001 | January 2021 | 100 |
| N002 | S002 | January 2021 | 150 |
| N003 | S001 | February 2021 | 100 |
| N004 | S002 | February 2021 | 150 |
| N005 | S003 | February 2021 | 200 |
| N006 | S001 | March 2021 | 100 |
| N007 | S003 | March 2021 | 200 |
| ... | ... | ... | ... |
订阅
| subscriber_id | active | monthly_invoice | created_at | cancelled_at |
| ------------- | ------ | --------------- | ------------- | ------------ |
| S001 | Yes | 100 | January 2021 | |
| S002 | No | 150 | January 2021 | March 2021 |
| S003 | Yes | 200 | February 2021 | |
| ... | ... | ... | ... | ... |
收入变更
| month | invoice_id | subscriber_id | dollar_change | change_type |
| ------------- | ---------- | ------------- | ------------- | ----------- |
| January 2021 | N001 | S001 | 100 | new |
| January 2021 | N002 | S002 | 150 | new |
| February 2021 | N003 | S001 | 0 | retain |
| February 2021 | N004 | S002 | 0 | retain |
| February 2021 | N005 | S003 | 200 | new |
| March 2021 | N006 | S001 | 0 | retain |
| March 2021 | N007 | S002 | -150 | removed |
| March 2021 | N008 | S003 | 0 | retain |
| ... | ... | ... | ... | ... |
步骤 1：计算您的 LTV 前指标
我们将首先逐步介绍查询，以确定以下三个基线指标，这些指标在计算生命周期价值中发挥作用
每月经常性收入 (MRR)
每个付款周期（在我们的例子中，是一个月）的总经常性收入让我们了解了我们可预测的收入来源。为了获得这个数字，我们将计算 Invoices 表中 amount_dollars 字段的总和。如果我们只是想计算这个值，我们会做类似的事情
SELECT
month,
sum(amount_dollars) AS mrr
FROM
invoices
GROUP BY month
这是输出结果
| month | MRR |
| ------------- | --- |
| January 2021 | 250 |
| February 2021 | 450 |
| March 2021 | 300 |
在我们获取 LTV 的最终查询中，我们将使用以下子查询计算 MRR
sum(amount_dollars) AS mrr,
每位客户的平均收入 (ARPC)
ARPC 让我们了解我们从每位客户获得的收入。我们将首先按月对活跃订阅数进行计数，然后将 MRR 除以该数字。
如果我们想从我们的 Invoices 表中计算 ARPC，我们将执行以下操作
SELECT
month,
sum(amount_dollars) AS mrr,
count(DISTINCT subscription_id) AS subscriptions,
(mrr / subscriptions) AS arpc
FROM
invoices
GROUP BY
month
这是此步骤后的输出
| month | MRR | subscriptions | ARPC |
| ------------- | --- | ------------- | ---- |
| January 2021 | 250 | 2 | 125 |
| February 2021 | 450 | 3 | 150 |
| March 2021 | 300 | 2 | 150 |
我们将在我们的最终 SQL 查询中包含以下子查询来计算 ARPC
(mrr / subscriptions) AS arpc
订阅流失率
流失率是一个比率，指示在最近的付款期内，有多少客户停止为您的服务付费。要计算订阅流失率，请将上个月的订阅结转数量除以上个月的订阅总数。
我们将在查询开始时使用两个 CTE 来计算流失率
WITH total_subscriptions AS (
SELECT
date_trunc('month', invoices.date) AS month,
count(DISTINCT invoices.subscription_id) AS subscriptions,
sum(amount_dollars) AS mrr
FROM
invoices
GROUP BY
1
),
churned_subscriptions AS (
SELECT
s.month,
s.subscriptions,
s.mrr,
lag(subscriptions) OVER (ORDER BY s.month) AS last_month_subscriptions,
count(DISTINCT CASE WHEN revenue_changes.change_type = 'removed' THEN
revenue_changes.subscription_id
END) AS churned_subscriptions
FROM
total_subscriptions s
LEFT JOIN revenue_changes ON s.month = revenue_changes.month
GROUP BY
1,
2,
3
)
我们从这些 CTE 获得的结果将如下所示
| month | churned_subscriptions | last_month_subscriptions |
| ------------- | --------------------- | ------------------------ |
| January 2021 | | |
| February 2021 | 0 | 2 |
| March 2021 | 1 | 3 |
步骤 2：LTV 的 SQL 查询
当我们准备好执行完整查询时，我们将从 Metabase 的主导航栏中选择+ 新建 > SQL 查询，然后输入以下代码
WITH total_subscriptions AS (
SELECT
date_trunc('month', invoices.date) AS month,
count(DISTINCT invoices.subscription_id) AS subscriptions,
sum(amount_dollars) AS mrr
FROM
invoices
GROUP BY
1
),
churned_subscriptions AS (
SELECT
s.month,
s.subscriptions,
s.mrr,
lag(subscriptions) OVER (ORDER BY s.month) AS last_month_subscriptions,
count(DISTINCT CASE WHEN revenue_changes.change_type = 'removed' THEN
revenue_changes.subscription_id
END) AS churned_subscriptions
FROM
total_subscriptions s
LEFT JOIN revenue_changes ON s.month = revenue_changes.month
GROUP BY
1,
2,
3
)
SELECT
month,
(mrr / subscriptions) AS arpc,
(churned_subscriptions / last_month_subscriptions::float) AS subscription_churn_rate,
(mrr / subscriptions) / (churned_subscriptions / last_month_subscriptions::float) AS ltv
FROM
churned_subscriptions
WHERE
month >= '2021-01-01'
一旦我们运行查询，我们最终将得到一个包含 LTV 列的表格，这是我们一直追求的指标
| month | MRR | subscription_total | ARPC | subscription_churn_rate | LTV |
| ------------- | --- | ------------------ | ---- | ----------------------- | ----- |
| January 2021 | 250 | 2 | 125 | | |
| February 2021 | 450 | 3 | 150 | 0.00 | |
| March 2021 | 300 | 2 | 150 | 0.33 | 454.5 |
步骤 3：可视化您的 LTV
最后，将此查询可视化为折线图可以帮助我们更好地分析该指标随时间的变化情况。以下是 Metabase 中的其他一些 LTV 计算，可视化为表格和折线图
现在我们有了这个指标，我们可以用它来制定诸如营销工作、人员配置需求和功能优先级排序等方面的决策。