Financial analyses using Orb data

Create your own financial reports or analyses using the data provided by Orb. Here are some common reports and how to get started in Orb.

Quick reference: Resources by use case

Use this table to determine which resources to enable for your reporting needs:

Use case	Required resources	Notes
Billings/AR reporting	`invoice_metadata`, `credit_note`, `customer_balance_transaction`	Add `invoice_line_item_billing` for line-item detail
Revenue recognition	`daily_line_item_revenue`, `credit_ledger_entry`, `credit_note_line_item`	Use `_event` variants if respecting accounting period locks
Collections/AR aging	`invoice_metadata`, `payment_attempt`, `payment_application`	See also AR aging in Orb
Customer/subscription analytics	`customer`, `subscription`, `subscription_version`, `plan`	Add `price_interval` for pricing history
Discount analysis	`adjustment`, `adjustment_interval`, `coupon`, `coupon_redemption`	Join with `invoice_line_item_billing` to see applied amounts
Credit tracking	`credit_ledger_entry`	Use `_event` variant for accounting period compliance

Base vs Event resources: Resources ending in _event (like daily_line_item_revenue_event) include lock_adjusted_timestamp for accounting period lock compliance. Use these when your finance team closes accounting periods in Orb.

If you issue invoices to customers in more than one currency, make sure you group by or filter to a specific invoicing_currency to avoid adding numbers together that reference different currencies. For example, you can’t sensibly add billing or revenue numbers together for JPY and USD currencies.

Billings reports

The following resource types can help you understand invoice details and line item values for invoices that have been issued / billed.

Resource type	Description
`invoice_line_item_billing` or `invoice_line_item_billing_event`	Contains the individual line item values for invoices that have been issued / billed. `invoice_line_item_billing_event` contains additional fields to enable recognition with respect to accounting period locks.
`invoice_metadata`	Contains data for every invoice in Orb, including draft and voided invoices. Contains the high level information about the invoice including status, customer, total, invoice number, and more. Credit notes are not included.
`invoice`	Contains data for all non-draft invoices. Contains broken out information about line items, adjustments, and address information in JSON fields. In general, we recommend combining `invoice_metadata` with other resources instead of using the `invoice` resource.

Net billings

To calculate net billings for a specific period, sum the total from invoices sent in the period, less the total from credit notes.

Resource type	Task	Steps
`invoice_metadata`	Sum invoice amounts	Filter out voided invoices using `voided_at` and filter out deleted invoices using `deleted_at` Filter to the desired date range using `invoice_date` Sum the `total`
`credit_note_line_item`	Remove credit note amounts	Filter out voided credit notes using `voided_at` and filter out deleted credit notes using `deleted_at` Filter to the desired invoice using `invoice_id` Sum the `total`
`customer_balance_transaction`	Remove applied customer balances	Filter to `action = 'applied_to_invoice'` Filter out deleted transactions using `deleted_at` Handle both decrements (credit applied) and increments (negative balance applied)

Combined query

WITH relevant_invoices AS (
  SELECT id
       , invoice_date
       , total

  FROM invoice_metadata AS invoice

  WHERE invoice.status IN ('issued', 'paid', 'synced')
    AND invoice.deleted_at IS NULL
    AND invoice.voided_at IS NULL
)

, aggregate_invoices AS (
  SELECT DATE_TRUNC('month', invoice_date) AS invoice_month
       , COUNT(*) AS cnt
       , SUM(total) AS total

  FROM relevant_invoices AS invoice

  GROUP BY 1
)

, aggregate_credit_notes AS (
  SELECT DATE_TRUNC('month', invoice.invoice_date) AS invoice_month
       , SUM(CAST(credit_note.total AS DECIMAL)) AS total

  FROM relevant_invoices AS invoice
  LEFT JOIN credit_note ON credit_note.invoice_id = invoice.id

  WHERE credit_note.voided_at IS NULL
    AND credit_note.deleted_at IS NULL

  GROUP BY 1
)

, aggregate_balance_transactions AS (
  -- Balance transactions can either reduce or increase invoice amount due:
  -- - type='decrement' with action='applied_to_invoice': customer credit used (reduces amount due)
  -- - type='increment' with action='applied_to_invoice': negative balance applied (increases amount due)
  SELECT DATE_TRUNC('month', invoice.invoice_date) AS invoice_month
       , SUM(CASE
           WHEN cbt.type = 'decrement' THEN cbt.amount   -- Credit applied (subtract from billings)
           WHEN cbt.type = 'increment' THEN -cbt.amount  -- Negative balance (add to billings)
           ELSE 0
         END) AS total

  FROM relevant_invoices AS invoice
  LEFT JOIN customer_balance_transaction AS cbt ON cbt.invoice_id = invoice.id

  WHERE cbt.action = 'applied_to_invoice'
    AND cbt.deleted_at IS NULL

  GROUP BY 1
)

SELECT invoice.invoice_month
     , invoice.cnt AS count
     , (invoice.total - COALESCE(credit_note.total, 0) - COALESCE(balance_transaction.total, 0)) AS total

FROM aggregate_invoices AS invoice
LEFT JOIN aggregate_credit_notes AS credit_note ON invoice.invoice_month = credit_note.invoice_month
LEFT JOIN aggregate_balance_transactions AS balance_transaction ON invoice.invoice_month = balance_transaction.invoice_month

ORDER BY 1 ASC

Invoices by status

View invoice totals by invoice status to view collections, refunds, and pending balances.

Resource type	Task	Steps
`invoice_metadata`	Filter to issued invoices	Filter our voided and deleted invoices used `voided_at` and `deleted_at` respectively. Filter to desired date range using `due_date` or `invoice_date`
`invoice_metadata`	Group by status	Group by `status` to get an associated breakdown

SELECT DATE_TRUNC('month', invoice.invoice_date)
     , count(*) AS count
     , SUM(invoice.total) AS total

FROM invoice_metadata AS invoice

-- The invoice statuses for issued invoices
WHERE invoice.status IN ('issued', 'paid', 'synced')

  AND invoice.deleted_at IS NULL
  AND invoice.voided_at IS NULL

GROUP BY 1
ORDER BY 1 ASC

SELECT invoice.status
     , count(*) AS count
     , SUM(invoice.total) AS total

FROM invoice_metadata AS invoice
WHERE invoice.deleted_at IS NULL
  AND invoice.voided_at IS NULL

GROUP BY 1
ORDER BY 1 ASC

AR Aging

To understand days outstanding for invoices issued through Orb to power your collections workflow, group issued invoices that have not been paid by number of days outstanding.

This report only works for customers invoicing through Orb. If you’re syncing invoices from Orb to a third party system, you’ll need to leverage that system’s reporting capabilities to understand invoices that are past due.

Resource type	Task	Steps
`invoice_metadata`	Filter to relevant invoices	Filter to `issued` or `synced` status Filter out voided and deleted invoices
`invoice_metadata`	Group invoices by days outstanding	Use `due_date` to bucket invoices into aging groups: Current, 1-30, 31-60, 61-90, 91+ days past due

Generate an AR Aging report

WITH unpaid_invoices AS (
  SELECT id
       , due_date
       , customer_id
       , total
       , CAST(amount_due AS DECIMAL) AS amount_due

  FROM invoice_metadata

  -- Include issued and synced invoices (unpaid)
  WHERE status IN ('issued', 'synced')
    AND deleted_at IS NULL
    AND voided_at IS NULL
)

, invoices_with_buckets AS (
  SELECT id
       , customer_id
       , total
       , amount_due
       , CASE
           WHEN due_date >= NOW() THEN 'Current'
           WHEN due_date >= NOW() - INTERVAL '30 days' THEN '1-30 days past due'
           WHEN due_date >= NOW() - INTERVAL '60 days' THEN '31-60 days past due'
           WHEN due_date >= NOW() - INTERVAL '90 days' THEN '61-90 days past due'
           ELSE '91+ days past due'
         END AS aging_bucket

  FROM unpaid_invoices
)

SELECT aging_bucket
     , customer.name
     , COUNT(*) AS invoice_count
     , SUM(total) AS total
     , SUM(amount_due) AS amount_due

FROM invoices_with_buckets
LEFT JOIN customer ON customer.id = invoices_with_buckets.customer_id

