如何使用 SQL 计算客户生命周期价值 (LTV)
了解如何使用 SQL 在 Metabase 中计算客户生命周期价值。
在我们的客户生命周期价值入门中，我们讨论了一些公司在此指标上犯的错误，并提供了一些关于如何使用 LTV 的指导。本指南将采用更实际的方法：我们将探讨订阅型公司如何使用 Metabase 中的 SQL 查询来估算客户在其作为客户的整个生命周期内将花费的总金额。
我们将首先回顾确定 LTV 的公式以及您需要使用的指标，然后我们将提供一个您可以运行以获取 LTV 数据的 SQL 查询示例。如果您只想查找该 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 计算，以表格和折线图的形式可视化
现在我们有了这个指标，我们可以用它来决定营销工作、人员需求和功能优先级等事项。