Check Close Variants Your Ads Are Showing For

Google changed how Close Variants work and now exact match keywords may also show an ad when Google believes the meaning of the search is the same as the keyword. Kind of like a synonym, but potentially even broader.

So here's a script you can use to see exactly what Google considers to be a 'close variant'. The script pulls all queries that were triggered by close variants. It also pulls the associated keyword. It joins the query and keyword and then puts the data on a spreadsheet.

On the spreadsheet, you can see line by line what the keyword is and what the close variant is. If you want, you can compare metrics to decide if a close variant that seems a little odd may actually be outperforming the keyword. 

I thought it'd also be interesting to add an automatic way to see how close the query is to the keyword. Whereas going from the keyword [optmyzr] to 'opmyzr' seems like a definite good case of a close variant because it's a simple typo in a brand name, going from [campsites in yosemite] to 'campgrounds yosemite' may be too much of a stretch from the original keyword.

To calculate the similarity of the close variant to the keyword, I found an algorithm for the Levenshtein distance which counts the number of characters that must be deleted, edited or added to go from one string to another string. A big number in the Levenshtein column means the query is more different from the keyword.

Grab the code below to try it in your Google Ads account. I've also included a table showing how match types work after the change from Google and a video that goes through using the script in case anyone is just getting their feet wet with scripts.

Automatically Refresh Weather Data in a Spreadsheet

Bidding by weather is a classic example of the power of AdWords Scripts but the code from Google can be daunting to implement for scripting newbies. Recently my company Optmyzr launched the ability to connect external data to a Rule Engine, making it possible to bid by weather without writing any code.

A solution like this uses a Google Sheet that is automatically updated with current weather conditions. While adding the API calls to fetch the weather is straightforward, getting it to automatically refresh this data is difficult. This post covers a solution for updating external data on a spreadsheet automatically.

First, grab a copy of this Google Spreadsheet that contains a function courtesy of Mogsdad on StackOverflow that handles updating the weather. It does this by removing and then adding back all functions that deal with importing external data.

This spreadsheet also contains the API connection strings that fetch the temperature and conditions for a zip code.

To be able to use the weather API, be sure to put in your own API key on the settings sheet. You can request one from OpenWeatherMap.orghttps://openweathermap.org/appid#get.

To automatically update the weather on a schedule, set a time-based trigger for the function RefreshImports by going to the "Tools" menu in the spreadsheet and selecting "Script Editor" and then clicking the fields shown in the screenshot below:

You'll also need to update the code at line 17 to reference the ID of your own copy of the spreadsheet. For example, grab this part of the URL:

And put that into line 17 of the script that's associated with the spreadsheet here:

That's it. Now you have a spreadsheet that automatically refreshes with current weather data and you can use it for other AdWords automations.

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.