Analyzing ad metrics in a data warehouse has many benefits. You can identify the best-performing and underperforming campaigns, improve attribution and audience segmentation, and build predictions based on historical data.
The best way to make all this work is to channel data from Google Ads to Redshift with the help of automation. However, in some cases, you may want to transfer data manually. In this article, we cover both options. Let’s start with the most efficient one.
Method 1. Connect Google Ads to Redshift automatically with a ready-to-use connector
Let’s see how to do all this using Coupler.io. This data integration and analytics solution is often used for automating PPC reports as it supports many ad and marketing data sources. With Coupler.io, you can easily connect Google Ads to Redshift in just a few minutes.
1. Collect your Google Ads data
To start transferring data, click the Proceed button in the widget below.
You will be asked to create a Coupler.io account (you can do this for free, no card required). Once this is done, connect your Google Ads account, specify your ad account, and select what data you want to load.
Here’s the list of data types you can transfer from Google Ads to Redshift:
- Campaign performance
- Keywords performance
- Ad group performance
- Ad performance
- Accounts
- Ad groups
- Ads
- Campaigns
- Campaign budgets
- Campaign criterion
- Campaign labels
- Accessible bidding strategies
- Bidding strategies
- Ad group criterion
- Call details
- Feed
- Feed item
- Labels
- User interest
Apart from that, you can also extract different constants, such as the language constant, mobile device constant, and others.
2. Organize and transform data
In the next step, you can preview the data that will be sent to Redshift. You can also edit the dataset, if needed.
For example, you can:
- Organize columns (hide, rearrange, rename)
- Split and merge columns
- Add new formula-based columns
- Perform calculations
- Sort and filter data
- Combine data from different accounts or apps into one dataset
The latter is especially useful when you work with several PPC platforms. For instance, you can analyze the performance of a campaign across multiple channels.
Once your data is ready, follow the wizard instructions to connect your Redshift account. Before proceeding, make sure you already created a cluster to load data to.
You will need to specify the Host, Port, Database, User, and your Password for the database user. Then, provide the name of the existing table for your data, or just type in a new name. In this case, the table with the new name will be created automatically.
3. Schedule updates
Toggle on the Automatic data refresh feature and specify your preferences for the update schedule. Coupler.io will regularly load data from Google Ads to Redshift at the selected interval. Your dataset can be refreshed as often as every 15 minutes.
Save and run the importer to complete the data transfer. Now, you can work with your data in Redshift.
As you can see, the process is quite straightforward and doesn’t take much time. With Coupler.io, you have far fewer steps compared to importing data into Redshift manually. In particular, you don’t need to upload data into an S3 bucket before sending it into Redshift.
Method 2. Move data from Google Ads to Redshift manually
As we already mentioned, this option is more time-consuming compared to using a third-party connector. Apart from that, you will have to regularly re-upload fresh data manually if you want to work with the latest metrics. For this reason, the manual method is not a good choice for continuous monitoring and analysis. However, it can be useful for one-time analysis or for cases when you don’t need frequent updates.
Here’s how it works.
1. Extract your ad data
Go to your Google Ads account and open the page with the data you want to export. It can be campaigns, reports on ad groups or landing pages, and so on. On each of these pages, you can find the Download button in the horizontal menu bar. Click it and choose the format. It’s better to opt for .csv.
Once you select the format, the file with the exported data will be downloaded to your computer. Now, let’s see how to load data from Google Ads to Redshift manually.
2. Upload your data in an S3 bucket
- If you don’t have an Amazon S3 bucket, you will need to create it. To do so, go to the AWS Management Console. In the list of services, click S3 -> Create bucket. Then, specify the preferred bucket name and AWS region.
- Once a bucket is created, in the top left corner, click Services -> IAM. Then, in the Access management list on the left, select Policies -> Create policy.
- In the Service dropdown list, select S3, and in the Access level list, select Read -> All read actions. In the Resources list, click Add ARNs and provide the requested information. Then, specify the Policy name and click Create policy.
- In the top left corner, click Services -> S3. Go to your bucket and click Upload.
- Then, click Add files, select the CSV file with the exported ad data, and press Upload.
- To proceed further, make sure you created a cluster for your data in Redshift. You can also prepare a table where your data will be loaded.The schema of the table should match the structure of your data.
3. Load data to Redshift with the v2 Query Editor
- Go to the V2 editor and connect to the cluster you created for your Google Ads data.
- Click Load data -> Browse S3, and select your bucket. For the S3 file location, specify the AWS region.
- Then, set your preferences for loading data: whether you want to load it into a table you created in advance or a new table. For the latter option, you will need to specify the cluster, schema, and table name.
- Choose the IAM role that has the required access to the S3 bucket with your data.
- Once this is done, click Load data. The query editor will generate and execute the COPY command to send the data from the S3 bucket to Redshift.
- You can monitor progress in the query editor. When your data is loaded, check if it was transferred correctly.
- If there are errors, check the STL_LOAD_ERRORS guide to help you troubleshoot.
Alternatively, you can load data from the AWS S3 bucket by executing the COPY command manually. To do so, you can use the query editor or connect via an SQL client.
Here’s an example:
COPY .
FROM ‘S3:///.csv’ 'aws_iam_role=arn:aws:iam:::role/'
csv;To use this formula, you will need to replace the <placeholders> with your actual information.
Utilizing the COPY command allows you to move data from Google Ads to Redshift with custom parameters. For more details, please refer to the AWS documentation.
Analyzing ad data in Redshift: Use case examples
Closed-loop analytics
Once your data is transferred from Google Ads to Redshift, there, you can combine it with data from other apps for more insights.
In particular, you can conduct closed-loop analysis. Bringing sales and marketing data together will help you better understand how your campaigns translate into revenue. You can analyze what generates the most leads and which channels are the most efficient.
We recommend combining such data types for analysis:
- Google Ads, Instagram ads, Facebook Ads, and other PPC apps – you can extract data on clicks, CTR and CPC metrics, ad spend, and conversions breakdown.
- Google Analytics 4 – details on user engagement, page views stats, website conversions, average session duration, and so on.
- CRM tools – customer and company details, lead status, and sales pipeline metrics.
- Social media platforms – information about reach, impressions, views, and followers.
Additionally, you can also include data from your email marketing platform, accounting apps, and various other sales data sources (apart from the CRMs). All of this will give you an overall picture. You can use this to improve audience segmentation, optimize budget, and increase conversions.
If you use Coupler.io to connect Google Ads to Redshift, you can plug in your other data sources in the same way. You will just need to set up a separate importer for each of the apps.
Automating this process is very convenient as you won’t need to collect the latest data in multiple systems separately.
Cross-channel PPC analytics
It’s also useful to analyze your Google Ads results together with data from other PPC platforms you use.
For example, if you are running an ad campaign across different platforms, you can compare performance and see which channels drive the most conversions. Later, you can redistribute your ad budget and allocate more to the best-performing campaigns and channels, which can help you get more conversions for the same ad spend.
Cross-channel analysis is also helpful for identifying overlapping audience segments and better understanding the user journey.
For cross-channel analysis, you may want to merge data from different PPC platforms into one dataset. You can easily do this in Coupler.io. You just need to add several data sources to the same importer. Then, you will be able to easily combine data in the preview and transform step.
Data is merged based on column names, so you can rename the columns in the datasets you want to merge so that they match. If needed, you can also perform data join in this step.
What is the best way to load data from Google Ads to Redshift?
The options we described in this article are arguably the most convenient ones. If you need to move data from Google Ads to Redshift as a one-time thing, then, the manual option can be a good fit. But for real-time analytics and timely insights, it’s better to use automation. Modern data integration tools are reliable, easy to use, and allow you to do this in a matter of minutes. Coupler.io can also be used to connect your other apps to the data warehouse for enhanced analysis.
But if using a ready-made connector is not for you, you can build the integration yourself. To do so, you will need to work with the Google Ads API and Amazon Redshift Data API and write custom scripts to connect the two platforms. This can be useful when you need a lot of flexibility or if your goals require a custom solution.
For more on this topic, check our other tutorials:
We hope our article has been useful to you. Thanks for reading!
