Export Budget Recommendations

Stay organized with collections Save and categorize content based on your preferences.

Tools icon

Applying budget recommendations can help optimize a campaign's bid strategy.
This script exports all budget recommendations in a Google Ads Editor-compliant format to a spreadsheet.

The script can be executed in manager accounts as well as in regular Google Ads accounts with no modifications.

Scheduling

Each time the script runs, it automatically detects whether it should resume a cycle across all accounts already in progress, or start a new one. As a result, regardless of how often you want to launch a fresh cycle, schedule the script Hourly.

How it works

The script uses a Google Ads Query Language (GAQL) query with the Google Ads Search feature to check the budget recommendations in your account.

Setup

Configuration consists of the following steps:

  1. Create a new script and copy in the sample code.
    1. Sign in to your Google Ads account.
    2. Click the Tools icon and select Scripts under BULK ACTIONS.
    3. Press the + icon to add a new script.
    4. Name it, remove any templated code, and copy-paste the provided source code.
    5. When prompted, click AUTHORIZE so the script can access the Google Ads account and drive on your behalf. This has to be done once for each script.
  2. Update UNIQUE_FILENAME_HERE in the source code.
  3. MIN_FREQUENCY controls the minimum frequency of the cycles. The next cycle doesn't start until at least this many days have passed since the start of the previous cycle.

    Note that the actual frequency can be longer if a cycle itself takes more time to complete than MIN_FREQUENCY. This might occur, for example, if there is a large number of accounts to process, or if the script itself is scheduled too infrequently to cycle through all of them within MIN_FREQUENCY days.

    INSERT_SPREADSHEET_URL_HERE and INSERT_EMAIL_ADDRESSES_HERE need to be filled out as well. The spreadsheet should be a new, blank spreadsheet, and the user who is executing the script needs to have access to the spreadsheet.

  4. ACCOUNT_CONDITIONS (optional) is a list of ManagedAccountSelector conditions that are AND-ed together to restrict returned entities to only those that satisfy all of the specified conditions.

    CAMPAIGN_CONDITIONS (optional) can be used to exclude a specific campaign or target only active campaigns. Conditions are written in GAQL and are AND-ed together.

  5. Click PREVIEW to run the script in preview mode: Results appear in the CHANGES / LOGS panel.

Source code

// Copyright 2020, 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 Campaign Budget Recommendation Export
 *
 * @overview The Campaign Budget Recommendation Apply script can run on all
 *     client accounts within a manager account hierarchy, splitting the work
 *     across multiple executions if necessary. Each execution of the script
 *     processes a subset of the hierarchy's client accounts that it hadn't
 *     previously processed or just a single child account, saving the results
 *     to a temporary file on Google Drive. The script helps you to set
 *     campaign ad rotation to 'optimize' based on recommendations.
 *
 * @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.0
 *   - Released initial version.
 */

const config = {
  /**
   * Name of file created on Drive to store data between script executions.
   * You must use a different filename for each each script running in the
   * account to avoid data being overwriten.
   */
  filename: 'UNIQUE_FILENAME_HERE',

  // Minimum number of days between the start of each cycle.
  minFrequency: 1,

  // Spreadsheet logging campaigns not using optimized ad rotation.
  spreadsheetUrl: 'INSERT_SPREADSHEET_URL_HERE',

  // Email addresses to be notified of results.
  emailRecipients: 'INSERT_EMAIL_ADDRESSES_HERE',

  /**
   * List of ManagedAccountSelector conditions to restrict the population
   * of child accounts that will be processed. Leave blank or comment out
   * to include all child accounts.
   * Example: "Name != 'Rainy Sky'" or "Name DOES_NOT_CONTAIN 'Rain'".
   */
  accountConditions: [],

  /**
   * List of conditions (based on Google Ads Query Language) to restrict the
   * population of campaigns that will be processed. Leave blank or comment out
   * to include all campaigns.
   * Example: "campaign.name = ''" or "campaign.name NOT LIKE 'promotion'" or
   * "campaign.name REGEXP_MATCH ''" or "campaign.status = 'ENABLED'"
   */
  campaignConditions: [],
};

