Skip to main content

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 is not yet deployed to customer Managed Data Warehouses. This page documents the intended schema for when the table is deployed.
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.
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).
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.