使用 SQL 计算 MRR 的方法与实践

Calculating MRR in SQL

Source | HN Comments

文章分享了使用 SQL 从 Stripe 数据计算 MRR 的方法。核心在于：1. 提取 Stripe 数据并分析数据结构；2. 规避陷阱，如订阅状态不准确、invoice 周期不适用等，并提出解决方案，例如使用 invoice line items 对象和过滤非订阅项目；3. 通过 SQL 转换，规范化季度、年度等非月度订阅，并与时间序列数据连接，最终计算 MRR、新增 MRR、流失 MRR 等指标。文章提供了详细的 SQL 代码示例，并强调了该方法的可调整性。

Blog Integrations Pricing Contact Docs Open main menu Sign InTry Definite now May 1, 202510 minute read

从原始 Stripe 数据计算 MRR 非常棘手：这是我们的方法

Steven Wang SQL Transformations For Calculating Stripe MRR | Definite

在 Definite 以及我们的客户中，一个常见的需求是使用来自 Stripe 的数据进行 MRR 报告。是的，Stripe 的 UI 已经提供了一个方便的仪表板来展示这些指标，但通常客户希望调整指标的定义，或者使用来自其他系统（如 Hubspot）的数据来丰富这些指标。在将原始数据从 Stripe API 提取到数据仓库之后，必须对数据应用一系列转换才能计算 MRR。

这样做并非易事，在这篇文章中，我们将分享我们遇到的一些陷阱，以及我们的 MRR 计算方法。请注意，这里分享的方法适用于我们特定的用例和 MRR 计算方式，其他方法可能会因业务需求而异。

提取 Stripe 数据及数据结构

第一步是提取原始的 Stripe 数据。对于数据提取，我们使用了 Singer Stripe tap 的一个分支版本，并将数据加载到 DuckDB data warehouse 中。数据的输出模式可以在这里找到。下面是一个 balance_transactions 对象模式的例子：

{
 "properties": {
  "fee": {
   "type": [
    "null",
    "integer"
   ]
  },
  "currency": {
   "type": [
    "null",
    "string"
   ]
  },
  "source": {
   "type": [
    "null",
    "string"
   ]
  },
  "fee_details": {
   "type": [
    "null",
    "array"
   ],
   "items": {
    "properties": {
     "application": {
      "type": [
       "null",
       "string"
      ]
     },
     "type": {
      "type": [
       "null",
       "string"
      ]
     },
     "description": {
      "type": [
       "null",
       "string"
      ]
     },
     "amount": {
      "type": [
       "null",
       "integer"
      ]
     },
     "currency": {
      "type": [
       "null",
       "string"
      ]
     }
    },
    "type": [
     "null",
     "object"
    ]
   }
  },
  "available_on": {
   "type": [
    "null",
    "integer"
   ]
  },
  "status": {
   "type": [
    "null",
    "string"
   ]
  },
  "description": {
   "type": [
    "null",
    "string"
   ]
  },
  "net": {
   "type": [
    "null",
    "integer"
   ]
  },
  "exchange_rate": {
   "type": [
    "null",
    "number"
   ]
  },
  "type": {
   "type": [
    "null",
    "string"
   ]
  },
  "sourced_transfers": {
   "items": {},
   "type": [
    "null",
    "array"
   ]
  },
  "id": {
   "type": [
    "null",
    "string"
   ]
  },
  "object": {
   "type": [
    "null",
    "string"
   ]
  },
  "created": {
   "type": [
    "null",
    "string"
   ],
   "format": "date-time"
  },
  "amount": {
   "type": [
    "null",
    "integer"
   ]
  },
  "updated": {
   "type": [
    "null",
    "string"
   ],
   "format": "date-time"
  }
 },
 "type": [
  "null",
  "object"
 ]
}

常见的陷阱

现在我们有了数据并了解了它的结构，让我们讨论一下在尝试使用这些数据计算 MRR 时遇到的一些陷阱：

陷阱 1：Subscriptions 对象只返回每个订阅的最新状态

其他指南和文章中找到的其他方法使用 Stripe 中的 subscriptions 对象来计算 MRR。从理论上讲，这是可行的，因为它具有开始日期、结束日期、计划间隔（月、季度、年度等）和金额。但是，Stripe API 仅返回每个订阅的最新状态。我们不知道计划是否已停止并重新启动、其价格是否已更改、订阅者是否已向计划添加或删除服务、其间隔是否已更改等等。因此，使用 subscriptions 对象将无法提供 MRR 的准确历史视图。

解决方案：我们必须使用历史 invoices 对象而不是 subscriptions 对象来构建 MRR 历史表。

陷阱 2：Invoice 周期开始和结束日期不可信

每个 invoice 都有一个开始和结束日期。尽管使用它们可能很诱人，但这些日期实际上是将项目添加到 invoice 的开始和结束日期，而不是每个订阅的计费周期。

解决方案：使用 invoice line items 对象。 Invoice 中的 line items 有一个包含开始和结束日期的 period 对象。我们应该使用这些日期来确定每月订阅周期。

