My Step-By-Step Blueprint For Learning and Mastering Google Analytics 4 (GA4)

Get the FREE e-book on Google Analytics 4 – GA4 (126 Pages)

Yes I want the FREE ebook

Last Updated: February 3, 2023

Note: Before you move forward, make sure that you have connected your GA4 property with a BigQuery project. If you haven’t then check out this article first: GA4 BigQuery – Connect Google Analytics 4 with BigQuery

What is the BigQuery GA4 schema?

GA4 BigQuery Export schema refers to the format and structure of the GA4 data and the Firebase data that is exported to BigQuery.

When you link a GA4 property with a BigQuery project and more than 24 hrs have elapsed, you should see a new dataset named “analytics_<property_id>” automatically added to the linked BigQuery project:

Here the ‘Property ID’ refers to your GA4 Property ID, which you can find in the property settings for your GA4 property:

The dataset named “analytics_<property_id>” contains the following two data tables which store the imported GA4 data in the linked BigQuery project:

#1 events_(<number of days>) – This data table stores all the GA4 event data from the previous day(s). 

This table is automatically updated for each day of export.

Get this 70 page detailed checklist containing screenshots, step-by-step instructions and links to articles

Do you want to set up Google Analytics 4 (GA4) fast and correctly?

Yes I want the ebook

The ‘events_’ data table corresponds to the ‘Daily’ frequency setting of ‘BigQuery Links’ in your GA4 property:

#2 events_intraday_<current date> –  This data table stores all the GA4 event data from the current day:

This table is automatically updated throughout the day.

The ‘events_intraday_’ data table corresponds to the ‘Streaming’ frequency setting of ‘BigQuery Links’ in your GA4 property:

Note(1): The ‘events_intraday_’ data table is automatically deleted at the end of each day once the ‘events_’ data table is complete.

Note(2): The ‘events_intraday_’ data table is not available to you in your BigQuery project, if you are using BigQuery Sandbox.

Rows and Columns of the GA4 Data Tables in BigQuery

Each data table in your BigQuery project is made up of rows and columns:

Each row in the data table corresponds to a single event. 

For example, in the screenshot above, the first row corresponds to the ‘first_visit’ event. 

Similarly, the second row corresponds to the ‘session_start’ event.

Each column (field) in the data table corresponds to an event parameter of GA4 events.

For example, in the screenshot above, the first column corresponds to the ‘event_date’ parameter of the ‘first_visit’ event and the ‘session_start’ event.

Similarly, the second column corresponds to the ‘event_timestamp’ parameter of the ‘first_visit’ event and the ‘session_start’ event.

All the event parameters listed for each GA4 event carry a special meaning. 

For example,

‘event_date’ denotes the date on which the GA4 event was logged.

‘event_timestamp’ denotes the time (in microseconds, UTC) at which the event was logged.

‘event_name’ denotes the name of the event .

‘event_params.key’ denotes the event parameters key.

For the complete list of the various fields in a GA4 data table in BigQuery, check out the official field reference from Google: https://support.google.com/analytics/answer/7029846?hl=en 

Get this 70 page detailed checklist containing screenshots, step-by-step instructions and links to articles

Do you want to set up Google Analytics 4 (GA4) fast and correctly?

Yes I want the ebook

Introduction to custom GA4 schema

When you send data from a GA4 property to BigQuery, you use the default schema (i.e. structure) provided by Google. 

As a result, Google automatically created a set of tables (‘events_‘ and ‘events_intraday_‘) in the pre-built dataset (“analytics_<property_id>“).

You do not get the option of creating the data tables you want or setting the fields you want to see in the data table(s):

When you use the default schema you have no control over the fields (columns) that appear in your data tables and no control over the number and type of data tables you see in your dataset.

If you want to see your data tables with only the fields you want then you need to first create your own schema (also called Custom Schema).

The custom schema that you create depends on the data source being used.

For example, if you are using GA4 as a data source then you define a schema by selecting the dimensions and metrics you want to see in your data table. 

Then you add one or more queries to it.

The GA4 data that you send to BigQuery via custom schema is called the custom GA4 data.

How to send custom GA4 data to BigQuery

Following is the 10,000 foot view of sending custom GA4 data to BigQuery:

