Page Location for add_to_cart events in BigQuery

For shop operators it is interesting where the user adds his products to the shopping cart. Does this happen on the search results page, in a category or in the classic product detail?

With good Google Analytics 4 (GA4) data and BigQuery connectivity, this is easy to determine.

Requirements

1. The event ‘add_to_cart’ must be recorded in GA4.
2. The event is assigned a ‘page_type’ (or this is different, e.g. recognizable via the URL).
3. The GA4 data is streamed to BigQuery.

Code in BigQuery

The procedure is simple:

1. GA4 Query data
2. extract the event values with UNNEST(event_params )
3. reduce to the ‘add_toCart’ event.

WITH ga_events AS (
 SELECT
  *  
  FROM `MY_GA_INSTANCE`, UNNEST(event_params) as event_params  
  WHERE event_name = 'add_to_cart'
)
SELECT  
  event_date,
  value.string_value as add_to_cart_location
FROM ga_events
WHERE key = 'page_type'

The result is a 2-column table with event_date and add_to_cart_location with the values (search_result, product, category …)

Display data in Google Datastudio.

We change the prescribed code so that event_date can be used as a date selector in Google Datastudio.

Copy to ClipboardSyntax Highlighter

WITH ga_events AS (
 SELECT
  *  
  FROM `MY_GA4_DATABASE`, UNNEST(event_params) as event_params  
  WHERE event_name = 'add_to_cart'
 AND _table_suffix between @DS_START_DATE and @DS_END_DATE

)
SELECT  
  event_date,
  value.string_value as add_to_cart_location
FROM ga_events
WHERE key = 'page_type'

Decisive here is the line:

AND _table_suffix between @DS_START_DATE and @DS_END_DATE

Which narrows the date range.

The result of the query can then be displayed in Google Datastudio with a pie chart.