How to Move Data from Google Ads to Redshift

You might be using Google Ads, one of the best advertising platforms, to build your brand awareness or acquire more customers. Given the importance of data Google Ads collects about the campaigns, it can be incredibly valuable for decision-making. This is where a robust data warehouse like Redshift plays an important role in helping you centralize data for advanced analytics. By moving your data from Google Ads to Redshift, you can seamlessly extract valuable insights to uplift your ad campaigns and achieve better results.

In this article, we’ll start with a brief introduction to Google Ads and Redshift. Then, we will explore the detailed methods to export your data from Google Ads to Redshift using three different approaches.

Google Ads Overview

Image Source

Google Ads is an online advertising platform that allows you to promote your products and services. Depending on your campaign type and goals, it displays ads in a wide range of formats, such as text, image, video, and call-only ads. With precise targeting options like keywords, interests, demographics, and location, you can create highly focused campaigns. Leveraging these options enables you to display your ads to potential customers, making it valuable for reaching people with specific interests.

Here are some of the top features of Google Ads:

Track Audience Performance: Google Ads offers a complete overview of your campaign, ad group, and account-level performance. This also helps you to track and manage all your marketing activities in one place. 

Pricing Plans: Google Ads runs on a pay-per-click (PPC) plan, which means you'll pay when a visitor clicks your ad. You can also adjust your advertising budget or pause your ads whenever you need. It allows you to set a flexible budget that can be controlled with per ad, per day, and monthly plans, and partnering with a paid search agency can help you maximize the effectiveness of your campaigns and ad spend.

Redshift Overview

Image Source

Redshift, developed by AWS, is a cloud-based data warehousing service designed to deliver high performance and scalability for analyzing massive datasets. Its architecture is optimized for columnar data storage, enabling swift processing of large data volumes compared to traditional row-oriented storage. Redshift leverages parallel processing across multiple nodes to execute complex queries quickly, even on a huge volume of data. By distributing and processing queried data in parallel across multiple nodes, you achieve faster query outcomes, enabling more efficient data analysis. This makes it an ideal choice for data warehousing and handling voluminous analytics workloads.

Here are some of the key features of Redshift:

Seamless Integration: Redshift allows you to seamlessly integrate with other AWS services, such as Amazon Athena, AWS Glue, and many more. This integration simplifies data ingestion from several sources, helping you build an end-to-end data pipeline.

Materialized Views: Redshift supports materialized views, which store precomputed result sets of queries, enhancing query performance for frequently accessed queries.

3 Ways to Move Data from Google Ads to Redshift

There are various methods available for transferring data from Google Ads to Amazon Redshift. Here are some of the most common methods used to integrate these platforms:

• Method 1: Manually Moving Data from Google Ads to Redshift
  
• Method 2: Move Google Ads Data to Redshift Using Google Ads API
  
• Method 3: Using SaaS Alternatives Like Estuary 

Method 1: Manually Moving Data from Google Ads Data to Redshift

Moving data from Google Ads to Redshift involves manual integration as well as technical knowledge of both platforms. Let’s dive into the step-by-step process.

Step 1: Convert Your Data From Google Ads to Spreadsheet (CSV) Format

• Log in to your Google Ads account, and navigate to the data that you want to export. You can choose to export selected campaigns, ad groups, or the current view with the required customization. Then modify the selected data in the exact way that you want it to be exported from Reports. You can filter the date, apply a segment, add secondary dimensions, or apply a table filter.
  
• Click on the Download button. 

Image Source

• A pop-up window will appear, choose the CSV file format to download the file to your computer.

Step 2: Uploading Google Ads to Redshift

Prerequisites: 

• An S3 bucket for staging temporary files. 
  
• An AWS root or IAM user with read and write access to the S3 bucket. 
  
• AWS CLI

The next step includes cleaning and transforming the .csv file and uploading the structured CSV to an AWS S3 bucket. In order to store data in Redshift, you must structure the data in the right format. Therefore, you need to clean the data before uploading it to Redshift. These changes have to be done manually.

• To upload CSV files into the Amazon S3 bucket from your local machine, install the AWS CLI. The AWS CLI is one of the simple ways to upload files into the S3 bucket. 
  
• To upload a .csv file to S3, you’ll need to provide the source and destination to the aws S3 CP command.

plaintext
AWS S3 CP D:\outputfile.csv S3://S3bucket_name/outputfile.csv

• On execution of the above command, the CSV file will be moved into the S3 bucket. Now run the below command to move CSV file data from the S3 bucket to Amazon Redshift with the COPY command.

plaintext
COPY db_name.table_name 
FROM ‘S3://S3bucket_name/outputfile.csv’ 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
csv;

• The COPY-FROM command is used to move the data into Amazon Redshift from the Amazon S3 bucket. ‘aws_iam_role’ will authenticate your AWS account id and role. Because you are loading in CSV file format, add CSV at the end of the command. 
  
• You can check if all the data is successfully copied in Redshift by using STL_LOAD_ERRORS. This command stores the history of all the records and shows encountered errors while loading the data from the file.

While using the CSV approach looks straightforward, it involves several steps to move data. It includes manually downloading of CSV files and copying them from the local machine to S3 and loading them into Redshift. In addition, you need to upload data continuously whenever you want to update it in Redshift. This can be an impractical approach for real-time scenarios and lead to data latency. 

