This project is a collection of SQL statements that can be used to calculate GA4 dimensions and metrics using BigQuery data in Looker Studio reports and dashboards. BigQuery is Google's serverless data warehousing solution. Using BigQuery, it's possible to get around GA4's 14-month data retention policy and data quota limits.
This project is aimed at marketing data analysts, marketing analytics managers, and IT administrators who want to implement SQL-based calculated fields for GA4 metrics using BigQuery data in Looker Studio reports.
The initial research revolved around exploring the BigQuery database schema to understand how GA4 data was being stored. To help understand the relationship between GA4 and BigQuery, I found an online resource that provided some sample GA4 BigQuery calculated fields. Playing around with these sample formulas helped me build out my initial set of GA4 metrics to a full set of 21 dimensions and metrics that span web performance reporting, campaign tracking, and audience analysis.
Data discrepancies between GA4 and BigQuery are a well-known issue. The next phase of this project was validating that the BigQuery data matched within a certain data variance with the GA4 data. If the numbers were way off, I knew I had to fix my SQL statements.
Some of these calculated fields are a combination of other calculated fields. When fine-tuning my SQL statements, what I found helpful was breaking down the SQL statements into their component parts, and verifying the data accuracy of each individual calculated field before combining into larger calculated fields.
Here are the instructions on how to use these GA4 BigQuery calculated fields in a Looker Studio report or dashboard. You can access the SQL file by downloading a copy of the project files from my GitHub repository to your local computer.
Prerequisite: You must have a Google Cloud Console account with a billing-enabled project. A GA4 property should already be connected to your BigQuery instance within your project in Google Cloud Console.