如何使用 SQL 计算客户生命周期价值 (LTV)
学习如何使用 SQL 在 Metabase 中计算客户生命周期价值。
使用 Metabase 学习 SQL
在我们关于客户生命周期价值的入门文章中，我们讨论了一些公司在此指标上犯错的原因，并就如何使用 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,
每客户平均收入 (APRC)
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 计算，以表格和折线图形式进行可视化：
现在我们有了这个指标，我们可以用它来做决定，比如营销工作、人员需求和功能优先级。
下一篇：使用联接合并表
如何使用联接合并来自两个表的信息。