#1 You would first need to create a BigQuery project and the corresponding dataset where you are going to store GA4 data in BigQuery. 

You can also use an existing project and/or dataset if you want.

Note: Instead of creating a new project, we are going to use the ‘GA4 BigQuery’ project we created earlier:

We would use the existing dataset named ‘analytics_298851313’ for storing custom data from GA4 into BigQuery:

#2 Figure out the overall layout and format (wireframe) of how your data table should look in the BigQuery.

#3 Based on your wireframe, create a custom schema and query via a third-party solution (connector).

#4 Create, configure and save your data transfer in BigQuery. 

We create data transfer in order to automatically send custom GA4 data to our BigQuery project on a regular basis.

#5 Backfill GA4 data in BigQuery.

#6 Query the GA4 data you need in BigQuery.

How to create a wireframe of your GA4 data table in BigQuery

Before you create a custom schema, you would need to first figure out the overall layout and format of how your data table should look like in the BigQuery.

It’s like creating a wireframe before you actually start designing a website. 

You can create this wireframe either in your head or on a piece of paper. 

You can also use an existing Google Analytics report as a wireframe.

Let’s extract GA4 data in the following format in BigQuery:

Here the screenshot of the GA4 exploration report acts as a wireframe for us.

From the GA4 screenshot above we can determine the following things about, how our data table should look in BigQuery:

1. Our data table should have one primary dimension called ‘Country’.
2. Our data table should have the following four metrics: ‘Total Users‘, ‘New Users‘, ‘Sessions‘ and ‘Engaged Sessions‘
3. The data table needs to have 10 rows.
4. The data in the data table should be sorted by ‘sessions’ in descending order.
5. The data in the data table should be from the last 30 days (not shown in the GA4 screenshot above).

Get this 70 page detailed checklist containing screenshots, step-by-step instructions and links to articles

Do you want to set up Google Analytics 4 (GA4) fast and correctly?

Yes I want the ebook

How to create custom schema and query

Based on your wireframe, create a custom schema via a third-party solution (connector). 

Follow the steps below to create a custom schema:

Step-1: Navigate to Supermetrics Query Manager and then click on the ‘Sign in with Google‘ button:

Step-2: Click on the name of your Google Account:

Step-3: Click on the ‘OK‘ button:

Step-4: Type ‘Google Analytics 4‘ in the search box:

Step-5: Click on the ‘Google Analytics 4‘ data source:

When you click on the ‘Google Analytics 4’ data source, a new tab would open in your browser window.

Step-6: Click on the ‘Sign in with Google‘ button:

Step-7: Click on the Google account which is linked to your Google Analytics account:

Step-8: Click on the ‘Allow‘ button:

You should now see a screen like the one below:

From the screenshot, we can conclude that the new data source (i.e. Google Analytics 4) is now connected to the Query Manager:

Note: The Supermetrics Query Manager works just like Supermetrics for Google Sheets:

Step-9: Click on the ‘SCHEMAS‘ tab:

Step-10: Click on the ‘DWH‘ tab:

Step-11: Click on the ‘+New schema‘ link:

