Google Ads to Redshift: Steps to Move Data in Minutes

on Tutorial • February 24th, 2020 • Write for Hevo

Your business uses Google Ads heavily to acquire more customers and build your brand. Given the importance of this data, moving data from Google Ads to a robust data warehouse Redshift for advanced analytics is a step in the right direction. In this blog, you will learn how to move data from Google Ads to Redshift for deeper analysis. This blog will discuss two approaches so that you can weigh your options and choose wisely.

Understanding Google Ads

Google Ads is an online advertising platform that allows businesses to showcase highly personalized ads in various formats – text ads, video ads, image ads. Advertising copy is placed on pages where Google Ads think is relevant. Businesses can choose to pay Google basis a flexible model (Pay per click or Pay for the advertisement shown).

Given the reach that Google has, this has become one of the most favorite advertising channels for modern marketers.

Understanding Amazon Redshift 

AWS Redshift is a data warehouse managed by Amazon Web Services (AWS). It is built using MPP (massively parallel processing) architecture and has the capacity to store large sets of data and perform advanced analytics. Designed to run complex analytical workloads in a cost-efficient fashion, Amazon Redshift has emerged to be popular cloud data warehouse choices for modern data teams. You can read more on Amazon Redshift here. 

Methods of loading data from Google Ads to Redshift

  1. By Building ETL Scripts
    The broad steps are as follows: (A) Extracting data from Google Ads (B) Cleaning and filtering data as per your use case (C) Finally, loading data into Redshift. This method would need a huge investment on the engineering side. A group of engineers would need to understand both Google Ads and Redshift ecosystems and hand code a custom solution to move data.
  2. Using Hevo Data – A Fully Managed Data Pipeline Platform
    Hevo comes pre-built with integration for both Google Ads and Redshift. With a few simple clicks, a sturdy data replication setup can be created between Google Ads and Redshift. Since Hevo is a managed platform, you would not need to invest in engineering resources. Hevo will handle the groundwork while your analysts can work with Redshift to uncover insights.

This blog will discuss the above two approaches in detail. In the end, you will have a deep understanding of both and you will be able to make the right decision by weighing the pros and cons of each. 

Google Ads to Redshift: Using Custom Code

Extracting Data from Google Ads 

Applications interact with the Google Ads platform using Google Ads API.  The Google Ads API is implemented using SOAP (simple object access protocol) and doesn’t support RESTful implementation. 

A number of different libraries are offered that could be used with many programming languages. The following languages and frameworks are officially supported. 

  • Python 
  • PHP 
  • JAVA 
  • .NET 
  • Ruby 
  • PERL 

Google Ads API is quite complex and exposes many functionalities to the user. One can pull out a number of reports using Google Ads API. The granularity of the results you would need can also be specified by passing specific parameters. You can decide the data you want to get in two ways. 

  • By using an AWQL-based report definition 
  • By using XML-based report definition 

Most Google Ads APIs are queried using AWQL which is similar to SQL. The following output formats are supported. 

  • CSV – Comma separated values format 
  • CSV FOR EXCEL – MS excel compatible format 
  • TSV – Tab separated value 
  • XML – Extensible markup language format 
  • GZIPPED-CSV – Compressed csv 
  • GZIPPED-XML – Compressed xml 

You can read more about data extraction from Google Ads here.

Once you have the necessary data extracted from Google Ads, the next step would be to load it into Redshift. 

Loading Google Ads data to Redshift 

As a prerequisite, you will need to create a Redshift table and map the schema from the extracted Google Ads data. When mapping the schema, you should be careful to map each attribute to the right data types supported by Redshift. Redshift supports the following data types. 

  • INT 
  • SMALLINT 
  • BIGINT 
  • DECIMAL 
  • VARCHAR 
  • CHAR 
  • DATE   
  • TIMESTAMP 
  • REAL 
  • DOUBLE PRECISION 
  • BOOLEAN 

Design a schema and map the data from the source. Follow the best practices published by Amazon when designing the Redshift database. 

While Redshift allows us to directly insert data into its tables, this is not the most recommended approach. Avoid using the INSERT command as it loads the data row by row. This slows the process because Redshift is not optimized to load data in this way. Instead, load the data to Amazon S3 and use the copy command to load it to Redshift. This is very useful, especially when handling large volumes of data. 

Google Ads to Redshift Using Custom Code: Limitations and Challenges 

  1. Accessing Google Ads Data in Real-time:  After successfully creating a program that loads data from Google ads to Redshift warehouse, you will be required to deal with the challenge of loading new and updated data. You may decide to replicate the data in real-time each time a new row or updated data is created. This process is slower and resource-intensive. Therefore, you will be required to write additional code and build cron jobs to run this in a continuous loop.  
  2. Infrastructure Maintenance: Google ads may update their APIs or something may break at Redshift’s end unexpectedly. In order to save your business from irretrievable data loss, you will be required to constantly maintain the code and monitor the health of the infrastructure.
  3. Ability to Transform: The above approach only allows you to move data from Google Ads to Redshift as is. In case you are looking to clean/transform the data before loading to the warehouse – say you want to convert currencies or standardize time zones in which ads were run, this would not be possible using the previous approach.

Hevo Data – A Hassle-Free Option to Move Data from Google Ads to Redshift

Hevo can move data from Google Ads to Redshift seamlessly in 2 simple steps: 

  • Authorize and configure your Google Ads data source

Adding Google Ads Data Source on Hevo

  • Configure the Redshift data warehouse where you want to move your data

Adding Google Redshift as a Destination

Hevo will now take care of all the heavy-weight lifting to move data from Google Ads to Redshift.

Advantages of Using Hevo: 

  1. No Data Loss – Hevo’s fault-tolerant architecture ensures that data is moved reliably from Google ads to Redshift.  
  2. 100’s of Out of the Box Integrations – In addition to Google Ads, Hevo brings data from the Databases, Cloud Applications, SDKs and so on into Redshift. Therefore, you will always have a reliable partner to cater to your growing data needs.  
  3. Minimal Setup – Minimum requirement is required to set up the Hevo platform because it is fully managed.  
  4. Automatic Schema Detection and Mapping – The schema of incoming Google Ads is automatically scanned by Hevo. In case of any change, Hevo handles it seamlessly by incorporating the change on Redshift. 
  5. Exceptional Support –  Hevo’s 24/7 support ensures you will always have the technical support over both email and chat is provided.  

In order to do advanced data analytics effectively, you will require to have reliable and updated Google Ads data. Sign up for a 14-day free trial with Hevo for faster and consistent delivery of Google Ads data into the Redshift.

What are your thoughts on moving data from Google Ads to Redshift? Let us know in the comments.

No-code Data Pipeline for Redshift