Working with invoices

Invoices look simple, but realized revenue, revenue per plan and paying-customer counts each depend on dropping the right rows.

Revenue: use `amount_paid`, not `total`

total is the gross amount before refunds, and it includes invoices that were never paid. Revenue you actually collected is what was paid, minus refunds, on paid invoices:

realized_revenue = Σ amount_paid − Σ refunded_amount   over status = 'paid'

Amounts are in major units (dollars, not cents). Don’t divide by 100.
void invoices carry real-looking totals. Filter status = 'paid'.
Paid invoices can be negative (credit notes). The formula above handles them.
Group by currency if you bill in more than one.

SELECT currency,
       sum(amount_paid) - sum(refunded_amount) AS net_revenue,
       sum(total)                              AS gross_total
FROM <namespace>.v2_6_invoices
WHERE env = 'live' AND status = 'paid'
GROUP BY currency;

Revenue per plan

An invoice can cover several plans, so plan_ids is a JSON array. To split by plan, sum the line items, which each name one plan. Line items have no currency of their own; take it from the invoice.

SELECT i.currency, li.plan_id, sum(li.amount) AS billed
FROM <namespace>.v2_6_invoice_line_items li
JOIN <namespace>.v2_6_invoices i
  ON i.org_id = li.org_id AND i.env = li.env AND i.invoice_id = li.invoice_id
WHERE i.env = 'live' AND i.status = 'paid'
GROUP BY i.currency, li.plan_id
ORDER BY billed DESC;

Line amounts are before discounts, tax and refunds, so on discounted invoices the split adds up to more than amount_paid. To filter invoices by plan without line items, parse plan_ids (see Querying → JSON columns).

Paying customers: `active` is not “paying”

Active subscriptions include your free plans and customers in a trial. A paying customer has a live, priced, non-trial base plan. Count distinct customers, because one customer can hold several subscription rows (a main plan and add-ons).

SELECT count(DISTINCT s.internal_customer_id) AS paying_customers
FROM <namespace>.v2_6_subscriptions s
JOIN <namespace>.v2_6_plans p
  ON p.org_id = s.org_id AND p.env = s.env AND p.internal_plan_id = s.internal_plan_id
WHERE s.env = 'live'
  AND s.status IN ('active', 'past_due')
  AND s.add_on = false
  AND coalesce(p.price_amount, 0) > 0
  AND coalesce(s.trial_ends_at, 0) <= <now in epoch ms>;   -- not in a trial

For a custom plan (is_custom = true) the catalog plan’s price may not be what the customer pays. Check the customer’s own prices through v2_6_customer_plan_items if custom plans matter for your count.

Lakehouse

Guides

Working with invoices

Revenue: use `amount_paid`, not `total`

Revenue per plan

Paying customers: `active` is not “paying”

​Revenue: use amount_paid, not total

​Revenue per plan

​Paying customers: active is not “paying”

Revenue: use `amount_paid`, not `total`

Revenue per plan

Paying customers: `active` is not “paying”