· 5 years ago · Aug 04, 2020, 08:28 AM
1// Copyright 2015, Google Inc. All Rights Reserved.
2//
3// Licensed under the Apache License, Version 2.0 (the "License");
4// you may not use this file except in compliance with the License.
5// You may obtain a copy of the License at
6//
7// http://www.apache.org/licenses/LICENSE-2.0
8//
9// Unless required by applicable law or agreed to in writing, software
10// distributed under the License is distributed on an "AS IS" BASIS,
11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12// See the License for the specific language governing permissions and
13// limitations under the License.
14
15/**
16 * @name Bid By Weather
17 *
18 * @overview The Bid By Weather script adjusts campaign bids by weather
19 * conditions of their associated locations. See
20 * https://developers.google.com/google-ads/scripts/docs/solutions/weather-based-campaign-management#bid-by-weather
21 * for more details.
22 *
23 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
24 *
25 * @version 1.2.2
26 *
27 * @changelog
28 * - version 1.2.2
29 * - Add support for video and shopping campaigns.
30 * - version 1.2.1
31 * - Added validation for external spreadsheet setup.
32 * - version 1.2
33 * - Added proximity based targeting. Targeting flag allows location
34 * targeting, proximity targeting or both.
35 * - version 1.1
36 * - Added flag allowing bid adjustments on all locations targeted by
37 * a campaign rather than only those that match the campaign rule
38 * - version 1.0
39 * - Released initial version.
40 */
41
42// Register for an API key at http://openweathermap.org/appid
43// and enter the key below.
44var OPEN_WEATHER_MAP_API_KEY = 'INSERT_OPEN_WEATHER_MAP_API_KEY_HERE';
45
46// Create a copy of https://goo.gl/A59Uuc and enter the URL below.
47var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
48
49// A cache to store the weather for locations already lookedup earlier.
50var WEATHER_LOOKUP_CACHE = {};
51
52// Flag to pick which kind of targeting "LOCATION", "PROXIMITY", or "ALL".
53var TARGETING = 'ALL';
54
55
56/**
57 * The code to execute when running the script.
58 */
59function main() {
60 validateApiKey();
61 // Load data from spreadsheet.
62 var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
63 var campaignRuleData = getSheetData(spreadsheet, 1);
64 var weatherConditionData = getSheetData(spreadsheet, 2);
65 var geoMappingData = getSheetData(spreadsheet, 3);
66
67 // Convert the data into dictionaries for convenient usage.
68 var campaignMapping = buildCampaignRulesMapping(campaignRuleData);
69 var weatherConditionMapping =
70 buildWeatherConditionMapping(weatherConditionData);
71 var locationMapping = buildLocationMapping(geoMappingData);
72
73 // Apply the rules.
74 for (var campaignName in campaignMapping) {
75 applyRulesForCampaign(campaignName, campaignMapping[campaignName],
76 locationMapping, weatherConditionMapping);
77 }
78}
79
80/**
81 * Retrieves the data for a worksheet.
82 *
83 * @param {Object} spreadsheet The spreadsheet.
84 * @param {number} sheetIndex The sheet index.
85 * @return {Array} The data as a two dimensional array.
86 */
87function getSheetData(spreadsheet, sheetIndex) {
88 var sheet = spreadsheet.getSheets()[sheetIndex];
89 var range =
90 sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
91 return range.getValues();
92}
93
94/**
95 * Builds a mapping between the list of campaigns and the rules
96 * being applied to them.
97 *
98 * @param {Array} campaignRulesData The campaign rules data, from the
99 * spreadsheet.
100 * @return {!Object.<string, Array.<Object>> } A map, with key as campaign name,
101 * and value as an array of rules that apply to this campaign.
102 */
103function buildCampaignRulesMapping(campaignRulesData) {
104 var campaignMapping = {};
105 for (var i = 0; i < campaignRulesData.length; i++) {
106 // Skip rule if not enabled.
107
108 if (campaignRulesData[i][5].toLowerCase() == 'yes') {
109 var campaignName = campaignRulesData[i][0];
110 var campaignRules = campaignMapping[campaignName] || [];
111 campaignRules.push({
112 'name': campaignName,
113
114 // location for which this rule applies.
115 'location': campaignRulesData[i][1],
116
117 // the weather condition (e.g. Sunny).
118 'condition': campaignRulesData[i][2],
119
120 // bid modifier to be applied.
121 'bidModifier': campaignRulesData[i][3],
122
123 // whether bid adjustments should by applied only to geo codes
124 // matching the location of the rule or to all geo codes that
125 // the campaign targets.
126 'targetedOnly': campaignRulesData[i][4].toLowerCase() ==
127 'matching geo targets'
128 });
129 campaignMapping[campaignName] = campaignRules;
130 }
131 }
132 Logger.log('Campaign Mapping: %s', campaignMapping);
133 return campaignMapping;
134}
135
136/**
137 * Builds a mapping between a weather condition name (e.g. Sunny) and the rules
138 * that correspond to that weather condition.
139 *
140 * @param {Array} weatherConditionData The weather condition data from the
141 * spreadsheet.
142 * @return {!Object.<string, Array.<Object>>} A map, with key as a weather
143 * condition name, and value as the set of rules corresponding to that
144 * weather condition.
145 */
146function buildWeatherConditionMapping(weatherConditionData) {
147 var weatherConditionMapping = {};
148
149 for (var i = 0; i < weatherConditionData.length; i++) {
150 var weatherConditionName = weatherConditionData[i][0];
151 weatherConditionMapping[weatherConditionName] = {
152 // Condition name (e.g. Sunny)
153 'condition': weatherConditionName,
154
155 // Temperature (e.g. 50 to 70)
156 'temperature': weatherConditionData[i][1],
157
158 // Precipitation (e.g. below 70)
159 'precipitation': weatherConditionData[i][2],
160
161 // Wind speed (e.g. above 5)
162 'wind': weatherConditionData[i][3]
163 };
164 }
165 Logger.log('Weather condition mapping: %s', weatherConditionMapping);
166 return weatherConditionMapping;
167}
168
169/**
170 * Builds a mapping between a location name (as understood by OpenWeatherMap
171 * API) and a list of geo codes as identified by Google Ads scripts.
172 *
173 * @param {Array} geoTargetData The geo target data from the spreadsheet.
174 * @return {!Object.<string, Array.<Object>>} A map, with key as a locaton name,
175 * and value as an array of geo codes that correspond to that location
176 * name.
177 */
178function buildLocationMapping(geoTargetData) {
179 var locationMapping = {};
180 for (var i = 0; i < geoTargetData.length; i++) {
181 var locationName = geoTargetData[i][0];
182 var locationDetails = locationMapping[locationName] || {
183 'geoCodes': [] // List of geo codes understood by Google Ads scripts.
184 };
185
186 locationDetails.geoCodes.push(geoTargetData[i][1]);
187 locationMapping[locationName] = locationDetails;
188 }
189 Logger.log('Location Mapping: %s', locationMapping);
190 return locationMapping;
191}
192
193/**
194 * Applies rules to a campaign.
195 *
196 * @param {string} campaignName The name of the campaign.
197 * @param {Object} campaignRules The details of the campaign. See
198 * buildCampaignMapping for details.
199 * @param {Object} locationMapping Mapping between a location name (as
200 * understood by OpenWeatherMap API) and a list of geo codes as
201 * identified by Google Ads scripts. See buildLocationMapping for details.
202 * @param {Object} weatherConditionMapping Mapping between a weather condition
203 * name (e.g. Sunny) and the rules that correspond to that weather
204 * condition. See buildWeatherConditionMapping for details.
205 */
206function applyRulesForCampaign(campaignName, campaignRules, locationMapping,
207 weatherConditionMapping) {
208 for (var i = 0; i < campaignRules.length; i++) {
209 var bidModifier = 1;
210 var campaignRule = campaignRules[i];
211
212 // Get the weather for the required location.
213 var locationDetails = locationMapping[campaignRule.location];
214 var weather = getWeather(campaignRule.location);
215 Logger.log('Weather for %s: %s', locationDetails, weather);
216
217 // Get the weather rules to be checked.
218 var weatherConditionName = campaignRule.condition;
219 var weatherConditionRules = weatherConditionMapping[weatherConditionName];
220
221 // Evaluate the weather rules.
222 if (evaluateWeatherRules(weatherConditionRules, weather)) {
223 Logger.log('Matching Rule found: Campaign Name = %s, location = %s, ' +
224 'weatherName = %s,weatherRules = %s, noticed weather = %s.',
225 campaignRule.name, campaignRule.location,
226 weatherConditionName, weatherConditionRules, weather);
227 bidModifier = campaignRule.bidModifier;
228
229 if (TARGETING == 'LOCATION' || TARGETING == 'ALL') {
230 // Get the geo codes that should have their bids adjusted.
231 var geoCodes = campaignRule.targetedOnly ?
232 locationDetails.geoCodes : null;
233 adjustBids(campaignName, geoCodes, bidModifier);
234 }
235
236 if (TARGETING == 'PROXIMITY' || TARGETING == 'ALL') {
237 var location = campaignRule.targetedOnly ? campaignRule.location : null;
238 adjustProximityBids(campaignName, location, bidModifier);
239 }
240
241 }
242 }
243 return;
244}
245
246/**
247 * Converts a temperature value from kelvin to fahrenheit.
248 *
249 * @param {number} kelvin The temperature in Kelvin scale.
250 * @return {number} The temperature in Fahrenheit scale.
251 */
252function toFahrenheit(kelvin) {
253 return (kelvin - 273.15) * 1.8 + 32;
254}
255
256/**
257 * Evaluates the weather rules.
258 *
259 * @param {Object} weatherRules The weather rules to be evaluated.
260 * @param {Object.<string, string>} weather The actual weather.
261 * @return {boolean} True if the rule matches current weather conditions,
262 * False otherwise.
263 */
264function evaluateWeatherRules(weatherRules, weather) {
265 // See https://openweathermap.org/weather-data
266 // for values returned by OpenWeatherMap API.
267 var precipitation = 0;
268 if (weather.rain && weather.rain['3h']) {
269 precipitation = weather.rain['3h'];
270 }
271 var temperature = toFahrenheit(weather.main.temp);
272 var windspeed = weather.wind.speed;
273
274 return evaluateMatchRules(weatherRules.temperature, temperature) &&
275 evaluateMatchRules(weatherRules.precipitation, precipitation) &&
276 evaluateMatchRules(weatherRules.wind, windspeed);
277}
278
279/**
280 * Evaluates a condition for a value against a set of known evaluation rules.
281 *
282 * @param {string} condition The condition to be checked.
283 * @param {Object} value The value to be checked.
284 * @return {boolean} True if an evaluation rule matches, false otherwise.
285 */
286function evaluateMatchRules(condition, value) {
287 // No condition to evaluate, rule passes.
288 if (condition == '') {
289 return true;
290 }
291 var rules = [matchesBelow, matchesAbove, matchesRange];
292
293 for (var i = 0; i < rules.length; i++) {
294 if (rules[i](condition, value)) {
295 return true;
296 }
297 }
298 return false;
299}
300
301/**
302 * Evaluates whether a value is below a threshold value.
303 *
304 * @param {string} condition The condition to be checked. (e.g. below 50).
305 * @param {number} value The value to be checked.
306 * @return {boolean} True if the value is less than what is specified in
307 * condition, false otherwise.
308 */
309function matchesBelow(condition, value) {
310 conditionParts = condition.split(' ');
311
312 if (conditionParts.length != 2) {
313 return false;
314 }
315
316 if (conditionParts[0] != 'below') {
317 return false;
318 }
319
320 if (value < conditionParts[1]) {
321 return true;
322 }
323 return false;
324}
325
326/**
327 * Evaluates whether a value is above a threshold value.
328 *
329 * @param {string} condition The condition to be checked. (e.g. above 50).
330 * @param {number} value The value to be checked.
331 * @return {boolean} True if the value is greater than what is specified in
332 * condition, false otherwise.
333 */
334function matchesAbove(condition, value) {
335 conditionParts = condition.split(' ');
336
337 if (conditionParts.length != 2) {
338 return false;
339 }
340
341 if (conditionParts[0] != 'above') {
342 return false;
343 }
344
345 if (value > conditionParts[1]) {
346 return true;
347 }
348 return false;
349}
350
351/**
352 * Evaluates whether a value is within a range of values.
353 *
354 * @param {string} condition The condition to be checked (e.g. 5 to 18).
355 * @param {number} value The value to be checked.
356 * @return {boolean} True if the value is in the desired range, false otherwise.
357 */
358function matchesRange(condition, value) {
359 conditionParts =
360
361 if (conditionParts.length != 3) {
362 return false;
363 }
364
365 if
366 return false;
367 }
368
369 if (conditionParts[0] <=
370 return true;
371 }
372 return false;
373}
374
375/**
376 * Retrieves the weather for a given location, using the OpenWeatherMap API.
377 *
378 * @param {string} location The location to get the weather for.
379 * @return {Object.<string, string>} The weather attributes and values, as
380 * defined in the API.
381 */
382function getWeather(location)
383 if (location in WEATHER_LOOKUP_CACHE)
384 Logger.log(
385 return WEATHER_LOOKUP_CACHE[
386 }
387
388 var url = Utilities.
389 'http://api.openweathermap.org/data/2.5/weather?APPID=%s&q=%s',
390 encodeURIComponent
391 encodeURIComponent(
392 var response = UrlFetchApp.fetch(
393 if (response.getResponseCode() != 200)
394 throw Utilities.
395 'Error returned by API: %s, Location searched: %s.',
396 response.getContentText(), location
397 }
398
399 var result = JSON.parse(response.getContentText());
400
401 // OpenWeatherMap's way of returning errors.
402 if (result.cod != 200) {
403 throw Utilities.formatString
404 'Error returned by API: %s, Location searched: %s.'
405 response.getContentText(), location
406 }
407
408 WEATHER_LOOKUP_CACHE[location] = result
409 return result
410}
411
412/**
413 * Adjusts the bidModifier for a list of geo codes for a campaign.
414 *
415 * @param {string} campaignName The name of the campaign.
416 * @param {Array} geoCodes The list of geo codes for which bids should be
417 * adjusted. If null, all geo codes on the campaign are adjusted.
418 * @param {number} bidModifier The bid modifier to use.
419 */
420function adjustBids(campaignName, geoCodes, bidModifier) {
421 // Get the campaign.
422 var campaign = getCampaign(campaignName);
423 if (!campaign) return null;
424
425 // Get the targeted locations.
426 var locations = campaign.targeting().targetedLocations().get();
427 while (locations.hasNext()) {
428 var location = locations.next();
429 var currentBidModifier = location.getBidModifier().toFixed(2);
430
431 // Apply the bid modifier only if the campaign has a custom targeting
432 // for this geo location or if all locations are to be modified.
433 if (!geoCodes || (geoCodes.indexOf(location.getId()) != -1
434 currentBidModifier != bidModifier)) {
435 Logger.log('Setting bidModifier = %s for campaign name = %s, ' +
436 'geoCode = %s. Old bid modifier is %s.', bidModifier,
437 campaignName, location.getId(), currentBidModifier
438 location.setBidModifier(bidModifier);
439 }
440 }
441}
442
443/**
444 * Adjusts the bidModifier for campaigns targeting by proximity location
445 * for a given weather location.
446 *
447 * @param {string} campaignName The name of the campaign.
448 * @param {string} weatherLocation The weather location for which bids should be
449 * adjusted. If null, all proximity locations on the campaign are adjusted.
450 * @param {number} bidModifier The bid modifier to use.
451 */
452function adjustProximityBids(campaignName, weatherLocation, bidModifier) {
453 // Get the campaign.
454 var campaign = getCampaign(campaignName);
455 if(campaign === null) return;
456
457 // Get the proximity locations.
458 var proximities = campaign.targeting().targetedProximities().get();
459 while (proximities.hasNext()) {
460 var proximity = proximities.next();
461 var currentBidModifier = proximity.getBidModifier().toFixed(2);
462
463 // Apply the bid modifier only if the campaign has a custom targeting
464 // for this geo location or if all locations are to be modified.
465 if (!weatherLocation ||
466 (weatherNearProximity(proximity, weatherLocation) &&
467 currentBidModifier != bidModifier)) {
468 Logger.log('Setting bidModifier = %s for campaign name = %s, with ' +
469 'weatherLocation = %s in proximity area. Old bid modifier is %s.'
470 bidModifier, campaignName, weatherLocation, currentBidModifier
471 proximity.setBidModifier(bidModifier);
472 }
473 }
474}
475
476/**
477 * Checks if weather location is within the radius of the proximity location.
478 *
479 * @param {Object} proximity The targeted proximity of campaign.
480 * @param {string} weatherLocation Name of weather location to check within
481 * radius.
482 * @return {boolean} Returns true if weather location is within radius.
483 */
484function weatherNearProximity(proximity, weatherLocation) {
485 // See https://en.wikipedia.org/wiki/Haversine_formula for details on how
486 // to compute spherical distance.
487 var earthRadiusInMiles = 3960.0;
488 var degreesToRadians = Math.PI / 180.0;
489 var radiansToDegrees = 180.0 / Math.PI;
490 var kmToMiles = 0.621371;
491
492 var radiusInMiles = proximity.getRadiusUnits() == 'MILES' ?
493 proximity.getRadius() : proximity.getRadius() * kmToMiles;
494
495 // Compute the change in latitude degrees for the radius.
496 var deltaLat = (radiusInMiles / earthRadiusInMiles) * radiansToDegrees;
497 // Find the radius of a circle around the earth at given latitude.
498 var r = earthRadiusInMiles * Math.cos(proximity.getLatitude()
499 degreesToRadians);
500 // Compute the change in longitude degrees for the radius.
501 var deltaLon = (radiusInMiles / r) * radiansToDegrees;
502
503 // Retrieve weather location for lat/lon coordinates.
504 var weather = getWeather(weatherLocation);
505 // Check if weather condition is within the proximity boundaries.
506 return (weather.coord.lat >= proximity.getLatitude() - deltaLat &&
507 weather.coord.lat <= proximity.getLatitude() + deltaLat &&
508 weather.coord.lon >= proximity.getLongitude() - deltaLon &&
509 weather.coord.lon <= proximity.getLongitude() + deltaLon);
510}
511
512/**
513 * Finds a campaign by name, whether it is a regular, video, or shopping
514 * campaign, by trying all in sequence until it finds one.
515 *
516 * @param {string} campaignName The campaign name to find.
517 * @return {Object} The campaign found, or null if none was found.
518 */
519function getCampaign(campaignName) {
520 var selectors = [AdsApp.campaigns(), AdsApp.videoCampaigns(),
521 AdsApp.shoppingCampaigns()];
522 for(var i = 0; i < selectors.length; i++) {
523 var campaignIter = selectors[i].
524 withCondition('CampaignName = "' + campaignName + '"').
525 get();
526 if (campaignIter.hasNext()) {
527 return campaignIter.next();
528 }
529 }
530 return null;
531}
532
533/**
534 * DO NOT EDIT ANYTHING BELOW THIS LINE.
535 * Please modify your spreadsheet URL and API key at the top of the file only.
536 */
537
538/**
539 * Validates the provided spreadsheet URL to make sure that it's set up
540 * properly. Throws a descriptive error message if validation fails.
541 *
542 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
543 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
544 * @throws {Error} If the spreadsheet URL hasn't been set
545 */
546function validateAndGetSpreadsheet(spreadsheeturl) {
547 if (spreadsheeturl == 'INSERT_SPREADSHEET_URL_HERE') {
548 throw new Error('Please specify a valid Spreadsheet URL. You can find' +
549 ' a link to a template in the associated guide for this script.');
550 }
551 var spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
552 return spreadsheet;
553}
554
555/**
556 * Validates the provided API key to make sure that it's not the default. Throws
557 * a descriptive error message if validation fails.
558 *
559 * @throws {Error} If the configured API key hasn't been set.
560 */
561function validateApiKey() {
562 if (OPEN_WEATHER_MAP_API_KEY == 'INSERT_OPEN_WEATHER_MAP_API_KEY_HERE') {
563 throw new Error('Please specify a valid API key for OpenWeatherMap. You ' +
564 'can acquire one here: http://openweathermap.org/appid');
565 }
566}