Skip to main content
is_order_sm_valid is the primary filter for order-based analysis. It tells you whether an order should be included in reporting.
If you’re querying any order-based table (obt_orders, obt_order_lines, or most rpt_* revenue tables), start with: WHERE is_order_sm_valid = TRUE.

​
What it means

At a high level:
  • TRUE: include the order in revenue / order-count reporting
  • FALSE: exclude the order (e.g., cancelled/voided/draft/uncollectible and other non-reportable states)
SourceMedium computes this at the order-line level and rolls it up to orders, so an order is considered valid if it has at least one valid order line.

​
What makes an order invalid

For current standard order tables, an order is invalid when any of these are true:
  • Payment status is missing, or is voided, cancelled, uncollectible, draft, or refunded.
  • Channel is outside Online DTC, Amazon, Retail, Wholesale, or TikTok Shop.
  • The merchant has exclude-$0-sales enabled and the order’s net revenue is 0.
Two common edge cases:
  • Unpaid or payment-due orders are not excluded just because they are unpaid. They are valid unless they hit one of the invalid statuses above.
  • Gift-card-only orders are flagged separately as is_order_only_gift_cards on order-line tables. They are not excluded by is_order_sm_valid itself, although some report tables may exclude them from placed-order counts.
A legacy tenant-specific exception can treat fully refunded orders as valid. If you are reconciling a single tenant and refunded orders behave unexpectedly, confirm the tenant configuration before assuming the global rule applies.

​
Why it matters

Most “why don’t my numbers match” issues come from one of these:
  • Missing the is_order_sm_valid = TRUE filter
  • Mixing valid + invalid orders across joined tables
  • Comparing valid-order metrics to external exports that include cancelled/test orders
SELECT
  COUNT(*) AS valid_order_count,
  SUM(order_net_revenue) AS net_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);