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

Reporting & Analysis
Written By Ken Williams
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.

  • Updated all references to “data studio” to “looker studio”

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 Looker Studio

In Looker 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 Looker Studio. I’ve also created an Example Looker 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 Looker 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):

Parameter Name Parameter ID Default Value
First Param first_param page_referrer
Second Param second_param page_location
Evt Name evt_name page_view

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

Ken Williams
Previous

How to Flatten a BigQuery Table with UNNEST
Next

Looker Studio + BigQuery + GA4: Viewing a Parameter Across Multiple Events