GROUP BY 1, 2
ORDER BY
  CASE aging_bucket
    WHEN 'Current' THEN 1
    WHEN '1-30 days past due' THEN 2
    WHEN '31-60 days past due' THEN 3
    WHEN '61-90 days past due' THEN 4
    WHEN '91+ days past due' THEN 5
  END
, 2 ASC

Revenue recognition reports

The following resource types can help you understand how to recognize your revenue.

Resource type	Description
`daily_line_item_revenue` or `daily_line_item_revenue_event`	Contains data for all invoice line items per day, including quantity and rate details. `daily_line_item_revenue_event` contains additional fields to enable recognition with respect to accounting period locks.
`credit_ledger_entry` or `credit_ledger_entry_event`	Contains data for all credit ledger entries. `credit_ledger_entry_event` contains additional fields to enable recognition with respect to accounting period locks.
`credit_note_line_item` or `credit_note_line_item_event`	Contains data for credit note amounts broken down by line item. Today we don’t expose daily revenue amounts for credit note line items, so these amounts will need to be prorated across the relevant invoice line item period. `credit_note_line_item_event` contains additional fields to enable recognition with respect to accounting period locks.

When analyzing revenue data, there are a few gotchas:

Where relevant, make sure you analyze all sources that contribute to revenue amounts (the three sources listed above). Leaving out any of these resources of revenue data may lead to incorrect numbers.
If you issue invoices to customers in more than one currency, make sure you group by currency to avoid adding numbers together that reference different currencies (e.g. you can’t add revenue numbers together for JPY and USD).

Recognized revenue

To understand how much revenue to recognize based on the invoice line items, you can leverage the daily_line_item_revenue resource.

Resource type	Task	Steps
`daily_line_item_revenue`	Filter to relevant line items	Filter out reverted entries with `reverted_at` Filter to the desired date range using `timeframe_start` and `timeframe_end`
`daily_line_item_revenue`	Sum the revenue amounts	Sum `rounded_amount` to get a final revenue number

Alternatively, to recognize revenue with respect to the accounting period lock posture in Orb, leverage the daily_line_item_revenue_event resource.

Resource type	Task	Steps
`daily_line_item_revenue_event`	Filter to relevant line items	Filter to the desired date range using `lock_adjusted_timestamp`
`daily_line_item_revenue_event`	Sum the revenue amounts	Sum `rounded_amount`, treating entries with `is_revert = False` as positive and `is_revert = True` as negative, to get a final revenue number.

WITH daily_line_items AS (
  SELECT timeframe_start
       , invoicing_currency
       , rounded_amount

  FROM daily_line_item_revenue

  -- We are interested in our revenue for calendar year 2024
  WHERE CAST(timeframe_start AT TIME ZONE 'UTC' AS DATE) >= '2024-01-01'
    AND CAST(timeframe_start AT TIME ZONE 'UTC' AS DATE) < '2025-01-01'
    AND reverted_at IS NULL
    -- We want to filter out partial invoices to avoid counting revenue multiple times
    AND is_partial_invoice IS FALSE
)

SELECT DATE_TRUNC('month', timeframe_start AT TIME ZONE 'UTC') AS month
     , invoicing_currency AS currency
     , SUM(rounded_amount) AS rounded_amount

FROM daily_line_items

GROUP BY 1, 2
ORDER BY 1 ASC NULLS FIRST

WITH daily_line_items AS (
  SELECT lock_adjusted_timestamp
       , invoicing_currency
       , rounded_amount
       , is_revert

  FROM daily_line_item_revenue_event

  -- We are interested in our revenue for calendar year 2024
  WHERE CAST(lock_adjusted_timestamp AT TIME ZONE 'UTC' AS DATE) >= '2024-01-01'
    AND CAST(lock_adjusted_timestamp AT TIME ZONE 'UTC' AS DATE) < '2025-01-01'
    -- We want to filter out partial invoices to avoid counting revenue multiple times
    AND is_partial_invoice IS FALSE
)

SELECT DATE_TRUNC('month', timeframe_start AT TIME ZONE 'UTC') AS month
     , invoicing_currency AS currency
     -- We need to handle revert entries appropriately
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (rounded_amount * -1)
               ELSE rounded_amount
     ) AS rounded_amount

