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

# rpt_order_attributed_marketing_utms

> Multi-touch UTM attribution table with conversion credit per UTM signal associated with each order.

<Warning>
  `rpt_order_attributed_marketing_utms` is not yet deployed to customer Managed Data Warehouses. This page documents the intended schema for when the table is deployed.
</Warning>

<Info>
  To get the UTM signal SourceMedium selected as the primary attribution source for an order, filter to `order_attributed_utms_is_sm_source_medium_selection = TRUE`. This corresponds to `sm_utm_final_source_priority = 1` in `fct_order_attribution_signals` and drives the `sm_channel` / `sm_utm_*` columns in `obt_orders`.
</Info>

<Note>
  Grain is one row per UTM signal per order — orders with multiple UTM touchpoints have multiple rows. Join to `obt_orders` via `order_id + sm_store_id` to bring in revenue and order context. For a single-row-per-order view, filter to `sm_order_attributed_utms_index = 1` (first touch) or `sm_order_attributed_utms_index_reversed = 1` (last touch).
</Note>

```yaml theme={null}
version: 2

models:
  - name: rpt_order_attributed_marketing_utms
    description: >
      Multi-touch UTM attribution report. Grain: one row per UTM signal associated with an order
      (sm_store_id + order_id + sm_order_attributed_utms_index).
      Each order can have multiple rows when multiple UTM signals were captured.
      Signals are ranked chronologically: sm_order_attributed_utms_index = 1 is the earliest (first touch);
      sm_order_attributed_utms_index_reversed = 1 is the most recent (last touch).
      Key joins: obt_orders via order_id + sm_store_id (many:1); fct_order_attribution_signals via sm_store_id + order_id (many:many).
    columns:
      - name: sm_order_attributed_utms_id
        description: >
          Surrogate key for the UTM signal row (order_id + sm_store_id + sm_order_attributed_utms_index). Unique per row.

      - name: sm_store_id
        description: >
          SourceMedium store identifier. Unique per tenant.

      - name: order_id
        description: >
          Platform order identifier. Not globally unique — pair with sm_store_id when joining to other tables.

      - name: order_attributed_sm_cleaned_utm_source
        description: >
          Cleaned and normalized utm_source for the UTM signal. SourceMedium applies standardization
          to remove casing and formatting inconsistencies.

      - name: order_attributed_utm_medium
        description: >
          utm_medium for the UTM signal.

      - name: order_attributed_utm_campaign
        description: >
          utm_campaign for the UTM signal.

      - name: order_attributed_utm_content
        description: >
          utm_content for the UTM signal.

      - name: order_attributed_utm_term
        description: >
          utm_term for the UTM signal.

      - name: order_attributed_utm_id
        description: >
          utm_id for the UTM signal when present.

      - name: order_attributed_gclid
        description: >
          Google Click ID (gclid) captured for the UTM signal when present.

      - name: order_attributed_fbclid
        description: >
          Facebook Click ID (fbclid) captured for the UTM signal when present.

      - name: order_attributed_referrer
        description: >
          Referrer URL captured for the UTM signal when present.

      - name: order_attributed_utms_first_seen_date
        description: >
          Earliest date this UTM signal was observed for the order. Falls back to order_processed_date_local
          when no event date is available.

      - name: order_attributed_utms_source_system_record_locations_csv
        description: >
          Comma-separated list of source object types (e.g., funnel_event, checkout_attribute) where
          this UTM signal was found. Useful for understanding which data sources contributed to the signal.

      - name: order_attributed_utms_is_sm_source_medium_selection
        description: >
          True when this UTM signal is SourceMedium's primary attribution selection for the order
          (final_source_priority = 1). This is the signal that drives sm_channel and sm_utm_* columns
          in obt_orders. Filter to TRUE to get one authoritative row per order.

      - name: sm_order_attributed_utms_index
        description: >
          Chronological rank of the UTM signal for the order (1 = earliest / first touch).
          Ordered by order_attributed_utms_first_seen_date ASC, then source priority DESC.

      - name: sm_order_attributed_utms_index_reversed
        description: >
          Reverse chronological rank of the UTM signal for the order (1 = most recent / last touch).
          Ordered by order_attributed_utms_first_seen_date DESC, then source priority ASC.

      - name: sm_order_attributed_utms_last_touch_conversion_credit
        description: >
          Conversion credit integer (0 or 1) assigned to the first-touch position (sm_order_attributed_utms_index = 1).
          Note: use sm_order_attributed_utms_index_reversed = 1 to identify the true last-touch signal.

      - name: sm_order_attributed_utms_first_touch_conversion_credit
        description: >
          Conversion credit integer (0 or 1) assigned when sm_order_attributed_utms_index = 1 (earliest signal).
          Sum across all rows per order always equals 1.

      - name: sm_order_attributed_utms_first_touch_last_touch_conversion_credit_shared
        description: >
          Conversion credit integer (0 or 1) for combined first-touch and last-touch models.
          Currently assigned when sm_order_attributed_utms_index = 1.

      - name: sm_order_attributed_utms_event_all_utm_events_shared_conversion_credit
        description: >
          Linear attribution credit for this UTM signal: 1 divided by the total number of UTM signals
          for the order. Sum across all rows per order always equals 1.

```