Method 2: Move Google Ads Data to Redshift Using Google Ads API

To move data from Google Ads to Amazon Redshift, you can also use Google Ads API, which enables interaction with the Google Ads platform directly. The API can be called either using REST or gRPC to get campaign and ad reports. However, to perform the data extraction and loading into Redshift, you must write manual scripts. 

Here's an overview of the steps involved in this process:

• Set up and Enable Google Ads API Account: Create a Google Developer Account and a new project specifically for the Google Ads API. This will allow you to access the Google Ads API and manage your API projects. In the Developers Console, navigate to the API Library, search for Google Ads API, and enable it for your project. Obtain the necessary credentials, including the Developer Token, Client ID, Client Secret, and Refresh Token. These credentials are essential for authentication and accessing data from the Google Ads platform. Use these credentials in your script to authenticate API requests.
  
• Set up an Amazon Redshift Account: Go to AWS Management Console and create an Amazon Redshift cluster. Select the appropriate cluster type, node type, and number of nodes based on your data size. Ensure the cluster has the necessary permissions to allow data ingestion. This includes setting up AWS IAM policies and roles. Then obtain the connection details, including the endpoint, port number, database name, and password.
  
• Write Custom Script: After setting up both your Google Ads and Redshift accounts, the next step is to establish the connection between these services. Install the necessary Python libraries like google-ads and boto3 to interact with Google Ads API and Amazon Redshift, respectively. Then you can start writing custom scripts to extract data from Google Ads API and load it into Redshift. The custom code should include OAuth authentication credentials, define data extraction with the time period, and specify the data loading schema.
  
• Test the Custom Script: After successfully writing the script, it is important to test it in order to ensure that it is executing properly. Initially, you can try with a small dataset and gradually move to larger datasets. However, this process of incorporating massive datasets can be burdensome and time-consuming.

While using a custom script process might look trouble-free, it requires a certain level of technical expertise in installing, developing, and working with APIs. Writing custom scripts can be challenging and time-consuming if you don't have programming experience. Additionally, you need to continuously monitor and maintain the code. 

Method 3: Using SaaS Tools Like Estuary 

If you are looking for a no-code and seamless alternative to move data from Google Ads to Redshift, using SaaS alternatives like Estuary Flow can be an ideal choice. It is a reliable data integration platform that allows you to connect a wide range of data sources and destinations, including Google Ads and Redshift. 

Flow uses Change Data Capture (CDC) to extract data from Google Ads and load it into Redshift in real time, eliminating the need for repetitive and manual tasks. This ensures your data received from Google Ads in Amazon Redshift is always up to date. 

Let’s explore the step-by-step process in detail:

Step 1: Capture your Data From the Source.

• Sign in to your Estuary account or sign up for free. Once you’ve logged in, click on Sources.

Image Source

• In the Sources window, click on + NEW CAPTURE.

Image Source

• You'll be directed to the Create Capture page, search for Google Ads in the search connectors box, and click on Capture.

Image Source

• Give the Capture a unique name. Fill in the details of your source, such as Account Customer ID and Start Date, and authenticate your Google account. Then, you will get a popup window where you can authorize access. 

Image Source

• Once you have filled in all the details and authorized your Google account, click on NEXT. Flow will initiate a connection with your Google Ads account and identify data.
  
• Next, click on SAVE AND PUBLISH.

Step 2: Set a Destination for your Data.

• To set up a destination for your data, there are two ways. You can either navigate to Estuary's dashboard and click on Destinations, or you can also click on Materialize Connections in the pop-up window after successfully completing the capture process. Then, click on + NEW MATERIALIZATION.
  
• In the search connectors box, search for Redshift and click on Materialization. You will be directed to the Redshift connector page.

Image Source

• Redshift has some prerequisites that you'll need to follow before you can connect to Flow successfully. Follow these steps before proceeding.
  
• In the Create Materialization page, provide the Materialization name and Endpoint config details such as Address, database username, and Password. You will also need to provide details of the Amazon S3 bucket as well as a secret access key for read/write permission. In case the data captured from Google Ads was not filled in automatically, you can add the data from the Source Collections section. The data collection from Google Ads might already be filled. If not, use the Source Collections option to locate and add them. Click on NEXT. 

Image Source

• Click on SAVE AND PUBLISH to complete the destination setup. Once you are done with these steps, Flow will continually replicate your data from Google Ads to Redshift in real-time. 

For more help, check the Estuary Flow documentation for: 

• How to create a Data Flow 
• Google Ads Source Connector 
• Amazon Redshift Materialization Connector

Conclusion 

Moving your data from Google Ads to Amazon Redshift can give you better results in optimizing and analyzing your ads and campaigns. Whether you choose to go with a manual approach or use Google Ads API, or SaaS alternatives, it is necessary to examine the advantages and limitations of each. 

The manual approach is convenient for small data transfers, but it can be time-consuming for larger datasets and lead to latency. On the other hand, Google Ads API offers control and flexibility over data but may require technical expertise to write, test, and debug scripts. However, choosing an automated tool will not only save you time but also improve accuracy and efficiency. 

Want to simplify your data integration process? Try Estuary Flow today and experience an automated end-to-end data pipeline to load Google Ads to Redshift with its no-code solution. Sign up and start your free trial today!