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
- Session-level touches: Only the first event of a session counts as a touch, so repeated clicks inside one session count once
Email/SMS touches are excluded from first touch and linear attribution but can receive last touch attribution for configured customers.
Columns
| Column | Type | Description |
|---|---|---|
sm_store_id | STRING | SourceMedium store identifier (brand/workspace). |
ad_group_platform_metadata | STRUCT | Struct containing ad group platform metadata (ad_group_id, ad_group_name, etc.). |
ad_group_platform_metadata.ad_campaign_tactic | STRING | The tactic of the campaign derived from campaign naming conventions. Possible values are “Prospecting”, “Retargeting”, and “Brand”. Default is “Prospecting”. |
ad_group_platform_metadata.ad_group_platform | STRING | |
ad_platform_metadata | STRUCT | Struct containing ad platform metadata (ad_id, ad_name, platform details). |
ad_platform_metadata.ad_campaign_tactic | STRING | The tactic of the campaign derived from campaign naming conventions. Possible values are “Prospecting”, “Retargeting”, and “Brand”. Default is “Prospecting”. |
ad_platform_metadata.ad_platform | STRING | |
attribution_metadata | STRUCT | Struct containing UTM parameters and event metadata for attribution tracking. |
attribution_metadata.event_fbclid | STRING | Meta click identifier retained as channel evidence. |
attribution_metadata.event_gclid | STRING | Google click identifier retained as channel evidence. |
attribution_metadata.event_referrer_domain | STRING | Canonical external marketing referrer after self/gateway suppression. |
attribution_metadata.event_referrer_domain_raw | STRING | Original referrer domain before non-marketing suppression. |
attribution_metadata.event_ttclid | STRING | TikTok click identifier retained as channel evidence. |
attribution_metadata.event_utm_campaign | STRING | UTM campaign parameter from the event for campaign attribution. |
attribution_metadata.event_utm_content | STRING | UTM content parameter from the event for A/B testing tracking. |
attribution_metadata.event_utm_medium | STRING | UTM medium parameter from the event for channel grouping. |
attribution_metadata.event_utm_source | STRING | UTM source parameter from the event for attribution analysis. |
attribution_metadata.event_utm_term | STRING | UTM term parameter from the event for paid search keyword tracking. |
attribution_metadata.sm_event_page_category | STRING | Page category classification for the event (product, collection, checkout, etc.). |
campaign_platform_metadata | STRUCT | Struct containing campaign platform metadata (campaign_id, campaign_name, etc.). |
campaign_platform_metadata.ad_campaign_tactic | STRING | The tactic of the campaign derived from campaign naming conventions. Possible values are “Prospecting”, “Retargeting”, and “Brand”. Default is “Prospecting”. |
campaign_platform_metadata.ad_campaign_type | STRING | The type of campaign that is associated with the ad, such as reach, discovery, search, display, or video (this campaign type will vary by advertising platform). |
campaign_platform_metadata.campaign_platform | STRING | |
days_to_conversion | STRUCT | Struct containing days between first touch and purchase conversion for each attribution dimension |
days_to_conversion.ad | INT64 | Days between first valid ad touch and purchase |
days_to_conversion.ad_group | INT64 | Days between first valid ad group touch and purchase |
days_to_conversion.campaign | INT64 | Days between first valid campaign touch and purchase |
days_to_conversion.email_sms | INT64 | Days between first valid email/sms touch and purchase |
days_to_conversion.landing_page | INT64 | Days between first valid landing page touch and purchase |
days_to_conversion.marketing_channel | INT64 | Days between first valid marketing channel touch and purchase |
dimension_value | STRUCT | The dimension value of the touch for the marketing channel. For example, a touchpoint with marketing channel of “landing page” will have the url path as the dimension value. |
dimension_value.ad | STRING | The dimension value of the touch for the ad_id marketing channel. |
dimension_value.ad_group | STRING | Dimension value for the ad group touchpoint in the customer journey. |
dimension_value.campaign | STRING | The dimension value of the touch for the campaign marketing channel. |
dimension_value.email_sms | STRING | The dimension value of the touch for the email_sms marketing channel. |
dimension_value.landing_page | STRING | Landing page dimension value |
dimension_value.marketing_channel | STRING | Marketing channel dimension value |
event_local_datetime | DATETIME | Event timestamp in the reporting timezone for time-based attribution. |
event_user_id | STRING | User identifier from the event tracking system for journey attribution. |
first_touch_conversion_impact | STRUCT | Struct containing conversion (1 or 0) attributed to this touchpoint as first touch for each dimension. |
first_touch_conversion_impact.ad | INT64 | Conversion attributed to this touchpoint as first touch for the ad dimension (1 or 0). |
first_touch_conversion_impact.ad_group | INT64 | Conversion attributed to this touchpoint as first touch for the ad group dimension (1 or 0). |
first_touch_conversion_impact.campaign | INT64 | Conversion attributed to this touchpoint as first touch for the campaign dimension (1 or 0). |
first_touch_conversion_impact.email_sms | INT64 | Conversion attributed to this touchpoint as first touch for the email/SMS dimension (1 or 0). |
first_touch_conversion_impact.landing_page | INT64 | Conversion attributed to this touchpoint as first touch for the landing page dimension (1 or 0). |
first_touch_conversion_impact.marketing_channel | INT64 | Conversion attributed to this touchpoint as first touch for the marketing channel dimension (1 or 0). |
first_touch_dimension_value | STRUCT | Struct containing the dimension values for the first valid touch in each attribution dimension. |
first_touch_dimension_value.ad | STRING | Ad identifier value for the first valid non-brand ad touch in the journey. |
first_touch_dimension_value.ad_group | STRING | Ad group identifier for the first valid non-brand ad group touch in the journey. |
first_touch_dimension_value.campaign | STRING | Campaign identifier for the first valid non-brand campaign touch in the journey. |
first_touch_dimension_value.email_sms | STRING | Email or SMS campaign identifier for the first valid touch in the journey. |
first_touch_dimension_value.landing_page | STRING | Landing page URL path for the first valid touch in the journey. |
first_touch_dimension_value.marketing_channel | STRING | Marketing channel value for the first valid non-brand touch in the journey. |
first_touch_revenue_impact | STRUCT | Struct containing revenue attributed to this touchpoint if it was the first touch for each dimension. |
first_touch_revenue_impact.ad | NUMERIC | Revenue attributed to this touchpoint as first touch for the ad dimension. |
first_touch_revenue_impact.ad_group | NUMERIC | Revenue attributed to this touchpoint as first touch for the ad group dimension. |
first_touch_revenue_impact.campaign | NUMERIC | Revenue attributed to this touchpoint as first touch for the campaign dimension. |
first_touch_revenue_impact.email_sms | NUMERIC | Revenue attributed to this touchpoint as first touch for the email/SMS dimension. |
first_touch_revenue_impact.landing_page | NUMERIC | Revenue attributed to this touchpoint as first touch for the landing page dimension. |
first_touch_revenue_impact.marketing_channel | NUMERIC | Revenue attributed to this touchpoint as first touch for the marketing channel dimension. |
is_valid_touch | STRUCT | Struct indicating whether touch is valid for each attribution dimension (marketing_channel, landing_page, ad, email_sms, campaign, ad_group). |
last_touch_conversion_impact | STRUCT | Struct containing conversion (1 or 0) attributed to this touchpoint as last touch for each dimension. |
last_touch_conversion_impact.ad | INT64 | Conversion attributed to this touchpoint as last touch for the ad dimension (1 or 0). |
last_touch_conversion_impact.ad_group | INT64 | Conversion attributed to this touchpoint as last touch for the ad group dimension (1 or 0). |
last_touch_conversion_impact.campaign | INT64 | Conversion attributed to this touchpoint as last touch for the campaign dimension (1 or 0). |
last_touch_conversion_impact.email_sms | INT64 | Conversion attributed to this touchpoint as last touch for the email/SMS dimension (1 or 0). |
last_touch_conversion_impact.landing_page | INT64 | Conversion attributed to this touchpoint as last touch for the landing page dimension (1 or 0). |
last_touch_conversion_impact.marketing_channel | INT64 | Conversion attributed to this touchpoint as last touch for the marketing channel dimension (1 or 0). |
last_touch_dimension_value | STRUCT | Struct containing the dimension values for the last valid touch in each attribution dimension. |
last_touch_dimension_value.ad | STRING | Ad identifier value for the last valid touch eligible for last touch attribution. |
last_touch_dimension_value.ad_group | STRING | Ad group identifier for the last valid touch eligible for last touch attribution. |
last_touch_dimension_value.campaign | STRING | Campaign identifier for the last valid touch eligible for last touch attribution. |
last_touch_dimension_value.email_sms | STRING | Email or SMS campaign identifier for the last valid touch eligible for last touch attribution. |
last_touch_dimension_value.landing_page | STRING | Landing page URL path for the last valid touch eligible for last touch attribution. |
last_touch_dimension_value.marketing_channel | STRING | Marketing channel value for the last valid touch eligible for last touch attribution. |
last_touch_revenue_impact | STRUCT | Struct containing revenue attributed to this touchpoint if it was the last touch for each dimension. |
last_touch_revenue_impact.ad | NUMERIC | Revenue attributed to this touchpoint as last touch for the ad dimension. |
last_touch_revenue_impact.ad_group | NUMERIC | Revenue attributed to this touchpoint as last touch for the ad group dimension. |
last_touch_revenue_impact.campaign | NUMERIC | Revenue attributed to this touchpoint as last touch for the campaign dimension. |
last_touch_revenue_impact.email_sms | NUMERIC | Revenue attributed to this touchpoint as last touch for the email/SMS dimension. |
last_touch_revenue_impact.landing_page | NUMERIC | Revenue attributed to this touchpoint as last touch for the landing page dimension. |
last_touch_revenue_impact.marketing_channel | NUMERIC | Revenue attributed to this touchpoint as last touch for the marketing channel dimension. |
linear_conversion_impact | STRUCT | Struct containing conversion credit (fractional) attributed to this touchpoint using linear model for each dimension. |
linear_conversion_impact.ad | FLOAT64 | Conversion credit attributed to this touchpoint using linear model for the ad dimension. |
linear_conversion_impact.ad_group | FLOAT64 | Conversion credit attributed to this touchpoint using linear model for the ad group dimension. |
linear_conversion_impact.campaign | FLOAT64 | Conversion credit attributed to this touchpoint using linear model for the campaign dimension. |
linear_conversion_impact.email_sms | FLOAT64 | Conversion credit attributed to this touchpoint using linear model for the email/SMS dimension. |
linear_conversion_impact.landing_page | FLOAT64 | Conversion credit attributed to this touchpoint using linear model for the landing page dimension. |
linear_conversion_impact.marketing_channel | FLOAT64 | Conversion credit attributed to this touchpoint using linear model for the marketing channel dimension. |
linear_revenue_impact | STRUCT | Struct containing revenue attributed to this touchpoint using linear attribution for each dimension. |
linear_revenue_impact.ad | FLOAT64 | Revenue attributed to this touchpoint using linear model for the ad dimension. |
linear_revenue_impact.ad_group | FLOAT64 | Revenue attributed to this touchpoint using linear model for the ad group dimension. |
linear_revenue_impact.campaign | FLOAT64 | Revenue attributed to this touchpoint using linear model for the campaign dimension. |
linear_revenue_impact.email_sms | FLOAT64 | Revenue attributed to this touchpoint using linear model for the email/SMS dimension. |
linear_revenue_impact.landing_page | FLOAT64 | Revenue attributed to this touchpoint using linear model for the landing page dimension. |
linear_revenue_impact.marketing_channel | FLOAT64 | Revenue attributed to this touchpoint using linear model for the marketing channel dimension. |
order_metadata | STRUCT | Struct containing order metadata fields (order_id, customer details, etc.). |
order_metadata.order_discount_codes_csv | STRING | A list of discount codes applied to an order. |
order_metadata.order_sequence | STRING | Customer lifecycle classification: 1st_order for the customer’s first order, repeat_order for every later order. Counts all orders; see sm_valid_order_sequence for valid orders only. |
order_metadata.sm_channel | STRING | Sales channel via hierarchy: (1) exclusion tag ‘sm-exclude-order’ -> excluded; (2) config sheet overrides; (3) default logic (amazon/tiktok_shop/walmart.com, pos/leap -> retail, wholesale tags -> wholesale, otherwise online_dtc). Note: excluded channel is omitted from Executive Summary and LTV. |
order_metadata.sm_order_type | STRING | Order classification (subscription vs one-time) derived from order attributes, tags, or subscription platforms (Shopify Subscription Contract, ReCharge, Skio, Loop, Stay AI). |
order_metadata.sm_utm_campaign | STRING | Last-click UTM campaign from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces. |
order_metadata.sm_utm_content | STRING | Last-click UTM content from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces. |
order_metadata.sm_utm_medium | STRING | Last-click UTM medium from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces. |
order_metadata.sm_utm_source | STRING | Last-click UTM source from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). For Shopify, order-level attribution includes allowlisted order customAttributes and legacy note_attributes, including parsing aggregate utmParams/GE_utmParams; if explicit utm_source is missing, sm_utm_source may be inferred from click IDs (e.g., gclid → google, fbclid → meta). When order-level attribution is set via Shopify order customAttributes (V3) using sm_utm_* or utm_* / (sm_)utmParams, it is treated as an explicit override and takes precedence over the default waterfall; keys and query-string parameters are parsed in a snake/camel-case agnostic way. Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces. |
order_metadata.sm_utm_term | STRING | Last-click UTM term from attribution waterfall (Shopify visits/landing/order attributes → website events → GA/GA4 → referrer). Native on Shopify/Chargebee; enriched via website tracking (Elevar, Blotout, Snowplow). Limited for marketplaces. |
order_metadata.sm_zero_party_attribution_source | STRING | Attributable source from post‑purchase survey tags (e.g., Fairing/Enquire). |
order_metadata.subscription_order_sequence | STRING | Subscription lifecycle classification: ‘First Subscription Order’ for initial subscription purchase, ‘Repeat Subscription Order’ for renewals. Based on subscription order index when available, otherwise inferred from order tags. Use for subscription cohort analysis. |
purchase_journey_type | STRING | Categorizes the purchase journey based on session count and duration of time elapsed from first touch to purchase |
purchase_local_datetime | DATETIME | Purchase event timestamp in the reporting timezone for conversion attribution. |
purchase_order_id | STRING | Canonical obt_orders.order_id of the purchase this touchpoint contributed to. |
purchase_order_revenue | NUMERIC | Total revenue from the purchase order for attribution calculations. |
sm_event_name | STRING | Standardized event name (page_view, purchase, add_to_cart, etc.). |
sm_is_purchase_attributable_by | STRUCT | Struct indicating which dimensions this purchase can be attributed by based on business rules. |
sm_touch_id | STRING | Unique identifier for each touchpoint in the customer journey. |
source_system | STRING | Event tracking platform emitting the touchpoint (elevar, snowplow, heap, etc.). |
unique_dimension_value_count | STRUCT | Count of distinct dimension values in the purchase journey. |
unique_dimension_value_count.ad_groups | INT64 | |
unique_dimension_value_count.ads | INT64 | |
unique_dimension_value_count.campaigns | INT64 | |
unique_dimension_value_count.email_sms | INT64 | |
unique_dimension_value_count.landing_pages | INT64 | |
unique_dimension_value_count.marketing_channels | INT64 | |
valid_event_index_to_next_purchase | INT64 | Sequence index from this touchpoint to the next purchase event. |
valid_touch_count | STRUCT | Struct containing count of valid touches for each dimension (marketing_channels, landing_pages, ads, campaigns, ad_groups, email_sms). |
valid_touch_count.ad_groups | INT64 | Count of valid non-brand ad group touches in the purchase journey. |
valid_touch_count.ads | INT64 | Count of valid non-brand ad touches in the purchase journey. |
valid_touch_count.campaigns | INT64 | Count of valid non-brand campaign touches in the purchase journey. |
valid_touch_count.email_sms | INT64 | Count of valid email/SMS touches in the purchase journey. |
valid_touch_count.landing_pages | INT64 | Count of valid landing page touches in the purchase journey. |
valid_touch_count.marketing_channels | INT64 | Count of valid non-brand marketing channel touches in the purchase journey. |
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 splits credit equally across valid touches. Only a session’s entry event counts as a touch, so repeated clicks inside one session count once, while the same channel in two separate sessions counts twice.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