See which rules matched an order and which rule set its channel, sub-channel, and order type.
Use fct_order_classification_matches to explain an order’s sm_channel, sm_sub_channel, and sm_order_type. It lists the Configuration Sheet rules that matched the order alongside SourceMedium’s built-in channel and subscription rules, and marks the rule that set each value.Delivered on Foundation and Pro warehouses. The order’s final values remain on obt_orders.
Grain: One row per sm_order_key, classification, and match_position.
Date field:order_processed_at_local_datetime, in the store’s reporting timezone. The table is partitioned by month on this field, so filter on it to keep queries small.
Join:obt_orders on sm_order_key (many matches to one order).
Every row reflects your current rules, not the rules in force when the order was placed. After you change a rule, refreshes re-evaluate historical orders under the new rules, so an older order’s explanation can change. sm_channel, sm_sub_channel, and sm_order_type on obt_orders are re-evaluated the same way, so the two tables agree.
classification names the order field being explained: sm_channel, sm_sub_channel, or sm_order_type. Each classification has its own deciding rule. A built-in rule can set sm_channel while a Configuration Sheet rule still sets sm_sub_channel: an order tagged sm-exclude-order gets the excluded channel but keeps the sub-channel your sheet assigns.For each order and classification, exactly one row has is_deciding_rule = TRUE. That row’s rule_outcome is the order’s value on obt_orders. The other rows are rules that also matched but did not set the value.
match_position starts at 1 and orders the matches in evaluation order. It counts only rules that matched, so it is not a rule’s priority, and the first match is not always the deciding rule.
sheet_rule_position is a Configuration Sheet rule’s place in evaluation order, where 1 is checked first. It is not the sheet’s row number: higher row values are checked first, and rules for channels other than Online DTC are boosted ahead of Online DTC rules. See the Channel Mapping tab schema. It is null for other rules.
rule_source is configuration_sheet (your Channel Mapping tab), system (SourceMedium’s built-in rules), or project_config (subscription classification settings SourceMedium maintains for your store; ask SourceMedium to change them). If your store has no Channel Mapping tab of its own, configuration_sheet rows come from SourceMedium’s default channel mapping.
rule_condition describes the rule. For a sheet rule it is the attribute, operator, and value as written in your sheet, such as source in 'facebook, fb'.
rule_outcome is the value the rule assigns, in the same warehouse form as the column it explains (for example online_dtc, not Online DTC).
rule_id identifies a rule definition. A sheet rule’s id is a hash of its exact attribute, operator, value, channel, sub-channel, vendor, and cost per order. Moving a rule keeps its id; any edit to those cells, including a change of capitalization, gives it a new one. Identical rows share an id, so tell them apart with sheet_rule_position. Built-in rules have names such as excluded_order_tag.
Blank sheet sub-channel. If the first matching sheet rule has no sub-channel, its sm_sub_channel row has a null rule_outcome and does not decide. The sub-channel falls back to the order’s sm_default_channel (the default_channel rule), or unknown when it has none. It never moves on to the next matching sheet rule, even though that rule appears in the matches.
No channel mapping. If no channel mapping is available for the order’s store, no_channel_mapping rows decide sm_channel and sm_sub_channel with a null outcome, and both are null on obt_orders.
No order lines. An order with no lines to classify gets a no_order_lines row and is classified one-time.
sm_order_type is decided at the order level from the order’s lines. When a line made the order a subscription, the deciding row names that line’s rule: rule_id reads like subscription_line:subscription_tags, and rule_condition like a line is Subscription: order tags mark a subscription. For each line’s own classification, use is_order_line_subscription on obt_order_lines.
This query selects the most recent valid order from the last 30 days and shows its classification matches in evaluation order. The second date filter limits how much of the matches table is scanned.To investigate a particular order, add AND order_id = '1001' (your order ID) inside recent_order, and widen both INTERVAL 30 DAY filters to reach the order’s date. If you have more than one store, also filter on sm_store_id, since order IDs can repeat across stores.
WITH recent_order AS ( SELECT sm_order_key, order_id, sm_channel, sm_sub_channel, sm_order_type FROM `your_project.sm_transformed_v2.obt_orders` WHERE is_order_sm_valid = TRUE AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) ORDER BY order_processed_at_local_datetime DESC, sm_order_key LIMIT 1)SELECT o.order_id, o.sm_channel, o.sm_sub_channel, o.sm_order_type, m.sm_order_key, m.classification, m.match_position, m.rule_source, m.rule_id, m.sheet_rule_position, m.rule_condition, m.rule_outcome, m.is_deciding_ruleFROM recent_order AS oJOIN `your_project.sm_transformed_v2.fct_order_classification_matches` AS m ON o.sm_order_key = m.sm_order_keyWHERE DATE(m.order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)ORDER BY m.classification, m.match_position;
Remove is_order_sm_valid = TRUE when you deliberately want to investigate an excluded, cancelled, or test order.
An order has multiple explanation rows, including separate deciding rows for its three classifications. Joining all matches to order revenue multiplies the order rows. Keep revenue and order-count reporting in obt_orders. If you need one decision per order in a join, filter to one classification and is_deciding_rule = TRUE first.
Key of (order, classification, match_position) within the current snapshot. Position and key can refer to a different rule after a configuration change; use rule_source and rule_id to identify the rule definition.
sm_order_key
STRING
SourceMedium order identifier, repeated across the order’s classification matches. Join to obt_orders on sm_order_key (many matches to one order).
sm_store_id
STRING
SourceMedium’s unique store identifier. For Shopify stores, derived from the myshopify.com domain; for other platforms (Amazon, TikTok Shop, Walmart.com), uses platform-specific identifiers.
classification
STRING
The order column the rule decides: sm_channel, sm_sub_channel or sm_order_type.
is_deciding_rule
BOOL
True on the one row per order and classification that set the order’s value.
match_position
INT64
Position among the logged matching rules for this order and classification (1 = first match). This is not the rule’s priority among all evaluated rules.
order_processed_at_local_datetime
DATETIME
The order’s processed time in the store’s reporting timezone, as in dim_orders.
rule_condition
STRING
The rule’s condition as written, e.g. “source in ‘facebook, fb’” for a sheet rule or “order tags contain sm-exclude-order” for a built-in rule.
rule_id
STRING
Identifier of the rule definition. A Configuration Sheet rule’s id is sheet_ followed by a hash of its exact attribute, operator, value, channel, sub_channel, vendor and cost_per_order: moving the rule keeps the id, any edit to those cells gives a new one, and identical rows share one. Built-in rules have a name, e.g. excluded_order_tag.
rule_outcome
STRING
The value the rule assigns to the classification, published in the same form as the column it explains (e.g. online_dtc). On the deciding row it equals the order’s value. A sheet rule with a blank sub-channel reads NULL and does not decide: the sub-channel falls back to the order’s default channel, never to the next sheet rule.
rule_source
STRING
Where the rule comes from: configuration_sheet (the store’s Channel Mapping tab), system (SourceMedium’s built-in logic) or project_config (the store’s subscription classification settings).
sheet_rule_position
INT64
For a Configuration Sheet rule, its position in the store’s evaluation order (1 = checked first). Not the sheet row number: higher row values are checked first, and rules for channels other than Online DTC are boosted ahead of Online DTC rules. NULL for other rules.
source_system
STRING
Originating platform for the record (e.g., Shopify, Amazon, TikTok Shop, Chargebee). Used for platform‑specific behavior and coverage.
