Automatically Put Pivoted AdWords Data in a Spreadsheet

I've written several free scripts that export AdWords data to a Google spreadsheet but the one I'll share below does something a little more and pivots the reports so that segment data shows up in columns rather than rows.

The Problem With Google's AdWords Reports

Here's what the typical output from an AdWords report looks like if you use a script like this one to add it in a Google sheet.

AdWords report data that was automatically added to a Google spreadsheet. The report contains keyword conversion data segmented by conversion type name and day of week, resulting in multiple rows of data for each keyword.

I highlighted the cells for the same keyword ID in green so that you can easily see that there are 7 rows of data for the same keyword. Each row has different values for the segments 'ConversionType' and 'DayOfWeek'.

Here's why I don't like getting my reports this way... In a typical AdWords optimization, I might evaluate a keyword to see if it meets certain criteria, and when it does, change the bid. For example, for keywords with more than 5 total "Sign Up" conversions, and at least 2 of those conversions on today's day of the week,  I might raise the bid.

This is not easy to do with the spreadsheet above because I can't simply build a formula for each row as that would ignore the data from the other 6 rows about the same entity.

A Better Format for AdWords Reports

To get the report the way I want, I need to do some vlookups and data aggregation so that all the data for a keyword is moved onto a single row. This is what my spreadsheet should look like to make optimizing easy:

AdWords report data that is aggregated by entities. Here there is one row for every keyword along with one column for every combination of metrics and segments.

Now you can see there is only 1 row for that same keyword ID as before (highlighted in green). Now I can write a spreadsheet formula that has access to all the elements I need on one row.

How Scripts Solved This Problem

To get the spreadsheet output in the format I wanted, I wrote a script that finds all possible values for the included segments and then combines this with the different metrics. For example, possible values for the DayOfWeek segment are 'Monday', 'Tuesday', 'Wednesday', etc. Combining this with my metrics, I get Monday.Conversions, Monday.AllConversions, etc. Each of these is then treated as a column in the sheet.

So now I can more easily do my optimization. I could also feed this sheet into a tool like Optmyzr's Rule Engine so that I could start automating account management based on factors like day of week, type of conversion, or any other segment I include on the sheet. Optmyzr is my company and also runs this blog.

How to run this script:

After installing the script code that's all the way at the bottom of this post into an AdWords account (a child account, not an MCC account), you can edit the following settings to customize it:

1. attributes: these are the AdWords reporting attributes. You will get a single spreadsheet row per unique combination of attributes
2. segments: these are the AdWords reporting segments. The values of each segment will be joined with metrics and create 1 column for each combination
3. metrics: the AdWords reporting metrics to include.
4. sourceReport: the report type from Google. See the link below for more information
5. spreadsheetUrl: the Url of the Google spreadsheet that this script will update.
6. tabName: the name of the sheet (tab) in the spreadsheet that should be updated.
7. reportVersion: the version of the AdWords API reports you’re using. The data available in the Ads API changes periodically so this ensures our script talks to the right version of AdWords.

Where to Find Valid Settings for the Script

Here is the list of acceptable attributes, segments and metrics for the keywords performance report:
https://developers.google.com/adwords/api/docs/appendix/reports/keywords-performance-report. As you can see, Google makes it really easy to see what are attributes vs. segments vs metrics. Just be sure to populate the right settings in the script based on what type of data you're adding.

So for example, if you wanted to add ApprovalStatus to the report, you'd put add it to the comma separated list of attributes as seen here:

Here is the full list of all AdWords reports with the available attributes, segments, and metrics to help you create correct settings.

One thing to watch out for:

Note that in the resulting spreadsheet, you can't total up the numbers across a row because metrics are included multiple times, once per included segment. For example, in a report with the 2 segments DayOfWeek and ConversionType, if a keyword has 10 conversions, then the sum of all the conversion columns for that row will be 20 (10 conversions * 2 segments).

Another Popular Script For Putting AdWords Data in Spreadsheets

Last June I wrote a script that makes it super easy to do custom data pulls from AdWords and put the data in a spreadsheet so I was happy to hear people still talking about this script at SMX West in March. You can find it on Search Engine Land if you're looking for more options to get AdWords data in spreadsheets without paying someone for it.

Working with Experiment Campaigns in AdWords Scripts

If you've started using Google's Draft and Experiment campaigns, you may have noticed some odd behavior with scripts. We were getting these campaigns back in our selectors, even if they were no longer active experiments. And if we tried to change their status to 'ended', we couldn't do so.

I'll explain the similarities and differences so that hopefully you can avoid some of the frustration I faced when we started using experiments in AdWords and some scripts started to break.

Experiment campaigns are like other campaigns in the following ways:

1. Experiment campaigns are returned in API reports like CAMPAIGN_PERFORMANCE_REPORT
2. Experiment campaigns are returned by AW Scripts selectors like campaignSelector = AdWordsApp.campaigns()

Experiment campaigns are unlike normal campaigns in some important ways:

1. Experiment campaigns, once enabled are always enabled. If you end the experiment, the ServingStatus becomes 'ENDED' but the CampaignStatus remains 'ENABLED'
2. Experiment campaigns cannot have labels
3. Experiment campaigns cannot have their status or budget changed (as these are tied to the corresponding 'base' campaign)

As you can see, there are situations where some of these oddities can lead to issues. For example, if you have a script that pauses any campaigns that have exceeded a certain budget for the month (like those that Optmyzr provides), the script will recognize that an experiment campaign has spent too much but it will be unable to label it or pause it.

So I wanted to exclude experiment campaigns from my selectors and found this was possible and documented. You can exclude campaigns of different types by adding the following to a selector:

• withCondition("CampaignExperimentType = BASE")
• withCondition("CampaignExperimentType = DRAFT")
• withCondition("CampaignExperimentType = EXPERIMENT")

But there is also an undocumented feature in AdWords Scripts that can help filter these campaigns:

• withCondition("ServingStatus = SERVING")

The other possible condition values are ENDED, PENDING, NONE, SUSPENDED

The cool trick I learned here is that you can use conditions that are not documented in the AdWords Scripts reference. You just refer to the fields in the corresponding API report and try adding them to selectors.

Thanks to Alex from the Google team for pointing this out.

Automatically Exclude Placements With a Particular Domain

AdWords lets advertisers show ads on the Display Network (GDN) and while it has ways to target ads to show on specific domains, or to exclude specific domains, they don't have a wildcard placement exclusion feature. 

So if you want to show your ads on all relevant placements, except those with a Polish domain extension (.pl) or an extension like .org, you would need to monitor automatic placements and add exclusions every time you saw one with this extension.

The following script makes it possible to automate excluding placements when the domain includes a particular string.

Run this daily or weekly in your AdWords account to prevent accruing too many clicks from unwanted placements.

Enjoy!
Frederick Vallaeys
Co-Founder, Optmyzr.com

Get alerted when keywords or product groups spend too much

Automated Rules in AdWords are great to set up alerts for when things spend too much without converting enough. But unfortunately these Automated Rules can only be run once per day, so they're not very useful if you want to get notified as soon as a keyword or product group exceeds your thresholds.

So here's a simple script that queries for keywords or product groups that have exceeded a specific amount of cost, and have fewer than a specified number of conversions to show for that cost.

It then emails the list of alerts to the user. The email can include deep links to AdWords to make it really easy to go and fix issues. If the instructions for the settings currentSetting.customerId, currentSetting.effectiveUserId, and currentSetting.ocid confuse you, check out an earlier post by Russ that explains deep linking to ad groups in AdWords in more detail.

Thanks,
Fred Vallaeys