Press "Enter" to skip to content

Google Analytics to BigQuery ETL: Steps to Move Data in Minutes

Google Analytics to bigquery blog banner.Google Analytics is one of the most widely used services when it comes to tracking website traffic, individual customer session data, purchasing information, along with other metrics. The vast amounts of data produced by Google Analytics presents an excellent opportunity for extracting useful and actionable business intelligence. However, as is, Google Analytics does not present an ideal format for analysing all the data that it produces. Hence, it becomes necessary to move data from the application to a Data warehouse, i.e move data from Google Analytics to BigQuery. This, in turn, enables deeper analysis. 

BigQuery is a data warehouse solution provided by Google that provides for very fast SQL-like queries of those extremely large datasets. The speed and efficiency of queries are due to the use of Google’s vast infrastructure and proprietary technology, such as the Dremel query engine. In this post, you will learn how to move data from Google Analytics to BigQuery. 

Two ways to move data from Google Analytics to BigQuery

  • Using a fully-managed data pipeline platform such as Hevo DataHevo comes pre-built with integration for both Google Analytics and Google BigQuery. What this means is easy and seamless integration right out of the box. With a few simple clicks, a sturdy data replication setup can be created between the applications. Hevo is a fully managed platform so that means that no coding or maintenance will be needed from your end. Hevo will handle the groundwork while your analysts can work with BigQuery to answer the big questions.
  • Using the BigQuery Data Transfer Service:

    The BigQuery Data Transfer Service is Google’s native intra-product data pipeline service. It automates the loading of data into BigQuery. The service works exclusively for migrating data from a number of Google services such as Google Analytics 360 and Google Ad Manager to BigQuery.

Let’s go through these options.

Method 1: Migrating Data from Google Analytics to BigQuery Using Hevo

The Hevo Data Integration Program can be set up to stream data from Google Analytics to BigQuery in 2 simple steps:

  1. Configure the data source by authenticating Google Analytics:
    A screenshot of configuring source for google analytics data export in Hevo..
  2. Configure the BigQuery warehouse where you want to move your Google Analytics data to:
    A screenshot of configuring Bigquery as destination in Hevo.

The resulting Hevo data pipeline will now reliably move Google Analytics data to the BigQuery warehouse for further analysis.

Method 2: Moving Data from Google Analytics to BigQuery using Google’s BigQuery Data Transfer Service

To begin with, Google Analytics 360 and BigQuery are both Google products. That being the case, it is not surprising that there is also a pre-existing data migration solution for getting information from one product to the other. This is known as the BigQuery Data Transfer Service. However, this solution is not the simplest, nor the easiest to implement. 

In order to use the BigQuery Data Transfer Service for migrating data from Google Analytics to BigQuery, you must first setup BigQuery Export. To do this:

  1. Create a Google-APIs-Console project
    • This requires logging into the Google APIs Console and either creating a new project or selecting an existing one.
  2. Enable BigQuery within the project
    • Edit the API Library settings of the chosen project to enable the BigQuery API.
  3. Setup Billing for the project
    • Ensure that a billing account has been setup. This is required to start using the service for data replication. Billing may be validated by creating a data set in your BigQuery project. If the data set is created, with no errors, then billing has been set up correctly.
  4. Add the Service account to the project
  5. Link BigQuery to Google Analytics 360
    • You would need to have an email address which has (a) EDIT access to Google Analytics Property and view you need (b) OWNER access to BigQuery project. 
    • In the Admin panel → Property, link BigQuery by entering your project Id

Once completed, data transfer will commence within 24 hours. 

Potential Issues with Migrating Data from Google Analytics to Bigquery Using the BigQuery Data Transfer Service

Despite the native status of the BigQuery Data Transfer Service and the BigQuery Export, this solution has its drawbacks.

Limited Updates Set by Google

When using Google’s data streaming option the number of updates to the data warehouse is determined and set by Google. If you are looking to get data streaming in real-time, this could be a limitation. 

Pipeline Maintenance

Exporting via the BigQuery Data Transfer Service requires that several key components be completed and maintained.

  1. The Google Analytics service account must always have EDIT access to the project. If for whatever reason this is changed then all proceeding exports will fail until permission has been restored.
  2. If the BigQuery API was somehow disabled in the project settings then the exports will fail.

Failure in any of these instances would result in the most serious consequences:

  1. Irretrievable Data Loss

    The greatest drawback of using the BigQuery Data Transfer Service is the risk of data loss. If any of the previously mentioned issues were to occur, or anything else that may cause an interruption in the data export, then that data would be lost, permanently. The BigQuery Data Transfer Service has no facilities to mitigate data loss as a result of a failed export. Hevo, on the other hand, maintains all problematic data exports within a staging area for you to review and recover.

  2. Limited Scope and Usability

    Another major issue with using the BigQuery Data Transfer Service is its limitation regarding integration. It cannot be used to integrate platforms and services outside of Google. In fact, there are even some Google services, such as Google Drive, that BigQuery does not integrate with.

  3. Limited Scope for Transforming Data

    Let’s say you want to change the time from PST to UTC while moving the data from Google Analytics to BigQuery, or you do not want to move data of specific campaigns. With BigQuery Data Transfer Service you will not be able to achieve these simple modifications too.

Advantages of Using Hevo 

The Hevo data integration platform lets you move data from Google Analytics to BigQuery seamlessly. Here are some other advantages:

  • Zero Data Loss – Hevo’s unique fault-tolerant architecture is built to ensure completeness of data. Hevo ensures that data is reliably moved from Google Analytics to BigQuery without data loss. 
  • Unlimited Integrations – Hevo can connect to any source and any destination. All while providing a common interface.
  • Low time to implementation – Once the simple setup procedure is complete, Hevo can migrate data from Google Analytics to BigQuery in no time 
  • Automatic schema detection, mapping, and evolution – Hevo analyses the schema of the data it receives for replication and automatically maps said data seamlessly onto the BigQuery table structure.
  • Fully Managed – The Hevo platform is fully managed and works out of the box. This will let you focus on extracting insights from your data and not worry about data availability.
  • Alerts and Notification – If any issues occur in the data replication Hevo automatically notifies the relevant stakeholders of your team with real-time alerts via email or Slack, allowing them to take timely action.
  • Scalability – Hevo is built to handle data of any scale. With Hevo, your business can grow without any data hiccups. 
  • Exceptional Support – Technical support for Hevo is provided on a 24/7 basis over both email and Slack.

Effective business intelligence requires accurate and up-to-date data. Hevo ensures that your access to this data is never compromised. Hevo ensures that the data is accurately moved from Google Analytics to BigQuery in real-time. 

What’s more? Hevo integrates with a wide array of data sources such as Cloud Applications,  Cloud Storage, Databases and more (www.hevodata.com/integrations), opening the door for a wide range of future possibilities that your business may need.

Sign up for a 14-day free trial here and experience efficient and effective data loading from Google Analytics to BigQuery.

ETL Data to Redshift, Bigquery, Snowflake

Move Data from any Source to Warehouse in Real-time

Sign up today to get $500 Free Credits to try Hevo!
Start Free Trial