How to Export Google Analytics Data to BigQuery?
What is Google Analytics?
Google Analytics (GA) is a free tool provided by Google to capture visitor insights from your website. It can track the source of the visitor, pages visited and other activities. If you have setup your GA script on your website, then it sends information to Google Analytics.
What is Google BigQuery?
Google BigQuery is Google’s fully managed, petabyte scale, low cost analytics data warehouse. BigQuery is NoOps-there is no infrastructure to manage and you don’t need a database administrator-so you can focus on analyzing data to find meaningful insights, use familiar SQL, and take advantage of Google’s pay-as-you-go model.
Extracting Raw Data from Google Analytics to Google BigQuery.
However, getting data out of Google Analytics is very tricky because the APIs don’t allow us to extract event-level data. Data from Google Analytics is always coming in the form of a report which means it has been aggregated and sampled by Google Analytics.
The paid tier of GA – Google Analytics 360 does give you an option to export all your Google Analytics Data in Google Big Query, but this at a cost of around $150,000 per year. You can try building your own solution but then you should be an expert is Google Analytics API, Google Big Query API and this is just the beginning, you must keep in mind that technologies like Google Analytics and Google Big Query are evolving and you might find what was working yesterday might not work today. Trust me we have been following this space closely.
The Best Way to Extract Raw Data from Google Analytics and load into BigQuery
So far we just scratched the surface of how you can extract raw data from Google Analytics. Things can get even more complicated if you want to integrate data coming from different sources. So instead of building and maintain your own solution or paying $150,000 for Google Analytics 360. Try Electrik.AI’s Google Analytics Hit Level Data Extractor and we will take care of everything in just a few clicks.
Here’s a Sample of the raw Google Analytics data that Electrik.AI will extract from Google Analytics and load it into your analytics warehouse:
Session Id: 496802989.156996
Visitor Id: VIDbdf938fe-8e1f-597e-89aa-775a80d2aae6
Event Date: 10/2/2019
Event Timestamp: 2019-10-02T07:11:19.476+10:00
Event Date in UTC: 10/1/2019
Property Id: UA-34208182-1
View Id: 63209589
Hit Order: 1
Hit Type: pageview
Pageview Order: 1
Browser Size: 1900×980
Channel grouping: Direct
Data Source: web
Landing Pagepath: /
Network Domain: optusnet.com.au
Network Location: optus internet pty ltd
Page Title: Automate ETL Testing, Data Warehouse/Migration Testing
Previous Pagepath: (entrance)
Region: New South Wales
Screen Resolution: 1920×1080
Session Duration: 472
Source Medium: (direct) / (none)
Time on Page: 34
User Type: New Visitor
*For detailed data model/schema please refer Link.
Learn more about Electrik.AI’s Google Analytics Hit Level Data Extractor.
Step by Step guide to Extract Google Analytics Raw Hit Level Data to Big Query.