The demand for various goods and services depends on many conditions. In some cases, weather conditions have a certain influence. For example, in rainy weather, there was an increase in demand for orders from food deliveries (sushi, pizza, etc.).
Changing bids manually must be done regularly, quickly, and in a large number of campaigns, which entails an increase in time and, accordingly, money costs.
In connection with these observations, it became necessary to automate the change in rates on days when it rains. Perfect for this purpose weather campaign management script.
To use the script, you need to register in the service. openweathermap and get an API key.
The second thing is to copy spreadsheet template, whose data will be used by the script. It contains 3 sheets to be filled out:
- Campaigns - a list of campaigns for which bids will be changed (names must be exactly the same as in Google AdWords)
The Weather Location and Weather Condition columns are associated with the Geo Target Mapping and Weather Condition sheets, respectively.
The Bid Modifier column contains the bid change factor under the selected weather conditions. In this example, for rainy weather, an increase in the rate by 20%, and then a decrease in the rate by 20% to the original value is chosen.
- Weather Condition - sheet with selected weather conditions
In this example, two types of weather conditions are defined: Sunny (Sunny) and Rainy (Rainy). Also selected are the air temperature in Fahrenheit, the amount of precipitation for the last 3 hours in mm and the wind speed in m/h.
- Geo Target Mapping - sheet with location name and geotargeting code
A detailed list of locations can be viewed here, and all valid geotargeting codes are here.
Multiple geotargeting codes can be defined for a single location. To do this, in the table, simply create rows with the same location and corresponding code.
It's important to remember that the campaign you're setting up the script for must have geo-targeting set to the selected location or it won't perform any bid changes.
After the settings described above are completed, add the script code below:
var OPEN_WEATHER_MAP_API_KEY = "OPEN_WEATHER_MAP_API_KEY";
var SPREADSHEET_URL = 'SPREADSHEET_URL';
var WEATHER_LOOKUP_CACHE = {};
function main() {
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var campaignRuleData = getSheetData(spreadsheet, 1);
var weatherConditionData = getSheetData(spreadsheet, 2);
var geoMappingData = getSheetData(spreadsheet, 3);
var campaignMapping = buildCampaignRulesMapping(campaignRuleData);
var weatherConditionMapping =buildWeatherConditionMapping(weatherConditionData);
var locationMapping = buildLocationMapping(geoMappingData);
for (var campaignName in campaignMapping) {
applyRulesForCampaign(campaignName, campaignMapping[campaignName], locationMapping, weatherConditionMapping);
}
}
function getSheetData(spreadsheet, sheetIndex) {
var sheet = spreadsheet.getSheets()[sheetIndex];
var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
return range.getValues();
}
function buildCampaignRulesMapping(campaignRulesData) {
var campaignMapping = {};
for (var i = 0; i < campaignRulesData.length; i++) {
if (campaignRulesData[i][4].toLowerCase() == 'yes') {
var campaignName = campaignRulesData[i][0];
var campaignRules = campaignMapping[campaignName] || [];
campaignRules.push({
'name': campaignName,
'location': campaignRulesData[i][1],
'condition': campaignRulesData[i][2],
'bidModifier': campaignRulesData[i][3]
});
campaignMapping[campaignName] = campaignRules;
}
}
return campaignMapping;
}
function buildWeatherConditionMapping(weatherConditionData) {
var weatherConditionMapping = {};
for (var i = 0; i < weatherConditionData.length; i++) {
var weatherConditionName = weatherConditionData[i][0];
weatherConditionMapping[weatherConditionName] = {
'condition': weatherConditionName,
'temperature': weatherConditionData[i][1],
'precipitation': weatherConditionData[i][2],
'wind': weatherConditionData[i][3]
};
}
return weatherConditionMapping;
}
function buildLocationMapping(geoTargetData) {
var locationMapping = {};
for (var i = 0; i < geoTargetData.length; i++) {
var locationName = geoTargetData[i][0];
var locationDetails = locationMapping[locationName] || {
'geoCodes': []
};
locationDetails.geoCodes.push(geoTargetData[i][1]);
locationMapping[locationName] = locationDetails;
}
return locationMapping;
}
function applyRulesForCampaign(campaignName, campaignRules, locationMapping, weatherConditionMapping) {
for (var i = 0; i < campaignRules.length; i++) {
var bidModifier = 1;
var campaignRule = campaignRules[i];
var locationDetails = locationMapping[campaignRule.location];
var weather = getWeather(campaignRule.location);
var weatherConditionName = campaignRule.condition;
var weatherConditionRules = weatherConditionMapping[weatherConditionName];
if (evaluateWeatherRules(weatherConditionRules, weather)) {
bidModifier = campaignRule.bidModifier;
adjustBids(campaignName, locationDetails.geoCodes, bidModifier);
}
}
return;
}
function toFahrenheit(kelvin) {
return (kelvin - 273.15) * 1.8 + 32;
}
function evaluateWeatherRules(weatherRules, weather) {
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);
}
function evaluateMatchRules(condition, value) {
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;
}
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;
}
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;
}
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;
}
function getWeather(location) {
if (location in WEATHER_LOOKUP_CACHE) {
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());
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;
}
function adjustBids(campaignName, geocodes, bidModifier) {
var campaignIterator = AdWordsApp.campaigns().withCondition( Utilities.formatString('CampaignName = "%s"', campaignName)).get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var locations = campaign.targeting().targetedLocations().get();
while (locations.hasNext()) {
var location = locations.next();
var currentBidModifier = location.getBidModifier().toFixed(2);
if (geocodes.indexOf(location.getId()) != -1 &&
currentBidModifier != bidModifier) {
location.setBidModifier(bidModifier);
}
}
}
}
In code, replace variable values OPEN_WEATHER_MAP_API_KEY and SPREADSHEET_URL to the received weather service API key and a link to the Bid by Weather table, respectively. After that, set the script execution frequency. That's all.
Thus, if your business is to some extent dependent on weather conditions, you can safely use this script and experiment with the settings. If you have any questions, we will be happy to answer them in the comments.
Author: web programmer at a strategic internet marketing agency Star Marketing Ekaterina Demyanchuk
