PostHog 收入指标建模实战基于 revenue_analytics 托管视图与 dbt 构建 MRR、ARR 与 LTV【免费下载链接】posthog:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.项目地址: https://gitcode.com/GitHub_Trending/po/posthog本篇围绕 PostHog 仓库中products/data_modeling/skills/modeling-revenue-metrics/技能文档展开讲解如何把支付与订阅数据转化为可复用的收入模型在 PostHog 托管的revenue_analytics_*HogQL 视图或外部 dbt 项目上正确定义并计算 MRR、ARR、总营收、新增/扩张/收缩/流失 MRR、ARPU、LTV 以及按客户/账户维度的收入。读完你会掌握选对数据源—避开六大坑—写出 HogQL/dbt 模型—注册治理的完整流程并理解仓库源码中托管视图 schema 的真实字段定义。数据从哪里来两条收入入口同一套托管视图收入数据进入 PostHog 有两条路径两者最终都汇入同一组托管的revenue_analytics_*视图managed views支付平台作为数据仓库源warehouse source——目前支持 StripeChargebee / Polar / RevenueCat 在规划中。业务跑在计费平台上时首选此路径通过setting-up-a-data-warehouse-source技能完成接入自定义收入事件custom revenue events——自行发送带收入属性的事件如带 revenue 属性的purchase_completed。适合没有受支持平台、或产品内已埋点收入数据的情况。如果两者都不存在技能建议先走suggesting-data-imports推荐一个数据源。在dbt栈上等价做法是把落到你数仓里的计费表如 Stripe 原始表做 staging。核心原则建模在托管视图上而不是原始表PostHog 会为每个源自动生成一组经过整理的视图文档明确要求不要从原始 Stripe 表重新推导收入——托管视图已经处理了递延收入确认deferred-revenue recognition、货币换算与稳定 schema。发现实际视图名带源前缀如stripe.prefix.…另有跨源的revenue_analytics.all.…SELECT table_name FROM system.information_schema.tables WHERE table_name ILIKE %revenue_analytics%各托管视图的粒度grain与用途如下托管视图粒度用途revenue_item从这里开始1 行 / 发票行项目总营收、MRR、按产品/客户/周期拆分的收入。实现递延收入 货币换算mrr1 行 / (客户, 订阅)当前 MRR 的实时快照——不是时间序列customer1 行 / 客户dim_customeremail、国家、cohort、元数据subscription1 行 / 订阅用于 churn/expansion 逻辑的订阅状态charge1 行 / 扣款原始扣款除非明确需要扣款粒度否则优先用revenue_itemproduct1 行 / 产品产品维度revenue_item的关键列包括amount已换算到项目基础货币、currency即该基础货币、original_amount/original_currency原始计费金额与货币、is_recurring、customer_id、subscription_id、product_id、group_0_key…group_4_keyB2B 账户 key、timestamp。这一列清单并非文档杜撰仓库源码可直接印证。revenue_item 托管视图的 schema 定义 中FIELDS依次声明了id、invoice_item_id、source_label、timestamp、created_at、is_recurring、product_id、customer_id、group_0_keygroup_4_key、invoice_id、subscription_id、session_id、event_name、coupon、coupon_id等字段并展开货币字段**BASE_CURRENCY_FIELDS视图后缀为revenue_item_revenue_view事件版后缀为revenue_item_events_revenue_view与文档中revenue_analytics.all.revenue_item_revenue_view这类命名规则一致。货币字段在 共享的 schema 定义 中统一定义被charge与revenue_item两个 schema 复用BASE_CURRENCY_FIELDS: FieldsDict { # Mostly helper fields original_currency: StringDatabaseField(nameoriginal_currency), original_amount: DecimalDatabaseField(nameoriginal_amount), enable_currency_aware_divider: BooleanDatabaseField(nameenable_currency_aware_divider), currency_aware_divider: DecimalDatabaseField(namecurrency_aware_divider), currency_aware_amount: DecimalDatabaseField(namecurrency_aware_amount), # Actual two fields we care about currency: StringDatabaseField(namecurrency), amount: DecimalDatabaseField(nameamount), }从源码结构看amount/currency是对外暴露的核心两列其余为支撑货币感知计算的辅助列——这也解释了为什么文档反复强调amount已是基础货币直接用于报表。建模之前必须知道的六个坑revenue gotchas原文档把六条规则放在建模步骤之前因为它们是最常踩的雷没有订阅配置MRR 就是空的。事件型收入只有配置了订阅subscription属性后 MRR 才会被填充。总营收有值而 MRR 为空是预期行为而非 bug——应该如实说明而不是去修它。mrr托管视图是当前快照不是历史current time 的 MRR。要看 MRR随时间变化应基于revenue_item按月求和is_recurring的amount见下文配方或按调度对mrr视图做月度快照物化。amount已经是基础货币。直接用于报表即可。只有当你需要另一种目标货币、或基于原始事件建模时才调用convertCurrency(original_currency, XXX, original_amount, timestamp)。把收入关联到人person靠元数据。需要 person/group 级收入时Stripe 客户上必须带posthog_person_distinct_id元数据或 person join。否则收入只能到客户级。不要基于 Revenue dashboard 建模——它正在退役约 2026-06-30。应对revenue_analytics_*视图和 person/group 收入属性建模。排除测试账户。确认filter_test_accounts的行为避免 QA/内部扣款虚增收入。指标定义让每个模型算出同一个数技能的 指标定义参考文件 给出了一套规范化定义金额默认以项目基础货币计revenue_item.amount已换算其他目标货币用convertCurrency()指标定义说明总营收Gross revenue周期内全部收入之和含经常性 一次性含负数退款周期内revenue_item上的sum(amount)MRR月度规范化经常性订阅收入revenue_item上按月的sum(amount) where is_recurring递延收入已把年费摊到服务期各月。托管mrr视图是实时快照而非历史ARRMRR × 12是 run-rate 推算不是预测New MRR上一周期没有 MRR 的客户贡献的 MRRExpansion MRR存量客户 MRR 的增量升级/加席位Contraction MRR仍在付费的存量客户 MRR 的减量Churned MRR取消或归零客户流失的 MRRNew − Contraction − Churn Expansion 可核对月度 MRR 桥接Churn rate周期内churned_customers / total_customers收入流失率用 churned MRR / 期初 MRRARPU周期内total_revenue / active_users覆盖全部收入而非仅订阅收入LTVARPU / churn_ratechurn rate 为 0 时取 Null有流失但无收入时取 0MRR 变动桥接MRR movement bridgeNew / Expansion / Contraction / Churn 四者共同分解两个月之间 MRR 的变化。按客户逐个计算设上月经常性 MRR 为m0本月为m1m0 0, m1 0→New计m1m0 0, m1 m0→Expansion计m1 - m00 m1 m0→Contraction计m0 - m1m0 0, m1 0→Churn计-m0。恒等式starting_MRR new expansion - contraction - churn ending_MRR应作为任何 MRR 模型的校验检查。聚合单元person 还是 group一次性选定B2C 用 person 粒度customer_id经客户元数据关联到 personsB2B 账户级收入用revenue_item上的group_0_key或相应group_N_key。且要在 MRR、churn、LTV 之间保持一致。PostHog 路径HogQL 配方工作流为写 HogQL每列必须加别名这是view-create的硬性要求→view-create建虚拟视图 →view-get验证 → 对昂贵的月度汇总做view-materialize对收入而言sync_frequency设为 daily 通常合适。三个现成配方都在 references/posthog/ 下。1. MRR 与 ARR 时间序列mrr_and_arr.sql由于托管mrr视图只是实时快照时间序列要从revenue_item的经常性行推导-- 建视图前把 revenue_item_view 换成真实视图名 -- SELECT table_name FROM system.information_schema.tables WHERE table_name ILIKE %revenue_item%; -- amount 已是项目基础货币。每个输出列都带别名view-create 要求。 SELECT toStartOfMonth(timestamp) AS month, sum(amount) AS mrr, -- 本月确认的经常性收入 sum(amount) * 12 AS arr_run_rate, -- MRR x 12 count(DISTINCT customer_id) AS paying_customers, round(sum(amount) / nullif(count(DISTINCT customer_id), 0), 2) AS arpa -- 户均收入 FROM revenue_item_view WHERE is_recurring GROUP BY month ORDER BY month2. 月度总营收经常性 vs 一次性gross_revenue_by_month.sqlSELECT toStartOfMonth(timestamp) AS month, sum(amount) AS gross_revenue, sumIf(amount, is_recurring) AS recurring_revenue, sumIf(amount, NOT is_recurring) AS one_time_revenue, sumIf(amount, amount 0) AS refunds FROM revenue_item_view GROUP BY month ORDER BY month注意sumIf(amount, amount 0)直接给出净退款额——总营收是含退款冲销的净值口径。3. 客户级收入 跨货币换算revenue_by_customer.sql这个配方演示了把事实视图与customer维度视图连接、以及从原始计费金额换算到指定报表货币示例中为 EURSELECT c.id AS customer_id, any(c.name) AS customer_name, any(c.country) AS country, sum(ri.amount) AS revenue_base_ccy, sum(convertCurrency(ri.original_currency, EUR, ri.original_amount, ri.timestamp)) AS revenue_eur, count(DISTINCT ri.subscription_id) AS subscriptions FROM revenue_item_view AS ri LEFT JOIN customer_view AS c ON ri.customer_id c.id GROUP BY customer_id ORDER BY revenue_base_ccy DESCconvertCurrency(原币种, 目标币种, 原始金额, timestamp)用原始金额 交易时刻汇率做换算而不是把已换算的amount再折算——后者会引入二次舍入误差这一点从配方写法可以明确读出。dbt 路径staging → fct_/dim_marts 测试对 dbt 栈技能要求staging 计费源 →fct_revenue_item、fct_mrr、dim_customermarts并配schema.yml测试。配方在 references/dbt/ 下。注意dbt 没有convertCurrency()需要自行提供汇率 seed。源与汇率 seed_sources_stripe.ymlsources: - name: stripe database: analytics schema: stripe_raw tables: - name: invoice_line_items # 行项目——收入确认的粒度 - name: invoices - name: subscriptions - name: customers seeds: # 静态汇率表PostHog 的 convertCurrency 在 dbt 中没有等价物。 # 提供 seeds/currency_rates.csv列为: currency, rate_date, rate_to_base。 - name: currency_ratesfct_revenue_item手工实现递延收入确认这是 PostHog 托管revenue_item视图的 dbt 对应物核心是把每个经常性行项目均摊到服务期[period_start, period_end]覆盖的每个日历月——年费计划每月只贡献 1/12 到 MRR而不是一次性全额计入单月一次性项目只落在计费当月。粒度为每行项目服务月一行{{ config(materializedtable) }} with items as ( select * from {{ source(stripe, invoice_line_items) }} ), rates as ( select currency, rate_date, rate_to_base from {{ ref(currency_rates) }} ), -- 覆盖所有行项目跨度的月度骨架generate_series 为 Postgres/DuckDB 语法 month_spine as ( select generate_series( date_trunc(month, min(period_start)), date_trunc(month, max(coalesce(period_end, period_start))), interval 1 month ) as month from items ), recognized as ( select i.id, i.subscription_id, i.customer_id, i.product_id, i.period_start::date as period_start, i.period_end::date as period_end, i.currency, i.amount as line_amount, (i.subscription_id is not null) as is_recurring, s.month::date as month, count(*) over (partition by i.id) as service_months from items i join month_spine s on s.month date_trunc(month, i.period_start) and s.month date_trunc(month, case when i.subscription_id is not null then coalesce(i.period_end, i.period_start) else i.period_start end) ) select r.id as revenue_item_id, r.month, r.subscription_id, r.customer_id, r.product_id, r.period_start, r.period_end, r.is_recurring, r.currency as original_currency, r.line_amount / r.service_months as original_amount, -- 该月汇率下的基础货币金额。汇率缺失时故意留 NULL -- 让 amount 上的 not_null 测试大声失败而不是悄悄按 1:1 处理。 -- 基础货币本身需以 rate_to_base 1.0 入 seed。 (r.line_amount / r.service_months) * rt.rate_to_base as amount from recognized r left join rates rt on rt.currency r.currency and rt.rate_date r.month一个值得注意的工程细节汇率缺失时amount故意保持 NULL让下游not_null测试失败报警而不是默默假设 1:1 平价——这是 dbt 测试体系在货币场景下的典型防御性用法。fct_mrr密集 (客户 × 月) 骨架 桥接指标配方开头就点明关键陷阱没有覆盖每个客户每个月的密集骨架时lag()会把客户上一个活跃月和下一个活跃月直接比较静默跳过中间的流失月。因此先用customers cross join months左连接补零with monthly as ( select customer_id, month, sum(amount) as mrr from {{ ref(fct_revenue_item) }} where is_recurring group by 1, 2 ), months as (select distinct month from monthly), customers as (select distinct customer_id from monthly), filled as ( -- 每客户每月一行缺失月补零churn 表现为 mrr - 0 select c.customer_id, m.month, coalesce(mo.mrr, 0) as mrr from customers c cross join months m left join monthly mo on mo.customer_id c.customer_id and mo.month m.month ), with_prev as ( select customer_id, month, mrr, lag(mrr) over (partition by customer_id order by month) as prev_mrr from filled ) select month, sum(mrr) as mrr, sum(mrr) * 12 as arr, sum(case when coalesce(prev_mrr, 0) 0 and mrr 0 then mrr else 0 end) as new_mrr, sum(case when prev_mrr 0 and mrr prev_mrr then mrr - prev_mrr else 0 end) as expansion_mrr, sum(case when mrr 0 and mrr prev_mrr then prev_mrr - mrr else 0 end) as contraction_mrr, sum(case when prev_mrr 0 and mrr 0 then prev_mrr else 0 end) as churned_mrr from with_prev group by month order by month四个桥接指标的判断条件与定义文件中per customer的规则逐条对应New 要求prev 0 且当前 0Expansion 是增量部分Contraction 是减量部分Churn 是整个上月余额。恒等式starting_mrr new expansion - contraction - churn ending_mrr依旧适用。dim_customer 与 schema.yml 测试dim_customer.sql 对应托管customer视图的一行一客户维度customer_id、customer_name、email、country、first_seen_date。schema.yml 的测试覆盖了三条核心约束dim_customer.customer_iduniquenot_nullfct_revenue_itemunique_combination_of_columns([revenue_item_id, month])保证行项目 × 服务月粒度唯一、revenue_item_id not_null、customer_id的not_nullrelationships指向dim_customer引用完整性、amount not_null汇率缺失即报警fct_mrrmonth唯一非空、mrr not_null。仓库源码中的实现印证与配套文档托管视图并非文档层面的约定而是由 PostHog 后端自动生成并注册进 HogQL database 的对象。从 products/revenue_analytics/backend/views/ 的目录结构看schemas/revenue_item.py声明列定义sources/stripe/与sources/events/分别实现 Stripe 源与事件源两种数据通路对应 SKILL 中两条收入入口sources/test/下附有test_stripe_revenue_item.py、test_events_mrr.py等测试与.ambr快照用于锁定各源生成视图的输出行为。Schema结构schemas/revenue_item.py中的source_suffix/events_suffix也解释了发现视图名时会出现revenue_item_revenue_view源版与revenue_item_events_revenue_view事件版两类后缀的原因。模型建好之后按 governance.md 的要求注册复用给列加注解column annotations若 MRR/ARR 是对外headline数字则提议进入 semantic layer 供其他模型发现与复用。相关技能与延伸阅读按 SKILL 文档的 File map 与 Companions围绕本主题的配套材料文件何时读references/revenue-metric-definitions.mdMRR、ARR、gross、new/expansion/contraction/churn、ARPU、LTV 的精确定义references/posthog/托管视图上的 HogQL 视图配方references/dbt/dbt staging fct_*/dim_*marts schema.yml测试modeling-warehouse-foundations基础机制view 与 dbt 的选型、view-create/view-materialize/sync_frequency工作流、convertCurrency()其他配套技能modeling-warehouse-foundations机制层、setting-up-a-data-warehouse-sourcesuggesting-data-imports把 Stripe/收入数据接进来、modeling-dimension-tables货币/plan 维度、querying-posthog-dataHogQL 与 semantic layer 指标检查。小结这套技能文档给出的收入建模方法论可以概括为四步先找数据Stripe 源或自定义事件dbt 则 stage 计费表、建在托管视图上revenue_item起步mrr只是快照、记住六个坑MRR 空是配置问题不是 bug、amount已是基础货币、person 关联靠元数据、别依赖即将退役的 Revenue dashboard、排除测试账户、选栈落地PostHog 侧 HogQL 物化 daily 同步dbt 侧递延收入 marts 汇率 seed 测试最后用 MRR 变动桥接恒等式做校验并注册到 semantic layer。所有 SQL 配方均可直接从仓库中复制改写把revenue_item_view占位符替换为实际发现的视图名即可运行。【免费下载链接】posthog:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.项目地址: https://gitcode.com/GitHub_Trending/po/posthog创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
PostHog 收入指标建模实战：基于 revenue_analytics 托管视图与 dbt 构建 MRR、ARR 与 LTV
PostHog 收入指标建模实战基于 revenue_analytics 托管视图与 dbt 构建 MRR、ARR 与 LTV【免费下载链接】posthog:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.项目地址: https://gitcode.com/GitHub_Trending/po/posthog本篇围绕 PostHog 仓库中products/data_modeling/skills/modeling-revenue-metrics/技能文档展开讲解如何把支付与订阅数据转化为可复用的收入模型在 PostHog 托管的revenue_analytics_*HogQL 视图或外部 dbt 项目上正确定义并计算 MRR、ARR、总营收、新增/扩张/收缩/流失 MRR、ARPU、LTV 以及按客户/账户维度的收入。读完你会掌握选对数据源—避开六大坑—写出 HogQL/dbt 模型—注册治理的完整流程并理解仓库源码中托管视图 schema 的真实字段定义。数据从哪里来两条收入入口同一套托管视图收入数据进入 PostHog 有两条路径两者最终都汇入同一组托管的revenue_analytics_*视图managed views支付平台作为数据仓库源warehouse source——目前支持 StripeChargebee / Polar / RevenueCat 在规划中。业务跑在计费平台上时首选此路径通过setting-up-a-data-warehouse-source技能完成接入自定义收入事件custom revenue events——自行发送带收入属性的事件如带 revenue 属性的purchase_completed。适合没有受支持平台、或产品内已埋点收入数据的情况。如果两者都不存在技能建议先走suggesting-data-imports推荐一个数据源。在dbt栈上等价做法是把落到你数仓里的计费表如 Stripe 原始表做 staging。核心原则建模在托管视图上而不是原始表PostHog 会为每个源自动生成一组经过整理的视图文档明确要求不要从原始 Stripe 表重新推导收入——托管视图已经处理了递延收入确认deferred-revenue recognition、货币换算与稳定 schema。发现实际视图名带源前缀如stripe.prefix.…另有跨源的revenue_analytics.all.…SELECT table_name FROM system.information_schema.tables WHERE table_name ILIKE %revenue_analytics%各托管视图的粒度grain与用途如下托管视图粒度用途revenue_item从这里开始1 行 / 发票行项目总营收、MRR、按产品/客户/周期拆分的收入。实现递延收入 货币换算mrr1 行 / (客户, 订阅)当前 MRR 的实时快照——不是时间序列customer1 行 / 客户dim_customeremail、国家、cohort、元数据subscription1 行 / 订阅用于 churn/expansion 逻辑的订阅状态charge1 行 / 扣款原始扣款除非明确需要扣款粒度否则优先用revenue_itemproduct1 行 / 产品产品维度revenue_item的关键列包括amount已换算到项目基础货币、currency即该基础货币、original_amount/original_currency原始计费金额与货币、is_recurring、customer_id、subscription_id、product_id、group_0_key…group_4_keyB2B 账户 key、timestamp。这一列清单并非文档杜撰仓库源码可直接印证。revenue_item 托管视图的 schema 定义 中FIELDS依次声明了id、invoice_item_id、source_label、timestamp、created_at、is_recurring、product_id、customer_id、group_0_keygroup_4_key、invoice_id、subscription_id、session_id、event_name、coupon、coupon_id等字段并展开货币字段**BASE_CURRENCY_FIELDS视图后缀为revenue_item_revenue_view事件版后缀为revenue_item_events_revenue_view与文档中revenue_analytics.all.revenue_item_revenue_view这类命名规则一致。货币字段在 共享的 schema 定义 中统一定义被charge与revenue_item两个 schema 复用BASE_CURRENCY_FIELDS: FieldsDict { # Mostly helper fields original_currency: StringDatabaseField(nameoriginal_currency), original_amount: DecimalDatabaseField(nameoriginal_amount), enable_currency_aware_divider: BooleanDatabaseField(nameenable_currency_aware_divider), currency_aware_divider: DecimalDatabaseField(namecurrency_aware_divider), currency_aware_amount: DecimalDatabaseField(namecurrency_aware_amount), # Actual two fields we care about currency: StringDatabaseField(namecurrency), amount: DecimalDatabaseField(nameamount), }从源码结构看amount/currency是对外暴露的核心两列其余为支撑货币感知计算的辅助列——这也解释了为什么文档反复强调amount已是基础货币直接用于报表。建模之前必须知道的六个坑revenue gotchas原文档把六条规则放在建模步骤之前因为它们是最常踩的雷没有订阅配置MRR 就是空的。事件型收入只有配置了订阅subscription属性后 MRR 才会被填充。总营收有值而 MRR 为空是预期行为而非 bug——应该如实说明而不是去修它。mrr托管视图是当前快照不是历史current time 的 MRR。要看 MRR随时间变化应基于revenue_item按月求和is_recurring的amount见下文配方或按调度对mrr视图做月度快照物化。amount已经是基础货币。直接用于报表即可。只有当你需要另一种目标货币、或基于原始事件建模时才调用convertCurrency(original_currency, XXX, original_amount, timestamp)。把收入关联到人person靠元数据。需要 person/group 级收入时Stripe 客户上必须带posthog_person_distinct_id元数据或 person join。否则收入只能到客户级。不要基于 Revenue dashboard 建模——它正在退役约 2026-06-30。应对revenue_analytics_*视图和 person/group 收入属性建模。排除测试账户。确认filter_test_accounts的行为避免 QA/内部扣款虚增收入。指标定义让每个模型算出同一个数技能的 指标定义参考文件 给出了一套规范化定义金额默认以项目基础货币计revenue_item.amount已换算其他目标货币用convertCurrency()指标定义说明总营收Gross revenue周期内全部收入之和含经常性 一次性含负数退款周期内revenue_item上的sum(amount)MRR月度规范化经常性订阅收入revenue_item上按月的sum(amount) where is_recurring递延收入已把年费摊到服务期各月。托管mrr视图是实时快照而非历史ARRMRR × 12是 run-rate 推算不是预测New MRR上一周期没有 MRR 的客户贡献的 MRRExpansion MRR存量客户 MRR 的增量升级/加席位Contraction MRR仍在付费的存量客户 MRR 的减量Churned MRR取消或归零客户流失的 MRRNew − Contraction − Churn Expansion 可核对月度 MRR 桥接Churn rate周期内churned_customers / total_customers收入流失率用 churned MRR / 期初 MRRARPU周期内total_revenue / active_users覆盖全部收入而非仅订阅收入LTVARPU / churn_ratechurn rate 为 0 时取 Null有流失但无收入时取 0MRR 变动桥接MRR movement bridgeNew / Expansion / Contraction / Churn 四者共同分解两个月之间 MRR 的变化。按客户逐个计算设上月经常性 MRR 为m0本月为m1m0 0, m1 0→New计m1m0 0, m1 m0→Expansion计m1 - m00 m1 m0→Contraction计m0 - m1m0 0, m1 0→Churn计-m0。恒等式starting_MRR new expansion - contraction - churn ending_MRR应作为任何 MRR 模型的校验检查。聚合单元person 还是 group一次性选定B2C 用 person 粒度customer_id经客户元数据关联到 personsB2B 账户级收入用revenue_item上的group_0_key或相应group_N_key。且要在 MRR、churn、LTV 之间保持一致。PostHog 路径HogQL 配方工作流为写 HogQL每列必须加别名这是view-create的硬性要求→view-create建虚拟视图 →view-get验证 → 对昂贵的月度汇总做view-materialize对收入而言sync_frequency设为 daily 通常合适。三个现成配方都在 references/posthog/ 下。1. MRR 与 ARR 时间序列mrr_and_arr.sql由于托管mrr视图只是实时快照时间序列要从revenue_item的经常性行推导-- 建视图前把 revenue_item_view 换成真实视图名 -- SELECT table_name FROM system.information_schema.tables WHERE table_name ILIKE %revenue_item%; -- amount 已是项目基础货币。每个输出列都带别名view-create 要求。 SELECT toStartOfMonth(timestamp) AS month, sum(amount) AS mrr, -- 本月确认的经常性收入 sum(amount) * 12 AS arr_run_rate, -- MRR x 12 count(DISTINCT customer_id) AS paying_customers, round(sum(amount) / nullif(count(DISTINCT customer_id), 0), 2) AS arpa -- 户均收入 FROM revenue_item_view WHERE is_recurring GROUP BY month ORDER BY month2. 月度总营收经常性 vs 一次性gross_revenue_by_month.sqlSELECT toStartOfMonth(timestamp) AS month, sum(amount) AS gross_revenue, sumIf(amount, is_recurring) AS recurring_revenue, sumIf(amount, NOT is_recurring) AS one_time_revenue, sumIf(amount, amount 0) AS refunds FROM revenue_item_view GROUP BY month ORDER BY month注意sumIf(amount, amount 0)直接给出净退款额——总营收是含退款冲销的净值口径。3. 客户级收入 跨货币换算revenue_by_customer.sql这个配方演示了把事实视图与customer维度视图连接、以及从原始计费金额换算到指定报表货币示例中为 EURSELECT c.id AS customer_id, any(c.name) AS customer_name, any(c.country) AS country, sum(ri.amount) AS revenue_base_ccy, sum(convertCurrency(ri.original_currency, EUR, ri.original_amount, ri.timestamp)) AS revenue_eur, count(DISTINCT ri.subscription_id) AS subscriptions FROM revenue_item_view AS ri LEFT JOIN customer_view AS c ON ri.customer_id c.id GROUP BY customer_id ORDER BY revenue_base_ccy DESCconvertCurrency(原币种, 目标币种, 原始金额, timestamp)用原始金额 交易时刻汇率做换算而不是把已换算的amount再折算——后者会引入二次舍入误差这一点从配方写法可以明确读出。dbt 路径staging → fct_/dim_marts 测试对 dbt 栈技能要求staging 计费源 →fct_revenue_item、fct_mrr、dim_customermarts并配schema.yml测试。配方在 references/dbt/ 下。注意dbt 没有convertCurrency()需要自行提供汇率 seed。源与汇率 seed_sources_stripe.ymlsources: - name: stripe database: analytics schema: stripe_raw tables: - name: invoice_line_items # 行项目——收入确认的粒度 - name: invoices - name: subscriptions - name: customers seeds: # 静态汇率表PostHog 的 convertCurrency 在 dbt 中没有等价物。 # 提供 seeds/currency_rates.csv列为: currency, rate_date, rate_to_base。 - name: currency_ratesfct_revenue_item手工实现递延收入确认这是 PostHog 托管revenue_item视图的 dbt 对应物核心是把每个经常性行项目均摊到服务期[period_start, period_end]覆盖的每个日历月——年费计划每月只贡献 1/12 到 MRR而不是一次性全额计入单月一次性项目只落在计费当月。粒度为每行项目服务月一行{{ config(materializedtable) }} with items as ( select * from {{ source(stripe, invoice_line_items) }} ), rates as ( select currency, rate_date, rate_to_base from {{ ref(currency_rates) }} ), -- 覆盖所有行项目跨度的月度骨架generate_series 为 Postgres/DuckDB 语法 month_spine as ( select generate_series( date_trunc(month, min(period_start)), date_trunc(month, max(coalesce(period_end, period_start))), interval 1 month ) as month from items ), recognized as ( select i.id, i.subscription_id, i.customer_id, i.product_id, i.period_start::date as period_start, i.period_end::date as period_end, i.currency, i.amount as line_amount, (i.subscription_id is not null) as is_recurring, s.month::date as month, count(*) over (partition by i.id) as service_months from items i join month_spine s on s.month date_trunc(month, i.period_start) and s.month date_trunc(month, case when i.subscription_id is not null then coalesce(i.period_end, i.period_start) else i.period_start end) ) select r.id as revenue_item_id, r.month, r.subscription_id, r.customer_id, r.product_id, r.period_start, r.period_end, r.is_recurring, r.currency as original_currency, r.line_amount / r.service_months as original_amount, -- 该月汇率下的基础货币金额。汇率缺失时故意留 NULL -- 让 amount 上的 not_null 测试大声失败而不是悄悄按 1:1 处理。 -- 基础货币本身需以 rate_to_base 1.0 入 seed。 (r.line_amount / r.service_months) * rt.rate_to_base as amount from recognized r left join rates rt on rt.currency r.currency and rt.rate_date r.month一个值得注意的工程细节汇率缺失时amount故意保持 NULL让下游not_null测试失败报警而不是默默假设 1:1 平价——这是 dbt 测试体系在货币场景下的典型防御性用法。fct_mrr密集 (客户 × 月) 骨架 桥接指标配方开头就点明关键陷阱没有覆盖每个客户每个月的密集骨架时lag()会把客户上一个活跃月和下一个活跃月直接比较静默跳过中间的流失月。因此先用customers cross join months左连接补零with monthly as ( select customer_id, month, sum(amount) as mrr from {{ ref(fct_revenue_item) }} where is_recurring group by 1, 2 ), months as (select distinct month from monthly), customers as (select distinct customer_id from monthly), filled as ( -- 每客户每月一行缺失月补零churn 表现为 mrr - 0 select c.customer_id, m.month, coalesce(mo.mrr, 0) as mrr from customers c cross join months m left join monthly mo on mo.customer_id c.customer_id and mo.month m.month ), with_prev as ( select customer_id, month, mrr, lag(mrr) over (partition by customer_id order by month) as prev_mrr from filled ) select month, sum(mrr) as mrr, sum(mrr) * 12 as arr, sum(case when coalesce(prev_mrr, 0) 0 and mrr 0 then mrr else 0 end) as new_mrr, sum(case when prev_mrr 0 and mrr prev_mrr then mrr - prev_mrr else 0 end) as expansion_mrr, sum(case when mrr 0 and mrr prev_mrr then prev_mrr - mrr else 0 end) as contraction_mrr, sum(case when prev_mrr 0 and mrr 0 then prev_mrr else 0 end) as churned_mrr from with_prev group by month order by month四个桥接指标的判断条件与定义文件中per customer的规则逐条对应New 要求prev 0 且当前 0Expansion 是增量部分Contraction 是减量部分Churn 是整个上月余额。恒等式starting_mrr new expansion - contraction - churn ending_mrr依旧适用。dim_customer 与 schema.yml 测试dim_customer.sql 对应托管customer视图的一行一客户维度customer_id、customer_name、email、country、first_seen_date。schema.yml 的测试覆盖了三条核心约束dim_customer.customer_iduniquenot_nullfct_revenue_itemunique_combination_of_columns([revenue_item_id, month])保证行项目 × 服务月粒度唯一、revenue_item_id not_null、customer_id的not_nullrelationships指向dim_customer引用完整性、amount not_null汇率缺失即报警fct_mrrmonth唯一非空、mrr not_null。仓库源码中的实现印证与配套文档托管视图并非文档层面的约定而是由 PostHog 后端自动生成并注册进 HogQL database 的对象。从 products/revenue_analytics/backend/views/ 的目录结构看schemas/revenue_item.py声明列定义sources/stripe/与sources/events/分别实现 Stripe 源与事件源两种数据通路对应 SKILL 中两条收入入口sources/test/下附有test_stripe_revenue_item.py、test_events_mrr.py等测试与.ambr快照用于锁定各源生成视图的输出行为。Schema结构schemas/revenue_item.py中的source_suffix/events_suffix也解释了发现视图名时会出现revenue_item_revenue_view源版与revenue_item_events_revenue_view事件版两类后缀的原因。模型建好之后按 governance.md 的要求注册复用给列加注解column annotations若 MRR/ARR 是对外headline数字则提议进入 semantic layer 供其他模型发现与复用。相关技能与延伸阅读按 SKILL 文档的 File map 与 Companions围绕本主题的配套材料文件何时读references/revenue-metric-definitions.mdMRR、ARR、gross、new/expansion/contraction/churn、ARPU、LTV 的精确定义references/posthog/托管视图上的 HogQL 视图配方references/dbt/dbt staging fct_*/dim_*marts schema.yml测试modeling-warehouse-foundations基础机制view 与 dbt 的选型、view-create/view-materialize/sync_frequency工作流、convertCurrency()其他配套技能modeling-warehouse-foundations机制层、setting-up-a-data-warehouse-sourcesuggesting-data-imports把 Stripe/收入数据接进来、modeling-dimension-tables货币/plan 维度、querying-posthog-dataHogQL 与 semantic layer 指标检查。小结这套技能文档给出的收入建模方法论可以概括为四步先找数据Stripe 源或自定义事件dbt 则 stage 计费表、建在托管视图上revenue_item起步mrr只是快照、记住六个坑MRR 空是配置问题不是 bug、amount已是基础货币、person 关联靠元数据、别依赖即将退役的 Revenue dashboard、排除测试账户、选栈落地PostHog 侧 HogQL 物化 daily 同步dbt 侧递延收入 marts 汇率 seed 测试最后用 MRR 变动桥接恒等式做校验并注册到 semantic layer。所有 SQL 配方均可直接从仓库中复制改写把revenue_item_view占位符替换为实际发现的视图名即可运行。【免费下载链接】posthog:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.项目地址: https://gitcode.com/GitHub_Trending/po/posthog创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