FROM daily_line_items

GROUP BY 1, 2
ORDER BY 1 ASC NULLS FIRST

Orb also provides a breakdown of each individual step in the invoicing process to allow for precise analysis of invoicing calculations. The documentation linked provides a great breakdown, but a quick summary is:

Value	Field Name	Description
List price / subtotal	`subtotal`	The original price before any adjustment, prepaid credits, or otherwise.
Adjusted subtotal	`adjusted_subtotal`	The list price minus any adjustments. If no adjustments have been applied, this value is the same as the list price.
Credits applied	`credits_applied`	The amount of prepaid credits applied to this line item. If no prepaid credits have been applied, this value is 0.
Conversion rate	`conversion_rate`	The rate at which usage in a virtual currency is converted to a real currency (e.g. from credits to USD). If the invoicing currency and the pricing currency are the same, the `conversion_rate_` is 1.
Partially invoiced amount	`partially_invoiced_amount`	The amount that has been invoiced already through a partial invoice. If no partial invoices have been issued, this value is 0.
Amount	`amount`	The amount of the line item before any final rounding.
Rounded amount	`rounded_amount`	The amount of the line item after rounding and before taxes, if applicable, are applied.

WITH daily_line_items AS (
  SELECT *
  FROM daily_line_item_revenue

  WHERE CAST(timeframe_start AT TIME ZONE 'UTC' AS DATE) >= '2025-01-01'
    AND CAST(timeframe_start AT TIME ZONE 'UTC' AS DATE) < '2025-02-01'
    AND reverted_at IS NULL
)

SELECT CAST(timeframe_start AT TIME ZONE 'UTC' AS DATE)
     , count(*) AS num_line_items

     -- Step 1: list price
     , SUM(subtotal) AS list_price

     -- Step 2: adjusted subtotal
     , SUM(adjusted_subtotal) AS list_price_minus_adjustments

     -- Step 3: track number of prepaid credits applied
     , SUM(credits_applied) AS credits_applied

     -- Step 4: subtract prepaid credits and convert
     , SUM((adjusted_subtotal - credits_applied) * conversion_rate) AS post_prepaid_credits

     -- Step 5: subtract any partial invoices to get the amount before rounding (can be computed two different ways)
     , SUM((adjusted_subtotal - credits_applied) * conversion_rate - partially_invoiced_amount) AS amount_complex
     , SUM(amount) AS amount_simple

     -- Step 6: any final rounding occurs
     , SUM(rounded_amount) AS rounded_amount

FROM daily_line_items

GROUP BY 1
ORDER BY 1 ASC

WITH daily_line_items AS (
  SELECT *
  FROM daily_line_item_revenue_event

  WHERE CAST(lock_adjusted_timestamp AT TIME ZONE 'UTC' AS DATE) >= '2025-01-01'
    AND CAST(lock_adjusted_timestamp AT TIME ZONE 'UTC' AS DATE) < '2025-02-01'
)

SELECT CAST(timeframe_start AT TIME ZONE 'UTC' AS DATE)
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN -1
               ELSE 1
     ) as num_line_items

     -- Step 1: list price
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (subtotal * -1)
               ELSE subtotal
     ) AS list_price

     -- Step 2: adjusted subtotal
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (adjusted_subtotal * -1)
               ELSE adjusted_subtotal
     ) AS list_price_minus_adjustments

     -- Step 3: track number of prepaid credits applied
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (credits_applied * -1)
               ELSE credits_applied
     ) AS credits_applied

     -- Step 4: subtract prepaid credits and convert
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (((adjusted_subtotal - credits_applied) * conversion_rate) * -1)
               ELSE (adjusted_subtotal - credits_applied) * conversion_rate
     ) AS post_prepaid_credits

     -- Step 5: subtract any partial invoices to get the amount before rounding (can be computed two different ways)
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (((adjusted_subtotal - credits_applied) * conversion_rate - partially_invoiced_amount) * -1)
               ELSE (adjusted_subtotal - credits_applied) * conversion_rate - partially_invoiced_amount
     ) AS amount_complex
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (amount * -1)
               ELSE amount
     ) AS amount_simple

     -- Step 6: any final rounding occurs
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (rounded_amount * -1)
               ELSE rounded_amount
     ) AS rounded_amount

FROM daily_line_items

GROUP BY 1
ORDER BY 1 ASC

Credit drawdown

To understand credit utilization and the revenue recognized from prepaid credits, leverage the credit_ledger_entry resources.

Resource type	Task	Steps
`credit_ledger_entry`	Filter to relevant ledger entries	Filter out reverted entries with `reverted_at`
`credit_ledger_entry`	Convert entries to revenue amounts	Use the `entry_type` to determine the revenue amount and multiply by the `cost_basis` from the credit block
`credit_ledger_entry`	Sum the revenue amounts based on their cost basis	Use the value calculated above

Alternatively, leverage credit_ledger_entry_event for a simpler approach—it includes the pre-calculated recognized_revenue_amount field and respects accounting period locks.

Resource type	Task	Steps
`credit_ledger_entry_event`	Sum the recognized revenue amounts	Sum `recognized_revenue_amount`, treating entries with `is_revert = False` as positive and `is_revert = True` as negative

The credit_ledger_entry_event resource includes a recognized_revenue_amount field that pre-calculates the revenue impact of each credit operation based on the credit block’s cost basis. This is simpler than manually calculating amount * cost_basis from the base resource.

WITH revenue_amounts AS (
    SELECT id
         , created_at
         , CAST(credit_block->>'per_unit_cost_basis' AS DECIMAL) AS cost_basis
         , CASE
               WHEN entry_type = 'amendment' THEN CAST(amount AS DECIMAL)
               WHEN entry_type = 'decrement' THEN CAST(amount AS DECIMAL)
               WHEN entry_type = 'credit_block_expiry' THEN CAST(amount AS DECIMAL)
               -- Everything else will recognize to 0
               ELSE 0
           END AS entry_amount

    FROM credit_ledger_entry
    WHERE reverted_at IS NULL
)

SELECT DATE_TRUNC('day', created_at AT TIME ZONE 'UTC') AS day
     , SUM(entry_amount * cost_basis) AS total_revenue

FROM revenue_amounts

GROUP BY 1
ORDER BY 1 ASC

WITH revenue_amounts AS (
    SELECT id
         , lock_adjusted_timestamp
         , is_revert
         , CASE
               WHEN entry_type = 'amendment' THEN recognized_revenue_amount
               WHEN entry_type = 'decrement' THEN recognized_revenue_amount
               WHEN entry_type = 'credit_block_expiry' THEN recognized_revenue_amount
               -- Everything else will recognize to 0
               ELSE 0
           END AS entry_recognized_amount

    FROM credit_ledger_entry_event
)

SELECT DATE_TRUNC('day', lock_adjusted_timestamp AT TIME ZONE 'UTC') AS day
     , SUM(CASE
               WHEN (is_revert = TRUE) THEN (entry_recognized_amount * -1)
               ELSE entry_recognized_amount
     ) AS total_revenue

FROM revenue_amounts

GROUP BY 1
ORDER BY 1 ASC

Deferred revenue

Deferred revenue represents amounts billed but not yet recognized—revenue you’ve invoiced but haven’t yet earned. To calculate deferred revenue: sum billings, less revenue recognized for a particular period.

Unbilled revenue

Unbilled revenue (also called “accrued revenue”) represents revenue recognized but not yet invoiced—typically usage charges accruing on draft invoices.

Unbilled revenue appears when revenue is recognized before billing occurs. This is common with usage-based charges billed in-arrears, where usage accrues throughout the billing period but the invoice isn’t issued until after the period ends.

Reconciliation reports

To reconcile bank account balances, invoice_metadata provides details for invoices with paid status that can be combined with feeds from payment processors like Stripe to perform an account level reconciliation. If you use Orb to power your payments, the relevant Orb identifiers are included in the resource for joining against Stripe’s data. For Stripe, the external identifiers on an item represent the Stripe Product ID. For other systems like Quickbooks or Netsuite, these identifiers maps to the Item ID in those systems.

Fetch Orb invoice identifiers for reconciliation

SELECT id
     , invoice_number
     , customer_id
     , issued_at
     , due_date
     , paid_at

FROM invoice_metadata

WHERE status = 'paid'
  AND deleted_at IS NULL
  AND voided_at IS NULL

Orb’s item resource also provides access to third party item identifiers to help with reconciliation.

Fetching item mappings

SELECT id
     , external_connections AS third_party_mappings

FROM item
WHERE archived_at IS NULL

Contra revenue reports

Invoice amounts are not always equal to the list price of the products being sold. For example, customers may be given discounts for special promotions, credit notes may be issued due to service outages, or customers may have balances that are applied against invoices from previous overpayment. The following resource types can help you understand the cases where the invoice list price may not be equal to the final amount paid by the customer.

Resource type	Description
`coupon` and `coupon_redemption`	Contains the coupons you’ve created with Orb and the associated Adjustment created when the coupon was redeemed.
`adjustment` and `adjustment_interval`	Adjustments are Orb modeling for discounts (amount, percent, or usage) and minimums or maximums. They are most useful for understanding the impetus for a given adjustment on an invoice. Adjustment Intervals are used to understand the period of time over which an Adjustment is relevant.
`credit_note` and `credit_note_line_item`	Credit Notes are the credits given to a customer’s invoices after the invoice has been issued. Since an issued invoice is an immutable financial document, Credit Notes all for changing of the final amount owed on the invoice by creating a new financial document, the Credit Note. Credit Note Line Items are the individual amounts applied to each invoice line item when a Credit Note is created and are useful to understand how each invoice line item’s amount is affected to the end customer.
`customer_balance_transaction`	Customer Balance Transactions are used to understand the amount(s) applied to an invoice from the customer’s outstanding balance. Customer Balance Transactions occur when there is an outstanding customer balance that is owed to the customer.

Credit notes

To understand the amount of money that was credited on invoices we can leverage the credit_note resource. Credit notes contain the reason, amount, and relevant invoice where they were applied. Since credit notes are financial documents applied on top of issued invoices, credit notes are always relevant to issued invoices.

Resource type	Task	Steps
`credit_note` and `invoice`	Filter to relevant credit notes	Filter out voided and deleted resources
`credit_note`	Sum credit note amounts	Sum the credit note `total` and aggregate the list of issuance `reason`

Breakdown issued credit notes

SELECT invoice.id
     , invoice.invoice_number
     , COUNT(*) AS num_credit_notes
     , SUM(CAST(credit_note.total AS DECIMAL)) AS total
     , STRING_AGG(DISTINCT(credit_note.reason), ', ') AS reasons

FROM credit_note
JOIN invoice_metadata AS invoice ON invoice.id = credit_note.invoice_id

WHERE credit_note.voided_at IS NULL
  AND credit_note.deleted_at IS NULL
  AND invoice.voided_at IS NULL
  AND invoice.deleted_at IS NULL

GROUP BY 1, 2

If you want to review how these amounts break down by individual line items, you can join accordingly.

Breakdown credit note amounts by invoice line item

SELECT invoice.id
     , invoice.invoice_number
     , credit_note.credit_note_number
     , cnli.id
     , SUM(CAST(cnli.amount AS DECIMAL)) AS amount
     , STRING_AGG(DISTINCT(credit_note.reason), ', ') AS reasons

FROM credit_note
JOIN credit_note_line_item AS cnli ON cnli.credit_note_id = credit_note.id
JOIN invoice_metadata AS invoice ON invoice.id = credit_note.invoice_id

WHERE credit_note.voided_at IS NULL
  AND credit_note.deleted_at IS NULL
  AND cnli.voided_at IS NULL
  AND cnli.deleted_at IS NULL
  AND invoice.voided_at IS NULL
  AND invoice.deleted_at IS NULL

GROUP BY 1, 2, 3, 4

Customer balance transactions

In addition to Credit Notes, invoices may also see their issued amount get decreased by any outstanding (and positive) customer balance. Customer balances can be thought of like a payment method and are used before charging the customer since they are owed balances to the customer. When using this balance, Orb tracks a Customer Balance Transaction to associate the amount applied to the relevant invoice. In rare cases, a customer may have a negative balance (they owe money from previous invoices). When this negative balance is applied to a new invoice, it increases the amount due. We can visualize the amounts applied and their relevant invoices like so.

Breakdown customer balance transactions by invoice

SELECT invoice.id
     , invoice.invoice_number
     , cbt.created_at
     , cbt.type
     , cbt.amount
     -- Positive = credit applied (reduced invoice), Negative = negative balance applied (increased invoice)
     , CASE
         WHEN cbt.type = 'decrement' THEN cbt.amount
         WHEN cbt.type = 'increment' THEN -cbt.amount
       END AS effect_on_invoice

FROM customer_balance_transaction AS cbt
JOIN invoice_metadata AS invoice ON invoice.id = cbt.invoice_id

WHERE cbt.action = 'applied_to_invoice'
  AND cbt.deleted_at IS NULL

Discounts and adjustments

The Adjustment and Adjustment Interval resources exist to keep track of discounts, minimums, and maximum applied to invoice line items. These could be for reduced pricing, promotions / sales, and more. If we want to look into the impact of a specific Adjustment or type of Adjustment, we can leverage the Adjustment table in conjunction with the Invoice Line Item Billing table to look at all line items that have been billed. For our use case, let’s look for a specific Adjustment and see its impact on our original list price.

Understanding adjustment contributions to issued invoices

WITH relevant_adjustments AS (
  SELECT id
  FROM adjustment
  WHERE '<filter criteria>'
)

, line_items AS (
  SELECT id
       , jsonb_array_elements(adjustments) AS element

  FROM invoice_line_item_billing
)

, enhanced_line_items AS (
  SELECT *
       , element->>'id' AS adjustment_id
       , element->>'type' AS adjustment_type
       , CAST(element->>'amount' AS DECIMAL) AS adjustment_amount

  FROM line_items
)

, relevant_line_items AS (
  SELECT *

  FROM enhanced_line_items AS line_items

  JOIN invoice_line_item_billing ON invoice_line_item_billing.id = line_items.id
  JOIN relevant_adjustments AS adjustments ON adjustments.id = line_items.adjustment_id
)

SELECT item_id
     , adjustment_type
     , SUM(adjustment_amount)
     , COUNT(*) AS num_billed_line_items

FROM relevant_line_items

GROUP BY 1, 2

Coupons

Coupons also exist within Orb to power discounts. The Coupon table tracks the associated metadata about the coupon (which may be applied to various subscriptions) and the Coupon Redemption table tracks each time the coupon is redeemed. We can run a similar analysis to the above to understand the coupons we care about. Starting from our adjustment of interest (or coupon of interest), we can get to the same set of discount amounts.

Linking coupons and adjustments

WITH relevant_adjustments AS(
  SELECT id
  FROM adjustment
  WHERE '<filter criteria>'
)

, relevant_coupons AS (
  SELECT *

  FROM relevant_adjustments AS adjustment
  JOIN coupon_redemption ON coupon_redemption.adjustment_id = adjustment.id
  JOIN coupon ON coupon.id = coupon_redemption.coupon_id
)

SELECT *
FROM relevant_coupons

Subscriptions

Subscriptions track the lifecycle of your end customers as they move from plan to plan. Orb allows for tracking a timeline of plans on a given subscription using the data exports. This timeline can be used to join against your data to understand which plan was active at a given time using AS OF joins. Note that start_time_inclusive and end_time_exclusive are the time filters that should be used to determine which time frame is relevant.

Creating a subscription plan timeline

WITH subscription_timeline AS (
  SELECT customer_id
       , subscription_id
       , JSON_VALUE(plan, '$.id') AS plan_id

       -- Keep track of when the given plan was active on the subscription.
       -- N.b. start time is _inclusive_ whereas end time is _exclusive_
       , created_at AS start_time_inclusive
       , LEAD(created_at) OVER (PARTITION BY customer_id, subscription_id ORDER BY created_at ASC) AS end_time_exclusive

       , ROW_NUMBER() OVER (PARTITION BY customer_id, subscription_id ORDER BY created_at ASC) AS sequence_number
  FROM subscription_version
)

SELECT *
FROM subscription_timeline
ORDER BY customer_id ASC, subscription_id ASC, start_time_inclusive ASC

Data quality tips

When querying Orb data exports, keep these common patterns in mind:

