rpt_order_attributed_marketing_utms

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.