/**
 * Possible Status for the script as a whole or for an individual account.
 * @enum {string}
 */
const Status = {
  NOT_STARTED: 'Not Started',
  STARTED: 'Started',
  FAILED: 'Failed',
  COMPLETE: 'Complete',
};

// Whether or not the script is running in a manager account.
const IS_MANAGER = typeof AdsManagerApp !== 'undefined';

// The maximum number of accounts that can be processed when using
// executeInParallel().
const MAX_PARALLEL = 50;

/**
 * The possible modes in which the script can execute.
 * @enum {string}
 */
const Mode = {SINGLE: 'Single', MANAGER_PARALLEL: 'Manager Parallel'};

/**
 * Main method, which will get executed first
 */
function main() {
  const mode = getMode();
  const stateManager = new StateManager();
  stateManager.loadState();

  // The last execution may have attempted the final set of accounts but
  // failed to actually complete the cycle because of a timeout in
  // processIntermediateResults(). In that case, complete the cycle now.
  if (stateManager.getAccountsWithStatus().length > 0) {
    completeCycleIfNecessary(stateManager);
  }

  // If the cycle is complete and enough time has passed since the start of
  // the last cycle, reset it to begin a new cycle.
  if (stateManager.getStatus() === Status.COMPLETE) {
    if (dayDifference(stateManager.getLastStartTime(), new Date()) >
        config.minFrequency) {
      stateManager.resetState();
    } else {
      console.log(
          `Waiting until ${config.minFrequency} ` +
          `days have elapsed since the start of the last cycle.`);
      return;
    }
  }

  // Find accounts that have not yet been processed. If this is the
  // beginning of a new cycle, this will be all accounts.
  const customerIds = stateManager.getAccountsWithStatus(Status.NOT_STARTED);

  // The status will be Status.NOT_STARTED if this is the very first
  // execution or if the cycle was just reset. In either case, it is the
  // beginning of a new cycle.
  if (stateManager.getStatus() === Status.NOT_STARTED) {
    stateManager.setStatus(Status.STARTED);
    stateManager.saveState();

    initializeCycle(customerIds);
  }

  // Don't attempt to process more accounts than specified, and
  // enforce the limit on parallel execution if necessary.
  let accountLimit = 50;

  if (mode === Mode.MANAGER_PARALLEL) {
    accountLimit = Math.min(MAX_PARALLEL, accountLimit);
  }

  const customerIdsToProcess = customerIds.slice(0, accountLimit);

  // Save state so that we can detect when an account timed out by it still
  // being in the Started state.
  stateManager.setAccountsWithStatus(customerIdsToProcess, Status.STARTED);
  stateManager.saveState();

  initializeExecution(customerIdsToProcess);
  executeByMode(mode, customerIdsToProcess, stateManager);
}

/**
 * Initializes a cycle for the script.
 *
 * @param {!Array<string>} customerIds The customerIds that this cycle
 *     will process.
 */
function initializeCycle(customerIds) {
  const spreadsheet = config.spreadsheetUrl;
  const ss = SpreadsheetApp.openByUrl(spreadsheet);
  const sheet = ss.getActiveSheet();
  sheet.clearContents();
  sheet.appendRow(
      ['Account', 'Campaign', 'Budget']);
  console.log('Accounts to be processed this cycle:');
  for (const customerId of customerIds) {
    console.log(customerId);
  }
}

/**
 * Initializes a single execution of the script.
 *
 * @param {!Array<string>} customerIds The customerIds that this
 *     execution will process.
 */
function initializeExecution(customerIds) {
  console.log('Accounts to be processed this execution:');
  for (const customerId of customerIds) {
    console.log(customerId);
  }
}

