The GA4 Update: Matching Big Query Data with Google Analytics - Product Performance

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.

SELECT
  i.item_name
 FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST (items) i
  where _table_suffix BETWEEN "20210101" and "20210131"
GROUP BY 1

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.

SELECT
  i.item_name,
  COUNT(DISTINCT case when event_name = "view_item" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS view_item,
  COUNT(DISTINCT case when event_name = "add_to_cart" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS add_to_cart,
  COUNT(DISTINCT case when event_name = "view_cart" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS view_cart,
  COUNT(DISTINCT case when event_name = "begin_checkout" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS begin_checkout,
  COUNT(DISTINCT case when event_name = "add_payment_info" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS add_payment_info,
  COUNT(DISTINCT case when event_name = "add_shipping_info" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS add_shipping_info,
  COUNT(DISTINCT case when event_name = "purchase" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS purchase,
 FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST (items) i
  where _table_suffix BETWEEN "20210101" and "20210131"
GROUP BY 1

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.

SELECT
  i.item_name,
  sum(case when event_name = "purchase" then i.quantity end) as qty,
  sum(case when event_name = "purchase" then i.price end) * sum(case when event_name = "purchase" then i.quantity end) as revenue,
 FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST (items) i
  where _table_suffix BETWEEN "20210101" and "20210131"
GROUP BY 1

Putting it all together

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

SELECT 
  *,
  round(add_to_cart/view_item,2) AS basketToDetailRate,
  round(purchase/view_item,2) AS buyToDetailRate,
  round(qty/purchase,2) AS avgQuantity,
FROM(
  SELECT
    i.item_name,
    COUNT(DISTINCT case when event_name = "view_item" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS view_item,
    COUNT(DISTINCT case when event_name = "add_to_cart" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS add_to_cart,
    COUNT(DISTINCT case when event_name = "view_cart" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS view_cart,
    COUNT(DISTINCT case when event_name = "begin_checkout" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS begin_checkout,
    COUNT(DISTINCT case when event_name = "add_payment_info" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS add_payment_info,
    COUNT(DISTINCT case when event_name = "add_shipping_info" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS add_shipping_info,
    COUNT(DISTINCT case when event_name = "purchase" then concat(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE  key = 'ga_session_id')) end ) AS purchase,
    sum(case when event_name = "purchase" then i.quantity end) as qty,
    sum(case when event_name = "purchase" then i.price end) * sum(case when event_name = "purchase" then i.quantity end) as revenue,
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
    UNNEST (items) i
    where _table_suffix BETWEEN "20210101" and "20210131"
  GROUP BY 1)

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.