Key Features
- Multi-dimensional attribution: Marketing channel, landing page, ad, campaign, ad group, and email/SMS
- Three attribution models: First touch, last touch, and linear
- Journey metadata: Session count, days to conversion, journey type classification
- Deduplication flags: Ensure unique contributions per session for linear attribution
Email/SMS touches are excluded from first touch and linear attribution but can receive last touch attribution for configured customers.
Key Columns
version: 2
models:
- name: obt_purchase_journeys_with_mta_models
description: >
Multi-Touch Attribution (MTA) model for purchase journeys with revenue impact calculations.
Grain: One row per touchpoint (sm_touch_id). Date field: event_local_datetime.
Critical filters: sm_event_name = 'purchase' for conversion rows; is_valid_touch.* for dimension-specific valid touches.
columns:
- name: sm_store_id
description: >
SourceMedium store identifier (brand/workspace).
- name: source_system
description: >
Event tracking platform emitting the touchpoint (elevar, snowplow, heap, etc.).
- name: sm_touch_id
description: >
Unique identifier for each touchpoint in the customer journey. Primary key.
- name: event_user_id
description: >
User identifier from the event tracking system for journey attribution.
- name: sm_event_name
description: >
Standardized event name (page_view, purchase, add_to_cart, etc.).
- name: event_local_datetime
description: >
Event timestamp in the reporting timezone for time-based attribution.
- name: valid_event_index_to_next_purchase
description: >
Sequence index from this touchpoint to the next purchase event.
- name: is_valid_touch
description: >
Struct indicating whether touch is valid for each attribution dimension (marketing_channel, landing_page, ad, email_sms, campaign, ad_group).
- name: unique_dimension_value_count
description: >
Count of distinct dimension values in the purchase journey.
- name: ad_platform_metadata
description: >
Struct containing ad platform metadata (ad_id, ad_name, platform details).
- name: campaign_platform_metadata
description: >
Struct containing campaign platform metadata (campaign_id, campaign_name, etc.).
- name: ad_group_platform_metadata
description: >
Struct containing ad group platform metadata (ad_group_id, ad_group_name, etc.).
- name: purchase_session_count
description: >
Number of distinct sessions in the purchase journey from first touch to conversion.
- name: order_metadata
description: >
Struct containing order metadata fields (order_id, customer details, etc.).
- name: dimension_value
description: >
Struct containing the dimension value of the touch for each attribution dimension.
- name: dimension_value.marketing_channel
description: >
Marketing channel dimension value for this touchpoint.
- name: dimension_value.landing_page
description: >
Landing page URL path for this touchpoint.
- name: dimension_value.ad
description: >
Ad identifier for this touchpoint.
- name: dimension_value.email_sms
description: >
Email or SMS campaign identifier for this touchpoint.
- name: dimension_value.campaign
description: >
Campaign identifier for this touchpoint.
- name: dimension_value.ad_group
description: >
Ad group identifier for this touchpoint.
- name: purchase_order_id
description: >
Order ID of the purchase event this touchpoint contributed to.
- name: purchase_order_revenue
description: >
Total revenue from the purchase order for attribution calculations.
- name: purchase_local_datetime
description: >
Purchase event timestamp in the reporting timezone for conversion attribution.
- name: linear_model_multiplier
description: >
Struct containing linear attribution multipliers for each dimension based on valid touch count.
- name: valid_touch_count
description: >
Struct containing count of valid touches for each dimension.
- name: valid_touch_count.marketing_channels
description: >
Count of valid non-brand marketing channel touches in the purchase journey.
- name: valid_touch_count.landing_pages
description: >
Count of valid landing page touches in the purchase journey.
- name: valid_touch_count.ads
description: >
Count of valid non-brand ad touches in the purchase journey.
- name: valid_touch_count.campaigns
description: >
Count of valid non-brand campaign touches in the purchase journey.
- name: valid_touch_count.ad_groups
description: >
Count of valid non-brand ad group touches in the purchase journey.
- name: valid_touch_count.email_sms
description: >
Count of valid email/SMS touches in the purchase journey.
- name: sm_is_purchase_attributable_by
description: >
Struct indicating which dimensions this purchase can be attributed by based on business rules.
- name: sm_is_purchase_attributable_by_last_touch
description: >
Struct indicating which dimensions allow last touch attribution for this purchase.
- name: purchase_journey_type
description: >
Categorizes the purchase journey: single_session, same_day_multi_session, or multi_day_multi_session.
- name: days_to_conversion
description: >
Struct containing days between first touch and purchase conversion for each attribution dimension.
- name: days_to_conversion.marketing_channel
description: >
Days between first valid marketing channel touch and purchase.
- name: days_to_conversion.landing_page
description: >
Days between first valid landing page touch and purchase.
- name: days_to_conversion.ad
description: >
Days between first valid ad touch and purchase.
- name: days_to_conversion.email_sms
description: >
Days between first valid email/sms touch and purchase.
- name: days_to_conversion.campaign
description: >
Days between first valid campaign touch and purchase.
- name: days_to_conversion.ad_group
description: >
Days between first valid ad group touch and purchase.
- name: first_touch_dimension_value
description: >
Struct containing the dimension values for the first valid touch in each attribution dimension.
- name: last_touch_dimension_value
description: >
Struct containing the dimension values for the last valid touch in each attribution dimension.
- name: first_touch_revenue_impact
description: >
Struct containing revenue attributed to this touchpoint if it was the first touch for each dimension.
- name: first_touch_revenue_impact.marketing_channel
description: >
Revenue attributed to this touchpoint as first touch for the marketing channel dimension.
- name: first_touch_revenue_impact.landing_page
description: >
Revenue attributed to this touchpoint as first touch for the landing page dimension.
- name: first_touch_revenue_impact.ad
description: >
Revenue attributed to this touchpoint as first touch for the ad dimension.
- name: first_touch_revenue_impact.campaign
description: >
Revenue attributed to this touchpoint as first touch for the campaign dimension.
- name: first_touch_revenue_impact.ad_group
description: >
Revenue attributed to this touchpoint as first touch for the ad group dimension.
- name: first_touch_revenue_impact.email_sms
description: >
Revenue attributed to this touchpoint as first touch for the email/SMS dimension.
- name: last_touch_revenue_impact
description: >
Struct containing revenue attributed to this touchpoint if it was the last touch for each dimension.
- name: last_touch_revenue_impact.marketing_channel
description: >
Revenue attributed to this touchpoint as last touch for the marketing channel dimension.
- name: last_touch_revenue_impact.landing_page
description: >
Revenue attributed to this touchpoint as last touch for the landing page dimension.
- name: last_touch_revenue_impact.ad
description: >
Revenue attributed to this touchpoint as last touch for the ad dimension.
- name: last_touch_revenue_impact.campaign
description: >
Revenue attributed to this touchpoint as last touch for the campaign dimension.
- name: last_touch_revenue_impact.ad_group
description: >
Revenue attributed to this touchpoint as last touch for the ad group dimension.
- name: last_touch_revenue_impact.email_sms
description: >
Revenue attributed to this touchpoint as last touch for the email/SMS dimension.
- name: linear_revenue_impact
description: >
Struct containing revenue attributed to this touchpoint using linear attribution for each dimension.
- name: linear_revenue_impact.marketing_channel
description: >
Revenue attributed to this touchpoint using linear model for the marketing channel dimension.
- name: linear_revenue_impact.landing_page
description: >
Revenue attributed to this touchpoint using linear model for the landing page dimension.
- name: linear_revenue_impact.ad
description: >
Revenue attributed to this touchpoint using linear model for the ad dimension.
- name: linear_revenue_impact.campaign
description: >
Revenue attributed to this touchpoint using linear model for the campaign dimension.
- name: linear_revenue_impact.ad_group
description: >
Revenue attributed to this touchpoint using linear model for the ad group dimension.
- name: linear_revenue_impact.email_sms
description: >
Revenue attributed to this touchpoint using linear model for the email/SMS dimension.
- name: first_touch_conversion_impact
description: >
Struct containing conversion (1 or 0) attributed to this touchpoint as first touch for each dimension.
- name: last_touch_conversion_impact
description: >
Struct containing conversion (1 or 0) attributed to this touchpoint as last touch for each dimension.
- name: linear_conversion_impact
description: >
Struct containing conversion credit (fractional) attributed to this touchpoint using linear model for each dimension.
- name: attribution_metadata
description: >
Struct containing UTM parameters and event metadata for attribution tracking.
- name: attribution_metadata.event_utm_source
description: >
UTM source parameter from the event for attribution analysis.
- name: attribution_metadata.event_utm_medium
description: >
UTM medium parameter from the event for channel grouping.
- name: attribution_metadata.event_utm_campaign
description: >
UTM campaign parameter from the event for campaign attribution.
- name: attribution_metadata.event_utm_content
description: >
UTM content parameter from the event for A/B testing tracking.
- name: attribution_metadata.event_utm_term
description: >
UTM term parameter from the event for paid search keyword tracking.
- name: attribution_metadata.event_referrer_domain
description: >
Referrer domain from the event for traffic source analysis.
- name: attribution_metadata.sm_event_page_category
description: >
Page category classification for the event (product, collection, checkout, etc.).
Example Queries
Revenue by Marketing Channel (First Touch)
SELECT
dimension_value.marketing_channel,
SUM(first_touch_revenue_impact.marketing_channel) as first_touch_revenue
FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
WHERE
sm_store_id = 'your-sm_store_id'
AND first_touch_revenue_impact.marketing_channel > 0
GROUP BY 1
ORDER BY 2 DESC
Compare Attribution Models for a Single Order
SELECT
purchase_order_id,
dimension_value.marketing_channel,
event_local_datetime,
linear_revenue_impact.marketing_channel as linear_revenue,
first_touch_revenue_impact.marketing_channel as first_touch_revenue,
last_touch_revenue_impact.marketing_channel as last_touch_revenue,
purchase_order_revenue
FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
WHERE
sm_store_id = 'your-sm_store_id'
AND purchase_order_id = 'ORDER_ID_HERE'
AND dimension_value.marketing_channel IS NOT NULL
ORDER BY event_local_datetime
Days to Conversion Analysis
SELECT
purchase_journey_type,
AVG(days_to_conversion.marketing_channel) as avg_days_to_conversion,
COUNT(DISTINCT purchase_order_id) as journeys
FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
WHERE
sm_store_id = 'your-sm_store_id'
AND sm_event_name = 'purchase'
AND days_to_conversion.marketing_channel IS NOT NULL
GROUP BY 1
ORDER BY 2
Key Behaviors
Email/SMS Attribution Rules
- First Touch: Email/SMS touches are excluded (can’t be the first touch)
- Linear: Email/SMS touches are excluded from linear attribution
- Last Touch: Email/SMS can receive last touch attribution for configured customers
Brand Campaign Handling
Brand campaigns appear in data but receive zero attribution to prevent brand search from inflating metrics.Session-Based Deduplication
Linear attribution usesis_first_occurrence_* flags to ensure unique contributions per session, preventing the same channel from being counted multiple times within a session.
Related Resources
Ad Attribution Performance
Daily aggregated attribution metrics at ad, campaign, and channel levels
MTA Models Reference
Complete guide to all MTA data models
Channel-Level Attribution
Understanding unattributed metrics and channel rollups