/**
 * Processes a single Google Ads account. This function can perform any sort of
 * processing on the account, followed by outputting results immediately
 * (e.g., sending an email, saving to a spreadsheet, etc.) and/or returning
 * results to be output later, e.g., to be combined with the output from other
 * accounts.
 *
 * @return {{recommendedCampaignBudgets: number}}
 *
 */
function processAccount() {
  let budgetCount = 0;
  const campaigns = [];
  const campaignConditions = config.campaignConditions || [];
  const spreadsheet = config.spreadsheetUrl;

  const query = 'SELECT recommendation.campaign_budget_recommendation, ' +
      'recommendation.campaign_budget ' +
      'FROM recommendation WHERE recommendation.type = "CAMPAIGN_BUDGET"';

  const recommendationResult = AdsApp.search(query);

  const ss = SpreadsheetApp.openByUrl(spreadsheet);
  const sheet = ss.getActiveSheet();

  for (const resultRow of recommendationResult) {
    const tmpRecommendationCampaign = resultRow.recommendation.campaignBudget;
    let subquery = `SELECT campaign_budget.resource_name, campaign.name, ` +
        `customer.descriptive_name ` +
        `FROM campaign_budget WHERE campaign_budget.resource_name = ` +
        `'${tmpRecommendationCampaign}'`;

    for (const campaignCondition of campaignConditions) {
      subquery = subquery.concat(' AND ' + campaignCondition);
    }

    const targetCampaignResult = AdsApp.search(subquery);
    if (targetCampaignResult.hasNext()) {
      const subQueryResultRow = targetCampaignResult.next();
      var account = subQueryResultRow.customer.descriptiveName;
      var targetCampaignName = subQueryResultRow.campaign.name;
    }
    const currentBudget = resultRow.recommendation.campaignBudgetRecommendation
                            .currentBudgetAmountMicros / (1000 * 1000);
    const recommendedBudget =
        resultRow.recommendation.campaignBudgetRecommendation
            .recommendedBudgetAmountMicros / (1000 * 1000);

    if (currentBudget != recommendedBudget) {
      campaigns.push([account, targetCampaignName, recommendedBudget]);
      budgetCount++;
    }
  }

  if (campaigns.length > 0) {
    const numCampaigns = campaigns.length;
    const range = sheet.getRange(sheet.getLastRow()+1, 1, numCampaigns, 3);
    range.setValues(campaigns);
  }

  return {recommendedCampaignBudgets: budgetCount};
}

/**
 * Main logic for consolidating or outputting results after
 * a single execution of the script. These single execution results may
 * reflect the processing on only a subset of your accounts.
 *
 * @param {!Object<string, {
 *       status: !Status,
 *       returnValue: ?Object,
 *       error: ?string
 *     }>} results The results for the accounts processed in this
 *    execution of the script, keyed by customerId. The status will be
 *    Status.COMPLETE if the account was processed successfully,
 *    Status.FAILED if there was an error, and Status.STARTED if it
 *    timed out. The returnValue field is present when the status is
 *    Status.COMPLETE and corresponds to the object you returned in
 *    processAccount(). The error field is present when the status is
 *    Status.FAILED.
 */
function processIntermediateResults(results) {
  console.log('Results of this execution:');
  for (const customerId in results) {
    const result = results[customerId];
    if (result.status === Status.COMPLETE) {
      console.log(
           `${customerId} : ${result.returnValue.recommendedCampaignBudgets}` +
           ` campaign budgets should be optimized.`);
    } else if (result.status === Status.STARTED) {
      console.error(`${customerId} : timed out`);
    } else {
      console.error(`${customerId} : failed due to " ${result.error} "`);
    }
  }
}

