Attribution by channel

Budget decisions depend on which channel gets credit for a sale. The same purchase can be credited to different channels depending on the field you group by.

How it works

Choose the attribution scope before you aggregate. For purchase reports, remove duplicate transactions and group their revenue by the source and medium of the scope you chose.

Common mistake

A field with the wrong scope gives valid SQL and the wrong answer: revenue is counted under a channel that did not bring that purchase.

Worked example

WITH purchases AS (
  SELECT 'organic' AS first_touch_source, 'email' AS session_source, 30.0 AS revenue UNION ALL
  SELECT 'organic', 'paid-search', 50.0 UNION ALL
  SELECT 'referral', 'email', 20.0
)
SELECT session_source, SUM(revenue) AS revenue
FROM purchases
GROUP BY session_source
ORDER BY revenue DESC, session_source

The first two purchases share an organic first touch but come from different session sources.

Grouping by session source gives email 50 and paid-search 50. Grouping by first touch would put the first two purchases under organic.

All BigQuery lessons