Guide: How to map Google Ads UI reports in SQL with Blendo

Analyzing your Google Ads campaigns in your Business Intelligence tool seems like a no-brainer. It is an easy way to consolidate data from multiple campaigns, different channels, and drill down to the ad level data. You then can better understand advertisement ROI and have better reporting.

But it’s not that simple.

In fact, users often ask us — “how can I better analyze my Google Ads data into my business intelligence tool?”.

That is something we want to help with a lot. After all, answering this question will help you understand better the data you get from Blendo and to compare your BI results and Google Ads vanilla UI quickly.

That’s why we created this guide to give you peace of mind and help you built the reports you can find inside Google Ads UI and how you can map it with your SQL.

And if you’re wondering “how Blendo can help me?” it’s not because we only make it easy for you to integrate Google Adwords with your data warehouse.

With Blendo, you can recreate these Google Ads reports into custom SQL tables that you can query with SQL or your business intelligence tool.

Here is a quick example.

With Blendo, you can get a vast number of attributes, segments, and metrics from AdWords API.

The Ad Group Performance Report, for example, has about 50+ resources that you can pull from Google Ads API. Blendo syncs everything for you, but also gives you the ability to create custom “reports”.

Such a “report” will mirror to one custom table into your data warehouse with the columns you chose.

So today, we’re discussing how to do precisely that. Here’s how.

1. Connect your analytics database and Google Ads

In our example, we will use PostgreSQL as a data warehouse destination.

With other competitive tools, the destination type would be important, but for Blendo, it is not. You may build replicas of your Google Ads UI reports with a few clicks with any data warehouse destination.

Connecting Adwords with PostgreSQL is a few clicks with Blendo. Creating custom reports after that is almost as easy. Though this is not the goal of this guide, so here are the links you need to achieve both results:

1. Connect your data warehouse to Blendo
2. Connect Google Ads to Blendo

2. Create a custom report from Adwords data source to analyze with SQL

After connecting your AdWords as a data source, you can start creating custom reports.

The steps you need to achieve it are:

1. Click Create Resource
2. Choose the Google Ads Report type (resembles those in Adwords’s UI). You may also find the report types supported here.
3. Choose the Attributes you need for the report. Blendo intelligently will give you only those that make sense with the report type you chose.
4. Choose your Segments for the report.
5. Choose your Metrics. These metrics are columns like CTR, Costs, or percentages (%). Again Blendo will always present you with those that make sense to see for the report you chose.

For more details, please check our documentation.

3. Mapping Google Ads UI reports with your SQL tables (but with no SQL effort)

OK, so here is the juice…

Following the setup we outline below, you will be able to have an exact mapping of the reports presented in Google Ads UI to your SQL database.

Campaign Performance Report (Campaigns report)

You will probably need to begin analyzing your ads data from a high level.

On whatever campaigns your marketing strategy needs to run, you always need to understand their ROI. For that reason, building effective reporting is incredibly important to any marketing campaign.

If you are looking to analyze your Google Ads data, you can begin from your Campaign Performance Report. With this data, you can understand which campaigns are the most effective at driving conversions and revenue.

You can quickly build a table into your database with data from your campaign performance reports.

With Blendo, you will need to create a custom report with the following settings:

• Select Resource: Report
• Name: Campaigns Report
• Report Type: CAMPAIGN_PERFORMANCE_REPORT
• Attributes: CampaignName, Amount, CampaignStatus, CampaignTrialType, BidStrategyType
• Metrics: Impressions, Interactions, InteractionRate, AverageCost, Cost, Conversions, CostPerConversion, ConversionRate

The configuration should be like the image below.

For the attributes, segments, and metrics we pull for the Campaign Performace Report, please see AdWords documentation and our Google Ads documentation.

Ad Group Performance Report (Ad Groups)

One of the essential things you need to get a high-quality score for your ads is better optimization over your ad groups.

And when your ads aren’t highly specific and optimized, you get a lower quality score.

Let me explain.

What if you are targeting “men’s black scarf” and “white scarf” in the same ad group? People may see the wrong ad, costing you clicks.

So, instead of trying to target 5 ads for 20 keywords, you’re optimizing to target 5 ads for a narrow set of keywords.