/**
 * Main logic for consolidating or outputting results after
 * the script has executed a complete cycle across all of your accounts.
 * This function will only be called once per complete cycle.
 *
 * @param {!Object<string, {
 *       status: !Status,
 *       returnValue: ?Object,
 *       error: ?string
 *     }>} results The results for the accounts processed in this
 *    execution of the script, keyed by customerId. The status will be
 *    Status.COMPLETE if the account was processed successfully,
 *    Status.FAILED if there was an error, and Status.STARTED if it
 *    timed out. The returnValue field is present when the status is
 *    Status.COMPLETE and corresponds to the object you returned in
 *    processAccount(). The error field is present when the status is
 *    Status.FAILED.
 */
function processFinalResults(results) {
  let numRecommendedBudgets = 0;

  console.log('Results of this cycle:');
  for (const customerId in results) {
    const result = results[customerId];
    if (result.status === Status.COMPLETE) {
      console.log(customerId + ': successful');
      numRecommendedBudgets += result.returnValue.recommendedCampaignBudgets;
    } else if (result.status === Status.STARTED) {
      console.warn(`${customerId} : timed out`);
    } else {
      console.error(`${customerId} : failed due to " ${result.error} "`);
    }
  }

    MailApp.sendEmail(
        config.emailRecipients,
        `Campaign Budget Recommendations`,
        `The following campaigns aren\'t capturing all the demand ` +
        `in the market: ' ${config.spreadsheetUrl}`);

  console.log(`Total number of budget recommendations: ` +
             `${numRecommendedBudgets}`);
}

/**
 * Runs the script on a list of accounts in a given mode.
 *
 * @param {string} mode The mode the script should run in.
 * @param {!Array<string>} customerIds The customerIds that this execution
 *     should process. If mode is Mode.SINGLE, customerIds must contain
 *     a single element which is the customerId of the Google Ads account.
 * @param {!Object} stateManager The object of the class StateManager.
 */
function executeByMode(mode, customerIds, stateManager) {
  switch (mode) {
    case Mode.SINGLE:
      const results = {};
      results[customerIds[0]] = tryProcessAccount();
      completeExecution(results, stateManager);
      break;

    case Mode.MANAGER_PARALLEL:
      if (customerIds.length === 0) {
        completeExecution({});
      } else {
        const accountSelector = AdsManagerApp.accounts().withIds(customerIds);
        accountSelector.executeInParallel(
            'parallelFunction', 'parallelCallback');
      }
      break;
  }
}

/**
 * Attempts to process the current Google Ads account.
 *
 * @return {!Object} The result of the processing if successful, or
 *     an object with status Status.FAILED and the error message
 *     if unsuccessful.
 */
function tryProcessAccount() {
  try {
    return {status: Status.COMPLETE, returnValue: processAccount()};
  } catch (e) {
    return {status: Status.FAILED, error: e.message};
  }
}

/**
 * The function given to executeInParallel() when running in parallel mode.
 * This helper function is necessary so that the return value of
 * processAccount() is transformed into a string as required by
 * executeInParallel().
 *
 * @return {string} JSON string representing the return value of
 *     processAccount().
 */
function parallelFunction() {
  const returnValue = processAccount();
  return JSON.stringify(returnValue);
}

/**
 * The callback given to executeInParallel() when running in parallel mode.
 * Processes the execution results into the format used by all execution
 * modes.
 *
 * @param {!Array<!Object>} executionResults An array of execution results
 *     from a parallel execution.
 * @param {!Object} stateManager The object of the class StateManager.
 */
function parallelCallback(executionResults, stateManager) {
  const results = {};

 for (const executionResult of executionResults) {
    let status;

    if (executionResult.getStatus() === 'OK') {
      status = Status.COMPLETE;
    } else if (executionResult.getStatus() === 'TIMEOUT') {
      status = Status.STARTED;
    } else {
      status = Status.FAILED;
    }

    results[executionResult.getCustomerId()] = {
      status: status,
      returnValue: JSON.parse(executionResult.getReturnValue()),
      error: executionResult.getError(),
    };
  }

  // After executeInParallel(), variables in global scope are reevaluated,
  // so reload the state.
  stateManager.loadState();

  completeExecution(results,stateManager);
}