Step-12: Type a name for your custom schema (say ‘GA4 Custom Schema’ and then click on the ‘OK’ button:

You should now see your schema listed:

Note: If you click on the ‘STANDARD‘ button you can see the list of all pre-built queries used by the STANDARD schema:

The lock sign next to ‘STANDARD’ indicates that the standard schema is locked for editing i.e. you can not edit this schema. 

Each schema query corresponds to one data table in your BigQuery project.

So when you create a GA4 data transfer in BigQuery, you will see a separate data table in your BigQuery project for each schema query listed in your Supermetrics Query manager:

Step-13: Click on the ‘QUERY‘ tab:

You should now see a screen like the one below:

Step-14: Click on the ‘Select Properties‘ button:

Step-15: Click on the ‘Select…‘ to open a drop-down menu:

Step-16: Click on the GA4 property you want to extract the custom data from:

Step-17: Click on the ‘Select dates‘ button to set the date range for your data table in BigQuery:

Step-18: Set your date range:

Step-19: Click on the ‘Select Metrics‘ button:

Step-20: Click on the ‘Select…‘ to open a drop-down menu:

Step-21: Select the metrics you want to add to your data table in BigQuery:

For this exercise, I would add the following four metrics one by one: ‘Total Users‘, ‘New Users‘, ‘Sessions‘ and ‘Engaged Sessions‘

Step-22: Click on the ‘Split by‘ button:

Step-23: Click on the ‘Select…‘ to open a drop-down menu:

Step-24: Select ‘Country’ and ‘Date‘ dimensions to splits rows (of the data table in BigQuery) by country and date: 

Note: Make sure that you always select the “Date” dimension. Otherwise, you will not be able to query data for a particular date in BigQuery.

Step-25: Set the number of rows to fetch to ’10’ from the drop-down menu:

Step-26: Set the ‘Sort rows‘ setting to ‘Sessions‘ and ‘Sort Direction‘ setting to ‘Descending‘:

Step-27: Click on the ‘Options‘ button:

Step-28: Select ‘Show all time values’ 

When you are splitting the data by date dimension, using this setting ensures all time values (dates, weeks, months, years) will be returned even if there is no data for some of them.

Step-29: Validate your query by clicking on the ‘Run‘ button:

You should now see a screen like the one below with status 200 OK and the preview of the data table:

Step-30: Click on the ‘SAVE AS‘ button to save this query in your custom schema:

Step-31: Give your query a name (say ‘Top Countries by Sessions’):

Step-32: Select your custom schema from the drop-down menu and then click on the ‘OK‘ button:

Congratulations. 

You have now successfully created your custom schema:

Note: Each query in your custom schema corresponds to one data table in your BigQuery project. 

Let us suppose, you are using a custom schema while creating a data transfer. 

Now if you create multiple queries in your custom schema (which is possible) then multiple data tables would automatically be created in your BigQuery project.

Get this 70 page detailed checklist containing screenshots, step-by-step instructions and links to articles

Do you want to set up Google Analytics 4 (GA4) fast and correctly?

Yes I want the ebook

How to create GA4 data transfer in BigQuery

We create data transfer in order to automatically send custom GA4 data to our BigQuery project on a regular basis.

Follow the steps below to create GA4 data transfer in BigQuery:

Step-1: Navigate to your BigQuery account: https://console.cloud.google.com/bigquery

Step-2: Make sure that you are in the right project:

Step-3: Click on the link ‘Data transfers‘ from the left-hand side navigation:

Step-4: Click on the ‘Enable‘ button to enable the ‘BigQuery Data Transfer API’:

Step-5: Click on the link ‘+CREATE TRANSFER‘:

Step-6: Click on the ‘Enable API‘ button:

Step-7: Click on the drop-down menu to choose a data source:

Step-8: Click on the ‘EXPLORE DATA SOURCES‘ button:

Step-9: Type ‘supermetrics google analytics 4‘ in the search box and then press the enter key:

Step-10: Click on the search result ‘Google Analytics 4 by Supermetrics‘:

Step-11: Click on the ‘ENROL‘ button:

Step-12: Click on ‘Google Analytics 4 by Supermetrics‘:

Step-13: Type a meaningful name for the ‘Transfer config name‘ field:

Step-14: Keep the ‘Repeats‘ setting to ‘Daily‘ to have new data added once a day and move on to the next step:

Step-15: Keep the scheduling options (which specify when the transfer will run) to ‘Start now’ (unless you want to change it) and then move on to the next step:

Note: The ‘Start date and run time‘ setting is locked for editing when the ‘Start now‘ setting is selected.

Step-16: Scroll down until you see a screen like the one below:

Step-17: Click on the ‘Data set‘ field:

Step-18: Select your data set from the drop-down menu:

We are going to use this data set for storing Custom GA4 data in BigQuery.

Step-19: Click on the ‘CONNECT SOURCE’ button:

Step-20: Click on the ‘Accept Agreement‘ button:

Step-21: Click on the ‘Continue‘ button:

Step-22: Click on the ‘Schema‘ drop-down menu:

Step-23: Select the custom schema you created earlier from the drop-down menu:

Note: If you do not see the schema drop-down menu then cancel the data transfer and try again after 24 hrs. 

Step-24: Click on the ‘Submit‘ button:

You should now see the ‘source connected‘ message just below ‘Third party connection‘:

Step-25: Click on the ‘SAVE‘ button to save the transfer and also start the initial data transfer:

You should now see a screen like the one below:

Note: If after following the instructions your data transfer is still not created then refresh the browser window and then recreate the data transfer.

How to backfill GA4 data in BigQuery

Follow the steps mentioned in this article to backfill GA4 data in your BigQuery project: How to backfill GA4 data in BigQuery.

How to query the GA4 data you need in BigQuery

Once your data transfer is complete then follow the steps below to find and query the GA4 data in BigQuery:

Step-1: Click on the ‘SQL workspace‘ link from the left-hand side navigation:

Step-2: Navigate to the data set that you created earlier for storing GA4 data in BigQuery:

You should now be to see the new data table(s) automatically created and added by Supermetrics (in addition to ‘events_’ and ‘events_intraday_’ data tables):

Step-3: Click on the data table corresponding to the custom query we created earlier to see what data it contains

If you remember, we created the ‘Top Countries by sessions’ query using the ‘GA4 Custom Schema’ via the Supermetrics Query Manager:

The BigQuery data table corresponding to the ‘Top Countries by sessions’ query is GAWA_TOP_COUNTRIES_BY_SESSIONS_20220809

Note: Since our custom schema contains only one query, therefore only one data table is automatically created and added. Had our custom schema got multiple queries (say four queries) then four data tables would have been automatically created and added.

Step-4: Click on the ‘Preview‘ tab to preview the data table:

At this point, you can query a particular set of data by clicking on the ‘Query’ button:

That’s how you can send custom GA4 data to BigQuery.

Other articles related to GA4 (Google Analytics 4)

#1 Google Analytics 4 Intro

1. What is GA4 (Google Analytics 4) – The Apps + Web Property?
2. Key Benefits of Using Google Analytics 4 (GA4)
3. Setup GA4 – Upgrade to GA4 – Implementation Guide
4. Google Analytics 4 (GA4) vs Universal Analytics – What is the Difference?
5. GA4 vs GA4 360 – Pricing, Limits, Billing and More
6. Google Analytics 4 Training & Tutorial with FREE GA4 ebook

#2 Google Analytics 4 Property

1. Google Analytics Account Hierarchy (Structure Explained)
2. Understanding Google Analytics Measurement ID (GA4)
3. Google Signals GA4 – See Demographics (Gender, Age) in Google Analytics 4
4. Using the GA4 (Google Analytics 4) Test Property
5. Google Analytics 4 Sub Properties Tutorial
6. Roll up Property in Google Analytics 4 (GA4) – Tutorial

#3 Google Analytics 4 Integrations

1. How to connect GA4 (Google Analytics 4) with Google Data Studio
2. How to link GA4 (Google Analytics 4) with Google Ads
3. How to link Google Search Console to Google Analytics 4 (GA4)
4. How to Install Google Analytics 4 on Shopify
5. GA4 Firebase Integration – Correctly Add App Data Streams to GA4 Property

#4 Google Analytics 4 Events

1. GA4 (Google Analytics 4) Event Tracking Setup Tutorial
2. Understanding Event Parameters in Google Analytics 4 (GA4)
3. Recommended Events in Google Analytics 4 (GA4)
4. Enhanced Measurement Events in Google Analytics 4 (GA4)
5. Automatically Collected Events in Google Analytics 4 (GA4)
6. How to Set Up GA4 Custom Events via Google Tag Manager
7. Events Report in Google Analytics 4 (GA4)
8. How to Rename Events in Google Analytics 4 (GA4)
9. How to Use Google Analytics 4 Event Builder
10. GA4 Form Interactions Tracking – Enhanced Measurement

#5 Google Analytics 4 Conversions

1. Google Analytics 4 Conversion Tracking Guide – GA4 Goals
2. How to Import Conversions from GA4 Property to Your Google Ads account
3. GA4 Conversion Rate – How to find it and use it

#6 Google Analytics 4 Dimensions

1. GA4 (Google Analytics 4) Dimensions Tutorial
2. GA4 (Google Analytics 4) Custom Dimensions Tutorial
3. GA4 User Properties (User Scoped Custom Dimensions) – Tutorial
4. Event Scoped Custom Dimensions in GA4 – Tutorial

#7 Google Analytics 4 Metrics

1. GA4 (Google Analytics 4) Metrics Tutorial with Free Google Analytics 4 Ebook
2. GA4 (Google Analytics 4) Custom Metrics Tutorial
3. What are Predictive Metrics in Google Analytics 4 (GA4)

#8 Google Analytics 4 Ecommerce

1. GA4 (Google Analytics 4) Ecommerce Tracking via GTM – Tutorial

#9 Google Analytics 4 Specialized Tracking

1. GA4 (Google Analytics 4) Enhanced Measurement Tracking Tutorial
2. Cross Domain Tracking in GA4 (Google Analytics 4) Setup Guide
3. GA4 Site Search – Tracking Site Search in Google Analytics 4
4. GA4 (Google Analytics 4) Scroll Tracking Tutorial
5. Self-referral Google Analytics 4 – Referral exclusion GA4
6. GA4 (Google Analytics 4) Data Import Tutorial
7. Google Analytics 4 Content Grouping – Create Content Groups in GA4
8. How to Track Single Page Apps in Google Analytics 4 (GA4)
9. utm_source, utm_medium, utm_campaign Parameters – GA4 (Google Analytics 4)
10. GA4 Form Tracking via Google Tag Manager

#10 Google Analytics 4 filters

1. GA4 filters – Understanding Data Filters in Google Analytics 4
2. How to Create and Test Filters in Google Analytics 4 (GA4)?
3. Exclude Internal Traffic in GA4 (Google Analytics 4) via IP Filter

#11 Google Analytics 4 Explorations

1. Free Form Report in GA4 (Google Analytics 4) – Exploration Report
2. How to Use the User Lifetime Report in Google Analytics 4 (GA4)
3. How to Use Path Exploration Report in GA4 (Google Analytics 4) – Path Analysis
4. How to Use Segment Overlap Report in Google Analytics 4 (GA4)
5. How to Use the Funnel Exploration Report in GA4 (Google Analytics 4) – Funnel Analysis
6. Cohort Exploration Report in Google Analytics 4 (GA4)
7. How to Create Landing Pages Report in GA4 (Google Analytics 4)
8. How to Create Google Ads report in GA4 (Google Analytics 4)
9. How to Segment GA4 Data by Data Stream
10. Organic Search Traffic Analysis in GA4 – Complete Guide
11. Google Analytics 4 (GA4) Outbound Links Tracking
12. How to Track Email Campaigns and Traffic in GA4 
13. How to view full page URLs in GA4?

#12 Google Analytics 4 Advanced

1. Understanding Google Analytics 4 Sessions
2. GA4 (Google Analytics 4) Measurement Protocol Tutorial
3. How to Build Comparisons (Advanced Segments) in Google Analytics 4 (GA4)
4. Understanding Automated Insights in Google Analytics 4 (GA4)
5. Understanding Channel Groupings in Google Analytics 4 (GA4)
6. Understanding Data Sampling in Google Analytics 4 (GA4)
7. Google Analytics 4 Regex (Regular Expressions) Tutorial
8. Google analytics 4 GDPR compliance checklist

#13 Google Analytics 4 Reports

1. How to Create Custom Insights in Google Analytics 4 (GA4)
2. How to Use Debug View Report in Google Analytics 4 (GA4)

#14 Google Analytics 4 Attribution

1. Guide to Attribution Models in GA4 (Google Analytics 4)
2. How to Change Attribution Models in GA4 (Google Analytics 4)?
3. GA4 (Google Analytics 4) Conversion Paths Report in Attribution
4. GA4 (Google Analytics 4) Model Comparison Report in Attribution
5. Advertising Snapshot in GA4 (Google Analytics 4) Attribution
6. GA4 Attribution Modelling Tutorial

#15 Google Analytics 4 Audiences

1. GA4 Audiences – Creating Custom Audience in Google Analytics 4
2. How to Create a Remarketing Audience in Google Analytics 4 (GA4)
3. Understanding Audience Triggers in Google Analytics 4 (GA4)
4. Google Analytics 4 (GA4) Predictive Audiences – Tutorial

#16 Google Analytics 4 BigQuery

1. GA4 BigQuery – Connect Google Analytics 4 with BigQuery
2. BigQuery GA4 Schema – Send Custom GA4 Data to BigQuery
3. How to Backfill GA4 Data in BigQuery
4. How to Connect and Export Data from GA4 to BigQuery