Flexible Budgets - Manager Account

This is a Manager Account script. For operating on a single account, use the Single Account version of the script.

MCC Flexible Budgets extends Flexible Budgets to run for multiple accounts under a single MCC. 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 an MCC account. You can have multiple budgets for the same account/campaign, but please make sure you only have one active budget at a time, or else a newer budget calculation may overwrite an older one. If an account/campaign is not specified in the spreadsheet, the script will not set a flexible budget for it.

Iterating over all managed accounts

Initially the script runs in the MCC account’s context. It first invokes

var mccAccount = AdWordsApp.currentAccount();
to store the MCC account, then invokes
var accountIterator = MccApp.accounts().get();
to get an iterator of all managed accounts under the MCC.

In each iteration, the script invokes

MccApp.select(account);
to switch to the managed account’s context so that it can perform operations on that account. After processing all the managed accounts, the script invokes
MccApp.select(mccAcount);
to restore the MCC account's context.

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 will happen when the script is scheduled to run daily over a period of time.

By default, this script will simulate an even budget distribution of $500 spent over 10 days.

function main() {
  testBudgetStrategy(calculateBudgetEvenly, 10, 500);
//  setNewBudget(calculateBudgetEvenly, campaignName, totalBudget, startDate, endDate);
}

The setNewBudget function call is commented out, which means we'll only run the testing 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 we calculate a new budget to make sure we're spending the budget evenly each day. After we exceed the initial budget allotment, we set the budget to zero to halt spending.

You can change the budget strategy used by changing which function is used, or modifying the function itself. The script comes with two pre-built strategies: calculateBudgetEvenly and calculateBudgetWeighted; we've 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 more during the last 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.

Allocating a budget

Let's look more closely at the calculateBudgetWeighted budget strategy:

function calculateBudgetWeighted(costSoFar, totalBudget, daysSoFar, totalDays) {
  var daysRemaining = totalDays - daysSoFar;
  var 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 the startDate to today.
  • totalBudget - how much we want to spend from startDate to endDate.
  • daysSoFar - how many days have elapsed from startDate to today.
  • totalDays - the total number of days between startDate and endDate.

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 for real

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 - the account ID (of format xxx-xxx-xxxx) of the campaign to apply the budget strategy to.
  • Campaign Name - the name of the campaign to apply the budget strategy to.
  • Start Date - the start date of your budget strategy - should be the current date or a day in the past.
  • End Date - the last day you want to advertise with this budget.
  • Total Budget - total amount you're trying to spend. This value is in account currency and may 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, campaignName, totalBudget, startDate, endDate);
}

The result for each campaign will be 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 may be referencing yesterday's total for a script that is scheduled to run after midnight.

Setup

  • Set up a spreadsheet-based script with the source code below. Use the MCC Flexible Budgets template spreadsheet.
  • Update the SPREADSHEET_URL in the code to reflect your spreadsheet's URL.
  • Schedule the script 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/adwords/scripts/docs/solutions/mccapp-flexible-budgets
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.0.3
 *
 * @changelog
 * - 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.
 */

var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

// 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.
var COLUMN = {
  accountId: 2,
  campaignName: 3,
  startDate: 4,
  endDate: 5,
  totalBudget: 6,
  results: 7
};

// Actual config (without header and margin) starts from this row
var CONFIG_START_ROW = 5;

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) {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
  var sheet = spreadsheet.getSheets()[0];

  var endRow = sheet.getLastRow();

  var mccAccount = AdWordsApp.currentAccount();
  sheet.getRange(2, 6, 1, 2).setValue(mccAccount.getCustomerId());

  for (var i = CONFIG_START_ROW; i <= endRow; i++) {
    Logger.log('Processing row %s', i);

    var accountId = sheet.getRange(i, COLUMN.accountId).getValue();
    var campaignName = sheet.getRange(i, COLUMN.campaignName).getValue();
    var startDate = new Date(sheet.getRange(i, COLUMN.startDate).getValue());
    var endDate = new Date(sheet.getRange(i, COLUMN.endDate).getValue());
    var totalBudget = sheet.getRange(i, COLUMN.totalBudget).getValue();
    var resultCell = sheet.getRange(i, COLUMN.results);

    var accountIter = MccApp.accounts().withIds([accountId]).get();
    if (!accountIter.hasNext()) {
      resultCell.setValue('Unknown account');
      continue;
    }
    var account = accountIter.next();
    MccApp.select(account);

    var campaignIter = AdWordsApp.campaigns()
        .withCondition('CampaignName = "' + campaignName + '"')
        .get();
    if (!campaignIter.hasNext()) {
      resultCell.setValue('Unknown campaign');
      continue;
    }
    var campaign = campaignIter.next();

    var today = new Date();
    if (today < startDate) {
      resultCell.setValue('Budget not started yet');
      continue;
    }
    if (today > endDate) {
      resultCell.setValue('Budget already finished');
      continue;
    }

  var costSoFar = campaign.getStatsFor(
      getDateStringInTimeZone('yyyyMMdd', startDate),
      getDateStringInTimeZone('yyyyMMdd', endDate)).getCost();
    var daysSoFar = datediff(startDate, today);
    var totalDays = datediff(startDate, endDate);
    var newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
    campaign.getBudget().setAmount(newBudget);
    Logger.log('AccountId=%s, CampaignName=%s, StartDate=%s, EndDate=%s, ' +
               'CostSoFar=%s, DaysSoFar=%s, TotalDays=%s, NewBudget=%s',
               accountId, campaignName, startDate, endDate,
               costSoFar, daysSoFar, totalDays, newBudget);
    resultCell.setValue('Set today\'s budget to ' + newBudget);
  }

  // update "Last execution" timestamp
  sheet.getRange(1, 3).setValue(today);
  MccApp.select(mccAccount);
}

// One calculation logic that distributes remaining budget evenly
function calculateBudgetEvenly(costSoFar, totalBudget, daysSoFar, totalDays) {
  var daysRemaining = totalDays - daysSoFar;
  var 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) {
  var daysRemaining = totalDays - daysSoFar;
  var 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) {
  var daysSoFar = 0;
  var costSoFar = 0;
  while (daysSoFar <= totalDays + 2) {
    var newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
    Logger.log('Day %s of %s, new budget %s, cost so far %s',
               daysSoFar + 1, totalDays, newBudget, costSoFar);
    costSoFar += newBudget;
    daysSoFar += 1;
  }
}

// Return number of days between two dates, rounded up to nearest whole day.
function datediff(from, to) {
  var 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 || AdWordsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * 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);
}

Send feedback about...

AdWords Scripts
AdWords Scripts
Need help? Visit our support page.