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.

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

Limit AdWords Overdelivery to Any Amount You Want

Google announced that daily budgets will now be able to overdeliver up to 100% rather than 20% as had been the case since the earliest days of AdWords.

Overdelivery allows Google to help advertisers meet monthly budget targets by making up for slow traffic days by spending more money on high volume days. They assume that advertisers will divide their monthly budget by 30.4, and set this as the daily budget. Then when there may not be a whole lot of traffic on Saturdays and Sundays, Google can exceed the daily budget on Mondays and Tuesdays when there might be more people looking for what the advertiser sells.

Here's Google's announcement:

To truly control budgets they way you need, you'll probably want to use tools, automations, and AdWords Scripts. Here's a very basic script that lets you enforce a more strict overdelivery for a campaign. The script assumes that your daily budgets are the baseline of what you'd like to deliver. Use the setting 'allowedOverdeliveryPercentage' to control a maximum spend for the day by setting a value between 0% and 100%. The script fetches every active campaign's daily budget and accrued cost for the day. If the cost exceeds the daily budget + the allowed percentage of overdelivery, it will label that campaign and pause it.

Important Notes:

• Remember to use another automation to re-enable all paused campaigns during the first hour of every day. You can look for campaigns that have the label set by the script and re-enable those. 
• The script can be run once per hour so you may still exceed the total cost until the next time the script runs. 
• The script doesn't deal with shared budgets.
• The script doesn't deal with shopping and video campaigns. Making it work for those is really easy, you just have to update the campaigns call to use the video and shopping methods for getting campaigns.

If you need more control over budgets, and you don't want to do any coding, consider our prebuilt scripts available as part of an Optmyzr subscription (Optmyzr is my company).

/******************************************
*
* Version 1.0 
* Created By: Frederick Vallaeys
* FreeAdWordsScripts.com
******************************************/

function main() {
  
  var allowedOverdeliveryPercentage = 0.2; // set percentage as decimal, i.e. 20% should be set as 0.2
  var labelName = "paused by overdelivery checker script";
  
  AdWordsApp.createLabel(labelName, "automatic label needed to reenable campaigns");
  
  var campaigns = AdWordsApp.campaigns()
   .withCondition("Status = ENABLED")
   .withCondition("Cost > 0")
   .forDateRange("TODAY");
  
  var campaignIterator = campaigns.get();
  
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    var campaignName = campaign.getName();
    var budgetAmount = campaign.getBudget().getAmount();
    var costToday = campaign.getStatsFor("TODAY").getCost();
    
    if(costToday > budgetAmount * (1 + allowedOverdeliveryPercentage)) {
      Logger.log(campaignName + " has spent " + costToday + " which is more than allowed.");
      campaign.applyLabel(labelName);
      campaign.pause();
    } else {
      Logger.log(campaignName + " has spent " + costToday + " and can continue to run.");
    }
  }

}