陷阱 3：并非所有 invoice line items 对象都用于订阅计划

非经常性费用可以在 invoice line items 对象中找到（一次性费用、滞纳金等）。我们需要过滤掉这些费用，不要将它们包含在 MRR 计算中。

解决方案：过滤 type = subscription 的 invoice line items 对象。

陷阱 4：折扣不适用于 invoice line items 对象的金额

虽然 invoice 对象显示了最终应付金额，但 invoice line items 对象显示了没有折扣的原始金额。对于每个 line item，我们必须查找它是否有折扣，如果有，则减去折扣金额。

解决方案：将 discounts 对象连接到 invoice line items 对象，并减去折扣金额（如果有）。

陷阱 5：某些订阅不是按月计费的（例如，季度或年度计划）

找到按年、季度甚至每 6 个月计费的订阅并不少见。在这些情况下，相应的 invoice line items 对象将仅在每个计费周期出现一次，而不是每月出现一次。此外，到期金额将是该期间的总金额，而不是每月金额。

解决方案：对非每月计划进行规范化，以便在计划有效的每个月内分配收入。 这是最棘手的部分，我们将在下面展示一些 SQL 转换来完成此操作。

SQL 转换

以下是我们计算 MRR 的方法概述。我们做出了一些假设：

数据和模式与 Singer Stripe 提取器输出紧密匹配。
每个 invoice line items 对象的 period start month 是要记录收入的月份。
流失月份是最后一个 invoice line items 对象的 period end month。

步骤 1：收集相关的 invoice line items 对象

with discounts as (
  select 
    id,
    unnest(from_json(json(discount_amounts), '["JSON"]')).amount as discount_amount
  from stripe.invoice_line_items 
  where type = 'subscription'
),
line_items as (
  select 
    i.id,
    i.subscription,
    -- subtract discounts
    (i.amount - coalesce(d.discount_amount::int, 0)) / 100 as amount,
    date_trunc('month', (period ->> 'start')::date) as period_start_month,
    date_trunc('month', (period ->> 'end')::date) as period_end_month,
    -- calculate invoice period length in months
    datediff('month', (period ->> 'start')::date, (period ->> 'end')::date) as period_length
  from stripe.invoice_line_items as i
  left join discounts as d
    on i.id = d.id
  join stripe.invoices as i2 
    on i.invoice = i2.id
  where type = 'subscription' -- filter for subscription line items
)
select * from line_items;

请注意，在上面的 SQL 中，我们：

仅过滤来自 subscriptions 对象的 line items 对象。
从金额中扣除折扣。
计算 line items 对象的 period length（以月为单位）。

步骤 2：规范化季度和年度计划

在我们的数据中，我们只有每月、3 个月和 12 个月的计划。其他 Stripe 实例可能具有不同的间隔（例如，6 个月计划），因此请进行相应的调整。让我们看一个具有续订的 3 个月订阅计划的示例：

我们希望规范化此订阅，以便将每个 invoice line items 对象分解为 3 个单独的月份。例如，对于 2023-11-01 到 2024-02-01 期间的第一个 line items 对象，应有 3 行，每行对应于这些开始和结束期间之间的每个月。此外，我们必须将每个金额除以 period length，因为我们想找到每个月的规范化收入（金额 / 3）。

在先前的 SQL 的基础上，我们可以创建此多月计划的规范化视图，如下所示：

...
quarter_plans as (
  select * 
  from line_items
  where period_length = 3
),
time_series as (
 SELECT 
   ('2017-01-01'::date + gs.i * INTERVAL '1 month')::date AS month
 FROM 
   generate_series(0, 240) AS gs(i) -- Alias generate_series as gs
 WHERE 
   ('2017-01-01'::date + gs.i * INTERVAL '1 month')::date <= date_trunc('month', current_date)
),
normalized_quarter_plans as (
select 
  ts.month,
  qp.subscription,
  qp.period_start_month,
  qp.period_end_month,
  qp.period_length,
  qp.amount / qp.period_length as mrr -- divide invoice amount by period length to normalize to month as mrr
from time_series as ts
cross join quarter_plans as qp
where ts.month >= qp.period_start_month and ts.month < qp.period_end_month
)
select * from normalized_quarter_plans;

现在，对于相同的 3 个月订阅，规范化结果如下所示：

请注意，我们没有包含 2024-11-01 的记录，因为该月没有新的 invoice line items 对象开始。我们认为此订阅在 2024-11-01 已流失（根据我们之前提到的假设）。

你将需要对所有多月 invoice line items 对象使用相同的方法，然后将这些记录与常规每月 invoices 对象联合起来。

步骤 3：将你的规范化月度收入与时间序列数据集连接

在为每个 invoice 创建规范化月度收入之后，我们现在需要为每个月创建一个时间序列条目。为此，我们将再次利用我们的时间序列 CTE，并将其连接到每个规范化的 invoice 月。为什么我们必须这样做？如果一个订阅被取消，然后在几个月后重新启动，那么我们的规范化 invoices 对象将没有这些中间月份的记录，因此我们必须与时间序列 CTE 进行交叉连接才能获得这些月份。这样做非常重要，以便我们以后可以计算重新激活 MRR。

