对某些产品和服务的需求会因天气而产生巨大差异。例如，与在寒冷的下雨天相比，用户在炎热的晴天更可能搜索游乐园信息。游乐园公司可能希望在天气晴好时提高出价，但这样每天都会导致大量手动操作。不过有了 Google Ads 脚本，您可通过编程方式获取天气信息，并在短短几分钟内调整出价。
此脚本使用 Google 电子表格存储广告系列及其关联地理位置的列表。系统会为每个地理位置调用 OpenWeatherMap API，并使用一些基本规则计算天气条件。如果规则的计算结果为 true，则系统会对广告系列的地理位置定位应用相应的地理位置出价调节系数。
运作方式
脚本通过读取电子表格中的数据来运行。该电子表格由三张单独的工作表组成：
1. 广告系列数据
一组规则决定在天气条件满足时向广告系列应用的出价调节系数。以下是必需的列：
- 广告系列名称：要修改的广告系列的名称。
- Weather Location：要检查其天气条件的地理位置。
- Weather Condition（天气条件）：要应用此规则的天气条件。
- 出价调节系数：在天气条件满足时应用的地理位置出价调节系数。
- Apply Modifier To（将系数应用于）：出价调节系数是仅应用于与天气位置匹配的广告系列地理位置定位，还是应用于所有广告系列地理位置定位。
- 已启用：指定
Yes可启用规则，指定No可停用规则。
示例
以下示例中有 3 个广告系列。
测试广告系列 1 说明了一个典型的使用场景。该广告系列定位到马萨诸塞州波士顿，并有两条规则：
- 如果马萨诸塞州波士顿的天气为
Sunny，应用出价调节系数1.3。 - 如果马萨诸塞州波士顿的天气为
Rainy，应用出价调节系数0.8。
测试广告系列 2 与测试广告系列 1 具有相同的出价规则，但定位到康涅狄格州。
测试广告系列 3 也使用相同的出价规则，但定位到佛罗里达州。由于佛罗里达州的天气规则会映射到整个州，而整个州并不是广告系列明确定位到的地理位置，因此“将系数应用于”设为 All Geo Targets，以便广告系列定位的城市会受到影响。
2. 天气数据
此工作表定义在广告系列数据工作表中使用的天气条件。 以下列是必需的：
- Condition Name：天气条件名称（例如
Sunny）。 - Temperature（温度）：华氏度。
- 降水：过去 3 小时内的降水量（以毫米为单位）。
- 风：风速，以英里/小时为单位。
上述工作表定义了两个天气条件：
Sunny：温度在 65 到 80 华氏度之间，过去 3 小时内的降水量低于 1 毫米，且风速低于 5 英里/小时。Rainy：过去 3 小时内的降水量超过 0 毫米，且风速低于 10 英里/小时。
天气状况
定义天气条件时，请按如下方式指定值：
below x：指定值为below x（例如below 10）above x：指定值为above x（例如above 70）x to y：指定的值介于x和y之间（包括这两个数值，例如65 to 80）
如果您将单元格留空，计算时不会考虑该参数。因此，在我们的示例中，由于 Rainy 天气条件的值为空，因此在计算此天气条件时不会考虑温度。
在计算天气条件时，天气条件以 AND 相连。在此示例中，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 进行匹配。
邻近区域定位使用半正弦公式验证指定的纬度和经度坐标是否位于邻近区域半径内。
脚本逻辑
脚本首先从所有三个工作表中读取规则。然后，它会依序尝试执行“Campaign”工作表中的每条规则。
对于执行的每条规则，脚本都会检查广告系列是否定位到指定位置。如果确定，脚本会检索当前的出价调节系数。
接下来，通过调用 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');
}
}