這段指令碼會擴充彈性預算，讓您在單一管理員帳戶下有多個帳戶執行。彈性預算可透過自訂預算分配計畫，動態調整每日廣告活動預算。
這個指令碼會針對每個指定帳戶/廣告活動和相應的預算 (與開始日期和結束日期相關) 讀取試算表，找出該廣告活動、計算當天的預算、設為廣告活動的每日預算，並將結果記錄在試算表中。而不會觸碰試算表中未指定的廣告活動。
運作方式
指令碼的運作方式與單一帳戶的彈性預算指令碼相同。唯一的額外功能是透過指定試算表支援多個帳戶。
前 2 個資料欄會指定要計算預算的廣告活動，接下來 3 個資料欄會指定預算資訊，以及上次記錄執行結果的廣告活動。
帳戶 ID 必須為廣告主帳戶，而非管理員帳戶。
一個帳戶/廣告活動可以有多筆預算，但請務必一次只使用一筆有效預算，否則較新的預算計算結果可能會覆寫舊的預算
如未在試算表中指定帳戶/廣告活動，指令碼就不會為其設定彈性預算。
測試預算策略
指令碼包含測試程式碼，可模擬執行數天的效果。這可讓您進一步瞭解排定每天執行指令碼後會發生的情況。
根據預設，此指令碼會模擬在 10 天內支出 $500 美元的預算平均分配方式。
您可以在主要方法中呼叫 testBudgetStrategy (而非 setNewBudget) 來執行測試程式碼：
function main() {
testBudgetStrategy(calculateBudgetEvenly, 10, 500);
// setNewBudget(calculateBudgetWeighted);
}
setNewBudget 函式呼叫會加上註解，表示指令碼正在執行測試程式碼。以下是範例的輸出結果：
Day 1.0 of 10.0, new budget 50.0, cost so far 0.0
Day 2.0 of 10.0, new budget 50.0, cost so far 50.0
Day 3.0 of 10.0, new budget 50.0, cost so far 100.0
Day 4.0 of 10.0, new budget 50.0, cost so far 150.0
Day 5.0 of 10.0, new budget 50.0, cost so far 200.0
Day 6.0 of 10.0, new budget 50.0, cost so far 250.0
Day 7.0 of 10.0, new budget 50.0, cost so far 300.0
Day 8.0 of 10.0, new budget 50.0, cost so far 350.0
Day 9.0 of 10.0, new budget 50.0, cost so far 400.0
Day 10.0 of 10.0, new budget 50.0, cost so far 450.0
Day 11.0 of 10.0, new budget 0.0, cost so far 500.0
系統會每天計算新的預算，確保每天平均支出預算。超出初始預算配額後，系統會將預算設為 0，以停止支出。
如要變更所用預算策略，您可以變更使用的函式，或是修改函式。這個指令碼有兩個預先建構的策略：calculateBudgetEvenly 和 calculateBudgetWeighted；上一個範例剛測試了前者，請更新 testBudgetStrategy 行以使用後者：
testBudgetStrategy(calculateBudgetWeighted, 10, 500);
按一下 [預覽] 並檢查記錄工具輸出。請注意，此預算策略在測試期間初期分配的預算較少，並在接下來幾天增加。
您可使用此測試方法模擬預算計算函式的變更，並嘗試自己的預算分配方式。
預算分配
讓我們進一步瞭解 calculateBudgetWeighted 預算策略：
// One calculation logic that distributes remaining budget in a weighted manner
function calculateBudgetWeighted(costSoFar, totalBudget, daysSoFar, totalDays) {
const daysRemaining = totalDays - daysSoFar;
const budgetRemaining = totalBudget - costSoFar;
if (daysRemaining <= 0) {
return budgetRemaining;
} else {
return budgetRemaining / (2 * daysRemaining - 1);
}
}
這個函式使用以下引數：
costSoFar：這個廣告活動從startDate到今天累積的費用。totalBudget：從startDate到endDate的支出。daysSoFar：從startDate到今天的已經過天數。totalDays：startDate到endDate之間的總天數。
You can write your own function as long as it takes these arguments. 您可以加入這些引數，自行撰寫函式。透過這些值，您可以比較您到目前為止花費的金額與整體支出，並判斷您目前在整筆預算的時間軸中處於哪個位置。
具體來說，這種預算策略會推算出剩餘的預算 (totalBudget - costSoFar)，然後除以剩餘天數的兩倍。這項數據會衡量廣告活動結束前的預算分配比例。
使用自 startDate起的費用，系統也會將「過慢天數」納入考量，以免未能用完所有預算。
實際工作環境預算
對預算策略滿意後，您需要先進行幾項變更，才能排定這個指令碼每天執行。
首先，更新試算表以指定帳戶、廣告活動、預算、開始日期和結束日期， 每個廣告活動預算各一列。
- 帳戶 ID：要套用預算策略的廣告活動帳戶 ID (格式為 xxx-xxx-xxxx)。
- Campaign Name：要套用預算策略的廣告活動名稱。
- 開始日期：預算策略的開始日期。(必須是今天或前一天的日期)。
- 結束日期：要使用這筆預算放送廣告的最後一天。
- 總預算：您預計支出的總金額。這個值以帳戶貨幣計算，且視指令碼的排程執行時間而定，有可能超過設定值。
接著，停用測試並啟用邏輯來實際變更預算：
function main() {
// testBudgetStrategy(calculateBudgetEvenly, 10, 500);
setNewBudget(calculateBudgetWeighted);
}
每個廣告活動的結果會記錄在「執行結果」欄中。
排程
請將這個指令碼排定於當地時區每天午夜左右執行，盡可能調節次日預算。但請注意，擷取的報表資料 (例如費用) 可能會延遲約 3 小時，因此 costSoFar 參數可能會參考昨天排定在午夜執行的指令碼共計昨天的總計值。
設定
點選下方按鈕即可在 Google Ads 帳戶中建立指令碼。
點選下方按鈕即可建立範本試算表副本。
更新指令碼中的
spreadsheet_url。排定每天執行指令碼。
原始碼
// 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 MCC Flexible Budgets
*
* @overview The MCC Flexible Budgets script dynamically adjusts campaign budget
* daily for accounts under an MCC account with a custom budget distribution
* scheme. See
* https://developers.google.com/google-ads/scripts/docs/solutions/manager-flexible-budgets
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 2.1
*
* @changelog
* - version 2.1
* - Split into info, config, and code.
* - version 2.0
* - Updated to use new Google Ads scripts features.
* - version 1.0.4
* - Add support for video and shopping campaigns.
* - version 1.0.3
* - Added validation for external spreadsheet setup.
* - version 1.0.2
* - Fix a minor bug in variable naming.
* - Use setAmount on the budget instead of campaign.setBudget.
* - version 1.0.1
* - Improvements to time zone handling.
* - version 1.0
* - Released initial version.
*/
/**
* Configuration to be used for the Flexible Budgets script.
*/
CONFIG = {
// URL of the default spreadsheet template. This should be a copy of
// https://docs.google.com/spreadsheets/d/17wocOgrLeRWF1Qi_BjEigCG0qVMebFHrbUS-Vk_kpLg/copy
// Make sure the sheet is owned by or shared with same Google user executing the script
'spreadsheet_url': 'YOUR_SPREADSHEET_URL',
'advanced_options': {
// Please fix the following variables if you need to reformat the
// spreadsheet
// column numbers of each config column. Column A in your spreadsheet has
// column number of 1, B has number of 2, etc.
'column': {
'accountId': 2,
'campaignName': 3,
'startDate': 4,
'endDate': 5,
'totalBudget': 6,
'results': 7
},
// Actual config (without header and margin) starts from this row
'config_start_row': 5
}
};
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const COLUMN = CONFIG.advanced_options.column;
const CONFIG_START_ROW = CONFIG.advanced_options.config_start_row;
function main() {
// Uncomment the following function to test your budget strategy function
// testBudgetStrategy(calculateBudgetEvenly, 10, 500);
setNewBudget(calculateBudgetWeighted);
}
// Core logic for calculating and setting campaign daily budget
function setNewBudget(budgetFunc) {
console.log(`Using spreadsheet - ${SPREADSHEET_URL}.`);
const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
const sheet = spreadsheet.getSheets()[0];
const endRow = sheet.getLastRow();
const mccAccount = AdsApp.currentAccount();
sheet.getRange(2, 6, 1, 2).setValue(mccAccount.getCustomerId());
const today = new Date();
for (let i = CONFIG_START_ROW; i <= endRow; i++) {
console.log(`Processing row ${i}`);
const accountId = sheet.getRange(i, COLUMN.accountId).getValue();
const campaignName = sheet.getRange(i, COLUMN.campaignName).getValue();
const startDate = new Date(sheet.getRange(i, COLUMN.startDate).getValue());
const endDate = new Date(sheet.getRange(i, COLUMN.endDate).getValue());
const totalBudget = sheet.getRange(i, COLUMN.totalBudget).getValue();
const resultCell = sheet.getRange(i, COLUMN.results);
const accountIter = AdsManagerApp.accounts().withIds([accountId]).get();
if (!accountIter.hasNext()) {
resultCell.setValue('Unknown account');
continue;
}
const account = accountIter.next();
AdsManagerApp.select(account);
const campaign = getCampaign(campaignName);
if (!campaign) {
resultCell.setValue('Unknown campaign');
continue;
}
if (today < startDate) {
resultCell.setValue('Budget not started yet');
continue;
}
if (today > endDate) {
resultCell.setValue('Budget already finished');
continue;
}
const costSoFar = campaign
.getStatsFor(
getDateStringInTimeZone('yyyyMMdd', startDate),
getDateStringInTimeZone('yyyyMMdd', endDate))
.getCost();
const daysSoFar = datediff(startDate, today);
const totalDays = datediff(startDate, endDate);
const newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
campaign.getBudget().setAmount(newBudget);
console.log(
`AccountId=${accountId}, CampaignName=${campaignName}, ` +
`StartDate=${startDate}, EndDate=${endDate}, ` +
`CostSoFar=${costSoFar}, DaysSoFar=${daysSoFar}, ` +
`TotalDays=${totalDays}, NewBudget=${newBudget}'`);
resultCell.setValue(`Set today's budget to ${newBudget}`);
}
// update "Last execution" timestamp
sheet.getRange(1, 3).setValue(today);
AdsManagerApp.select(mccAccount);
}
// One calculation logic that distributes remaining budget evenly
function calculateBudgetEvenly(costSoFar, totalBudget, daysSoFar, totalDays) {
const daysRemaining = totalDays - daysSoFar;
const budgetRemaining = totalBudget - costSoFar;
if (daysRemaining <= 0) {
return budgetRemaining;
} else {
return budgetRemaining / daysRemaining;
}
}
// One calculation logic that distributes remaining budget in a weighted manner
function calculateBudgetWeighted(costSoFar, totalBudget, daysSoFar, totalDays) {
const daysRemaining = totalDays - daysSoFar;
const budgetRemaining = totalBudget - costSoFar;
if (daysRemaining <= 0) {
return budgetRemaining;
} else {
return budgetRemaining / (2 * daysRemaining - 1);
}
}
// Test function to verify budget calculation logic
function testBudgetStrategy(budgetFunc, totalDays, totalBudget) {
let daysSoFar = 0;
let costSoFar = 0;
while (daysSoFar <= totalDays + 2) {
const newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
console.log(
`Day ${daysSoFar + 1} of ${totalDays}, ` +
`new budget ${newBudget}, cost so far ${costSoFar}`);
costSoFar += newBudget;
daysSoFar += 1;
}
}
// Return number of days between two dates, rounded up to nearest whole day.
function datediff(from, to) {
const millisPerDay = 1000 * 60 * 60 * 24;
return Math.ceil((to - from) / millisPerDay);
}
// Produces a formatted string representing a given date in a given time zone.
function getDateStringInTimeZone(format, date, timeZone) {
date = date || new Date();
timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
return Utilities.formatDate(date, timeZone, format);
}
/**
* 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;
}
/**
* 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 == 'YOUR_SPREADSHEET_URL') {
throw new Error(
'Please specify a valid Spreadsheet URL. You can find' +
' a link to a template in the associated guide for this script.');
}
return SpreadsheetApp.openByUrl(spreadsheeturl);
}