GA4 multi-touch attribution in BigQuery: build a custom attribution model in SQL

A custom multi-touch attribution model in BigQuery is five steps over the GA4 export. Each one is a CTE you can check on its own:

1. Build sessions. One row per user_pseudo_id and ga_session_id, with its start time and the first campaign source and medium seen in it.
2. Pick the conversion. One row per conversion with its time and value, for example each user’s first purchase.
3. Join the touchpoints. Every session for the same user in a lookback window, such as 90 days, up to the conversion.
4. Assign credit. ROW_NUMBER and COUNT give position-based credit. POW(0.5, days / half-life), normalized per conversion, gives time decay.
5. Roll up by channel. Sum credit × conversion value for each model and channel.

The full SQL is below. Two limits to know first: user_pseudo_id identifies a browser, not a person, so cleared cookies and other devices split journeys; and GA4’s data-driven model is not in the export, so these are rule-based models of your own.

What does the GA4 BigQuery export include?

The export holds raw events. That is why you can define your own conversion, lookback window and credit rule. It is also why the totals will not match the GA4 interface: Google’s reporting surfaces comparison says the export has no modeling and no data-driven attribution.

• Identity. user_pseudo_id is the browser’s identifier. user_id is present only if your site sets it, for example after login.
• Traffic source. The export schema includes collected_traffic_source on each event, with the manual UTM fields and click IDs such as gclid, and session_traffic_source_last_click, the session’s last-click source.
• Volume. On a standard property, the daily export is limited to 1 million events a day. The streaming export has no event limit, at extra BigQuery cost.

Link the property using Google’s BigQuery Export setup guide. A model can only use the days that have been exported, so check which tables arrive and their date coverage before building paths.

How do you build a custom attribution model in BigQuery?

The example below builds sessions, finds a browser identifier’s first purchase in the selected data, and collects preceding sessions within an illustrative 90-day window. It then compares position-based and time-decay credit. These are custom rules, not a reproduction of Google’s data-driven model.

The first block prepares touchpoints with a conversion ID, browser key, touch time, channel, conversion time, and value. Review the source table, scan period, key event, and lookback window before adapting it. Include enough earlier data to cover the lookback period.

The example joins on user_pseudo_id. Cleared cookies and different devices can split journeys. It selects one purchase per browser key in the scanned data; it is not an all-orders revenue model.

Position-based credit

Position-based (U-shaped) credit gives 40% to the first touch, 40% to the last touch, and splits the remaining 20% evenly across the middle touches. The 40/20/40 split is a common convention, not a Google Analytics standard. A conversion with one touch gets 100%, and a conversion with two touches gets 50/50.

