How to load data from Google Analytics to Google BigQuery

Blendo Team

This post helps you with loading your data from Google Analytics to BigQuery. If you are looking to get analytics-ready data without the manual hassle you can integrate Google Analytics to BigQuery with Blendo, so you can focus on what matters, getting value out of your analytics data.

Access your data on Google Analytics

The first step in loading any Google Analytics data you own to any kind of 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 but also to embed them into other applications.

Data from Google Analytics is always coming in the form of a report, which means that 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.

You will need more time to read this post than integrating Google Analytics to BigQuery.

Effortlessly Sync All Your Google Analytics Data to BigQuery

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.

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 for tracking the traffic on a website and an invaluable tool, especially for marketers.

Google Analytics has evolved into a very powerful tool, which 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, there’s also the option of 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,

  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. If for example you want to push data into Google BigQuery, then 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 an spreadsheet looks like.

Ofcourse, when you are dealing with tabular data stores, like Microsoft SQL Server, this is not an option. Instead, you will have to flatten out your own 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 that 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 straight forward, but nonetheless equally important with any other case of a data source.

In order to understand and model correctly Google Analytics data, you will need to understand that any data coming out of it are 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 because of the report nature of data, you will not find any primary keys that can be used for deduplication and reference. 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, there are a few options on how to do this, for example, you can use the console directly as it is described here and do not forget to follow the best practices.

Another option is to post data through the JSON API, as we see again APIs play an important role in both the extraction but also the loading of data into our data warehouse. In its simplest case, it’s just a matter of one HTTP POST request using a tool like CURL or Postman.

After you have loaded data into Google Cloud Storage, you have to create a Load Job for BigQuery to actually 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 URIs 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 use Blendo that can handle everything automatically for you.

Blendo with one click integrates with sources or services, creates analytics-ready data and syncs your Google Analytics to BigQuery right away.

Combine website traffic and event data that live in Google Analytics with Sales or Marketing data and supercharge your business insights.

Blendo is the easiest way to automate powerful data integrations.

Try Blendo free for 14 days. No credit card required.