Row-level attribution signals table for tracing the inputs SourceMedium evaluated for each order’s attribution.
version: 2models: - name: fct_order_attribution_signals description: > Row-level order attribution signals table for custom modeling, debugging, and traceability. Grain: one row per (sm_store_id, sm_order_key, evidence_type, evidence_source, evidence_row_id). Date field: order_processed_at_local_datetime. Critical filters: evidence_type and evidence_source for isolating traffic, zero-party, or discount signals; sm_utm_final_source_priority = 1 for the primary traffic winner. Key joins: dim_orders via sm_order_key (many:1). columns: - name: sm_order_attribution_signal_key description: > Stable surrogate key for the signal row. Unique per evidence record. - name: sm_store_id description: > 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. - name: source_system description: > Originating platform for the record (e.g., Shopify, Amazon, TikTok Shop, Chargebee). Used for platform‑specific behavior and coverage. - name: sm_order_key description: > Stable SourceMedium order key. Unique per order. Key joins: `obt_order_lines` (1:many via `sm_order_key`); `dim_customers` (many:1 via `sm_customer_key`). Platform caveat: TikTok Shop coverage may be limited. Multiple rows per order are expected in this model because each order can keep several attribution signals. - name: order_id description: > Platform order identifier. Not globally unique across stores; pair with `sm_store_id` and `source_system` when needed for scoping. - name: order_processed_at description: > UTC timestamp when the order was processed. - name: order_processed_at_local_datetime description: > Order processed timestamp converted to reporting timezone (from order_processed_at UTC). Primary date field for order analytics and time-based filtering. - name: evidence_type description: > High-level class of evidence on the row. Current values are `traffic_source_candidate`, `zero_party_candidate`, and `order_discount_code`. `traffic_source_candidate` includes both primary traffic candidates and MTA supporting-context rows. - name: evidence_source description: > Specific source that produced the evidence row, such as `shopify_custom_attribute_override`, `website_event_tracking_purchase`, `pps_response`, or `order_discount_code`. - name: evidence_row_id description: > Source-specific identifier for the evidence record within an order. Examples include `shopify_landing_site:1`, `ga4:2`, `segment_website_event_tracking:evt_123`, an MTA touch id, or a normalized discount code such as `welcome10`. - name: sm_utm_source description: > Canonicalized source value for traffic evidence after SourceMedium inference and normalization. Null for non-traffic rows. - name: sm_utm_medium description: > Canonicalized medium value for traffic evidence after SourceMedium inference and normalization. Null for non-traffic rows. - name: sm_utm_campaign description: > Canonicalized campaign value for the traffic evidence row. Null for non-traffic rows. - name: sm_utm_content description: > Canonicalized content value for the traffic evidence row. Null for non-traffic rows. - name: sm_utm_term description: > Canonicalized term value for the traffic evidence row. Null for non-traffic rows. - name: sm_utm_id description: > Canonicalized `utm_id` value for the traffic evidence row. Null for non-traffic rows. - name: sm_utm_source_medium description: > Combined canonical source / medium pair used by SourceMedium channel mapping and attribution analysis. - name: raw_utm_source description: > Raw source captured on the evidence row before inference and normalization. - name: raw_utm_medium description: > Raw medium captured on the evidence row before inference and normalization. - name: raw_utm_campaign description: > Raw campaign captured on the evidence row before inference and normalization. - name: raw_utm_content description: > Raw content captured on the evidence row before inference and normalization. - name: raw_utm_term description: > Raw term captured on the evidence row before inference and normalization. - name: raw_utm_id description: > Raw `utm_id` captured on the evidence row before inference and normalization. - name: raw_referrer description: > Raw referrer value captured with the evidence row before canonicalization. - name: raw_landing_page_url description: > Raw landing page URL captured with the evidence row when available. - name: raw_referring_site description: > Raw referring-site URL captured from the order record when available. - name: sm_attribution_inference_method description: > How SourceMedium produced the canonicalized attribution fields on the row. Current values include `raw_utm`, `referrer_domain_inferred`, `gclid_inferred`, `fbclid_inferred`, `fbclid_inferred_both_present`, `mta_touch`, and `not_applicable`. - name: sm_utm_final_source_priority description: > Per-order rank among `traffic_source_candidate` rows. A value of `1` means this row is the winning primary traffic source for the order. Higher values are lower-ranked fallbacks. Null means the row is not part of the primary traffic ranking. Values can be sparse, such as `1`, `3`, `4`, `5`, `7`, and `8`. - name: attribution_signal_raw description: > Raw non-UTM attribution signal captured on the row, such as a post-purchase survey answer, zero-party tag, or discount code. Null for standard traffic rows. - name: attribution_signal_parsed description: > Parsed or normalized version of `attribution_signal_raw` used for grouped analysis. Null for standard traffic rows. - name: attribution_signal_type description: > Type of non-traffic signal on the row. Current values are `zero_party` and `discount_code`. - name: sm_marketing_channel description: > Standardized SourceMedium marketing channel mapping for traffic evidence after canonicalization. Null for non-traffic rows. - name: _shard_id description: > Internal shard identifier used for sharded model execution and debugging. Not intended for customer-facing analysis.
fct_order_attribution_signals is the customer-facing row-level table for custom attribution modeling, order-level traceability, and debugging. It keeps the winning traffic source and the supporting signal rows side by side so you can rebuild SourceMedium’s rules, inspect explicit overrides, and trace source hierarchy without dropping into raw staging models.
Use this table when you need to answer “why did this order end up attributed this way?” rather than “how much revenue did a channel get?” For aggregated attribution reporting, stay in the report and MTA tables.
mta_first_touch and mta_last_touch use evidence_type = traffic_source_candidate, but they are supporting-context rows. They always have sm_utm_final_source_priority = NULL, and an order can have at most one row of each type when that order appears in MTA journey data.
SourceMedium keeps multiple traffic candidates on the order, then assigns a per-order rank in sm_utm_final_source_priority.
1 means “this was the winning primary traffic source for the order.”
Higher numbers are lower-ranked fallbacks kept for debugging.
NULL means the row is contextual rather than part of the primary traffic ranking.
For the current customer-facing evidence sources, the preferred traffic-source order is:
shopify_custom_attribute_override
shopify_landing_site
shopify_note
website_event_tracking_purchase
google_analytics_transaction
shopify_order_referring_site_utms
The numeric priority values in the table are ordering signals, not a reusable source code system. Some numbers are skipped, so you might see values such as 1, 3, 4, 5, 7, and 8 instead of a compact 1..6 sequence. The relative ordering matters, not the specific numeric values.
mta_first_touch and mta_last_touch rows are kept in the signals table as traffic context, but they do not compete for the primary traffic winner. They always have sm_utm_final_source_priority = NULL, and an order will have at most one of each when the order appears in MTA journey data.
evidence_type: Broad class of evidence, such as traffic_source_candidate, zero_party_candidate, or order_discount_code.
evidence_source: The specific capture mechanism or upstream source.
evidence_row_id: Source-specific identifier that distinguishes one evidence record from another on the same order. Examples include shopify_landing_site:1, ga4:2, segment_website_event_tracking:evt_123, an MTA touch id, or a normalized discount code such as welcome10.
sm_utm_source, sm_utm_medium, sm_utm_campaign, sm_utm_content, sm_utm_term, sm_utm_id: Final normalized traffic fields for the row.
sm_utm_source_medium: Canonical source / medium pair that usually makes debugging faster.
sm_marketing_channel: Final channel grouping for traffic rows after normalization. See Channel Mapping if the final grouped value is what you need to debug.
This is the fastest way to understand which capture mechanism is actually driving the primary traffic winner for a store.
SELECT evidence_source, COUNT(DISTINCT sm_order_key) AS winning_ordersFROM `your_project.sm_transformed_v2.fct_order_attribution_signals`WHERE sm_store_id = 'your-sm_store_id' AND evidence_type = 'traffic_source_candidate' AND sm_utm_final_source_priority = 1 AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)GROUP BY 1ORDER BY 2 DESC;
