> ## 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.

# fct_order_classification_matches

> 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`](/docs/data-activation/data-tables/sm_transformed_v2/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).

<Warning>
  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.
</Warning>

## 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](/docs/help-center/raw-data-source-overviews/configuration-sheet/schema#channel-mapping-tab). 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`](/docs/data-activation/data-tables/sm_transformed_v2/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.

```sql theme={null}
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_rule
FROM recent_order AS o
JOIN `your_project.sm_transformed_v2.fct_order_classification_matches` AS m
  ON o.sm_order_key = m.sm_order_key
WHERE 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.

## 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

| Column | Type | Description |
| - | - | - |
| `sm_order_classification_match_key` | STRING | 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. |

## Related resources

* [Channel mapping](/docs/help-center/core-concepts/data-definitions/channel-mapping)
* [Create custom channel mappings](/docs/data-inputs/configuration-sheet/how-can-i-create-order-channels-and-subchannels)
* [Order type](/docs/data-transformations/order-segmentation/order-type)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.