That way, you will have the best shot for a higher CTR and ROI.

Pulling your ad group data into a database can give you the flexibility to analyze at a higher level than the ad and keywords itself.

Lets to that now, with Blendo.

We will build an ad group performance custom report in Blendo that will create the necessary table and the data you will need for your analysis into your data warehouse by using the following settings:

• Select Resource: Report
• Name: Ad Groups Report
• Report Type: ADGROUP_PERFORMANCE_REPORT
• Attributes: AdGroupName, CampaignName, AdGroupStatus, CpcBid, TargetCpa, AdGroupType
• Metrics: Impressions, Interactions, InteractionRate, AverageCost, Cost, Conversions, CostPerConversion, ConversionRate

The configuration should be like the image below.

For the attributes, segments, and metrics we pull for the Ad Group Performace Report, please see AdWords documentation and our Google Ads documentation.

Ad Performance Report (Ads report)

Indeed Google Ads has many vanilla reports on your Ads performance. What if you want to combine these data to aggregate information, audit your agency, or even combine it with other channels like Facebook Ads and have a complete Ad performance report.

Recreating the Ad performance report into your database will give you the ability to dig into raw data and combine them to your liking.

Create the report inside Blendo, and that will create the relevant table and replicate the information you need into your database. To do that, let’s use the following data:

• Select Resource: Report
• Name: Ads Performance Report
• Report Type: AD_PERFORMANCE_REPORT
• Attributes: Id, Headline, CampaignName, AdGroupName, Status, AdType
• Metrics: Impressions, Interactions, InteractionRate, AverageCost, Cost, Conversions, CostPerConversion, ConversionRate

The configuration should be like the image below.

For the attributes, segments, and metrics we pull for the Ad Performace Report, please see AdWords documentation and our Google Ads documentation.

Click Performance Report

Clicks, conversions, and their costs can vary often.

As a marketing analyst, you try to understand what impact can minor changes can have on your costs. And if we’re fair, it sucks trying to when you have too many campaigns to monitor.

Having these data into your BI dashboard can provide you with the information you need to understand campaign performance.

So first, let’s get your Click Performance data into your database, and then drill down to your data within your BI.

Start by creating this report inside Blendo:

• Select Resource: Report
• Name: Click Performance Report
• Report Type: CLICK_PERFORMANCE_REPORT
• Attributes: CampaignId, CampaignName, AdGroupName, AdGroupStatus, GclId, ClickType, Clicks

The configuration should be like the image below.

Note: According to Google Ads’ API documentation, the Click Performance report includes stats aggregated at each click level and contains both valid and invalid clicks. For non-Search campaigns, the Clicks field represents the number of valid and invalid interactions instead of clicks.

For the attributes, segments, and metrics we pull for the Click Performace Report, please see AdWords documentation and our Google Ads documentation.

Search Query Performance Report (Search Terms)

Understanding which search terms make sense to add in your keyword list is essential for your ad and CTR performance.

Remember the example of the “men’s black scarf” vs. “white scarf”? They are vastly different when talking about buying and clicking intent.

Analyze your Search Query report data and dig into the terms that make sense for your campaigns then.

To recreate the Search Terms report from your Google Ads UI into your database, do the following:

• Select Resource: Report
• Name: Search Terms Report
• Report Type: SEARCH_QUERY_PERFORMANCE_REPORT
• Attributes: Query, QueryTargetingStatus
• Segments: QueryMatchTypeWithVariant
• Metrics: Clicks, Impressions, Ctr, AverageCpc, Cost, AbsoluteTopImpressionPercentage, TopImpressionPercentage, Conversions, ViewThroughConversions, CostPerConversion, ConversionRate

The configuration should be like the image below.

For the attributes, segments, and metrics we pull for the Search Query Performance Report, please see AdWords documentation and our Google Ads documentation.

Keywords Performance Report

To compete in your Google Ads campaigns understanding how your keywords behave could make the difference between a good ROI or a failed campaign. Get your Search Keywords data, compile your Keywords Performance Report, and understand which of the search terms perform better.

