Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt

Use this file to discover all available pages before exploring further.

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 with shipping and taxes included (after discounts/refunds)

​
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).

​
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 >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY net_revenue DESC
LIMIT 50;