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.
- A re-sent purchase event repeats the transaction id, the items and the money of the first one, with a later
event_timestamp. Either keep one row perecommerce.transaction_idor group the events by it. Both reach the same rows, because the copies agree on every field this report reads. session_traffic_source_last_click.cross_channel_campaignis the channel of the session that placed the order.traffic_sourceis the channel that first brought the user, and it answers a different question.- A
SELECT *inside aWITHstep reads only the fields a later step uses, so it costs the same as naming them. - This sample has seven daily tables and no others. The window filter decides which days count, and here it changes no bytes. A real export keeps months of shards, and there the same filter is what prunes them.
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 mediumEach 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.