GA4’s built-in attribution reports show one model at a time. When you want to compare how your conversions would be credited under different attribution models — first-touch, last-touch, linear, and data-driven — you need BigQuery. This guide shows you how to implement multiple attribution models in SQL and understand the strategic differences in the results.

Why Attribution Model Comparison Matters

• Last-touch (GA4 default): Google Search gets credit as the last click before purchase — even if Facebook introduced the customer
• First-touch: Facebook gets credit for introducing the customer
• Linear: Credit split equally across all touchpoints
• Data-driven: Credit weighted by statistical contribution from your own conversion data

Building User Journey Touchpoints

-- Build user touchpoint journeys with ordering
WITH touchpoints AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    COALESCE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source'),
      traffic_source.source
    ) as source,
    COALESCE(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium'),
      traffic_source.medium
    ) as medium,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ASC) as touch_number,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) as reverse_touch_number,
    COUNT(*) OVER (PARTITION BY user_pseudo_id) as total_touches,
    MAX(CASE WHEN event_name = 'purchase' 
      THEN (SELECT value.float_value FROM UNNEST(event_params) WHERE key = 'value') 
      ELSE 0 END) OVER (PARTITION BY user_pseudo_id) as conversion_value,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) 
      OVER (PARTITION BY user_pseudo_id) as is_converting
  FROM `project.dataset.events_*`
  WHERE event_name IN ('session_start', 'purchase')
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAYS))
)
SELECT * FROM touchpoints WHERE is_converting = 1;

First-Touch Attribution

-- First-touch: credit to first touchpoint
SELECT
  'first_touch' as model,
  CONCAT(source, ' / ', medium) as channel,
  SUM(conversion_value) as attributed_revenue,
  COUNT(DISTINCT user_pseudo_id) as attributed_conversions
FROM converting_journeys
WHERE touch_number = 1
GROUP BY source, medium
ORDER BY attributed_revenue DESC;