To recreate the Keywords Performance Report from your Google Ads UI into your database, do the following:

• Select Resource: Report
• Name: Keyword Performance Report
• Report Type: KEYWORDS_PERFORMANCE_REPORT
• Attributes: Criteria, CampaignName, AdGroupName, CampaignStatus, CpcBid, FinalUrls
• Metrics: Impressions, Interactions, InteractionRate, AverageCost, Cost, Conversions, CostPerConversion, ConversionRate

The configuration should be like the image below.

For the attributes, segments, and metrics we pull for the Keywords Performace Report, please see AdWords documentation and our Google Ads documentation.

Geographic Performance Report (Geographic Report)

Understanding which campaigns work better is also a combination of the geographic location of your ads.

This is a critical detail…

The targeting of the exact territory or country that you need (or more importantly that you don’t need) to serve can make a massive change in the performance of your campaigns.

To recreate the Geographic Performance Report from your Google Ads UI into your database, do the following:

• Select Resource: Report
• Name: Geographic Performance Report
• Report Type: GEO_PERFORMANCE_REPORT
• Attributes: CountryCriteriaId (ID of the Country Location associated with the impression)
• Segments: LocationType
• Metrics: Clicks, Impressions, Ctr, AverageCpc, Cost

You may also use the CityCriteriaId (ID of the City Location), MetroCriteriaId (ID of the metro area Location), MostSpecificCriteriaId (ID of the most specific Location criterion) in the Attributes section (depending on your Geo criteria).

The configuration should be like the image below.

For the attributes, segments, and metrics we pull for the Geo Performace Report, please see AdWords documentation and our Google Ads documentation.

Bid Strategies report

With the Bid Strategies report, you can understand how your Smart Bidding strategies are performing.

To recreate the Bid Strategies Report from your Google Ads UI into your database, do the following:

• Select Resource: Report
• Name: Bid Strategies Report
• Report Type: BID_GOAL_PERFORMANCE_REPORT
• Attributes: Name, Type, Status
• Metrics: CampaignCount, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, CostPerConversion

The configuration should be like the image below.

Display Keyword Performance Report

Let’s see how you can dig into the data from your Google Ads Display campaigns. The Display Keyword Performance report includes data from Display and YouTube Networks. If you compile this report, you can understand which of the keyword terms perform better.

To recreate the Display Keyword Performance Report from your Google Ads UI into your database, do the following:

• Select Resource: Report
• Name: Display Keyword Performance Report
• Report Type: DISPLAY_KEYWORD_PERFORMANCE_REPORT
• Attributes: Criteria, CampaignName, AdGroupName, Status
• Metrics: Clicks, Impressions, Ctr, AverageCpc, Cost

The configuration should be like the image below.

For the attributes, segments, and metrics we pull for the Display Keyword Performace Report, please see AdWords documentation and our Google Ads documentation.

Managed Placements Report (Placement Performance Report)

Let’s see now how you can find the managed placements performance report.

To recreate the Managed Placements Report from your Google Ads UI into your database, do the following:

• Select Resource: Report
• Name: Display Keyword Performance Report
• Report Type: PLACEMENT_PERFORMANCE_REPORT
• Attributes: Criteria, CampaignName, AdGroupName, Status
• Metrics: Clicks, Impressions, Ctr, AverageCpc, Cost, InteractionRate

Note1: you may segment by various attributes like AdNetworkType1, AdNetworkType2. See more here.

Note2: According to Google Ads documentation, if you have auto-targeting enabled and there was traffic from other domains, Placement Performance Report in the API will show those domains as well (ref)

The configuration should be like the image below.

For the attributes, segments, and metrics we pull for the Display Keyword Performace Report, please see AdWords documentation and our Google Ads documentation.

Conclusion

Google AdWords is a beast of a PPC platform, in more ways than one. It generates revenue for your business, but it also holds a lot of data that you can dig into.

Building scripts or browsing over multiple tabs and excel sheets can take hours and days.

We hope these custom reports will help you work smarter, not harder.

With Blendo, you can integrate any data source, within minutes, without having to write any code, designed to offer self-serve analytics-ready data in modern data warehouses. Don’t hesitate to sign up for a free trial.