Modeling Revenue Metrics

PostHogOfficialPublished by PostHog

Skill
Guidelines
Data and AI
MIT
No issues found

From the author

Build reusable revenue models — MRR, ARR, gross revenue, new/expansion/contraction/churn, ARPU, LTV, and per-customer/per-account revenue — on either PostHog data-warehouse views (HogQL) or an external dbt project. Use when the user wants to model, define, or compute recurring revenue, monthly/annual recurring revenue, churn or retention of revenue, lifetime value, average revenue per user, or revenue by customer, cohort, product, or currency. On PostHog, build on the managed *_revenue_view views (revenue_item, mrr, customer, subscription, charge, product) fed by Stripe or custom revenue events — not raw Stripe tables — and normalize money with convertCurrency(). In dbt, stage the payment source and compute fct_mrr / fct_revenue_item / dim_customer marts with tests. Covers picking the right source, the subscription-config gotcha that leaves MRR empty, currency handling, and linking revenue to persons/groups. Read modeling-warehouse-foundations first for the view-vs-dbt mechanics.

— the description by PostHog

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

This skill provides a structured approach to modeling recurring revenue metrics using either PostHog's managed revenue views (HogQL) or an external dbt project. It emphasizes using pre-curated revenue views rather than raw billing data to ensure accuracy in currency conversion, deferred revenue recognition, and schema stability. The guide covers the full lifecycle of revenue modeling, from identifying data sources and handling common pitfalls like empty MRR, to building specific marts for customer, subscription, and revenue analysis.

What it does

Defines standard revenue metrics including MRR, ARR, gross revenue, and churn.
Provides instructions for modeling on PostHog managed revenue views.
Outlines dbt workflows for staging billing sources and creating revenue marts.
Addresses common revenue modeling challenges like currency normalization and test account filtering.
Explains how to link revenue data to specific persons or groups.
Offers recipes for common revenue queries and reporting.

When to use it

When you need to compute recurring revenue metrics like MRR or ARR.
When you are setting up revenue analytics in PostHog or a dbt-managed warehouse.
When you need to analyze revenue by customer, cohort, product, or currency.
When you need to reconcile revenue data from billing platforms like Stripe.

Advantages

Uses managed views to handle complex logic like deferred revenue automatically.
Ensures consistent metric definitions across the organization.
Provides clear separation between live snapshots and historical time-series data.
Offers flexible implementation paths via HogQL or dbt.

Usage examples

“How do I calculate monthly recurring revenue using PostHog?”: The agent will guide you to sum the recurring amount from the revenue_item managed view grouped by month, ensuring you avoid using the live snapshot mrr view for historical trends.
“Why is my MRR showing as empty in my revenue model?”: The agent will explain that MRR requires a subscription configuration and that this is expected behavior if the configuration is missing, rather than a data error.

Install

Download the skill as a ZIP:
https://autogineer.com/agent-skills/posthog/modeling-revenue-metrics/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip modeling-revenue-metrics.zip -d .agents/skills

Documentation by PostHog

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

Each source gets its own views. There is no cross-source view. Names depend on the source type:

Warehouse source: <source>.<prefix>.<kind>_revenue_view, for example stripe.prod.mrr_revenue_view. The <prefix>. part is absent when the source has no prefix.
Custom revenue events: revenue_analytics.events.<event_name>.<kind>_events_revenue_view, where every non-alphanumeric character in the event name becomes _.

<kind> is one of the managed views in the table below. Discover the exact names. Do not search for revenue_analytics, because it does not match warehouse source views:

SELECT table_name FROM system.information_schema.tables
WHERE position(table_name, 'revenue_view') > 0 ORDER BY table_name

If this returns nothing, the project has no revenue source configured yet. Check the governed mrr metric (metric-list) or discover warehouse billing tables, and read their columns before modeling. Do not guess view names.

Managed view	Grain	Use for
`revenue_item` (start here)	1 / invoice line item	Gross revenue, monthly recurring revenue, revenue by product/customer/period. Implements deferred revenue + currency.
`mrr`	1 / (customer, subscription)	Live snapshot of current MRR — not a time series.
`customer`	1 / customer	`dim_customer`: email, country, cohort, metadata.
`subscription`	1 / subscription	Subscription state for churn/expansion logic.
`charge`	1 / charge	Raw charges; prefer `revenue_item` unless you specifically need charges.
`product`	1 / product	Product dimension.

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)

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.
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.
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.
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.
Don't build on the Revenue dashboard — it's being retired (~2026-06-30). Model against the managed revenue views and the person/group revenue properties.
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

File	Read when
`references/revenue-metric-definitions.md`	Precise definitions: MRR, ARR, gross, new/expansion/contraction/churn, ARPU, LTV.
`references/posthog/`	HogQL view recipes on the managed views.
`references/dbt/`	dbt staging + `fct_`/`dim_` marts + `schema.yml` tests.

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

Files

ATTRIBUTION.mdAdded by AutoGineer867 B
LICENSELicence file added by AutoGineer1.0 KiB
SKILL.md8.3 KiB
references/dbt/_sources_stripe.yml862 B
references/dbt/dim_customer.sql324 B
references/dbt/fct_mrr.sql1.9 KiB
references/dbt/fct_revenue_item.sql2.7 KiB
references/dbt/schema.yml1.1 KiB
references/posthog/gross_revenue_by_month.sql595 B
references/posthog/mrr_and_arr.sql1.1 KiB
references/posthog/revenue_by_customer.sql1.1 KiB
references/revenue-metric-definitions.md5.5 KiB

references/posthog/mrr_and_arr.sql

-- Monthly Recurring Revenue (and ARR) over time, from the managed revenue_item view.
-- The managed `mrr` view is only a live snapshot, so derive the time series from recurring revenue_item rows.
--
-- Before creating the view, replace <revenue_item_view> with the real name for your project:
--   SELECT table_name FROM system.information_schema.tables WHERE position(table_name, 'revenue_item') > 0;
-- e.g. `stripe.<prefix>.revenue_item_revenue_view` or `revenue_analytics.events.<event>.revenue_item_events_revenue_view`.
--
-- `amount` is already in the project base currency. Every output column is aliased (required by view-create).
SELECT
    toStartOfMonth(timestamp)            AS month,
    sum(amount)                          AS mrr,          -- recognized recurring revenue this month
    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 -- avg revenue per account
FROM <revenue_item_view>
WHERE is_recurring
GROUP BY month
ORDER BY month

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Published by

PostHog

Publisher

PostHog (Official)

Source

PostHog/skills · skills/omnibus/modeling-revenue-metrics · commit a5d30e7

Licence

MIT License (redistribution allowed)

LICENSE (added by AutoGineer from LICENSE)
ATTRIBUTION.md (written by AutoGineer)

Synced

2026-11-09

AutoGineer is not affiliated with or endorsed by PostHog or PostHog.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download modeling-revenue-metrics.zip