How to load data from Google Analytics to Google BigQuery
Access your data on Google Analytics
The first step in loading any Google Analytics data you own to any data warehouse solution is to access them and start extracting it.
The Google Analytics Reporting API is the most advanced programmatic method to access report data in Google Analytics. The API also allows you to programmatically interact with your Google Analytics account, creating reports and dashboards that can be viewed from within your GA account and embed them into other applications.
Data from Google Analytics is always coming in the form of a report, which means you have to construct a report and request it from Google Analytics for a specific time period.
Google Analytics is accessed in the same way that every other Google API is. You need to leverage the Google API console to manage applications and access to the various APIs, including Google Analytics.
In addition to the above, the things that you have to keep in mind when dealing with the Google Analytics API are:
- Rate limits - Every API, has some rate limits that you have to respect.
- Authentication - You authenticate on Google Analytics using an OAuth.
- Paging and dealing with big amount of data - Platforms like Google Analytics that are dealing with clickstream data tend to generate a lot of data, like events on your web properties.
About Google Analytics
Google Analytics is a freemium web analytics service offered by Google that tracks and reports website traffic. Google Analytics is the most commonly used service to track traffic on a website and an invaluable tool, especially for marketers.
Google Analytics has evolved into a powerful tool that offers reports about your visitors and a full set of tools to perform clickstream analysis ranging from a live view to complex funnel analytics and event tracking.
The free version of Google Analytics offers a sampled view of data which suffices for most cases as it gives a very accurate view of what is happening. Another option is using Google Analytics Premium, which also grants you access to all the raw events, but this is part of a paid version of the service.
Transform and prepare your Google Analytics Data
After you have accessed data on Google Analytics, you will have to transform it based on two main factors:
- The limitations of the database that is going to be used
- The type of analysis that you plan to perform
Each system has specific limitations on the data types and data structures that it supports. Suppose for example you want to push data into Google BigQuery. In that case, you can send nested data like JSON directly, but keep in mind that every data you get from Google Analytics are in the form of a tabular report closer to what a CSV or a spreadsheet looks like.
When dealing with tabular data stores, like Microsoft SQL Server, this is not an option. Instead, you will have to flatten out your data, just as in the case of JSON, before loading into the database.
Also, you have to choose the right data types. Again, depending on the system you will send data to and the data types that the API exposes to you, you will have to make the right choices. These choices are important because they can limit the expressivity of your queries and limit your analysts on what they can do directly out of the database. Google Analytics has a very limited set of available data types, which means that your work to do these mappings is much easier and straightforward, but equally important with any other data source case case of a data source.
To understand and model Google Analytics data correctly, you will need to understand that any data coming out of it is in the form of a report. The report is like a spreadsheet, and it can be naturally mapped into a table. So more or less, you will end up with a one-to-one mapping between a report and a table on your database.
You also need to keep in mind that you will not find any primary keys that can be used for deduplication and reference because of the report nature of data. This is something that you have to construct by understanding the nature of your reports data.
Also, as Google analytics is sampling data to generate the report, you might see slightly different values if you pull the same report, for the same period, more than once.
Load data from Google Analytics to Google BigQuery
If you want to load Google Analytics data to Google BigQuery, you have to use one of the following supported data sources:
- Google Cloud Storage
- Sent data directly to BigQuery with a POST request
- Google Cloud Datastore Backup
- Streaming insert
- App Engine log files
- Cloud Storage logs
From the above list of sources, 5 and 6 are not applicable in our case.
For Google Cloud Storage, you first have to load data you own into it, and there are a few options on how to do this. For example, you can use the console directly as described here and do not forget to follow the best practices.
Another option is to post data through the JSON API. APIs play an important role in both the extraction and the loading of data into our data warehouse. It’s just a matter of one HTTP POST request using a tool like CURL or Postman in its simplest case.
After you have loaded data into Google Cloud Storage, you have to create a Load Job for BigQuery actually to load data into it. This Job should point to the source data in Cloud Storage that have to be imported, this happens by providing source URLs that point to the appropriate objects.
The best way to load data from Google Analytics to BigQuery
So far, we just scraped the surface of what you can do with BigQuery and how to load data into it. Things can get even more complicated if you want to integrate data coming from different sources.
Are you striving to achieve results right now?
Instead of writing, hosting, and maintaining a flexible data infrastructure, RudderStack can handle everything automatically for you.
RudderStack, with one click, integrates with sources or services, creates analytics-ready data, and syncs your Google Analytics to BigQuery right away.