/**
 * Completes a single execution of the script by saving the results and
 * calling the intermediate and final result handlers as necessary.
 *
 * @param {!Object<string, {
 *       status: !Status,
 *       returnValue: ?Object,
 *       error: ?string
 *     }>} results The results of the current execution of the script.
 * @param {!Object} stateManager The object of the class StateManager.
 */
function completeExecution(results, stateManager) {
  for (const customerId in results) {
    const result = results[customerId];
    stateManager.setAccountWithResult(customerId, result);
  }
  stateManager.saveState();

  processIntermediateResults(results);
  completeCycleIfNecessary(stateManager);
}

/**
 * Completes a full cycle of the script if all accounts have been attempted
 * but the cycle has not been marked as complete yet.
 *
 * @param {!Object} stateManager The object of the class StateManager.
 */
function completeCycleIfNecessary(stateManager) {
  if (stateManager.getAccountsWithStatus(Status.NOT_STARTED).length === 0 &&
      stateManager.getStatus() != Status.COMPLETE) {
    stateManager.setStatus(Status.COMPLETE);
    stateManager.saveState();
    processFinalResults(stateManager.getResults());
  }
}

/**
 * Determines what mode the script should run in.
 *
 * @return {string} The mode to run in.
 */
function getMode() {
  if (IS_MANAGER) {
    return Mode.MANAGER_PARALLEL;
  } else {
    return Mode.SINGLE;
  }
}

/**
 * Finds all customer IDs that the script could process. For a single account,
 * this is simply the account itself.
 *
 * @return {!Array<string>} A list of customer IDs.
 */
function getCustomerIdsPopulation() {
  if (IS_MANAGER) {
    const customerIds = [];

    let selector = AdsManagerApp.accounts();
    const conditions = config.accountConditions || [];
    for (const condition of conditions) {
      selector = selector.withCondition(condition);
    }

    const accounts = selector.get();
    for (account of accounts) {
      customerIds.push(account.next().getCustomerId());
    }

    return customerIds;
  } else {
    return [AdsApp.currentAccount().getCustomerId()];
  }
}

/**
 * Returns the number of days between two dates.
 *
 * @param {!Date} from The older Date object.
 * @param {!Date} to The newer (more recent) Date object.
 * @return {number} The number of days between the given dates (possibly
 *     fractional).
 */
function dayDifference(from, to) {
  return (to.getTime() - from.getTime()) / (24 * 3600 * 1000);
}

/**
 * Loads a JavaScript object previously saved as JSON to a file on Drive.
 *
 * @param {string} filename The name of the file in the account's root Drive
 *     folder where the object was previously saved.
 * @return {?Object} The JavaScript object, or null if the file was not found.
 */
function loadObject(filename) {
  const files = DriveApp.getRootFolder().getFilesByName(filename);

  if (!files.hasNext()) {
    return null;
  } else {
    const file = files.next();

    if (files.hasNext()) {
      throwDuplicateFileException(filename);
    }

    return JSON.parse(file.getBlob().getDataAsString());
  }
}

/**
 * Saves a JavaScript object as JSON to a file on Drive. An existing file with
 * the same name is overwritten.
 *
 * @param {string} filename The name of the file in the account's root Drive
 *     folder where the object should be saved.
 * @param {!Object} obj The object to save.
 */
function saveObject(filename, obj) {
  const files = DriveApp.getRootFolder().getFilesByName(filename);

  if (!files.hasNext()) {
    DriveApp.createFile(filename, JSON.stringify(obj));
  } else {
    const file = files.next();

    if (files.hasNext()) {
      throwDuplicateFileException(filename);
    }

    file.setContent(JSON.stringify(obj));
  }
}

/**
 * Throws an exception if there are multiple files with the same name.
 *
 * @param {string} filename The filename that caused the error.
 */
function throwDuplicateFileException(filename) {
  throw new Error(`Multiple files named ${filename} ` +
                  `detected. Please ensure there is only one file named ` +
                  `${filename} and try again.`);
}

