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.
How to read the matches
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.
Fallbacks
- 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.
Order type
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.
Explain a recent order
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.
Remove is_order_sm_valid = TRUE when you deliberately want to investigate an excluded, cancelled, or test order.
Avoid double-counting orders
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.
Columns
