如何使用 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)
- 訂閱次數
- 每位客戶平均收益 (APRC)
- 訂閱客戶流失率
- 客戶終身價值 (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)
APRC 讓我們瞭解我們從每位客戶獲得多少收益。我們將首先計算每月分組的有效訂閱次數，然後將 MRR 除以該數字。
如果我們想從 Invoices 表格計算 APRC，我們會執行
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 查詢中包含以下子查詢來計算 APRC
(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 計算，視覺化為表格和折線圖
現在我們有了這個指標，我們可以利用它來制定行銷工作、人員配置需求和功能優先順序等方面的決策。