下面的 SQL 显示了如何执行此操作的一般思路：

...
normalized_invoices as (
	// union of all month-normalized annual, quarter, month, etc. plans
),
time_series as (
 SELECT 
   ('2017-01-01'::date + gs.i * INTERVAL '1 month')::date AS month
 FROM 
   generate_series(0, 240) AS gs(i) -- Alias generate_series as gs
 WHERE 
   ('2017-01-01'::date + gs.i * INTERVAL '1 month')::date <= date_trunc('month', current_date)
),
agg_month as (
  select
    month, 
    subscription,
    sum(mrr) as mrr -- there can be multiple invoices in the same month
  from normalized_invoices
  group by 1, 2
),
-- join to subscriptions to get subscription start and end months
agg_month_2 as (
  select 
    a.month,
    a.subscription,
    a.mrr,
    date_trunc('month', s.start_date) as subscription_start_month,
    date_trunc('month', greatest(s.cancel_at, s.canceled_at)) as subscription_end_month
  from agg_month as a
  join stripe.subscriptions as s
    on a.subscription = s.id
),
-- CTE of every subscription and month combo, need in case a subscription stops and starts
agg_month_timeseries as (
    select 
      ts.month,
      ag.*
    from time_series as ts
    cross join (
      select distinct
        subscription,
        subscription_start_month,
        subscription_end_month
      from agg_month_2
    ) as ag
    where ts.month >= ag.subscription_start_month
),
mrr_timeseries as (
  select 
    ait.month,
    ait.subscription,
    ait.subscription_start_month,
    ait.subscription_end_month,
    coalesce(ag.mrr, 0) as mrr
  from agg_month_timeseries ait
  left join agg_month_2 ag
    on ait.month = ag.month
    and ait.subscription = ag.subscription
)
select * from mrr_timeseries;

单个订阅的结果如下所示：

请注意，即使在没有 invoice 对象的月份（2024-06-01 到 2024-07-01，2024-10-01）期间，我们仍然有这些月份的行，MRR 为 0。

步骤 4：计算 MRR 指标

现在我们可以计算 MRR 指标，例如流失率、扩张率、收缩率等。

...
mrr_timeseries_2 as (
  select 
    month,
    subscription,
    subscription_start_month,
    subscription_end_month,
    coalesce(lag(mrr) over (partition by subscription order by month asc), 0) as mrr_prev_month,
    mrr - coalesce(lag(mrr) over (partition by subscription order by month asc), 0) as mrr_change,
    row_number() over (partition by subscription order by month asc) mrr_rank_asc,
    row_number() over (partition by subscription order by month desc) mrr_rank_desc
  from mrr_timeseries
),
final as (
select 
  month,
  subscription,
  subscription_start_month,
  subscription_end_month,
  
  -- mrr
  case 
    when mrr > 0 then mrr
    when mrr = 0 and mrr_rank_desc = 1 and (subscription_end_month > date_trunc('month', current_date) or subscription_end_month is null) then mrr_prev_month --invoice not issued yet
    else mrr
  end as mrr,
  
  -- new mrr
  case
    when mrr_rank_asc = 1 then mrr_change
    else 0
  end as new_mrr,
  
		-- contraction mrr
  case
    when mrr_change < 0 and mrr > 0 then mrr_change
    else 0
  end as contraction_mrr,
  
  -- expansion mrr
  case
    when mrr_change > 0 and mrr_rank_asc > 1 and mrr_prev_month > 0 then mrr_change
    else 0
  end as expansion_mrr,
  
  -- reactivation mrr
  case
    when mrr_change > 0 and mrr_rank_asc > 1 and mrr_prev_month = 0 then mrr_change
    else 0
  end as reactivation_mrr,
  
  -- churn mrr
  case
    when mrr = 0 and mrr_rank_desc = 1 and (subscription_end_month > date_trunc('month', current_date) or subscription_end_month is null) then 0 --invoice not issued yet
    when mrr = 0 and mrr_prev_month > 0 then mrr_change
    else 0
  end as churn_mrr
  
from mrr_timeseries_2
)
select * from final;

最终输出应包含从订阅开始日期开始的每个月中每个订阅的记录：

结论

上面提到的方法是我们目前为自己和客户服务的方法。它应被视为如何使用原始 Stripe 数据计算 MRR 的总体纲要，并且可以进行调整以适应不同的需求。如果你正在寻找类似的东西，但又不想经历从 Stripe API 提取数据、将其加载到数据仓库以及自己运行 SQL 转换的麻烦，请查看 https://www.definite.app/，我们在那里已经完成了繁重的工作。

数据不必那么难

获得分析的新标准。立即注册或联系我们，我们将在 30 分钟内为你设置。 Try Definite now hello@definite.app Product Home Pricing Documentation Resources Contact Blog Connector Database Legal Data Processing Addendum Privacy Policy Cookie Policy Terms of Service Refund Policy Fulfillment Policy Security Policy SOC 2 Type II SOC2 ©2024 Definite. All rights reserved.