Working with invoices

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.

Getting Started

Configure Pricing

Billing & Subscriptions

Customers

Additional Resources

Lakehouse

Working with invoices

Revenue: `total` is not realized revenue

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

Cross-reference: which overage?

​Revenue: total is not realized revenue

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

​Cross-reference: which overage?

Revenue: `total` is not realized revenue

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

Cross-reference: which overage?