Skip to main content
SourceMedium exposes multiple revenue fields because “revenue” can mean different things depending on the question you’re answering. This page focuses on the order-level revenue fields you’ll see most often in:
  • your_project.sm_transformed_v2.obt_orders
  • your_project.sm_transformed_v2.rpt_executive_summary_daily
  • Many SQL Query Library recipes

​
Key fields (order-level)

These are the canonical order-level fields in obt_orders:
  • order_gross_revenue: line-item revenue before discounts, excluding gift card purchases
  • order_discounts: discounts applied (see sign convention below)
  • order_refunds: refunds applied (see sign convention below)
  • order_net_revenue: gross revenue after discounts and refunds
  • order_net_revenue_before_refunds: gross revenue after discounts, before refunds
  • order_total_revenue: net revenue plus net shipping, net taxes, and net shipping taxes

​
Date basis

For order-level analysis, use order_processed_at_local_datetime as the default order date in the brand’s reporting timezone. Daily transaction/report tables can include later adjustments on the adjustment date. For example, refund-only transaction rows may appear on the refund date while the original placed-order row uses the order processed date. When reconciling two reports, match both the revenue field and the date basis.

​
Important sign convention (discounts + refunds)

In most SourceMedium tables, discounts and refunds are stored as negative numbers (or 0). That means net revenue is additive: 
-- Conceptual relationship
order_net_revenue = order_gross_revenue + order_discounts + order_refunds
If you see positive refunds in your data, treat it as a data-quality edge case (some platforms can emit adjustments that violate the expected sign conventions).

​
Why revenue may not match another report

Most revenue discrepancies come from comparing two reports that use different definitions or grains. For the broader cross-tool checklist, see Why would external reports not match the SourceMedium dashboard?.
DifferenceWhat to compare
Gross vs net vs total revenueConfirm whether discounts, refunds, shipping, taxes, and shipping taxes are included.
Order date vs adjustment dateConfirm whether refunds or other adjustments are reported on the original order date or the later adjustment date.
Order-level vs line-level grainConfirm whether the report sums one row per order, one row per line item, or one row per transaction.
Valid order filtersConfirm whether test orders, exchanges, returns, samples, or excluded orders are included.
Channel or customer filtersConfirm whether both reports use the same channel, customer type, subscription, product, and store filters.
If two reports use the same date range but different revenue fields, they can both be correct. Start by matching the field definition before treating the difference as a data issue.

​
Quick sanity-check query

SELECT
  sm_store_id,
  SUM(order_gross_revenue) AS gross_revenue,
  SUM(order_discounts) AS discounts,
  SUM(order_refunds) AS refunds,
  SUM(order_net_revenue) AS net_revenue,
  SUM(order_net_revenue_before_refunds) AS net_revenue_before_refunds,
  SUM(order_total_revenue) AS total_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_processed_at_local_datetime >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY net_revenue DESC
LIMIT 50;