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.
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.
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.
SELECT event_name, param1.value.string_value AS Page_Path, param2.value.string_value AS Page_Title, count(1) AS Pageviews FROM `<project ID>.<dataset ID>.events_*`, UNNEST(event_params) as param1, UNNEST(event_params) as param2 WHERE _TABLE_SUFFIX BETWEEN '20200401' AND '20200402' AND event_name = 'page_view' AND param1.key='page_location' AND param2.key='page_title' GROUP BY 1, 2, 3 ORDER 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:
SELECT event_name, (SELECT value.string_value FROM UNNEST(event_params) AS param WHERE param.key='page_location') AS page_location, (SELECT value.string_value FROM UNNEST(event_params) AS param WHERE param.key='page_title') AS page_title, COUNT(1) AS pageviews FROM `<project ID>.<dataset ID>.events_*` WHERE _TABLE_SUFFIX BETWEEN '20200401' AND '20200402' AND event_name = 'page_view' GROUP BY 1, 2, 3 ORDER 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.
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
SELECT event_name, param1.value.string_value AS First_Parameter, param2.value.string_value AS Second_Parameter, count(1) AS eventCount FROM `<project ID>.<dataset ID>.events_*`, UNNEST(event_params) as param1, UNNEST(event_params) as param2 WHERE _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE AND event_name=@evt_name AND param1.key=@first_param AND param2.key=@second_param GROUP 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.
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|
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.
Now try to copy this chart and change the parameters to see how flexible this approach is.