> ## Documentation Index
> Fetch the complete documentation index at: https://docs.withorb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/financial-operations/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        |

<Tip>
  **Base vs Event resources**: Resources ending in `_event` (like `daily_line_item_revenue_event`) include `lock_adjusted_timestamp` for [accounting period lock](/revenue-reporting/revenue-recognition#actions-in-the-past-and-accounting-period-locks) compliance. Use these when your finance team closes accounting periods in Orb.
</Tip>

<Warning>
  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.
</Warning>

## 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.<br /><br />`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              | <ol><li>Filter out voided invoices using `voided_at` and filter out deleted invoices using `deleted_at`</li><li>Filter to the desired date range using `invoice_date`</li><li>Sum the `total`</li></ol>          |
| `credit_note_line_item`        | Remove credit note amounts       | <ol><li>Filter out voided credit notes using `voided_at` and filter out deleted credit notes using `deleted_at`</li><li>Filter to the desired invoice using `invoice_id`</li><li>Sum the `total`</li></ol>       |
| `customer_balance_transaction` | Remove applied customer balances | <ol><li>Filter to `action = 'applied_to_invoice'`</li><li>Filter out deleted transactions using `deleted_at`</li><li>Handle both decrements (credit applied) and increments (negative balance applied)</li></ol> |

```sql Combined query {9-10,16,25,30-31,38,45} [expandable] theme={null}
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](/invoicing/structure#invoice-states) to view collections, refunds, and pending balances.

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

<CodeGroup>
  ```sql Filter to issued invoices {1,10-11} theme={null}
  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
  ```

  ```sql Group by status {1,7-8} theme={null}
  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
  ```
</CodeGroup>

### 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.

<Note>
  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.
</Note>

| Resource type      | Task                               | Steps                                                                                                  |
| ------------------ | ---------------------------------- | ------------------------------------------------------------------------------------------------------ |
| `invoice_metadata` | Filter to relevant invoices        | <ol><li>Filter to `issued` or `synced` status</li><li>Filter out voided and deleted invoices</li></ol> |
| `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    |

```sql Generate an AR Aging report [expandable] theme={null}
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.<br /><br />`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.<br /><br />`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.<br /><br />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.<br /><br /> `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 | <ol><li>Filter out reverted entries with `reverted_at`</li><li>Filter to the desired date range using `timeframe_start` and `timeframe_end`</li></ol> |
| `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](/revenue-reporting/revenue-recognition#actions-in-the-past-and-accounting-period-locks), 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. |

<CodeGroup>
  ```sql Aggregate revenue using daily_line_item_revenue {9-11,17-18} [expandable] theme={null}
  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
  ```

  ```sql Aggregate revenue using daily_line_item_revenue_event {9-11,18-22} [expandable] theme={null}
  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
  ```
</CodeGroup>

Orb also provides a breakdown of each individual step in the invoicing process to allow for precise analysis of [invoicing calculations](https://docs.withorb.com/invoicing/invoice-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.                                                                                                                |

<CodeGroup>
  ```sql Breaking down each contribution to the final amount [expandable] theme={null}
  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
  ```

  ```sql Breakdown using daily_line_item_revenue_event [expandable] theme={null}
  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
  ```
</CodeGroup>

### 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](/revenue-reporting/revenue-recognition#actions-in-the-past-and-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 |

<Tip>
  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.
</Tip>

<CodeGroup>
  ```sql Calculate revenue contribution from credit ledger entries {4-11,14,18} [expandable] theme={null}
  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
  ```

  ```sql Calculate revenue contribution using credit_ledger_entry_event {4-11,17-20} [expandable] theme={null}
  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
  ```
</CodeGroup>

### 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.

<Note>
  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.
</Note>

## 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.

```sql Fetch Orb invoice identifiers for reconciliation [expandable] theme={null}
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.

```sql Fetching item mappings theme={null}
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.<br /><br />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.<br /><br />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` |

```sql Breakdown issued credit notes {4-5,10-13}[expandable] theme={null}
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.

```sql Breakdown credit note amounts by invoice line item [expandable] theme={null}
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.

```sql Breakdown customer balance transactions by invoice [expandable] theme={null}
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.

```sql Understanding adjustment contributions to issued invoices {4} [expandable] theme={null}
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.

```sql Linking coupons and adjustments {10-12} [expandable] theme={null}
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.

```sql Creating a subscription plan timeline [expandable] theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
-- 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`                    |