Skip to main content
Available on request — The Autumn Lakehouse is provisioned per customer. Contact us at hey@useautumn.com to get access.
The same lesson as Working with balances applies across the rest of the schema: the warehouse stores inputs; the business number is a derivation that drops rows. Columns are named like a clean OLTP table, but realized revenue, MRR, and paying-customer counts each require knowing which rows to exclude and which column actually carries truth. The traps below all surfaced from running realistic BI queries against live data.

​
Revenue: total is not realized revenue

sum(total) over invoices overstates what you collected — typically by low double digits — because total is the gross, pre-refund amount and includes discounts, proration, and partially-paid invoices. Realized revenue is what cleared, net of refunds: 
realized_revenue = Σ amount_paid − Σ refunded_amount   over status = 'paid'
Three traps around it:
  • Amounts are in the currency’s major unit (dollars), not Stripe cents. Sanity-check one known invoice before scaling — porting Stripe intuition will put you off by 100×.
  • void invoices carry real-looking totals and must be excluded. Filter status = 'paid', not merely non-null.
  • paid includes negative totals (credit notes / refunds modeled as negative invoices) — expected, and correctly handled by the amount_paid − refunded_amount form.
SELECT
  sum(amount_paid) - sum(refunded_amount) AS net_revenue,
  sum(total)                              AS gross_total_do_not_use
FROM `<catalog>`.`<namespace>.v2_3_invoices`
WHERE env = 'live' AND status = 'paid';

​
Paying customers: status = 'active' is not “paying”

Active subscriptions include your entire free base and everyone mid-trial. A naive count() of active rows can overstate paying customers by orders of magnitude. A paying customer has an active, priced, non-trial base subscription — and you must dedup, because one customer holds several subscription rows (versions + add-ons). 
SELECT uniqExact(s.internal_customer_id) AS paying_customers
FROM `<catalog>`.`<namespace>.v2_3_subscriptions` AS s
INNER JOIN `<catalog>`.`<namespace>.v2_3_plans` AS p
  ON p.internal_id = s.internal_product_id
WHERE s.env = 'live'
  AND s.status = 'active'
  AND s.add_on = false
  AND coalesce(p.price_amount, 0) > 0
  AND coalesce(s.trial_ends_at, 0) <= toUnixTimestamp(now()) * 1000;  -- not currently in trial
Row count ≠ customer count: subscription rows exceed distinct customers because of plan versions, duplicate base rows, and add-ons. Anything customer-level needs uniqExact(internal_customer_id), never count().

​
Cross-reference: which overage?

Overage (on the balances side) has the same “two figures” hazard as revenue here — billable (what you invoice) vs displayed (what the dashboard header shows) diverge by undrawn grants. If a report combines revenue and overage, make sure both pages agree on which overage you mean. See Working with balances → Overage.
All queries here use env = 'live' (not production) and the `<catalog>`.`<namespace>.v2_3_…` addressing form. Join on internal_* ids — external ids are mutable and versioned. See Querying for the addressing and IS NULL footguns.