如何使用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）
每客户平均收入（ARPC）
订阅流失率

月度经常性收入（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计算的可视化，既以表格形式，也以折线图形式

Customer LTV table.

Visualizing our LTV over time.

现在我们有了这个指标，我们可以用它来做出关于营销活动、人员需求以及功能优先级等决策。

自助分析

嵌入式分析

文档

学习

Metabase 基础知识

提高你的数据技能