Google Analytics to Snowflake: Steps to Move Data In Minutes

on Tutorial • February 23rd, 2020 • Write for Hevo

Google Analytics is the most popular web analytics service on the market, used to gather crucial information on website events: web traffic, purchases, signups, and other aspects of browser/customer behavior. However, the vast amount of data that Analytics provides makes it necessary for many users to search for ways to more deeply analyze the information found within the platform. Enter Snowflake, a platform designed from the ground up to be a cloud-based data warehouse. You can read more about Snowflake here. For many users of Analytics, Snowflake is the ideal solution for their data analysis needs, and in this article, we will walk you through the process of moving your data from Google Analytics to Snowflake.

Methods to Move data from Google Analytics to Snowflake

Before we get started, there are essentially two ways to move your data from Analytics to Snowflake:

Method 1: Write a Custom Script

This would need you to understand the Google Analytics API, build a code to bring data from it, clean and prepare the data and finally, load it to Snowflake. This can be a time-intensive task and (let’s face it) not the best use of your time as a developer.

Method 2: Implement a Platform like Hevo Data, an Official Snowflake Data Integration Partner

Hevo, a Data Integration Platform gets the same results in a fraction of time with none of the hassles. Hevo can help you bring  Google Analytics data to Snowflake in real-time without having to write a single line of code.

This article provides an overview of both the above approaches. This will allow you to assess the pros and cons of both and choose the route that suits your use case best

Google Analytics to Snowflake: Writing Custom Code

Step 1 – Accessing Data on Google Analytics

The first step in moving your data is to access it, which can be done using the Google Analytics Reporting API. Using this API, you can create reports and dashboards, both for use in your Analytics account as well as in other applications, such as Snowflake. However, when using the Reporting API, it is important to remember that only those with a paid Analytics 360 subscription will be able to utilize all the features of the API, such as viewing event-level data, while users of the free version of Analytics can only create reports using less targeted aggregate data.

Step 2 – Transforming and Preparing Google Analytics Data for Transferral to Snowflake

Before transferring data to Snowflake, the user must define a complete and well-ordered schema for all included data. In some cases, such as with JSON or XML data types, data does not need a schema in order to be transferred directly to Snowflake. However, many data types cannot be moved quite so readily, and if you are dealing with (for example) Microsoft SQL server data, more work is required on the part of the user to ensure that the data is compatible with Snowflake.

Google Analytics reports are conveniently expressed in the manner of a spreadsheet, which maps well to the similarly tabular data structures of Snowflake. On the other hand, it is important to remember that these reports are samples of primary data, and as such, may contain different values during separate report instances, even over the same time period sampled.

Because Analytics reports and Snowflake data profiles are so similarly structured, a common technique is to map each key embedded in a Report API endpoint response to a mirrored column on the Snowflake data table, thereby ensuring a proper conversion of necessary data types. Because data conversion is not automatic, it is incumbent on the user to revise data tables to keep up with any changes in primary data types.

Step 3 – Transferring Data from Google Analytics to Snowflake

There are three primary ways of transferring your data to Snowflake:

  1. COPY INTO – The COPY INTO command is perhaps the most common technique for data transferral, whereby data files (stored either locally or in a storage solution like Amazon S3 buckets) are copied into a data warehouse.
  2. PUT – The PUT command may also be used, which allows the user to stage files prior to the execution of the COPY INTO command.
  3. Upload – Data files can be uploaded into a service such as the previously mentioned Amazon S3, allowing for direct access of these files by Snowflake.

Step 4 – Maintaining Data on Snowflake

Maintaining an accurate database on Snowflake is a never-ending battle; with every update to Google Analytics, older data on Snowflake must be analyzed and updated to ensure the integrity of the overarching data tables. This task is made somewhat easier by creating UPDATE statements in Snowflake, but you must also take care to identify and delete any duplicate records that appear in the database.

Overall, maintenance of your newly-created Snowflake database can be a time-consuming project, which is all the more reason to look for time-saving solutions such as Hevo.

Google Analytics to Snowflake: Why Use Hevo?

Although there are other methods of integrating data from Google Analytics to Snowflake, those not using Hevo must be prepared to deal with a number of limitations:

  1. Heavy Engineering Bandwidth: Building, testing, deploying, and maintaining the infrastructure necessary for proper data transferral requires a great deal of effort on the end user’s part.
  2. Not Automatic: Each time a change is made in Google Analytics, time must be taken to manually alter the code to ensure data integrity.
  3. Not Real-time: The steps as set out in this article must be performed every single time data is moved from Analytics to Snowflake. For most users, who will be moving data on a regular basis, following these steps every time will be a cumbersome, time-consuming ordeal.
  4. Possibility of Irretrievable Data Loss: If at any point during this process an error occurs say, something changes in Google Analytics API or on Snowflake, serious data corruption and loss can result.

Hevo being an official Snowflake partner, can move Google Analytics data to Snowflake in 2 simple steps: 

  1. Authorize and configure your Google Analytics data source
  2. Configure the snowflake data warehouse where you want to move your data

Hevo will now take care of all the heavy-weight lifting to move data from Google Analytics to Snowflake. Here are some of the benefits of Hevo:

  1. Reduced Time to Implementation: With a few clicks of a mouse, users can swiftly move their data from source to destination. This will drastically reduce time to insight and help your business make key decisions faster. 
  2. End to End Management: The burden of overseeing the inessential minutiae of data migration is removed from the user, freeing them to make more efficient use of their time.
  3. A Robust System for Alerts and Notifications: Hevo offers users a wide array of tools to ensure that changes and errors are detected and that the user is notified as to their presence.
  4. Complete, Consistent Data Transfer: Whereas some data migration solutions can lead to the loss of data as errors appear, Hevo uses a proprietary staging mechanism to quarantine problematic data fields so that the user can fix errors on a case-to-case basis and move this data.
  5. Comprehensive Scalability: With Hevo, it is no problem to incorporate new data sets, regardless of file size. In addition to Google Analytics, Hevo is also able to interface with a number of other analytics, marketing, and cloud applications; Hevo aims to be the one source solution for all your data transfer needs.
  6. 24/7 Support: Hevo provides a team of product experts, ready to assist 24 hours a day, 7 days a week.

For users who seek a more in-depth understanding of their web traffic, moving data from Google Analytics to their Snowflake data warehouse becomes an important feat. However, sifting through this can be an arduous and time-intensive process, a process that a tool like Hevo can streamline immensely, with no effort needed from the user’s end. Furthermore, Hevo is compatible with a wide range of data sources, allowing the user to interface with databases, cloud storage solutions, and more.

Still not sure that Hevo is right for you? Then try our risk-free, expense-free 14-day trial, and experience for yourself the ease and efficiency provided by Hevo Data Integration Platform.

No-code Data Pipeline for Snowflake