Your paid social team says Facebook drove 300 sales. Your SEO team says organic drove 250. Your total sales were 400. Everyone is right within their attribution model — and everyone is also wrong about the full picture. This guide implements multiple attribution models in BigQuery using GA4 event data so you can compare them side by side and make an informed budget allocation decision.

Setting Up the Attribution Dataset

-- Build session-level table with all touchpoints
WITH session_data AS (
  SELECT user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') AS session_id,
    event_name, event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='source') AS source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium') AS medium,
    (SELECT value.float_value FROM UNNEST(event_params) WHERE key='value') AS revenue
  FROM `project.analytics_XXXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260430'
    AND event_name IN ('session_start','purchase')
),
sessions AS (
  SELECT user_pseudo_id, session_id,
    MIN(event_timestamp) AS session_time,
    MAX(CASE WHEN event_name='session_start' THEN source END) AS source,
    MAX(CASE WHEN event_name='session_start' THEN medium END) AS medium,
    MAX(CASE WHEN event_name='purchase' THEN revenue END) AS revenue,
    MAX(CASE WHEN event_name='purchase' THEN 1 ELSE 0 END) AS converted
  FROM session_data GROUP BY 1,2
),
converting_users AS (SELECT DISTINCT user_pseudo_id FROM sessions WHERE converted=1),
ordered AS (
  SELECT s.*,
    ROW_NUMBER() OVER(PARTITION BY s.user_pseudo_id ORDER BY s.session_time) AS touch_num,
    COUNT(*) OVER(PARTITION BY s.user_pseudo_id) AS total_touches
  FROM sessions s INNER JOIN converting_users c USING(user_pseudo_id)
  WHERE s.session_time <= (
    SELECT MIN(session_time) FROM sessions s2
    WHERE s2.user_pseudo_id=s.user_pseudo_id AND s2.converted=1
  )
)

First Touch Attribution

-- First Touch: credit to first session in the path
SELECT CONCAT(source,'/',medium) AS channel,
  COUNT(*) AS first_touch_conversions,
  SUM(revenue) AS first_touch_revenue
FROM ordered WHERE touch_num=1
GROUP BY 1 ORDER BY 2 DESC;

Last Touch Attribution

-- Last Touch: credit to final session before conversion
SELECT CONCAT(source,'/',medium) AS channel,
  COUNT(*) AS last_touch_conversions,
  SUM(revenue) AS last_touch_revenue
FROM ordered WHERE touch_num=total_touches
GROUP BY 1 ORDER BY 2 DESC;

Linear Attribution

-- Linear: split credit equally across all touchpoints
SELECT CONCAT(source,'/',medium) AS channel,
  SUM(1.0/total_touches) AS linear_conversions,
  SUM(revenue/total_touches) AS linear_revenue
FROM ordered GROUP BY 1 ORDER BY 2 DESC;

Which Model to Use for Budgeting

Linear attribution is the fairest starting point for budget allocation. First touch is best for measuring brand awareness channel ROI. Last touch is what most ad platforms use by default — useful for platform comparison. Channels that score high in first touch but low in last touch are top-of-funnel influencers. Channels high across all models are indispensable.

Related: BigQuery Attribution Window, GA4 Channel Grouping.