Where the buyers came from

The team buys traffic from several places and wants to know which of them produces buyers. This capstone asks for that report, and nothing in it tells you how to build the query.

How it works

Each purchase event carries the transaction it belongs to, what the order was worth, and the channel of the session that placed it. A few of those events are a re-send of an earlier one, so bring the events down to one row per transaction first, then add those rows up by channel.

Common mistake

Adding the re-sent purchase events up with the rest raises the revenue by 2.72% here. Three transactions arrive twice in the window, and the extra money looks like a good week.

Worked example

SELECT
  session_traffic_source_last_click.cross_channel_campaign.medium AS medium,
  COUNT(*) AS purchase_events,
  COUNT(DISTINCT ecommerce.transaction_id) AS transactions
FROM `learn.ga4.events_*`
WHERE event_name = 'purchase' AND _TABLE_SUFFIX = '20240104'
GROUP BY medium
ORDER BY medium

Each row is one medium of 4 January, with its purchase events beside its transactions.

One row has more events than transactions, so a purchase event was sent twice that day.

The medium comes from the session that placed the order, so a return visit through another channel is credited to that channel.

All BigQuery lessons