Export Ad rotation Recommendations

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

Tools icon

Applying ad rotation recommendations to show the most relevant ad for each search query is relevant for many advertisers. With this script, you won't need to look into each account, it exports all ad rotation recommendations in a Google Ads Editor-compliant format to a spreadsheet for you.

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 ad rotation 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 Ad Rotation Optimizer
 *
 * @overview The Ad Rotation Optimizer 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',
};

/**
 * 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', 'Ad rotation']);
  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 {{recommendedAdRotations: number}}
 *
 */
function processAccount() {
  let adRotationCount = 0;
  const campaignConditions = config.campaignConditions || [];
  const spreadsheet = config.spreadsheetUrl;
  const campaigns = [];

  let query =
      `SELECT campaign.name, customer.descriptive_name ` +
      `FROM recommendation ` +
      `WHERE recommendation.type = 'OPTIMIZE_AD_ROTATION'`;

  for (const campaignCondition of campaignConditions) {
    query = query.concat(` AND ${campaignCondition}`);
  }

  const recommendationResult = AdsApp.search(query);

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

  for (const resultRow of recommendationResult) {
    campaigns.push([resultRow.customer.descriptiveName,
                    resultRow.campaign.name, 'Optimize']);
    adRotationCount++;
  }

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

/**
 * 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) {
    let result = results[customerId];
    if (result.status === Status.COMPLETE) {
      console.log(
          `${customerId}: ${result.returnValue.recommendedAdRotations}` +
          ` campaigns ad rotation should be changed to 'optimize'.`);
    } else if (result.status === Status.STARTED) {
      console.warn(`${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 numRecommendedAdRotations = 0;

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

  MailApp.sendEmail(
      config.emailRecipients,
      `Ad Rotation Settings`,
      `The following campaigns do not have optimized ad rotations: ` +
      `${config.spreadsheetUrl}`);

  console.log(
     `Total number of ad rotation recommendations: ` +
     `${numRecommendedAdRotations}`);
}

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

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

/**
 * 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.
 */
function executeByMode(mode, customerIds) {
  switch (mode) {
    case Mode.SINGLE:
      let results = {};
      results[customerIds[0]] = tryProcessAccount();
      completeExecution(results);
      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.
 */
function parallelCallback(executionResults) {
  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);
}

/**
 * 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.
 */
function completeExecution(results) {
  for (const customerId in results) {
    let result = results[customerId];
    stateManager.setAccountWithResult(customerId, result);
  }
  stateManager.saveState();

  processIntermediateResults(results);
  completeCycleIfNecessary();
}

/**
 * Completes a full cycle of the script if all accounts have been attempted
 * but the cycle has not been marked as complete yet.
 */
function completeCycleIfNecessary() {
  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) {
    let customerIds = [];

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

    const accounts = selector.get();
    for (const account of accounts) {
      customerIds.push(account.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.`);
}

const stateManager = (function() {
  /**
   * @type {{
   *   cycle: {
   *     status: string,
   *     lastUpdate: string,
   *     startTime: string
   *   },
   *   accounts: !Object<string, {
   *     status: string,
   *     lastUpdate: string,
   *     returnValue: !Object
   *   }>
   * }}
   */
  let state = {};

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

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

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

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

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

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

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

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

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

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

  /**
   * Gets the start time of the current cycle.
   *
   * @return {!Object} Date object for the start of the last cycle.
   */
  const getLastStartTime = function() {
    return new Date(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.
   */
  const getAccountsWithStatus = function(status) {
    const customerIds = [];

    for (const customerId in state.accounts) {
      if (!status || 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.
   */
  const setAccountsWithStatus = function(customerIds, status) {
    const date = Date();

    for (const customerId of customerIds) {

      if (state.accounts[customerId]) {
        state.accounts[customerId].status = status;
        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.
   */
  const setAccountWithResult = function(customerId, result) {
    if (state.accounts[customerId]) {
      state.accounts[customerId].status = result.status;
      state.accounts[customerId].returnValue = result.returnValue;
      state.accounts[customerId].error = result.error;
      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.
   */
  const getResults = function() {
    return state.accounts;
  };

  return {
    loadState: loadState,
    saveState: saveState,
    resetState: resetState,
    getStatus: getStatus,
    setStatus: setStatus,
    getLastStartTime: getLastStartTime,
    getAccountsWithStatus: getAccountsWithStatus,
    setAccountsWithStatus: setAccountsWithStatus,
    setAccountWithResult: setAccountWithResult,
    getResults: getResults,
  };
})();