This script extends Flexible Budgets to run for multiple accounts under a single manager account. Flexible Budgets can dynamically adjust your campaign budget daily with a custom budget distribution scheme.
The script reads a spreadsheet for each specified account/campaign and corresponding budget (associated with start date and end date), finds the campaign, calculates the budget for the current day, sets it as the campaign's daily budget, and records the result in the spreadsheet. It won't touch campaigns not specified in the spreadsheet.
How it works
The script works the same way the single account Flexible Budget script does. The only additional functionality is that it supports multiple accounts through the specified spreadsheet.
The first 2 columns specify the campaign for which to calculate a budget, the next 3 specify its budget information, and the last records the execution result.
The account ID should be an advertiser account, not a manager account.
You can have multiple budgets for the same account/campaign, but make sure you only have one active budget at a time, or else a newer budget calculation might overwrite an older one
If an account/campaign is not specified in the spreadsheet, the script won't set a flexible budget for it.
Testing budget strategies
The script includes testing code to simulate the effects of running for multiple days. This gives you a better idea of what happens when the script is scheduled to run daily over a period of time.
By default, this script simulates an even budget distribution of $500 spent over 10 days.
You can run the testing code by calling testBudgetStrategy instead of
setNewBudget in the main method:
function main() {
testBudgetStrategy(calculateBudgetEvenly, 10, 500);
// setNewBudget(calculateBudgetWeighted);
}
The setNewBudget function call is commented out, indicating the script is
running test code. Here is the output from the example:
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
Each day a new budget is calculated to make sure the budget is spent evenly each day. After the initial budget allotment is exceeded, the budget is set to zero to halt spending.
You can change the budget strategy used by changing which function is used, or
by modifying the function itself. The script comes with two pre-built
strategies: calculateBudgetEvenly and calculateBudgetWeighted; the previous
example just tested the former—update the testBudgetStrategy line to
use the latter:
testBudgetStrategy(calculateBudgetWeighted, 10, 500);
Click Preview and check the logger output. Notice that this budget strategy allocates less budget early in the period and increases it for the next few days.
You can use this test method to simulate changes to the budget calculation functions and try your own approach to distributing a budget.
Budget allocation
Let's look more closely at the calculateBudgetWeighted budget strategy:
// 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);
}
}
This function takes the following arguments:
costSoFar: How much has this campaign accrued in cost from thestartDateto today.totalBudget: How much to spend fromstartDatetoendDate.daysSoFar: How many days have elapsed fromstartDateto today.totalDays: Total number of days betweenstartDateandendDate.
You can write your own function as long as it takes these arguments. Using these values, you can compare how much money you've spent so far against how much to spend overall and determine where you currently are within the timeline for the entire budget.
In particular, this budget strategy figures out how much budget remains
(totalBudget - costSoFar) and divides that by twice the number of days
remaining. This weighs the budget distribution towards the end of the campaign.
By using the cost since startDate, it also takes into account "slow days"
where you don't spend the entire budget you set.
Budgeting in production
Once you're happy with your budget strategy, you'll need to make a few changes before you can schedule this script to run daily.
First, update the spreadsheet to specify account, campaign, budget, start date, end date—one row for each campaign budget.
- Account ID: Account ID (of format xxx-xxx-xxxx) of the campaign to apply the budget strategy to.
- Campaign Name: Name of the campaign to apply the budget strategy to.
- Start Date: Start date of your budget strategy. This should be the current date or a day in the past.
- End Date: Last day you want to advertise with this budget.
- Total Budget: Total amount you're trying to spend. This value is in the account currency and can be exceeded depending on when the script is scheduled to run.
Next, disable the test and enable the logic to actually change the budget:
function main() {
// testBudgetStrategy(calculateBudgetEvenly, 10, 500);
setNewBudget(calculateBudgetWeighted);
}
The result for each campaign is recorded in the Execution Result column.
Scheduling
Schedule this script to run daily, at or shortly after midnight in the local
timezone so as to direct as much as possible the upcoming day's budget. Note,
however, that retrieved reports data such as cost could be delayed by about 3
hours, so the costSoFar parameter might be referencing yesterday's total for
a script that is scheduled to run after midnight.
Setup
Click the button below to create the script in your Google Ads account.
Click the button below to make a copy of the template spreadsheet.
Update
spreadsheet_urlin your script.Schedule the script to run Daily.
Source code
// 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);
}