WITH events AS (
  SELECT
    user_pseudo_id,
    CONCAT(user_pseudo_id, '.', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_key,
    TIMESTAMP_MICROS(event_timestamp) AS event_ts,
    event_name,
    collected_traffic_source.manual_source AS utm_source,
    collected_traffic_source.manual_medium AS utm_medium,
    ecommerce.purchase_revenue AS purchase_revenue
  -- Replace with your own Google Cloud project ID and GA4 export dataset.
  FROM `your-project-id.analytics_YOUR_PROPERTY_ID.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260930'
),
sessions AS (
  -- One row per session, credited to the first campaign source seen in it.
  SELECT
    user_pseudo_id,
    session_key,
    MIN(event_ts) AS session_start_ts,
    COALESCE(
      ARRAY_AGG(IF(utm_source IS NULL, NULL, CONCAT(utm_source, ' / ', IFNULL(utm_medium, '(not set)'))) IGNORE NULLS ORDER BY event_ts LIMIT 1)[SAFE_OFFSET(0)],
      '(direct) / (none)'
    ) AS channel
  FROM events
  GROUP BY user_pseudo_id, session_key
),
conversions AS (
  -- First purchase per user; swap 'purchase' for your own key event.
  SELECT
    user_pseudo_id,
    event_ts AS conversion_ts,
    IFNULL(purchase_revenue, 0) AS conversion_value,
    CONCAT(user_pseudo_id, '.', CAST(UNIX_MICROS(event_ts) AS STRING)) AS conversion_id
  FROM events
  WHERE event_name = 'purchase'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_ts) = 1
),
touchpoints AS (
  -- Every session in the 90 days up to and including the converting session.
  SELECT
    c.conversion_id,
    s.user_pseudo_id AS user_key,
    s.session_start_ts AS touch_ts,
    s.channel,
    c.conversion_ts,
    c.conversion_value
  FROM conversions AS c
  JOIN sessions AS s
    ON s.user_pseudo_id = c.user_pseudo_id
   AND s.session_start_ts <= c.conversion_ts
   AND s.session_start_ts >= TIMESTAMP_SUB(c.conversion_ts, INTERVAL 90 DAY)
),
ranked AS (
  SELECT
    conversion_id,
    channel,
    conversion_value,
    ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY touch_ts, channel) AS touch_pos,
    COUNT(*) OVER (PARTITION BY conversion_id) AS touch_count
  FROM touchpoints
),
position_based AS (
  SELECT
    'position_based' AS model,
    conversion_id,
    channel,
    conversion_value,
    CASE
      WHEN touch_count = 1 THEN 1.0
      WHEN touch_count = 2 THEN 0.5
      WHEN touch_pos IN (1, touch_count) THEN 0.4
      ELSE 0.2 / NULLIF(touch_count - 2, 0)
    END AS credit
  FROM ranked
)

Time-decay credit

Time decay gives more credit to touches closer to the conversion. Each touch gets a weight of POW(0.5, days before conversion / half-life), and the weights are normalized so each conversion's credit sums to 1. The example uses a 7-day half-life, which you can change in one place.

,
time_decay_raw AS (
  SELECT
    t.conversion_id,
    t.channel,
    t.conversion_value,
    -- half-life in days; change 7.0 to tune how quickly credit decays
    POW(0.5, TIMESTAMP_DIFF(t.conversion_ts, t.touch_ts, HOUR) / 24.0 / p.half_life_days) AS raw_weight
  FROM touchpoints AS t
  CROSS JOIN (SELECT 7.0 AS half_life_days) AS p
),
time_decay AS (
  SELECT
    'time_decay' AS model,
    conversion_id,
    channel,
    conversion_value,
    raw_weight / SUM(raw_weight) OVER (PARTITION BY conversion_id) AS credit
  FROM time_decay_raw
)

Rolling credit up by channel

Stack the two models, multiply each touch's credit by the conversion value, and sum by channel. The example values each conversion by its purchase revenue; set conversion_value to 1 instead if you want attributed conversion counts.

,
credits AS (
  SELECT model, conversion_id, channel, conversion_value, credit FROM position_based
  UNION ALL
  SELECT model, conversion_id, channel, conversion_value, credit FROM time_decay
)
SELECT
  model,
  channel,
  ROUND(SUM(credit * conversion_value), 2) AS attributed_value
FROM credits
GROUP BY model, channel
ORDER BY model, attributed_value DESC

At each stage, check the grain. A duplicated session or a many-to-many join can multiply conversion value without a query error. Keep unmatched conversions visible, and decide how repeat purchases, refunds and late events are handled. Those cases are part of the definition, not cleanup for later.

For a reporting setup that does not need a warehouse, read GA4 attribution reports without BigQuery. To compare how each model assigns credit before you pick one, read multi-touch attribution and models explained.

How do you report attribution in Looker Studio?

Connect the model’s output table, not the raw export, to Looker Studio with the BigQuery connector. It can read a table or a custom SQL query. Check permissions and query costs before sharing the report.

Build views that help the reader compare models and investigate the records behind a total:

• Model comparison. A table of attributed value by channel under each model, side by side. This shows stakeholders how much the rule changes the answer.
• Conversions by week and channel. A stacked column chart to spot trends in each channel.
• Campaign performance. A table of campaigns within each channel, if your touchpoints keep the campaign name.

For a model selector, stack the models in one table with a model column and add a drop-down control on it. Keep the eligible input the same for every model, so the comparison reflects the weighting rule rather than a different population. Put the conversion definition, lookback window and data-through date beside the selector, so nobody has to read the SQL to know what the report counts.

Check the model before sharing it

1. 1Total attributed value equals total conversion value for each model.
2. 2Unmatched conversions, with no session in the lookback window, are counted and shown.
3. 3A handful of journeys inspected by hand get the credit you expect.
4. 4The report states the conversion, lookback window, identity key and data-through date.

What does it mean when the models disagree?

If a channel gains credit under one model, inspect the paths behind the change. It may appear earlier in the recorded journey, or the rule may reward repeat visits. Neither proves that more spend will cause more sales.

Use experiments and other business evidence when the decision needs a causal answer. Attribution helps prioritize an investigation; it should not turn a weighting assumption into a promised return.