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

When using Google Analytics for Firebase (or just Firebase Analytics), 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_typecontent_authorshares
politicsJen31
fashionBill27
fashionJen8

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

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):

NameDisplay NameDefault ValueAllow Modify in Reports
first_paramFirst Parameterpage_referrerTrue
second_paramSecond Parameterpage_locationTrue
evt_nameEvent Namepage_viewTrue

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.

Leave a Reply

Your email address will not be published.