Always filter reverted and voided entries

Orb uses soft deletes and reverts for audit compliance. Entries are never physically deleted—they’re marked with a timestamp. Include the appropriate filters based on the resource:

Resource	Filter fields
`daily_line_item_revenue`	`reverted_at IS NULL`
`credit_ledger_entry`	`reverted_at IS NULL`
`invoice_metadata`	`voided_at IS NULL AND deleted_at IS NULL`
`invoice_line_item_billing`	`voided_at IS NULL`
`credit_note`	`voided_at IS NULL AND deleted_at IS NULL`
`credit_note_line_item`	`voided_at IS NULL AND deleted_at IS NULL`
`customer_balance_transaction`	`deleted_at IS NULL`

For event tables (_event suffix), use the is_revert flag instead:

SUM(CASE WHEN is_revert THEN -amount ELSE amount END)

Handle nullable numeric fields

Many revenue fields are nullable due to historical data or backfills. Always use COALESCE when aggregating:

SUM(COALESCE(rounded_amount, 0))
SUM(COALESCE(credits_applied, 0))

Distinguish timestamp fields

Revenue data includes multiple timestamp fields with different meanings:

Field	Meaning
`timeframe_start` / `timeframe_end`	The period this revenue covers (daily for `daily_line_item_revenue`)
`line_item_start_date` / `line_item_end_date`	The full invoice line item period
`invoice_date`	When the invoice was issued
`created_at` / `timestamp`	When the record was created
`lock_adjusted_timestamp`	Timestamp adjusted for accounting period locks (event tables only)

Use timeframe_start for revenue recognition timing and invoice_date for billing timing.

Partial invoices require special handling

When threshold billing creates partial invoices, the same line item may appear multiple times. Filter or aggregate carefully:

-- Exclude partial invoices to avoid double-counting
WHERE is_partial_invoice = FALSE

-- Or sum only the incremental amounts
SELECT SUM(rounded_amount - COALESCE(partially_invoiced_amount, 0))

Join tables on the right keys

Common join patterns between resources:

From	To	Join key
`daily_line_item_revenue`	`invoice_metadata`	`invoice_id`
`daily_line_item_revenue`	`invoice_line_item_billing`	`invoice_line_item_id`
`credit_ledger_entry`	`customer`	`customer_id`
`credit_note_line_item`	`invoice_metadata`	`invoice_id` (via `credit_note`)
`subscription_version`	`customer`	`customer_id`

Get started

Architecture

Essentials

Events and metrics

Subscription management

Usage alerting

Invoicing

Collections

Revenue reporting

Data exports

Simulations

Extending Orb

Financial analyses using Orb data

Quick reference: Resources by use case

Billings reports

Net billings

Invoices by status

AR Aging

Revenue recognition reports

Recognized revenue

Credit drawdown

Deferred revenue

Unbilled revenue

Reconciliation reports

Contra revenue reports

Credit notes

Customer balance transactions

Discounts and adjustments

Coupons

Subscriptions

Data quality tips

Always filter reverted and voided entries

Handle nullable numeric fields

Distinguish timestamp fields

Partial invoices require special handling

Join tables on the right keys

​Quick reference: Resources by use case

​Billings reports

​Net billings

​Invoices by status

​AR Aging

​Revenue recognition reports

​Recognized revenue

​Credit drawdown

​Deferred revenue

​Unbilled revenue

​Reconciliation reports

​Contra revenue reports

​Credit notes

​Customer balance transactions

​Discounts and adjustments

​Coupons

​Subscriptions

​Data quality tips

​Always filter reverted and voided entries

​Handle nullable numeric fields

​Distinguish timestamp fields

​Partial invoices require special handling

​Join tables on the right keys

Quick reference: Resources by use case

Billings reports

Net billings

Invoices by status

AR Aging

Revenue recognition reports

Recognized revenue

Credit drawdown

Deferred revenue

Unbilled revenue

Reconciliation reports

Contra revenue reports

Credit notes

Customer balance transactions

Discounts and adjustments

Coupons

Subscriptions

Data quality tips

Always filter reverted and voided entries

Handle nullable numeric fields

Distinguish timestamp fields

Partial invoices require special handling

Join tables on the right keys