class StateManager {

  constructor() {
  /**
   * @type {{
   *   cycle: {
   *     status: string,
   *     lastUpdate: string,
   *     startTime: string
   *   },
   *   accounts: !Object<string, {
   *     status: string,
   *     lastUpdate: string,
   *     returnValue: !Object
   *   }>
   * }}
   */
    this.state = {};
  }

  /**
   * Loads the saved state of the script. If there is no previously
   * saved state, sets the state to an initial default.
   */
  loadState() {
    this.state = loadObject(config.filename);
    if (!this.state) {
      this.resetState();
    }
  };

  /**
   * Saves the state of the script to Drive.
   */
  saveState() {
    saveObject(config.filename, this.state);
  };

  /**
   * Resets the state to an initial default.
   */
  resetState() {
    this.state = {};
    const date = Date();

    this.state.cycle = {
      status: Status.NOT_STARTED,
      lastUpdate: date,
      startTime: date,
    };

    this.state.accounts = {};
    const customerIds = getCustomerIdsPopulation();

    for (const customerId of customerIds) {
      this.state.accounts[customerId] = {
        status: Status.NOT_STARTED,
        lastUpdate: date,
      };
    }
  };

  /**
   * Gets the status of the current cycle.
   *
   * @return {string} The status of the current cycle.
   */
  getStatus() {
    return this.state.cycle.status;
  };

  /**
   * Sets the status of the current cycle.
   *
   * @param {string} status The status of the current cycle.
   */
  setStatus(status) {
    const date = Date();

    if (status === Status.IN_PROGRESS &&
        this.state.cycle.status === Status.NOT_STARTED) {
      this.state.cycle.startTime = date;
    }

    this.state.cycle.status = status;
    this.state.cycle.lastUpdate = date;
  };

  /**
   * Gets the start time of the current cycle.
   *
   * @return {!Object} Date object for the start of the last cycle.
   */
  getLastStartTime() {
    return new Date(this.state.cycle.startTime);
  };

  /**
   * Gets accounts in the current cycle with a particular status.
   *
   * @param {?string} status The status of the accounts to get.
   *     If null, all accounts are retrieved.
   * @return {!Array<string>} A list of matching customerIds.
   */
   getAccountsWithStatus(status) {
    const customerIds = [];

    for (const customerId in this.state.accounts) {
      if (!status || this.state.accounts[customerId].status === status) {
        customerIds.push(customerId);
      }
    }

    return customerIds;
  };

  /**
   * Sets accounts in the current cycle with a particular status.
   *
   * @param {!Array<string>} customerIds A list of customerIds.
   * @param {string} status A status to apply to those customerIds.
   */
  setAccountsWithStatus(customerIds, status) {
    const date = Date();

    for (const customerId of customerIds) {
      if (this.state.accounts[customerId]) {
        this.state.accounts[customerId].status = status;
        this.state.accounts[customerId].lastUpdate = date;
      }
    }
  };

  /**
   * Registers the processing of a particular account with a result.
   *
   * @param {string} customerId The account that was processed.
   * @param {{
   *       status: string,
   *       returnValue: Object,
   *       error: string
   *     }} result The object to save for that account.
   */
  setAccountWithResult(customerId, result) {
    if (this.state.accounts[customerId]) {
      this.state.accounts[customerId].status = result.status;
      this.state.accounts[customerId].returnValue = result.returnValue;
      this.state.accounts[customerId].error = result.error;
      this.state.accounts[customerId].lastUpdate = Date();
    }
  };

  /**
   * Gets the current results of the cycle for all accounts.
   *
   * @return {!Object<string, {
   *       status: string,
   *       lastUpdate: string,
   *       returnValue: Object,
   *       error: string
   *     }>} The results processed by the script during the cycle,
   *    keyed by account.
   */
  getResults() {
    return this.state.accounts;
  };
}