How to pull custom data from Google Analytics to BigQuery

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.

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

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

three dots menu 1

Step-3: Click on ‘Create Dataset’:

create data set bigquery

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

overlay bigquery

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

data set id

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:

create data set data location

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.

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

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:

supermetrics sign in with google

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

chose and account

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

welcome to supermetrics query manager

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

search a data source
search a data source google analytics

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

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

connect to google analytics

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

choose ga account

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

supermetrics want to access

You should now see a screen like the one below:

supermetrics query manager

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

selected data source

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

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

schemas

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

DWH

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

new schema 1

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

new schema name

You should now see your new schema listed:

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:

standard schema supermetrics
list of standard schema supermetrics

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

ua custom schema 1

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

query tab

You should now see a screen like the one below:

UA query

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

Select Views: < select your reporting view >

select views

Select dates: <select your date range>

select dates 1

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

select metrics 1

Split to rows: ‘Country’, ‘Date’:

split to rows

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:

number of rows to fetch

Sort rows: Sessions

sort rows sessions 1

Sort direction: Descending

sort direction descending

Secondary Sort: Automatic

secondary sort automatic

Options: Try to avoid Google’s data sampling:

try to avoid google data sampling

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

entire query setup

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

run button

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

preview data table

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

save as

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

query name

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

save query

Congratulations. 

You have now successfully created your custom schema:

successfuly created 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:

universal analytics bigquery

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

data transfers bigquery 2

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

create data transfer bigquery 1

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:

choose a data source 1

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

google analytics by supermertrics

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

explore data sources 1

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

transfer config name 2

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

schedule options 1

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

start date and run time

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

click on data set

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

select the destination data source

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

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

connect source

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

accept agreement 2

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

continue button 1

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

select schema

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

select custom schema 1

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:

submit button 1

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

source connected 2

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

save data transfer 1

You should now see a screen like the one below:

schedule data transfer

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:

data transfer current status

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

The transfer run has completed successfully 2

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

SQL workspace 2


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

data set 1

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

list of data tables

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

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

successfuly created custom schema

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

top countries by sessions
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:

preview data table 1

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

query button

That’s how you can pull custom data from Google Analytics to 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

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