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

Reporting & Analysis
Written By Ken Williams
The same parameter across multiple events.

Sometimes you have multiple actions (with separate event names) that share a common parameter. This post will show you how to view how frequently each event occurred by the value of your event parameter.

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

Let’s Start with an Example

Imagine that you have an app that contains courses (like LinkedIn Learning, or Coursera). When a user interacts with a course they might create multiple events, such as: “course_viewed”, “course_started” and “course_completed”. To track which course is being interacted with, each of those events might contain a parameter called “course_name”.

So, as an Analyst, you might need to create a conversion funnel that displays how many users viewed, started and completed each course. I like to visualize this with a stacked bar chart so that it sort of looks like a funnel, which requires the data to look like this:

Course_Name Event_Name Unique_Users
Algebra course_viewed 100
Algebra course_started 65
Algebra course_completed 45
Trigonometry course_viewed 110

Viewing a Parameter Across Multiple Events in BigQuery

To generate this table in BigQuery, you will need to use the UNNEST operator on the event_params column. 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.

Here is what it looks like in BigQuery:

SELECT
  params.value.string_value AS Course_Name,
  event_name,
  COUNT(DISTINCT user_pseudo_id) AS Unique_Users
FROM `<project ID>.<dataset ID>.events_*`,
  UNNEST(event_params) as params
WHERE
  _TABLE_SUFFIX BETWEEN '20200401' AND '20200402'
  AND event_name IN ('course_viewed','course_started','course_completed')
  AND params.key='course_name'
GROUP BY 1, 2

Viewing a Parameter Across Multiple Events in Looker Studio

To create this stacked bar chart in Looker Studio you have two options. Regardless of whether you choose option 1 or 2 above, your chart will create a dropoff funnel for each course name, and should look something like this.

Parameter stacked bar

Option 1

You could use the same query above as a Custom Query in Looker Studio. If you do this, I suggest you add a few other fields to use as filters and replace the start and end date with parameters (see below).

To create the stacked bar chart we discussed, you’ll connect to this new data source, set Course_Name as your dimension, and set your breakdown dimension to event_name.

SELECT
  event_date,
  platform,
  event_name,
  params.value.string_value AS Course_Name,
  COUNT(1) AS eventCount,
  COUNT(DISTINCT user_pseudo_id) AS Unique_Users
FROM `<project ID>.<dataset ID>.events_*`,
  UNNEST(event_params) as params
WHERE
  _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
  AND event_name IN ('course_viewed','course_started','course_completed')
  AND params.key='course_name'
GROUP BY 1, 2, 3, 4

Option 2

Alternatively, Looker Studio can also create this with filters rather than a custom query. To do this, simply create a filter that selects the proper Event Names and Event Param Name. Your dimension will be the Event Param Name, and your breakdown dimension will be the Event Name.

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:

Ken Williams
Previous

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

Using BigQuery and Looker Studio with Google Analytics 4