如何使用 SQL 计算客户生命周期价值 (LTV)
了解如何在 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)。
发票 (Invoices)
| 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 | |
| ... | ... | ... | ... | ... |
收入变更 (Revenue changes)
| 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 的主导航栏中选择**新建 (New)** > **SQL 查询 (SQL query)**，然后输入以下代码：
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 计算的可视化表格和折线图：
现在我们有了这个指标，我们可以用它来做决策，例如关于营销活动、人员需求和功能优先级。