Introduction
This tutorial delves into the advanced implementation of a Google Analytics 4 (GA4) dashboard in Looker Studio, leveraging BigQuery as the primary data source.
By the end of this guide, you will have constructed a robust acquisition dashboard to facilitate data-driven decision-making for stakeholders. If you are not yet familiar with GA4’s data export schema in BigQuery, it is highly advisable to review the official Google Analytics 4 export schema documentation. This documentation provides crucial insights into column structures, expected values, and nested fields.
For instance, the event_params column consists of an array of records. Each record contains a key (parameter name) and a value, which is a struct that supports multiple data types such as string_value, int_value, and double_value.
Here is the dashboard that we will be constructing.
Filters Ribbon
Filters enhance dashboard interactivity by enabling refined data views based on acquisition attributes. The primary filters included in this dashboard are:
- Acquired Campaign
- Acquired Source
- Acquired Medium
- Date Range Picker
Available Dimensions and Metrics
Below is a comprehensive list of dimensions and metrics accessible via the BigQuery connector:
| Dimension | Type |
|---|---|
| Acquired Campaign | Text |
| Acquired Medium | Text |
| Acquired Source | Text |
| Advertising ID | Text |
| App ID | Text |
| App Install Source | Text |
| App Version | Text |
| City | Geo - City |
| Continent | Geo - Continent |
| Conversion | Boolean |
| Country | Geo - Country |
| Days Since First Touch | Number |
| Device Category | Text |
| Device Model (OS) | Text |
| Event Date | Date |
| Event Name | Text |
| Event Param Value | Number |
| Event Param Value (String) | Text |
| Event Previous Time | Date & Time |
| Event Time | Date & Time |
| Event Time Offset | Number |
| Event Value (USD) | Currency (USD - US Dollar ($)) |
| Firebase App ID | Text |
| First Touch Time | Date & Time |
| Language | Text |
| Limit Ad Tracking Enabled | Text |
| LTV Currency | Text |
| LTV Revenue | Currency (USD - US Dollar ($)) |
| Mobile Brand Name | Text |
| Mobile Model Name | Text |
| Mobile Marketing Name | Text |
| OS | Text |
| OS Version | Text |
| Platform | Text |
| Region | Country subdivision (1st level) |
| Stream ID | Text |
| Time Zone Offset | Number |
| User ID | Text |
| User Property Name | Text |
| User Property Value (Double) | Number |
| User Property Value (Integer) | Number |
| User Property Value (String) | Text |
| User Pseudo ID | Text |
| Event Count | Number |
| Unique Users | Number |
User Overview Section
This section consists of:
- Three scorecards: Total Users, Active Users, and New Users
- A time series chart displaying these metrics over time
Total Users
- Metric: Unique Users
- To display percentage change, enable the Comparison Date Range option and set it to Previous Period.
Active Users
Since July 2023, Google Analytics 4 introduced the is_active_user field in BigQuery exports. If you are using this newer schema, simply apply a distinct count on user_pseudo_id. Otherwise, use the following filter combination:
Filter Conditions:
Event Param Name = engagement_time_msecANDEvent Param Value > 0- OR
Event Param Name = session_engagedANDEvent Param Value = 1
Metric: COUNT DISTINCT on user_pseudo_id
New Users
- Metric: Unique Users
- Filter:
Event Name = first_visit
Time Series Chart for Users
Since this chart contains multiple metrics, a blended data source is required, consisting of three tables:
- Table 1: Total Users (Unique Users)
- Table 2: Active Users (using the filters mentioned above)
- Table 3: New Users (using
first_visitevent filter)
Join key: Event Date
Join type: Left Outer
Sessions Overview Section
This section contains:
- Three scorecards: Sessions, Engaged Sessions, and Engagement Rate
- A time series chart
Sessions
- Metric: Event Count
- Filter:
Event Name = session_start
Engaged Sessions
- Metric: Event Count
- Filter:
Event Param Name = session_engagedANDEvent Param Value = 1
Engagement Rate
- Formula: Engaged Sessions / Sessions
- Use blended data sources with a cross join (no join key required).
Events Overview
This section presents a tabular view of events and their respective event counts.
- Dimension: Event Name
- Metric: Event Count
Pages Overview
This section analyzes page views per page title.
Filters:
Event Name = page_viewEvent Param Name = page_title
Metrics & Dimensions:
- Dimension:
Event Param Value (String)→ Rename to Page Title - Metric:
Event Count→ Rename to Views
Performance Overview
This section visualizes key performance indicators across three charts:
Sessions by Country
- Chart Type: Geo Chart
- Geo Dimension: Country
- Metric: Event Count
- Filter:
Event Name = session_start - Zoom Area: World
Top 5 Countries (Sessions)
- Chart Type: Bar Chart
- Data Source: Duplicate Sessions by Country
- Limit Bars to: 5 (Descending order by Sessions)
Sessions by Source / Medium
- Metric: Sessions (Event Count with
session_startfilter) - Custom Dimension:
CONCAT(Acquired Source, " / ", Acquired Medium)
Conclusion
By following this structured approach, you have developed a dynamic acquisition dashboard in Looker Studio powered by BigQuery. Mastering the manipulation of Google Analytics 4 data in SQL enables advanced analysis, custom metrics development, and deeper insights into user behavior. Continue exploring the dataset to refine your dashboard further and extract meaningful business intelligence.