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.

1. 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 `revenueitem (see the recipe), or materialize a monthly snapshot of the mrr` view on a schedule.

1. `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.

1. 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.

1. 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.

1. 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).