In this article, you will learn to access a nested field in a GA4 BigQuery export data table.

Consider the following data table:

This data table is for June 18, 2023 which is denoted by 20230618.

Let us suppose you want to query the data which meets the following requirements:

1. You want to retrieve the data only for June 18, 2023.
2. You want to retrieve the ‘event_date’ and ‘event_name’ columns of the data table.
3. You want to see only those rows where ‘event_name’ = ‘page_view’
4. You want to see the value of ‘page_location’ event parameter as ‘page_location’ column.
5. You want to retrieve only the first 10 rows of the data table.

#1 Use the following text prompt in ‘GA4 BigQuery Composer’

Write SQL which meets the following requirements:
1) Show data for June 18, 2023
2) Show only the ‘event_date’ and ‘event_name’ columns of the data table.
3) Show only those rows where the value of ‘event_name’ column is ‘page_view’.
4) Add a new column named ‘page_location’ that contains the value of the ‘page_location’ event parameter. The ‘page_location’ event parameter is an array of structures within the ‘event_params’ array of structures.
5) Show only the first 10 rows of the data table.
My table ID is: dbrt-ga4.analytics_207472454.events_20240416

Note: Use your table ID instead of mine. Otherwise, your SQL code won’t work.

#2 Copy the generated SQL code by clicking on the ‘Copy code’ button:

#3 Paste the copied code in the SQL editor and then click on the ‘Run’ button:

Congratulations!

You just created the first meaningful report in BigQuery, where you can see the list of web pages which got views on June 18, 2023.

Consider the following data table:

This data table is for June 18, 2023 which is denoted by 20230618.

Let us suppose you want to query the data which meets the following requirements:

1. You want to retrieve the data only for June 18, 2023.
2. You want to retrieve the ‘event_date’ and ‘event_name’ columns of the data table.
3. You want to see the value of ‘page_location’ event parameter as ‘page_location’ column.
4. You want to see the value of ‘content_group’ event parameter as ‘content_group’ column.
5. You want to see only those rows where ‘content_group’ is ‘Google Analytics’
6. You want to retrieve only the first 10 rows of the data table.

#1 Use the following text prompt in ‘GA4 BigQuery Composer’

Write SQL which meets the following requirements:
1) Show data for June 18, 2023
2) Show only the 'event_date' and 'event_name' columns of the data table.
3) Add a new column named 'page_location' that contains the values of the 'page_location' event parameter. The 'page_location' event parameter is an array of structures within the 'event_params' array of structures.
4) Add a new column named 'content_group' that contains the values of the 'content_group' event parameter. The 'content_group' event parameter is an array of structures within the 'event_params' array of structures.
5) Show only those rows of the data table where the string value of the ‘content_group’ event parameter is 'Google Analytics'.
6) Show only the first 10 rows of the data table.
My table ID is: dbrt-ga4.analytics_207472454.events_20240416

Note: Use your table ID instead of mine. Otherwise, your SQL code won’t work.

#2 Copy the generated SQL code by clicking on the ‘Copy code’ button:

#3 Paste the copied code in the SQL editor and then click on the ‘Run’ button: