adwords weather script setup guide

Let’s say you run an online clothing store. Your store sells sweatshirts all year round, but you know your sweatshirts sell better when it’s colder outside. As an AdWords user, you are able to adjust your bids based on the weather patterns to allow you to maximize your targeting as well as your profits. This blog will run through the steps on how to set up the Google AdWords weather script to allow your business to not be held back by ever-changing weather patterns.

1. Register for the Mandatory API Key

Google partners with OpenWeatherMap to get all of the weather data needed to make the script work. Go to http://openweathermap.org/appid to create a new account and register for their API key.

2. Create a Google Sheet with Your Location Information

Head to the Bid by Weather Google Sheet and save a copy for your account. The first tab is for instructions. The other three tabs (Campaigns, Weather Condition, and Geo Target Mapping) all need to be filled out. Each of those three tabs has mandatory columns that need to be filled in.

  • Campaigns
    • Campaign Name
    • Weather Location
    • Weather Condition
    • Bid Modifier
    • Enabled

weather script campaigns tab

For the Campaigns rule, your campaigns will have to already be targeting the desired weather locations listed in your spreadsheet. If multiple rules match for a certain location and campaign, the last rule that shows up gets the pick so be careful not to overlap.

  • Weather Condition
    • Condition Name
    • Temperature (in Fahrenheit)
    • Precipitation (in mm of rain in last 3 hrs)
    • Wind (in mph)

weather script condition tab

The four columns in the Weather Condition are “ANDed” together in the script coding. This means the rule for the first line would apply when it’s Sunny AND 65 to 80º F AND precipitation is below 1 mm AND wind is below 5 mph. If you leave a field blank, like in the second row for Rainy, then that column is ignored in the rule.

weather script geo target tab

3. Create the Weather Script in AdWords

Just like setting up AdWords’ countdown script, we’ll need to create a new script. Below is the base code for the weather script, and it’s a long one. The two lines in blue are the only items you’ll need to change for your account.

// Register for an API key at http://openweathermap.org/appid
// and enter the key below.
var OPEN_WEATHER_MAP_API_KEY = "INSERT_OPEN_WEATHER_MAP_API_KEY_HERE";

// Create a copy of http://goo.gl/SNE5H7 and enter the URL below.
var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';

// A cache to store the weather for locations already lookedup earlier.
var WEATHER_LOOKUP_CACHE = {};


/**
 * The code to execute when running the script.
 */
function main() {
  // Load data from spreadsheet.
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var campaignRuleData = getSheetData(spreadsheet, 1);
  var weatherConditionData = getSheetData(spreadsheet, 2);
  var geoMappingData = getSheetData(spreadsheet, 3);

  // Convert the data into dictionaries for convenient usage.
  var campaignMapping = buildCampaignRulesMapping(campaignRuleData);
  var weatherConditionMapping =
      buildWeatherConditionMapping(weatherConditionData);
  var locationMapping = buildLocationMapping(geoMappingData);

  // Apply the rules.
  for (var campaignName in campaignMapping) {
    applyRulesForCampaign(campaignName, campaignMapping[campaignName],
        locationMapping, weatherConditionMapping);
  }
}

/**
 * Retrieves the data for a worksheet.
 * @param {Object} spreadsheet The spreadsheet.
 * @param {number} sheetIndex The sheet index.
 * @return {Array} The data as a two dimensional array.
 */
function getSheetData(spreadsheet, sheetIndex) {
  var sheet = spreadsheet.getSheets()[sheetIndex];
  var range =
      sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  return range.getValues();
}

/**
 * Builds a mapping between the list of campaigns and the rules
 * being applied to them.
 * @param {Array} campaignRulesData The campaign rules data, from the
 *     spreadsheet.
 * @return {Object.<string, Array.<Object>> } A map, with key as campaign name,
 *     and value as an array of rules that apply to this campaign.
 */
function buildCampaignRulesMapping(campaignRulesData) {
  var campaignMapping = {};
  for (var i = 0; i < campaignRulesData.length; i++) {
    // Skip rule if not enabled.

    if (campaignRulesData[i][4].toLowerCase() == 'yes') {
      var campaignName = campaignRulesData[i][0];
      var campaignRules = campaignMapping[campaignName] || [];
      campaignRules.push({
          'name': campaignName,

          // location for which this rule applies.
          'location': campaignRulesData[i][1],

          // the weather condition (e.g. Sunny)
          'condition': campaignRulesData[i][2],

          // bid modifier to be applied.
          'bidModifier': campaignRulesData[i][3]
      });
      campaignMapping[campaignName] = campaignRules;
    }
  }
  Logger.log('Campaign Mapping: %s', campaignMapping);
  return campaignMapping;
}

