Last Updated: August 22, 2022

What is custom schema and why do you need one?

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

As a result, Google automatically created a set of tables in the pre-built dataset.

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

If you want to see your data tables with only the fields you want, then you need to create and use 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 GA3 (Universal Analytics) 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 GA3 data that you send to BigQuery via custom schema is called the custom GA3 data.

Overview of pulling custom data from Google Analytics to BigQuery

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

#1 You would first need to create a BigQuery project and the corresponding dataset where you are going to store GA3 data in BigQuery. You can also use an existing project and/or dataset if you want.

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

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

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

#5 Backfill GA3 data in BigQuery.

#6 Query the GA3 data you need in BigQuery.

My Step-By-Step Blueprint For Learning Google Analytics BigQuery

Get the only ebook on Google Analytics BigQuery ever published (60+ Pages)

Yes I want the FREE ebook

#1 Creating a new BigQuery project and data set

Follow the steps below:

Step-1: Create a new BigQuery project and name it ‘Universal Analytics’:

If you are new to BigQuery, then check out this article on creating a new BigQuery project: How to create a new BigQuery project

Step-2: Click on the three dots menu next to your project ID (this ID could be different in your case):

Step-3: Click on ‘Create Dataset’:

You should now see an overlay on the right-hand side:

Step-4: Name your dataset. Let’s say UA_data_set:

Step-5: Select the data location nearest to you from the drop-down menu and then click on the ‘Create dataset’ button at the bottom:

We are going to use this new data set for storing data from GA3.

#2 Deciding the format of how your data table should look in BigQuery.

Before you create a custom schema, you will need to first decide the overall layout and format of what 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.

My Step-By-Step Blueprint For Learning Google Analytics BigQuery

Get the only ebook on Google Analytics BigQuery ever published (60+ Pages)

Yes I want the FREE ebook

Let’s extract Google Analytics data in the following format in BigQuery:

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

From the GA 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 three metrics: ‘Sessions’, “%New Sessions’ and ‘New users’.
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 month (not shown in the GA screenshot above).

#3 Creating custom schema and query

Based on your desired format, 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‘ in the search box:

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

When you click on the ‘Google Analytics’ 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-21: 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) 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 ‘UA Custom Schema‘) and then click on the ‘OK‘ button:

You should now see your 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:

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 ‘UA Custom Schema‘:

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

You should now see a screen like the one below:

Step-15: Set up the various configuration settings of your query like the one below:

Select Views: < select your reporting view >

Select dates: <select your date range>

Select Metrics: ‘Sessions’, ‘New Sessions’ and ‘New users’:

Split to rows: ‘Country’, ‘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.

Number of rows to fetch: 10:

Sort rows: Sessions

Sort direction: Descending

Secondary Sort: Automatic

Options: Try to avoid Google’s data sampling:

The entire Query set-up may look like the one below:

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

My Step-By-Step Blueprint For Learning Google Analytics BigQuery

Get the only ebook on Google Analytics BigQuery ever published (60+ Pages)

Yes I want the FREE ebook

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

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

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

Step-19: 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.

So if you create multiple queries in your custom schema (which is possible) then multiple data tables would automatically be created in your BigQuery project when you use the custom schema while creating a data transfer. 

#4 Creating, configuring and saving your data transfer in BigQuery.

If you want to automatically send GA3 data to your BigQuery project on a regular basis, then you should create, configure and save your data transfer in BigQuery.

Follow the steps below:

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

Step-2: Make sure that you are in the ‘Universal Analytics‘ project:

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

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

Note: If instead of the ‘+CREATE TRANSFER’ button, you see the ‘Enable‘ button and then click on it to enable the ‘BigQuery Data Transfer API. Then click on the link ‘+CREATE TRANSFER‘.

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

Step-6: Click on the ‘Google Analytics by Supermetrics‘ button:

Note: If you don’t see the ‘Google Analytics by Supemertrics’ option in the drop-down menu, then click on the link ‘Explore Data Sources’. Then find, click and enroll ‘Google Analytics by Supemertrics’:

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

Step-8: Keep the ‘Schedule Options’ to ‘Daily‘ and ‘Start Now‘:

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

Step-9: Scroll down and then click on the ‘Data Set‘ field:

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

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

Step-11: Click on the ‘Connect source‘ button:

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

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

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

Step-15: 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-16: Select the Google Analytics view (from which you want to send data to BigQuery) from the ‘Accounts‘ drop-down menu and then click on the ‘Submit‘ button:

You should now see the ‘Source Connected‘ message just below ‘Third party connection‘:

Step-17: 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.

Step-18: Click on the browser refresh button after a couple of minutes have elapsed so that you can see the current status of your data transfer:

Step-19: Keep refreshing your browser window until you see the message ‘The transfer run has completed successfully‘:

#5 Backfilling GA3 data in BigQuery.

Follow the steps mentioned in this article to backfill GA3 (Universal Analytics) data in your BigQuery project: How to backfill Google Analytics data in BigQuery

#6 Querying the GA3 data you need in BigQuery.

Once your data transfer is complete, then follow the steps below to find and query the GA3 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 GA3 data in BigQuery:

You should now be to see the new data table(s) automatically created and added by Supermetrics:

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 ‘UA Custom Schema’ via the Supermetrics Query Manager:

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

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 pull custom data from Google Analytics to BigQuery.

Other articles on Google Analytics BigQuery

1. Advantages of using Google BigQuery for Google Analytics
2. Cost of using BigQuery for Google Analytics
3. Guide to BigQuery Cost optimization
4. What is Google BigQuery Sandbox and how to use it
5. Understanding the BigQuery User Interface
6. Sending data from Google Analytics to BigQuery without 360
7.  How to connect GA4 (Google Analytics 4) with BigQuery
8. events_& events_intraday_ tables in BigQuery for GA4 (Google Analytics 4)
9. Using Google Cloud pricing calculator for BigQuery
10. How to access BigQuery Public Data Sets
11. How to use Google Analytics sample dataset for BigQuery
12. Connect and transfer data from Google Sheets to BigQuery
13. How to query Google Analytics data in BigQuery
14. How to send data from Google Ads to BigQuery
15. What is BigQuery Data Transfer Service & how it works.
16. How to send data from Facebook ads to BigQuery
17. How to send data from Google Search Console to BigQuery
18. Best Supermetrics Alternative – Dataddo
19. Google Analytics BigQuery Tutorial
20. How to backfill Google Analytics data in BigQuery
21. How to connect and export data from GA4 to BigQuery