MTA Data Reference - SourceMedium

MTA data lives in two tables in the sm_experimental dataset of your managed warehouse. A third table in sm_transformed_v2 helps when one order looks wrong. Full column lists are on each table’s schema page; this page covers how the tables are shaped and how to query them.

obt_purchase_journeys_with_mta_models

One row per touch point per purchase. The purchase event itself is also a row, with sm_event_name = 'purchase', and it carries the journey-level fields. Filter on it whenever you count purchases, so each order is counted once. Identifiers and timing:

Column	Meaning
`sm_store_id`	Your store identifier
`source_system`	The event feed the journey came from (`ga4`, `elevar`, and so on)
`sm_touch_id`	Unique id of the touch point
`purchase_order_id`	The order this touch contributed to
`sm_event_name`	Standardized event name; `purchase` marks the purchase row
`event_local_datetime`	When the touch happened
`purchase_local_datetime`	When the order was placed (on every row)
`purchase_order_revenue`	Net revenue of the order (on every row)

Per-touch structs, one field per dimension (marketing_channel, landing_page, ad, campaign, ad_group, email_sms):

Column	Meaning
`dimension_value`	The value of this touch in each dimension, for example `dimension_value.marketing_channel`
`first_touch_revenue_impact`	Revenue credited to this touch under first touch
`last_touch_revenue_impact`	Revenue credited to this touch under last touch
`linear_revenue_impact`	Revenue credited to this touch under linear
`first_touch_conversion_impact`, `last_touch_conversion_impact`, `linear_conversion_impact`	The same for conversions (1, 0, or a fraction)
`is_valid_touch`	Whether this touch is valid in each dimension; fields are `landing_page`, `email_sms`, and `<dimension>_non_brand`, `<dimension>_all`, `<dimension>_last_touch` for `marketing_channel`, `ad_id`, `campaign_id`, and `ad_group_id`

Journey-level structs, populated on the purchase row. Count fields are plural (marketing_channels, landing_pages, ads, campaigns, ad_groups, email_sms):

Column	Meaning
`valid_touch_count`	Number of valid touches per dimension. Linear credit is 1 divided by this
`unique_dimension_value_count`	Number of distinct values per dimension
`sm_is_purchase_attributable_by`	Whether the purchase is attributable per dimension (on every row)
`days_to_conversion`	Days from the first valid touch to purchase, per dimension (singular field names)
`first_touch_dimension_value`, `last_touch_dimension_value`	The first and last valid value per dimension (singular field names)
`purchase_journey_type`	`single_session`, `same_day_multi_session`, or `multi_day_multi_session`

Metadata structs: attribution_metadata (UTMs, referrer, click ids, page category), ad_platform_metadata, campaign_platform_metadata, ad_group_platform_metadata, and order_metadata (sales channel, order type, order sequence, discount codes).

obt_purchase_journeys_with_mta_models schema

Full column list and descriptions.

Revenue by marketing channel under all three models

Sum the credit on touch rows. Every model adds up to the same attributable revenue.

SELECT
  dimension_value.marketing_channel AS channel,
  SUM(first_touch_revenue_impact.marketing_channel) AS first_touch_revenue,
  SUM(last_touch_revenue_impact.marketing_channel) AS last_touch_revenue,
  SUM(linear_revenue_impact.marketing_channel) AS linear_revenue
FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
WHERE sm_store_id = 'your-sm_store_id'
  AND DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND dimension_value.marketing_channel IS NOT NULL
GROUP BY 1
ORDER BY 4 DESC

Attribution rate per dimension

Read the purchase rows only.

SELECT
  COUNT(*) AS purchases,
  SAFE_DIVIDE(COUNTIF(sm_is_purchase_attributable_by.marketing_channels), COUNT(*)) AS channel_rate,
  SAFE_DIVIDE(COUNTIF(sm_is_purchase_attributable_by.ads), COUNT(*)) AS ad_rate,
  SAFE_DIVIDE(COUNTIF(sm_is_purchase_attributable_by.landing_pages), COUNT(*)) AS landing_page_rate,
  SAFE_DIVIDE(COUNTIF(sm_is_purchase_attributable_by.email_sms), COUNT(*)) AS email_sms_rate
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 DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)

Journey length by journey type

SELECT
  purchase_journey_type,
  COUNT(*) AS purchases,
  AVG(valid_touch_count.marketing_channels) AS avg_channel_touches,
  AVG(days_to_conversion.marketing_channel) AS avg_days_to_conversion
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 sm_is_purchase_attributable_by.marketing_channels
  AND DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1
ORDER BY 2 DESC

rpt_ad_attribution_performance_daily

Daily ad platform performance with MTA credit joined on. One row per store, date, ad platform (source_system), sales channel (sm_channel), and waterfall_level entity. Use it for ROAS and for comparing platform-reported results with SourceMedium credit.

Column	Meaning
`date`	Performance date
`source_system`	Ad platform
`sm_channel`	Sales channel of the ad; credit is only assigned on `Online DTC` rows
`waterfall_level`	`ad_level`, `ad_group_level`, `campaign_level`, or `channel_level`
`ad_id`, `ad_name`, `ad_group_id`, `ad_group_name`, `ad_campaign_id`, `ad_campaign_name`	Entity ids and names; lower levels are null above their level
`ad_campaign_type`, `ad_campaign_tactic`, `ad_platform_campaign_objective`	Campaign classification; tactic defaults to `prospecting`
`ad_creative_title`, `ad_creative_image_url`	Creative details on `ad_level` rows
`ad_spend`, `ad_impressions`, `ad_clicks`	Platform performance, preserved exactly
`ad_platform_reported_conversions`, `ad_platform_reported_revenue`	What the platform claims
`sm_first_touch_revenue`, `sm_last_touch_revenue`, `sm_linear_revenue`	SourceMedium credit by model
`sm_first_touch_conversions`, `sm_last_touch_conversions`, `sm_linear_conversions`	The same for conversions

rpt_ad_attribution_performance_daily schema

Full column list and descriptions.

Campaign ROAS, platform-reported versus linear

SELECT
  source_system AS ad_platform,
  ad_campaign_name,
  SUM(ad_spend) AS spend,
  SAFE_DIVIDE(SUM(ad_platform_reported_revenue), SUM(ad_spend)) AS platform_roas,
  SAFE_DIVIDE(SUM(sm_linear_revenue), SUM(ad_spend)) AS linear_roas
FROM `your_project.sm_experimental.rpt_ad_attribution_performance_daily`
WHERE sm_store_id = 'your-sm_store_id'
  AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND ad_campaign_id IS NOT NULL
  AND LOWER(ad_campaign_tactic) <> 'brand'
GROUP BY 1, 2
HAVING spend > 0
ORDER BY 3 DESC

fct_order_attribution_signals

When a single order looks wrong, start here. This table holds one row per piece of attribution evidence SourceMedium kept for an order: raw and canonical UTMs, click ids, referrer, zero-party survey answers, and discount codes. The row with sm_utm_final_source_priority = 1 is the traffic source the main dashboard credits. Filter on sm_store_id and order_id, then join to purchase_order_id in the purchase journey table for the MTA view of the same order.

fct_order_attribution_signals schema

Full column list and the workflow for tracing a single order.

​obt_purchase_journeys_with_mta_models

obt_purchase_journeys_with_mta_models schema

​Revenue by marketing channel under all three models

​Attribution rate per dimension

​Journey length by journey type

​rpt_ad_attribution_performance_daily