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.

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 typeDescription
invoice_line_item_billingContains the individual line item values for invoices that have been issued / billed.
invoice_metadataContains 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.
invoiceContains 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 typeTaskSteps
invoice_line_item_billingSum invoice amounts
  1. Filter out voided invoices using voided_at and filter out deleted invoices using deleted_at
  2. Filter to the desired date range using invoice_date
  3. Sum the total
credit_note_line_itemRemove credit note amounts
  1. Filter out voided credit notes using voided_at and filter out deleted credit notes using deleted_at
  2. Filter to the desired invoice using invoice_id
  3. Sum the total
customer_balance_transactionRemove applied customer balances
  1. Filter out deleted customer balance transactions using deleted_at
  2. Filter to the desired invoice using invoice_id
  3. Sum the amount
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 (
  SELECT DATE_TRUNC('month', invoice.invoice_date) AS invoice_month
       , SUM(cbt.amount) AS total

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

  WHERE cbt.type = 'decrement'
    AND cbt.amount < 0
    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 typeTaskSteps
invoice_metadataFilter to issued invoices
  1. Filter our voided and deleted invoices used voided_at and deleted_at respectively.
  2. Filter to desired date range using due_date or invoice_date
invoice_metadataGroup by statusGroup 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

​
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 typeTaskSteps
invoice_metadataFilter to relevant invoices
  1. Filter out voided and deleted invoices
  2. Filter to desired date range with due_date
invoice_metadataFilter to invoices that have been issued but not paid
  1. Filter to issued invoices with status
  2. Filter out invoices that are not yet due
invoice_metadataGroup invoices by days outstandingUse due_date to bucket invoices into groups
Generate an AR Aging report
WITH unpaid_invoices AS (
  SELECT id
       , due_date
       , customer_id
       , total

	FROM invoice_metadata
	WHERE status = 'issued'
    AND deleted_at IS NULL
    AND voided_at IS NULL
)

, invoices_enhanced AS (
	SELECT upaid_invoices.total
       , CAST(invoices.amount_due AS DECIMAL) AS amount_due
       , CASE
           WHEN unpaid_invoices.due_date > NOW() - INTERVAL '30 days' THEN '0-30'
           WHEN unpaid_invoices.due_date > NOW() - INTERVAL '60 days' THEN '30-60'
           WHEN unpaid_invoices.due_date > NOW() - INTERVAL '90 days' THEN '60-90'
           ELSE '90+'
         END AS days_due

	FROM unpaid_invoices
  JOIN invoices ON invoices.id = unpaid_invoices.id

	-- Don't include invoices due in the future
	WHERE unpaid_invoices.due_date < NOW()
)

SELECT invoices_enhanced.days_due
     , customer.name
     , SUM(invoices_enhanced.total) AS total
     , SUM(invoices_enhanced.amount_due) AS amount_due

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

GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC

​
Revenue recognition reports

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

Resource typeDescription
daily_line_item_revenueContains data for all invoice line items per day, including quantity and rate details.
credit_ledger_entryContains data for all credit ledger entries.
credit_note_line_itemContains 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.

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 typeTaskSteps
daily_line_item_revenueFilter to relevant line items
  1. Filter out reverted entries with reverted_at
  2. Filter to the desired date range using timeframe_start and timeframe_end
daily_line_item_revenueSum the revenue amountsSum rounded_amount to get a final revenue number
Aggregate revenue for invoice line items
WITH daily_line_items AS (
  SELECT timeframe_start
       , invoicing_currency
       , rounded_amount

  FROM daily_line_item_revenue

  -- We are interested in our revenue for calenday 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

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:

ValueField NameDescription
List price / subtotalsubtotalThe original price before any adjustment, prepaid credits, or otherwise.
Adjusted subtotaladjusted_subtotalThe list price minus any adjustments. If no adjustments have been applied, this value is the same as the list price.
Credits appliedcredits_appliedThe amount of prepaid credits applied to this line item. If no prepaid credits have been applied, this value is 0.
Conversion rateconversion_rateThe 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 amountpartially_invoiced_amountThe amount that has been invoiced already through a partial invoice. If no partial invoices have been issued, this value is 0.
AmountamountThe amount of the line item before any final rounding.
Rounded amountrounded_amountThe amount of the line item after rounding and before taxes, if applicable, are applied.
Breaking down each contribution to the final amount
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

​
Credit drawdown

To understand credit utilization, sum the (converted) amount from credits applied.

Resource typeTaskSteps
credit_ledger_entryFilter to relevant ledger entriesFilter out reverted entries with reverted_at
credit_ledger_entryConvert entries to revenue amountsUse the entry_type to determine the revenue amount

Orb today doesn’t support a first class field with this amount so we’ve provided the logic to do this below.
credit_ledger_entrySum the revenue amounts based on their cost basisUse the value calculated above and multiple by the cost_basis
Calculate revenue contribution from credit ledger entries
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

​
Deferred revenue

To understand deferred revenue, sum billings, less revenue recognized for a particular period. See above sections for report guidance.

When looking at billed line item amounts, you can reference the relevant tax field to understand the tax implications to billings and your deferred revenue.

Understanding tax amounts
SELECT DATE_TRUNC('day', timeframe_start AT TIME ZONE 'UTC') AS day
     , item_id
     , SUM(tax_amount) AS tax_amount

FROM invoice_line_item_billing

WHERE voided_at IS NULL

GROUP BY 1, 2
ORDER BY 1 ASC

​
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 typeDescription
coupon and coupon_redemptionContains the coupons you’ve created with Orb and the associated Adjustment created when the coupon was redeemed.
adjustment and adjustment_intervalAdjustments 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_itemCredit 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_transactionCustomer 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 typeTaskSteps
credit_note and invoiceFilter to relevant credit notesFilter out voided and deleted resources
credit_noteSum credit note amountsSum 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.

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

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

-- When customer balances are applied to invoices, they
-- are considered to be a negative decrement
WHERE cbt.type = 'decrement'
  AND cbt.amount < 0
  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

Was this page helpful?