/**
 * Builds a mapping between a weather condition name (e.g. Sunny) and the rules
 * that correspond to that weather condition.
 * @param {Array} weatherConditionData The weather condition data from the
 *      spreadsheet.
 * @return {Object.<string, Array.<Object>>} A map, with key as a weather
 *     condition name, and value as the set of rules corresponding to that
 *     weather condition.
 */
function buildWeatherConditionMapping(weatherConditionData) {
  var weatherConditionMapping = {};

  for (var i = 0; i < weatherConditionData.length; i++) {
    var weatherConditionName = weatherConditionData[i][0];
    weatherConditionMapping[weatherConditionName] = {
      // Condition name (e.g. Sunny)
      'condition': weatherConditionName,

      // Temperature (e.g. 50 to 70)
      'temperature': weatherConditionData[i][1],

      // Precipitation (e.g. below 70)
      'precipitation': weatherConditionData[i][2],

      // Wind speed (e.g. above 5)
      'wind': weatherConditionData[i][3]
    };
  }
  Logger.log('Weather condition mapping: %s', weatherConditionMapping);
  return weatherConditionMapping;
}

/**
 * Builds a mapping between a location name (as understood by OpenWeatherMap
 * API) and a list of geo codes as identified by AdWords scripts.
 * @param {Array} geoTargetData The geo target data from the spreadsheet.
 * @return {Object.<string, Array.<Object>>} A map, with key as a locaton name,
 *     and value as an array of geo codes that correspond to that location
 *     name.
 */
function buildLocationMapping(geoTargetData) {
  var locationMapping = {};
  for (var i = 0; i < geoTargetData.length; i++) {
    var locationName = geoTargetData[i][0];
    var locationDetails = locationMapping[locationName] || {
      'geoCodes': []      // List of geo codes understood by AdWords scripts.
    };

    locationDetails.geoCodes.push(geoTargetData[i][1]);
    locationMapping[locationName] = locationDetails;
  }
  Logger.log('Location Mapping: %s', locationMapping);
  return locationMapping;
}

/**
 * Applies rules to a campaign.
 * @param {string} campaignName The name of the campaign.
 * @param {Object} campaignRules The details of the campaign. See
 *     buildCampaignMapping for details.
 * @param {Object} locationMapping Mapping between a location name (as
 *     understood by OpenWeatherMap API) and a list of geo codes as
 *     identified by AdWords scripts. See buildLocationMapping for details.
 * @param {Object} weatherConditionMapping Mapping between a weather condition
 *     name (e.g. Sunny) and the rules that correspond to that weather
 *     condition. See buildWeatherConditionMapping for details.
 */
function applyRulesForCampaign(campaignName, campaignRules, locationMapping,
                               weatherConditionMapping) {
  for (var i = 0; i < campaignRules.length; i++) {
    var bidModifier = 1;
    var campaignRule = campaignRules[i];

    // Get the weather for the required location.
    var locationDetails = locationMapping[campaignRule.location];
    var weather = getWeather(campaignRule.location);
    Logger.log('Weather for %s: %s', locationDetails, weather);

    // Get the weather rules to be checked.
    var weatherConditionName = campaignRule.condition;
    var weatherConditionRules = weatherConditionMapping[weatherConditionName];

    // Evaluate the weather rules.
    if (evaluateWeatherRules(weatherConditionRules, weather)) {
      Logger.log('Matching Rule found: Campaign Name = %s, location = %s, ' +
          'weatherName = %s,weatherRules = %s, noticed weather = %s.',
          campaignRule.name, campaignRule.location,
          weatherConditionName, weatherConditionRules, weather);
      bidModifier = campaignRule.bidModifier;
      adjustBids(campaignName, locationDetails.geoCodes, bidModifier);
    }
  }
  return;
}

/**
 * Converts a temperature value from kelvin to fahrenheit.
 * @param {number} kelvin The temperature in Kelvin scale.
 * @return {number} The temperature in Fahrenheit scale.
 */
function toFahrenheit(kelvin) {
  return (kelvin - 273.15) * 1.8 + 32;
}

/**
 * Evaluates the weather rules.
 * @param {Object} weatherRules The weather rules to be evaluated.
 * @param {Object.<string, string>} weather The actual weather.
 * @return {boolean} True if the rule matches current weather conditions,
 *     False otherwise.
 */
function evaluateWeatherRules(weatherRules, weather) {
  // See http://bugs.openweathermap.org/projects/api/wiki/Weather_Data
  // for values returned by OpenWeatherMap API.
  var precipitation = 0;
  if (weather.rain && weather.rain['3h']) {
    precipitation = weather.rain['3h'];
  }
  var temperature = toFahrenheit(weather.main.temp);
  var windspeed = weather.wind.speed;

  return evaluateMatchRules(weatherRules.temperature, temperature) &&
      evaluateMatchRules(weatherRules.precipitation, precipitation) &&
      evaluateMatchRules(weatherRules.wind, windspeed);
}

