Mrr And Arr

1-- Monthly Recurring Revenue (and ARR) over time, from the managed revenue_item view.
2-- The managed `mrr` view is only a live snapshot, so derive the time series from recurring revenue_item rows.
3--
4-- Before creating the view, replace <revenue_item_view> with the real name for your project:
5--   SELECT table_name FROM system.information_schema.tables WHERE table_name ILIKE '%revenue_item%';
6-- e.g. `revenue_analytics.all.revenue_item_revenue_view` (cross-source) or `stripe.<prefix>.…`.
7--
8-- `amount` is already in the project base currency. Every output column is aliased (required by view-create).
9SELECT
10    toStartOfMonth(timestamp)            AS month,
11    sum(amount)                          AS mrr,          -- recognized recurring revenue this month
12    sum(amount) * 12                     AS arr_run_rate, -- MRR x 12
13    count(DISTINCT customer_id)          AS paying_customers,
14    round(sum(amount) / nullif(count(DISTINCT customer_id), 0), 2) AS arpa -- avg revenue per account
15FROM <revenue_item_view>
16WHERE is_recurring
17GROUP BY month
18ORDER BY month