BigQuery GA4 schema – Send Custom GA4 Data to BigQuery

Last Updated: September 9, 2022

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:

analytics property id

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

property id ga4

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

events data table ga4 bigquery

This table is automatically updated for each day of export.

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

BigQuery Links ga4
daily frequency

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

events intraday data table ga4 bigquery

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:

BigQuery Links ga4 1
streaming frequency

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:

Rows and Columns of the GA4 Data Tables in BigQuery

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

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.

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 

the official field reference from Google

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

no option to create your own data tables
no control over the fields that appear in data table

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:

ga4 bigquery project

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

analytics 298851313

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

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

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:

sign in with google

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

Click on the name of your Google Account

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

welcome to query manager

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

type google analytics 4
pick a data source

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

click on 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:

sign with google button

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

Click on the Google account which is linked to your Google Analytics account

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

allow button

You should now see a screen like the one below:

super metrics query manager

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

selected data source google analytics 4

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

supermetrics for google sheets

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

supermetrics schemas tab

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

supermetrics dwh tab 1

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

supermetrics new schema

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

supermetrics new schema name

You should now see your schema listed:

supermetrics new 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:

supermetrics standard schemas

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:

Each schema corresponds to one data table in your BigQuery project

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

supermetrics query tab

You should now see a screen like the one below:

supermetrics query tab 2

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

select properties

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

select properties 2

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

select properties 3
select properties 4

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

select dates

Step-18: Set your date range:

select dates 2

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

select metrics

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

select metrics 2

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

select metrics 3

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

split by

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

split by 2

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

split to rows 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:

number of rows to fetch 10

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

sort rows sessions

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

supermetrics options

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.

supermetrics show all time values

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

supermetrics query manager run

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

supermetrics query manager data table

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

supermetrics query manager save as button

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

supermetrics query manager query name

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

supermetrics query manager save query

Congratulations. 

You have now successfully created your custom schema:

ga4 custom schema top countries by sessions

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.

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:

you are in the right project

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

data transfers bigquery

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

enable the BigQuery Data Transfer API

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

create transfer bigquery

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

enable api

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

choose a data source

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

explore data sources

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

search marketplace
supermetrics google analytics 4

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

Google Analytics 4 by Supermetrics

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

Enrol Google Analytics 4 by Supermetrics

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

Click on Google Analytics 4 by Supermetrics

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

Transfer config name

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

schedule options daily

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:

schedule options start now

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

locked for editing

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

scroll down

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

data set

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.

select your data set

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

connect source button

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

accept agreement

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

continue button

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

schema drop down menu

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

select custom schema

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:

submit button

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

source connected

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

save data transfer bigquery

You should now see a screen like the one below:

transfer custom ga4 data to bigquery

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:

sql workspace bigquery

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

navigate to the data set

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

new data tables added by supermetrics

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

top countries by sessions query data table bigquery

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

top countries by sessions query

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

top countries by sessions query data table bigquery
top countries by sessions query data table schema bigquery

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:

top countries by sessions query data table preview bigquery

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

query top countries by sessions query data table in bigquery

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

#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

#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 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

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

#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 GA

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."



Here’s what we’re going to cover in this training…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

#5 The number 1 reason why conversion optimization is not working for your business.

#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master digital analytics and conversion optimization in record time.



   

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and BeyondSECOND EDITION OUT NOW!
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

About the Author

Himanshu Sharma

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade
error: Alert: Content is protected !!