某些產品和服務的需求因天氣而異。例如，相較於天氣冷和下雨，使用者更可能在晴朗的晴天搜尋遊樂園的相關資訊。遊樂園公司可能想在天氣宜的時候提高出價，但每天做的事情可能需要不少工夫。不過，只要使用 Google Ads 指令碼，就能在短短幾分鐘內，透過程式輔助方式擷取天氣資訊並調整出價。
這個指令碼使用 Google 試算表儲存廣告活動及其相關位置的清單。每個地點都會呼叫 OpenWeatherMap API，而天氣狀況是以一些基本規則來計算。如果規則評估為 true，系統會為廣告活動的指定地區套用相應的地區出價調節係數。
運作方式
指令碼的運作方式是讀取試算表中的資料。試算表包含三個個別工作表：
1. 廣告活動資料
一組規則會決定當符合天氣條件時，要套用至廣告活動的出價調節係數。必要欄如下：
- 廣告活動名稱：要修改的廣告活動名稱。
- Weather Location (天氣位置)：要檢查天氣狀況的位置。
- Weather Condition：要套用此規則的天氣狀況。
- 出價調節係數：符合天氣條件時要套用的地區出價調節係數。
- Apply Modifier To：是否將出價調節係數套用至與天氣位置相符的廣告活動指定地理區域，或套用至所有廣告活動指定地理區域。
- Enabled：指定
Yes可啟用規則，指定No可停用規則。
範例
以下範例有三個廣告活動，
測試廣告活動 1 說明一般使用情境。廣告活動以麻州波士頓為指定目標 且有兩項規則：
- 如果波士頓的天氣為
Sunny，請套用1.3的出價調節係數。 - 如果波士頓的天氣為
Rainy，請套用0.8的出價調節係數。
測試廣告活動 2 的出價規則與測試廣告活動 1 相同，但以康乃狄克州為目標。
測試廣告活動 3 也使用相同的出價規則，但指定佛羅裡達。由於佛羅裡達的天氣規則會對應至整個州，而非廣告活動明確指定的地區，因此「Apply Modifier To」設為 All Geo Targets，這樣廣告活動指定的城市才會受到影響。
2. 天氣資料
這份工作表定義了廣告活動資料表中使用的天氣條件。您必須使用的資料欄如下：
- Condition Name (條件名稱)：天氣狀況名稱 (例如
Sunny)。 - Temperature：華氏的溫度。
- 降雨量：過去 3 小時內的降雨量 (以公釐為單位)。
- 風速：風速，以每小時英里為單位。
上方顯示的表單定義了兩種天候：
Sunny：溫度介於華氏 65 到 80 度之間、過去三小時降雨量低於 1 公釐，且風速低於 5 英里/小時。Rainy：過去三小時降雨量超過 0 公釐，且風速低於 10 英里/小時。
天候不佳
定義天氣條件時，請指定下列值：
below x：指定值為below x(例如below 10)above x：指定值為above x(例如above 70)x to y：指定值介於x和y之間 (含頭尾，例如65 to 80)
如果將儲存格留空，計算結果就不會納入該參數。因此在本範例中，由於 Rainy 天氣條件中有空白的溫度欄，因此在計算這個天氣狀況時，不會考慮溫度。
在計算天氣狀況時，天氣狀況會相互結合。在這個範例中，Sunny 天氣條件的評估方式如下：
const isSunny = (temperature >= 65 && temperature <= 80) && (precipitation < 1) && (wind < 5);
3. 天氣位置資料
這份工作表定義了廣告活動資料表中使用的天氣位置，由以下兩個資料欄組成：
- 天氣位置：這是天氣位置名稱，如 OpenWeatherMap API 所解讀的名稱。
- 指定地理區域代碼：這是指定地理區域代碼，如 Google Ads 所瞭解。
指令碼可讓您為單一天氣地點指定多個指定地理區域代碼，因為天氣位置不一定會像 Google Ads 中的指定目標選項一樣精細。為單一天氣位置對應至多個地理位置，只要建立多個資料列包含相同天氣位置，但每一列的地理代碼不同即可。
在這個範例中，定義了三個天氣位置：
Boston, MA：地理代碼10108127Connecticut：地理編碼1014778、1014743和1014843，對應於康乃狄克州三個城市Florida：地理代碼21142
指定鄰近目標
使用 Matching Geo Targets 的廣告活動規則可運用 TARGETING 標記套用至目標地區和/或指定鄰近目標。
指定地區與地理代碼與地區 ID 相符。
指定鄰近目標會使用 haversine 公式，確認指定的經緯度座標位於鄰近半徑範圍內。
指令碼邏輯
指令碼會先讀取三張表單中的規則，接著會嘗試依序執行廣告活動工作表中的每項規則。
對於已執行的規則，指令碼會檢查廣告活動是否指定指定位置。如果是的話，指令碼會擷取目前的出價調節係數。
接下來，呼叫 OpenWeatherMap API，擷取該位置的天氣情況。接著會評估天氣條件規則，查看該位置的天氣狀況是否與規則中指定的內容相符。如果條件相符，且新的出價調節係數與目前的出價調節係數不同，指令碼就會修改該位置的出價調節係數。
如果天氣條件不相符、出價調節係數值相同，或是規則的 Apply Modifier To 為 Matching Geo
Targets，但廣告活動並未指定對應至規則的地點，系統就不會進行任何變更。
設定
- 前往 openweathermap.org 註冊 API 金鑰。
- 複製範本試算表並編輯廣告活動和天氣規則。
- 使用下方的原始碼建立新指令碼。
- 更新指令碼中的
OPEN_WEATHER_MAP_API_KEY、SPREADSHEET_URL和TARGETING變數。 - 依需要排定執行時段。
原始碼
// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
/**
* @name Bid By Weather
*
* @overview The Bid By Weather script adjusts campaign bids by weather
* conditions of their associated locations. See
* https://developers.google.com/google-ads/scripts/docs/solutions/weather-based-campaign-management#bid-by-weather
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 2.0
*
* @changelog
* - version 2.0
* - Updated to use new Google Ads Scripts features.
* - version 1.2.2
* - Add support for video and shopping campaigns.
* - version 1.2.1
* - Added validation for external spreadsheet setup.
* - version 1.2
* - Added proximity based targeting. Targeting flag allows location
* targeting, proximity targeting or both.
* - version 1.1
* - Added flag allowing bid adjustments on all locations targeted by
* a campaign rather than only those that match the campaign rule
* - version 1.0
* - Released initial version.
*/
// Register for an API key at http://openweathermap.org/appid
// and enter the key below.
const OPEN_WEATHER_MAP_API_KEY = 'INSERT_OPEN_WEATHER_MAP_API_KEY_HERE';
// Create a copy of https://goo.gl/A59Uuc and enter the URL below.
const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
// A cache to store the weather for locations already lookedup earlier.
const WEATHER_LOOKUP_CACHE = {};
// Flag to pick which kind of targeting "LOCATION", "PROXIMITY", or "ALL".
const TARGETING = 'ALL';
/**
* According to the list of campaigns and their associated locations, the script
* makes a call to the OpenWeatherMap API for each location.
* Based on the weather conditions, the bids are adjusted.
*/
function main() {
validateApiKey();
// Load data from spreadsheet.
const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
const campaignRuleData = getSheetData(spreadsheet, 1);
const weatherConditionData = getSheetData(spreadsheet, 2);
const geoMappingData = getSheetData(spreadsheet, 3);
// Convert the data into dictionaries for convenient usage.
const campaignMapping = buildCampaignRulesMapping(campaignRuleData);
const weatherConditionMapping =
buildWeatherConditionMapping(weatherConditionData);
const locationMapping = buildLocationMapping(geoMappingData);
// Apply the rules.
for (const 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) {
const sheet = spreadsheet.getSheets()[sheetIndex];
const 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) {
const campaignMapping = {};
for (const rules of campaignRulesData) {
// Skip rule if not enabled.
if (rules[5].toLowerCase() == 'yes') {
const campaignName = rules[0];
const campaignRules = campaignMapping[campaignName] || [];
campaignRules.push({
'name': campaignName,
// location for which this rule applies.
'location': rules[1],
// the weather condition (e.g. Sunny).
'condition': rules[2],
// bid modifier to be applied.
'bidModifier': rules[3],
// whether bid adjustments should by applied only to geo codes
// matching the location of the rule or to all geo codes that
// the campaign targets.
'targetedOnly': rules[4].toLowerCase() ==
'matching geo targets'
});
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) {
const weatherConditionMapping = {};
for (const weatherCondition of weatherConditionData) {
const weatherConditionName = weatherCondition[0];
weatherConditionMapping[weatherConditionName] = {
// Condition name (e.g. Sunny)
'condition': weatherConditionName,
// Temperature (e.g. 50 to 70)
'temperature': weatherCondition[1],
// Precipitation (e.g. below 70)
'precipitation': weatherCondition[2],
// Wind speed (e.g. above 5)
'wind': weatherCondition[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 Google Ads 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) {
const locationMapping = {};
for (const geoTarget of geoTargetData) {
const locationName = geoTarget[0];
const locationDetails = locationMapping[locationName] || {
'geoCodes': [] // List of geo codes understood by Google Ads scripts.
};
locationDetails.geoCodes.push(geoTarget[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 Google Ads 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 (const rules of campaignRules) {
let bidModifier = 1;
const campaignRule = rules;
// Get the weather for the required location.
const locationDetails = locationMapping[campaignRule.location];
const weather = getWeather(campaignRule.location);
Logger.log('Weather for %s: %s', locationDetails, weather);
// Get the weather rules to be checked.
const weatherConditionName = campaignRule.condition;
const 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;
if (TARGETING == 'LOCATION' || TARGETING == 'ALL') {
// Get the geo codes that should have their bids adjusted.
const geoCodes = campaignRule.targetedOnly ?
locationDetails.geoCodes : null;
adjustBids(campaignName, geoCodes, bidModifier);
}
if (TARGETING == 'PROXIMITY' || TARGETING == 'ALL') {
const location = campaignRule.targetedOnly ? campaignRule.location : null;
adjustProximityBids(campaignName, location, 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 https://openweathermap.org/weather-data
// for values returned by OpenWeatherMap API.
let precipitation = 0;
if (weather.rain && weather.rain['3h']) {
precipitation = weather.rain['3h'];
}
const temperature = toFahrenheit(weather.main.temp);
const 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;
}
const rules = [matchesBelow, matchesAbove, matchesRange];
for (const rule of rules) {
if (rule(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];
}
const url=`http://api.openweathermap.org/data/2.5/weather?APPID=${OPEN_WEATHER_MAP_API_KEY}&q=${location}`;
const response = UrlFetchApp.fetch(url);
if (response.getResponseCode() != 200) {
throw Utilities.formatString(
'Error returned by API: %s, Location searched: %s.',
response.getContentText(), location);
}
const 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. If null, all geo codes on the campaign are adjusted.
* @param {number} bidModifier The bid modifier to use.
*/
function adjustBids(campaignName, geoCodes, bidModifier) {
// Get the campaign.
const campaign = getCampaign(campaignName);
if (!campaign) return null;
// Get the targeted locations.
const locations = campaign.targeting().targetedLocations().get();
for (const location of locations) {
const currentBidModifier = location.getBidModifier().toFixed(2);
// Apply the bid modifier only if the campaign has a custom targeting
// for this geo location or if all locations are to be modified.
if (!geoCodes || (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);
}
}
}
/**
* Adjusts the bidModifier for campaigns targeting by proximity location
* for a given weather location.
*
* @param {string} campaignName The name of the campaign.
* @param {string} weatherLocation The weather location for which bids should be
* adjusted. If null, all proximity locations on the campaign are adjusted.
* @param {number} bidModifier The bid modifier to use.
*/
function adjustProximityBids(campaignName, weatherLocation, bidModifier) {
// Get the campaign.
const campaign = getCampaign(campaignName);
if(campaign === null) return;
// Get the proximity locations.
const proximities = campaign.targeting().targetedProximities().get();
for (const proximity of proximities) {
const currentBidModifier = proximity.getBidModifier().toFixed(2);
// Apply the bid modifier only if the campaign has a custom targeting
// for this geo location or if all locations are to be modified.
if (!weatherLocation ||
(weatherNearProximity(proximity, weatherLocation) &&
currentBidModifier != bidModifier)) {
Logger.log('Setting bidModifier = %s for campaign name = %s, with ' +
'weatherLocation = %s in proximity area. Old bid modifier is %s.',
bidModifier, campaignName, weatherLocation, currentBidModifier);
proximity.setBidModifier(bidModifier);
}
}
}
/**
* Checks if weather location is within the radius of the proximity location.
*
* @param {Object} proximity The targeted proximity of campaign.
* @param {string} weatherLocation Name of weather location to check within
* radius.
* @return {boolean} Returns true if weather location is within radius.
*/
function weatherNearProximity(proximity, weatherLocation) {
// See https://en.wikipedia.org/wiki/Haversine_formula for details on how
// to compute spherical distance.
const earthRadiusInMiles = 3960.0;
const degreesToRadians = Math.PI / 180.0;
const radiansToDegrees = 180.0 / Math.PI;
const kmToMiles = 0.621371;
const radiusInMiles = proximity.getRadiusUnits() == 'MILES' ?
proximity.getRadius() : proximity.getRadius() * kmToMiles;
// Compute the change in latitude degrees for the radius.
const deltaLat = (radiusInMiles / earthRadiusInMiles) * radiansToDegrees;
// Find the radius of a circle around the earth at given latitude.
const r = earthRadiusInMiles * Math.cos(proximity.getLatitude() *
degreesToRadians);
// Compute the change in longitude degrees for the radius.
const deltaLon = (radiusInMiles / r) * radiansToDegrees;
// Retrieve weather location for lat/lon coordinates.
const weather = getWeather(weatherLocation);
// Check if weather condition is within the proximity boundaries.
return (weather.coord.lat >= proximity.getLatitude() - deltaLat &&
weather.coord.lat <= proximity.getLatitude() + deltaLat &&
weather.coord.lon >= proximity.getLongitude() - deltaLon &&
weather.coord.lon <= proximity.getLongitude() + deltaLon);
}
/**
* Finds a campaign by name, whether it is a regular, video, or shopping
* campaign, by trying all in sequence until it finds one.
*
* @param {string} campaignName The campaign name to find.
* @return {Object} The campaign found, or null if none was found.
*/
function getCampaign(campaignName) {
const selectors = [AdsApp.campaigns(), AdsApp.videoCampaigns(),
AdsApp.shoppingCampaigns()];
for (const selector of selectors) {
const campaignIter = selector.
withCondition(`CampaignName = "${campaignName}"`).
get();
if (campaignIter.hasNext()) {
return campaignIter.next();
}
}
return null;
}
/**
* DO NOT EDIT ANYTHING BELOW THIS LINE.
* Please modify your spreadsheet URL and API key at the top of the file only.
*/
/**
* Validates the provided spreadsheet URL to make sure that it's set up
* properly. Throws a descriptive error message if validation fails.
*
* @param {string} spreadsheeturl The URL of the spreadsheet to open.
* @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
* @throws {Error} If the spreadsheet URL hasn't been set
*/
function validateAndGetSpreadsheet(spreadsheeturl) {
if (spreadsheeturl == 'INSERT_SPREADSHEET_URL_HERE') {
throw new Error('Please specify a valid Spreadsheet URL. You can find' +
' a link to a template in the associated guide for this script.');
}
const spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
return spreadsheet;
}
/**
* Validates the provided API key to make sure that it's not the default. Throws
* a descriptive error message if validation fails.
*
* @throws {Error} If the configured API key hasn't been set.
*/
function validateApiKey() {
if (OPEN_WEATHER_MAP_API_KEY == 'INSERT_OPEN_WEATHER_MAP_API_KEY_HERE') {
throw new Error('Please specify a valid API key for OpenWeatherMap. You ' +
'can acquire one here: http://openweathermap.org/appid');
}
}