I am slowly making my way through creating GA reports in BigQuery. Check out my others here:

• Overview metrics

• Channel acquisition

• Landing pages

First, let’s get the product names by unnesting items.

Shopping behaviour

GA4 removed all the checkout steps and action types, and we are just using events. As I say, everything in GA4 is an event! So we count the user id and session id for each of the events.

Transaction data

Attached to the item array with the item name, you can also get details on what was bought, quantity and price.

If you sum up the item price, it may not be the same as your total revenue as it won’t include shipping or discounts across the entire cart.
You only need to get the quantity and revenue from the purchase event. Otherwise, you could get quantity from add to cart or other events. For revenue, you need to times the price by the quantity.

Putting it all together

Now, we can add these all together and add in the percentages and averages.

This is one of the neater queries. As usual, let me know if there is a report you would like to see or if you have any questions.