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:

1. Rate limits - Every API, has some rate limits that you have to respect.
2. Authentication - You authenticate on Google Analytics using an OAuth.
3. 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.

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:

1. The limitations of the database that is going to be used
2. 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:

1. Google Cloud Storage
2. Sent data directly to BigQuery with a POST request
3. Google Cloud Datastore Backup
4. Streaming insert
5. App Engine log files
6. 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.