Once you understand how the GA4 data is stored in BigQuery data tables, it will become easier for you to query it.
What is the GA4 BigQuery Export Schema?
The GA4 BigQuery export schema refers to the structure of GA4 and Firebase data that is exported to a Google BigQuery project.
This schema defines how the data is organized within datasets and data tables.
To understand the GA4 BigQuery export schema, you will first need to understand the basic structure of how data is stored in a BigQuery project.
- Google Cloud Platform (GCP) can consist of one or more organizations.
- Each organization can consist of one or more projects.
- One such project can be a BigQuery project. It’s the environment within which all BigQuery datasets and operations reside.
- Each project has a name, project number, and project ID.
- Each project can consist of one or more datasets.
- Each dataset can consist of one or more data tables.
Project ID and Data Sets.
When you click on a project ID, you can see the datasets:
Here, the project id ‘dbrt-ga4’ contains the following data sets:
- GA4_native_backfill.
- analytics_207472454
- analytics_29885131
- analytics_39658446
- custom_ga4
- ga3_data_backup
- google_ads
- historical_ga4_data etc
For every GA4 property and each Firebase project connected to your BigQuery project, a unique dataset called “analytics_<property_id>” is created in your BigQuery project.
The “property_id” is your GA4 Property ID.
The dataset named in the following format: “analytics_<property_id>” is the dataset meant for GA4:
Click on the dataset named in the following format: “analytics_<property_id>” .
Click on the 'Details' tab:
You should now be able to see the data set information like ‘Dataset ID’, Dataset creation date, Last modification date, Data location etc:
Note down the Dataset ID by copying it to the clipboard:
We will reference this dataset ID later when querying the data.
Click on the ‘analytics_’ dataset again.
You should now see the following data tables:
Each dataset consists of one or more data tables.
The ‘analytics_207472454’ dataset contains the following six data tables: