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

Last Updated: November 11, 2021

If you are looking for the best Supermetrics alternative then you are in the right place. 

Don’t get me wrong, this article is not to bash Supermetrics. I love and use Supermetrics and recommend it to others all the time. They provide one of the best and most exhaustive sets of connectors in the market. 

If you are here then you probably found Supermetrics to be quite expensive to use.  

So, based on popular demand, I did some extensive research to find the best Supermetrics alternative and actually used it for a couple of weeks. 

Instead of providing you with the list of options to choose from, I will give you only one alternative, and that is Dataddo: https://www.dataddo.com/ 

Yes, the name sounds a bit weird (but you can remember it by saying ‘data ddo’) but they are good at what they do and, above all, they are very affordable:

We do not have any business/affiliate relationship with this company. You are not going to find any affiliate link in this article for this company. Nor did anyone from this company ask me to review them. 

The recommendation is based purely on the features, price and personal usage.

You can find the complete list of connectors that Dataddo provides here: https://www.dataddo.com/connectors 

Dataddo also lets you create custom schemas for no additional charge. They provide a free 14 day trial of their tool. No credit card required.

Instead of telling you what Dataddo can do or can’t do (which you can easily find out on their website), I am going to show you, step by step, how I used it to send Google Analytics data to BigQuery without using Google Analytics 360.

Hopefully, this will give you a good idea of what to expect from this tool. 

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

Send Google Analytics data to BigQuery via Datadoddo

Follow the steps below to send Google Analytics data to BigQuery via Datadoddo:

Step-1: Log in to your Google Cloud Console account and then create a new project called ‘Google Analytics’. We are going to use this project for storing Google Analytics data in BigQuery. Alternatively, If you want, you can use an existing project.

Step-2: Navigate to BigQuery https://console.cloud.google.com/bigquery and make sure that the ‘Google Analytics’ project is selected.

Step-3: Click on the ‘Google Analytics’ project ID (this ID could be different in your case):

Step-4: On the right-hand side of your screen, find and click on the button ‘Create Dataset’:

Step-5: Name your dataset (say GA_data_set) and then click on the ‘Create dataset’ button at the bottom:

We are going to use this new data set for storing Google Analytics data.

Step-6: Navigate to https://www.dataddo.com/

Step-7: Click on the button ‘Try DataDo Free’ and then set up your account by following the on-screen instructions:

Step-8: Once you have created your account then navigate to the page: https://app.dataddo.com/

Step-9: You should see a video pop-up like the one below: 

Close it by clicking on the close button.

You should now see a screen like the one below:

Step-10: Scroll-down the screen and then find and click on the ‘Google Analytics’ data source:

Step-11: Click on the ‘Authorize’ button to authorize Dataddo to access your Google Analytics account:

Step-12: Click on the Google account which is associated with your Google Analytics account:

Step-13: Click on the ‘Allow’ button:

Step-14: Select your Google Analytics account, property and view which you want to use for pulling your data into BigQuery:

Step-15: Click on the ‘Next’ button:

Step-16: Name your data source and then select the metrics and dimensions you want to pull in your BigQuery project:

Here you get the chance to create your custom schema. 

Step-17: Click on the ‘Next’ button.

Step-18: You should now see a screen like the one below: 

Again, click on the ‘Next’ button.

Step-19: Click on the ‘Data Warehouse’ 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

Step-20: Keep the recommended setup for your data warehouse and then click on the ‘See Preview’ button:

Step-21: Click on the ‘Save and create flow’ button:

Step-22: Click on the ‘Add Destination’ button to add the destination data source:

Step-23: Scroll down your screen and then click on the ‘Create new destination’ button:

Step-24: Click on the ‘Google BigQuery’ button:

Step-25: Click on the ‘Authorize’ button to authorize Dataddo to access your Google Cloud account:

Step-26: Click on the Google account which is associated with your Google Cloud account:

Step-27: Click on the ‘Allow’ button:

Step-28: Click on the ‘Next’ button:

Step-29: Enter your destination name (e.g. ‘Google Big Query’), select your BigQuery project and the BigQuery data set you created earlier:

Here we selected the project ‘Google Analytics’ and the data set ‘GA_data_set’ which we created earlier.

Step-30: Click on the ‘Connection Check’ button:

You should now see a screen like the one below:

Step-31: Click on the ‘Create Flow’ button at the top right-hand side:

Step-32: Name your data flow (e.g. ‘Google Analytics to BigQuery’):

Step-33: Enter the name of the database table which you want to use for collecting Google Analytics data in BigQuery. 

You can give any name you want:

Step-34: Select the time schedule, timezone and data sync period. 

These settings determine when Dataddo should pull data from your Google Analytics data into BigQuery:

Step-35: Click on the ‘Create Flow’ button at the top right-hand side.

You should now see an overlay like the one below. Do not close this overlay:

This overlay has got important instructions on how to define your data table schema.

Step-36: Navigate back to your Google Cloud BigQuery account and make sure that the ‘Google Analytics’ account is selected:

Step-37: Click on the ‘Google Analytics’ project ID (this ID could be different in your case):

Step-38: Click on the ‘GA_data_set‘ (the data set we created earlier):

Step-39: Click on the ‘CREATE TABLE’ button:

Step-40: Navigate back to your Dataddo account and follow the instructions for setting up your BigQuery data table:

Step-41: According to my instructions, I need to do the following:

1. Set the table name to ‘google_analytics‘
2. Select “Edit as text” and then copy and paste the table definition below:

gadate:DATETIME,

gasourcemedium:STRING,

gasessions:NUMERIC,

gapageviews:NUMERIC,

gausers:NUMERIC,

gaavgtimeonpage:NUMERIC,

gagoalcompletionsall:NUMERIC,

gatransactions:NUMERIC

Step-42: Click on the ‘Create Table’ button.

Step-43: Navigate back to your Dataddo account and click on the ‘Close’ button:

You should now see a screen like the one below:

Step-44: Click on the ‘Force run’ button at the very bottom of your screen:

Step-45: Type CONFIRM in capital letters in the text box and then click on the ‘Insert data to storage’ button:

Step-46: Navigate back to your Google Cloud account and then click on the ‘google_analytics’ data table under the data set ‘GA_data_set’:

Step-47: Click on the ‘Preview’ tab. 

You should now be able to see your Google Analytics data in your BigQuery data table:

Note: Click on the browser refresh button if you don’t see any data in the ‘google_analytics’ data table

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

That’s how you can send Google Analytics data to BigQuery via Dataddo.

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