Data Studio + BigQuery + GA4: How to View Multiple Parameters on the Same Event

Multiple parameters on the same event.

When using Google Analytics 4 it can be surprisingly difficult to create a table that shows multiple different parameters of the same event as dimensions.

As an example, let’s say that you have a news app, and your authors create content in three categories: “sports”, “politics”, or “fashion”. Every time a user shares an article, you fire an event called “share” with the parameters “content_type” and “content_author”. You might want to pull a table like the example below so that you can view how many shares an author has received in each category.

content_type content_author shares
politics Jen 31
fashion Bill 27
fashion Jen 8

The solution to this problem is to use “UNNEST(event_params)” for each parameter that you would like to include in your chart.
If you’re familiar with UNNEST you can keep reading, but if this concept is new to you then you’ll want to take a look at my post on How to Flatten a BigQuery Table with UNNEST.

Contents

Viewing Multiple Parameters on the Same Event in BigQuery

In this BigQuery example, I’ve plugged in the parameters ‘page_location’ and ‘page_title’ so that you can experiment with any dataset containing a web stream, but you can modify these as needed.

 1SELECT
 2  event_name,
 3  param1.value.string_value AS Page_Path,
 4  param2.value.string_value AS Page_Title,
 5  count(1) AS Pageviews
 6FROM `<project ID>.<dataset ID>.events_*`,
 7  UNNEST(event_params) as param1,
 8  UNNEST(event_params) as param2
 9WHERE
10  _TABLE_SUFFIX BETWEEN '20200401' AND '20200402'
11  AND event_name = 'page_view'
12  AND param1.key='page_location'
13  AND param2.key='page_title'
14GROUP BY 1, 2, 3
15ORDER BY Pageviews desc

Alternatively, if you are selecting a large number of parameters (more than 2) I prefer to use subqueries because they keep the query simple. For example:

 1SELECT
 2    event_name,
 3    (SELECT value.string_value FROM UNNEST(event_params) AS param WHERE param.key='page_location') AS page_location,
 4    (SELECT value.string_value FROM UNNEST(event_params) AS param WHERE param.key='page_title') AS page_title,
 5    COUNT(1) AS pageviews
 6FROM `<project ID>.<dataset ID>.events_*`
 7WHERE _TABLE_SUFFIX BETWEEN '20200401' AND '20200402'
 8  AND event_name = 'page_view'
 9GROUP BY 1, 2, 3
10ORDER BY Pageviews desc

Viewing Multiple Parameters on the Same Event in Data Studio

In Data Studio, the only way to make this work properly is to use a Custom Query.

Custom Queries

If you’re new to Custom Queries, they are a way of creating a data source from the results of a SQL query, rather than just the raw table that you have sitting in BigQuery (learn more from Google help).

One very cool feature about custom queries is that you can set parameters with them. Parameters are like variables that you can change without modifying your SQL. As an example, you can use parameters to modify the date range, or run the same query on multiple event names. You can even create a chart, copy it, and change the parameters of the duplicate chart so that the same query displays two different charts on the same page of your report.

The steps below show you how to use a custom query with parameters to solve the problem of pulling multiple parameters on the same event in Data Studio. I’ve also created an Example Data Studio Report that you can copy and modify.

1. Start by adding a new BigQuery Data Source

2. Copy the following code block

 1SELECT
 2  event_name,
 3  param1.value.string_value AS First_Parameter,
 4  param2.value.string_value AS Second_Parameter,
 5  count(1) AS eventCount
 6FROM `<project ID>.<dataset ID>.events_*`,
 7  UNNEST(event_params) as param1,
 8  UNNEST(event_params) as param2
 9WHERE
10  _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
11  AND event_name=@evt_name
12  AND param1.key=@first_param
13  AND param2.key=@second_param
14GROUP BY 1, 2, 3

3. In Data Studio, select “Custom Query”

Paste in the code as shown below. You will need to replace ‘helpful-helper-516.analytics_sample’ with your Google Cloud Platform project ID and dataset ID.

Custom query

4. Lastly, you will need to enable your parameters

Start by checking the “Enable data parameters” checkbox as shown above. Then create three more parameters like this (you can change the “Default Value” column if needed):

Name Display Name Default Value Allow Modify in Reports
first_param First Parameter page_referrer True
second_param Second Parameter page_location True
evt_name Event Name page_view True

Once that is saved you can try it out. When you click on the table to edit the data you will notice a new menu where you can enter “Parameters” under “ADD A FILTER”. You can see that your default values are automatically selected, but just type anything in that box to view new parameters or events.

Custom query parameters

Now try to copy this chart and change the parameters to see how flexible this approach is.

Other Resources for BigQuery and GA4

Here are a few other posts that I’ve created for using BigQuery with data from Google Analytics 4: