How to Attribute Revenue by Channel in BigQuery

Ask five tools which channel drove your revenue and you get five answers, none of which you fully trust. The GA4 BigQuery export lets you settle it yourself, with revenue tied to source and medium in one query you control. Here is how to attribute revenue by channel.

Why do it in BigQuery at all

The GA4 interface gives you channel revenue, but it is pre-aggregated, sometimes thresholded, and you cannot see or change the logic behind it. In BigQuery you work from raw purchase events, so you decide exactly how revenue maps to a channel and you can reconcile every number against your backend. That control is the whole reason to leave the interface.

Where revenue and source live

Two pieces have to come together. Purchase revenue sits in the ecommerce record on the purchase event, as ecommerce.purchase_revenue. The traffic that earned it sits in the traffic-source fields: collected_traffic_source holds the event-level source and medium, while session_traffic_source_last_click holds the session-level last click added in July 2024.

For a straightforward first pass, read source and medium directly off the purchase event and sum the revenue:

SELECT
  collected_traffic_source.manual_source AS source,
  collected_traffic_source.manual_medium AS medium,
  COUNT(*)                                AS purchases,
  ROUND(SUM(ecommerce.purchase_revenue), 2) AS revenue
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'
GROUP BY source, medium
ORDER BY revenue DESC

Roll source and medium into channels

Source and medium are granular, and for reporting you usually want channels like Organic Search or Paid Social. Wrap the same CASE logic you use to recreate the default channel grouping around the source and medium, then group by the channel instead:

SELECT
  CASE
    WHEN collected_traffic_source.manual_medium = 'organic' THEN 'Organic Search'
    WHEN REGEXP_CONTAINS(collected_traffic_source.manual_medium, r'^(cpc|ppc)$') THEN 'Paid Search'
    WHEN collected_traffic_source.manual_medium = 'referral' THEN 'Referral'
    WHEN collected_traffic_source.manual_source IS NULL THEN 'Direct'
    ELSE 'Other'
  END AS channel,
  ROUND(SUM(ecommerce.purchase_revenue), 2) AS revenue
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'purchase'
GROUP BY channel
ORDER BY revenue DESC

Event-level vs session last-click

The choice of source field decides your attribution model. Reading collected_traffic_source off the purchase event gives you the source recorded with that event, which is close to a last-touch view. Using session_traffic_source_last_click attributes to the session's last click, which is what the GA4 interface leans on. Pick one deliberately and keep it consistent, because mixing them makes your numbers impossible to compare.

Reconcile before you report

Raw revenue attribution is only trustworthy once it ties out, so validate it:

• Sum ecommerce.purchase_revenue across all channels and compare it to your backend revenue for the period.
• Check for a large Direct or Other bucket, which usually signals missing UTMs rather than genuine direct sales.
• Send a unique transaction_id on every purchase so duplicates do not inflate a channel's revenue.

Beyond last click

Reading a single source per purchase is a last-touch view, and it under-credits the channels that introduced a customer earlier. To go further, assemble each buyer's ordered list of sessions and their sources, then spread credit across them – first touch, linear, or position-based. That needs user-level stitching and more SQL, but the export is the only place you can build it honestly, because the interface only offers Google's fixed models.

Whichever model you choose, state it next to the number, since a revenue-by-channel figure means nothing until the reader knows whether it credits the first click, the last, or a share of both. And subtract refunds: ecommerce.refund_value records money returned, so a channel's true contribution is purchase revenue minus refunds, not gross sales.

Five tools gave you five revenue-by-channel answers because each hid its own logic, and the BigQuery export replaces all of them with one you can read line by line. Pull revenue from the ecommerce record, map source and medium to channels with your own CASE, choose event-level or session last-click on purpose, and reconcile against the backend. Do that and channel revenue stops being five arguments and becomes one number you can defend.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.