modeling-revenue-metrics

Modeling revenue metrics

Turn payment/subscription data into durable revenue models. Read modeling-warehouse-foundations first for the view-vs-dbt decision, the view-* workflow, and convertCurrency(); this skill is the revenue-specific layer on top. Metric definitions live in references/revenue-metric-definitions.md; copy-paste recipes in references/posthog/ and references/dbt/.

Step 1 — find where revenue lives

Revenue reaches PostHog two ways; both feed the same managed revenue_analytics_* views:

• A payment platform as a warehouse source — Stripe today (Chargebee/Polar/RevenueCat coming). Best when the business runs on a billing platform. Connect via setting-up-a-data-warehouse-source.
• Custom revenue events — you send events (e.g. purchase_completed) with a revenue property. Best when there's no supported platform or you already track revenue in-product.

If neither exists yet, use suggesting-data-imports to recommend a source. In dbt, the equivalent is staging whichever billing tables landed in the warehouse.

Step 2 — model on the managed views, not raw tables

PostHog auto-generates a curated set of views per source. Do not re-derive revenue from raw Stripe tables — the managed views already handle deferred-revenue recognition, currency, and a stable schema.

Discover the exact names (they're prefixed by source, e.g. stripe.<prefix>.…, plus a cross-source revenue_analytics.all.…):

SELECT table_name FROM system.information_schema.tables WHERE table_name ILIKE '%revenue_analytics%'

Key revenue_item columns: amount (already converted to the project base currency), currency (that base currency), original_amount / original_currency (as charged), is_recurring, customer_id, subscription_id, product_id, group_0_key…group_4_key (B2B account keys), timestamp.

Rules before you model (revenue gotchas)

1. MRR is empty without a subscription config. For event-based revenue, MRR only populates when a subscription property is configured. Empty MRR + populated gross revenue is expected behaviour, not a bug — say so instead of "fixing" it.
2. The mrr managed view is a current snapshot, not history ("MRR at the current time"). For MRR over time, sum recurring amount per month from revenue_item (see the recipe), or materialize a monthly snapshot of the mrr view on a schedule.
3. amount is already in base currency. Use it directly for reporting. Only call convertCurrency(original_currency, 'XXX', original_amount, timestamp) when you need a different target currency, or when working from raw events.
4. Link revenue to people via metadata. Person/group-level revenue needs posthog_person_distinct_id metadata on the Stripe customer (or the person join). Without it, revenue is customer-level only.
5. Don't build on the Revenue dashboard — it's being retired (~2026-06-30). Model against the revenue_analytics_* views and the person/group revenue properties.
6. Exclude test accounts. Confirm filter_test_accounts behaviour so QA/internal charges don't inflate revenue.

Step 3 — build the model

PostHog: write the HogQL (alias every column), view-create, verify with view-get, then view-materialize the expensive monthly rollups (a daily sync_frequency is usually right for revenue). Recipes: references/posthog/ — mrr_and_arr.sql, gross_revenue_by_month.sql, revenue_by_customer.sql.

dbt: stage the billing source → fct_revenue_item, fct_mrr, dim_customer marts with tests. Recipes: references/dbt/. Note dbt has no convertCurrency() — supply a rate seed.

Then register the model (references/governance.md in foundations): annotate columns and, if MRR/ARR is a headline number, propose it to the semantic layer.

File map

Companions

modeling-warehouse-foundations (mechanics), setting-up-a-data-warehouse-source + suggesting-data-imports (get Stripe/revenue data in), modeling-dimension-tables (currency/plan dimensions), querying-posthog-data (HogQL + the semantic-layer metric check).