/**
 * Evaluates a condition for a value against a set of known evaluation rules.
 * @param {string} condition The condition to be checked.
 * @param {Object} value The value to be checked.
 * @return {boolean} True if an evaluation rule matches, false otherwise.
 */
function evaluateMatchRules(condition, value) {
  // No condition to evaluate, rule passes.
  if (condition == '') {
    return true;
  }
  var rules = [matchesBelow, matchesAbove, matchesRange];

  for (var i = 0; i < rules.length; i++) {
    if (rules[i](condition, value)) {
      return true;
    }
  }
  return false;
}

/**
 * Evaluates whether a value is below a threshold value.
 * @param {string} condition The condition to be checked. (e.g. below 50).
 * @param {number} value The value to be checked.
 * @return {boolean} True if the value is less than what is specified in
 * condition, false otherwise.
 */
function matchesBelow(condition, value) {
  conditionParts = condition.split(' ');

  if (conditionParts.length != 2) {
    return false;
  }

  if (conditionParts[0] != 'below') {
    return false;
  }

  if (value < conditionParts[1]) {
    return true;
  }
  return false;
}

/**
 * Evaluates whether a value is above a threshold value.
 * @param {string} condition The condition to be checked. (e.g. above 50).
 * @param {number} value The value to be checked.
 * @return {boolean} True if the value is greater than what is specified in
 *     condition, false otherwise.
 */
function matchesAbove(condition, value) {
  conditionParts = condition.split(' ');

  if (conditionParts.length != 2) {
    return false;
  }

  if (conditionParts[0] != 'above') {
    return false;
  }

  if (value > conditionParts[1]) {
    return true;
  }
  return false;
}

/**
 * Evaluates whether a value is within a range of values.
 * @param {string} condition The condition to be checked (e.g. 5 to 18).
 * @param {number} value The value to be checked.
 * @return {boolean} True if the value is in the desired range, false otherwise.
 */
function matchesRange(condition, value) {
  conditionParts = condition.replace('\w+', ' ').split(' ');

  if (conditionParts.length != 3) {
    return false;
  }

  if (conditionParts[1] != 'to') {
    return false;
  }

  if (conditionParts[0] <= value && value <= conditionParts[2]) {
    return true;
  }
  return false;
}

/**
 * Retrieves the weather for a given location, using the OpenWeatherMap API.
 * @param {string} location The location to get the weather for.
 * @return {Object.<string, string>} The weather attributes and values, as
 *     defined in the API.
 */
function getWeather(location) {
  if (location in WEATHER_LOOKUP_CACHE) {
    Logger.log('Cache hit...');
    return WEATHER_LOOKUP_CACHE[location];
  }

  var url = Utilities.formatString(
      'http://api.openweathermap.org/data/2.5/weather?APPID=%s&q=%s',
      encodeURIComponent(OPEN_WEATHER_MAP_API_KEY),
      encodeURIComponent(location));
  var response = UrlFetchApp.fetch(url);
  if (response.getResponseCode() != 200) {
    throw Utilities.formatString(
        'Error returned by API: %s, Location searched: %s.',
        response.getContentText(), location);
  }

  var result = JSON.parse(response.getContentText());

  // OpenWeatherMap's way of returning errors.
  if (result.cod != 200) {
    throw Utilities.formatString(
        'Error returned by API: %s,  Location searched: %s.',
        response.getContentText(), location);
  }

  WEATHER_LOOKUP_CACHE[location] = result;
  return result;
}

/**
 * Adjusts the bidModifier for a list of geo codes for a campaign.
 * @param {string} campaignName The name of the campaign.
 * @param {Array} geocodes The list of geo codes for which bids should be
 *     adjusted.
 * @param {number} bidModifier The bid modifier to use.
 */
function adjustBids(campaignName, geocodes, bidModifier) {
  // Get the campaign.
  var campaignIterator = AdWordsApp.campaigns().withCondition(
      Utilities.formatString('CampaignName = "%s"', campaignName)).get();
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();

    // Get the targeted locations.
    var locations = campaign.targeting().targetedLocations().get();
    while (locations.hasNext()) {
      var location = locations.next();
      var currentBidModifier = location.getBidModifier().toFixed(2);

      // Apply the bid modifier only if the campaign has a custom targeting
      // for this geo location.
      if (geocodes.indexOf(location.getId()) != -1 &&
          currentBidModifier != bidModifier) {
        Logger.log('Setting bidModifier = %s for campaign name = %s, ' +
            'geoCode = %s. Old bid modifier is %s.', bidModifier, campaignName,
            location.getId(), currentBidModifier);
        location.setBidModifier(bidModifier);
      }
    }
  }
}

As with any script, make sure you authorize it, run it and set a schedule. And that’s it. Let your seasonal business boom!