Export Keyword Recommendations

Applying keyword recommendations can improve a campaign's targeting. This script collects all deduplicated keyword recommendations and put them into an editor-friendly spreadsheet, based on the schedule you choose.

To avoid duplicates, all accounts under the manager account where the script is running are checked for each keyword. The script needs to run in the highest manager account of each logical distribution. On completion, you need to add only final URLs. All keywords you book using the spreadsheet output are labeled.

Tip: Run your script several times to process all recommendations in large accounts. Completed accounts might be processed in the next cycle without any changes.

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 keyword 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.

Warning: The UNIQUE_FILENAME_HERE must be unique across all scripts using this template in your account. Otherwise, the intermediate data from different scripts will overwrite one another. The UNIQUE_FILENAME_HERE should also not be the same as any other file you might already have in your Drive account.

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 Keyword Recommendation Export
 *
 * @overview The Keyword Recommendation Export 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 get all
 *     recommended keywords from under the account where it's running. It will
 *     print all deduplicated keyword recommendations into a spreadsheet,
 *     which is editor friendly formatted.
 *
 * @author Google Ads Scripts Team [[e-mail]]
 *
 * @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: 6,

  // 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. (e.g. "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',
};

const keywordMatchtypeArray = [];

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

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

let stateManager;

/**
 * Main method, which will get executed first
 */
function main() {
  const mode = getMode();
  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();
  }

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

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

  initializeExecution(customerIds);
  executeByMode(mode, customerIds);
}

/**
 * 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 group', 'Keyword', 'Criterion Type']);

  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) {
  if (IS_MANAGER) {
    const accounts = AdsManagerApp.accounts().withIds(customerIds).get();
    for (const account of accounts) {
      AdsManagerApp.select(account);
      const initialQuery = `SELECT campaign_criterion.keyword.text, ` +
          `campaign_criterion.keyword.match_type ` +
          `FROM campaign_criterion ` +
          `WHERE campaign_criterion.type = 'KEYWORD' ` +
          `AND campaign_criterion.status = 'ENABLED'`;

      const existingKeywordsResult =
          AdsApp.search(initialQuery);
      for (const row of existingKeywordsResult) {
        keywordMatchtypeArray.push(
            row.campaignCriterion.keyword.text.toLowerCase() + '-' +
            row.campaignCriterion.keyword.matchType.toLowerCase());
      }
    }
  } else {
    const initialQuery = `SELECT campaign_criterion.keyword.text, ` +
        `campaign_criterion.keyword.match_type ` +
        `FROM campaign_criterion ` +
        `WHERE campaign_criterion.type = 'KEYWORD' ` +
        `AND campaign_criterion.status = 'ENABLED'`;

    const existingKeywordsResult =
        AdsApp.search(initialQuery);
    for (const row of existingKeywordsResult) {
      keywordMatchtypeArray.push(
          row.campaignCriterion.keyword.text.toLowerCase() + '-' +
          row.campaignCriterion.keyword.matchType.toLowerCase());
    }
  }
  console.log('Existing keywords collected');
  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 {{suggestedKeywords: number}}
 */
function processAccount() {
  let keywordCount = 0;
  const campaignConditions = config.campaignConditions || [];
  const spreadsheet = config.spreadsheetUrl;
  const relevantKeywords = [];

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

  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) {
    const keyword = resultRow.recommendation.keywordRecommendation.keyword.text;
    const matchtype =
        resultRow.recommendation.keywordRecommendation.keyword.matchType;
    const tmpKey = keyword.toLowerCase() + '-' + matchtype.toLowerCase();

    if (keywordMatchtypeArray.indexOf(tmpKey) === -1) {
      relevantKeywords.push([
        resultRow.customer.descriptiveName,
        resultRow.campaign.name,
        resultRow.adGroup.name,
        keyword,
        matchtype,
      ]);

      keywordMatchtypeArray.push(tmpKey);
      keywordCount++;
    }
  }
  if (relevantKeywords) {
    const numKeywords = relevantKeywords.length;
    const range = sheet.getRange(sheet.getLastRow() + 1, 1, numKeywords, 5);
    range.setValues(relevantKeywords);
  }

  return {suggestedKeywords: keywordCount};
}

/**
 * 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.suggestedKeywords}` +
          ` keyword recommendations.`);
    } else if (result.status === Status.STARTED) {
      console.warn(`${customerId}: timed out`);
    } else {
      console.error(`${customerId}: failed due to "${result.error}"`);
    }
  }
}

/**
 * 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 numSuggestedKeywords = 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`);
      numSuggestedKeywords += result.returnValue.suggestedKeywords;
    } else if (result.status === Status.STARTED) {
      console.warn(`${customerId}: timed out`);
    } else {
      console.error(`${customerId}: failed due to "${result.error}"`);
    }
  }

  MailApp.sendEmail(
      config.emailRecipients,
      'Recommended Keywords',
      `Deduplicated relevant keywords in an editor friendly format: ` +
      `${config.spreadsheetUrl}`);

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

/**
 * 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) {
  const results = {};
  switch (mode) {
    case Mode.SINGLE:
      results[customerIds[0]] = tryProcessAccount();
      completeExecution(results);
      break;

    case Mode.MANAGER_SEQUENTIAL:
      const accounts = AdsManagerApp.accounts().withIds(customerIds).get();

      const managerAccount = AdsApp.currentAccount();
      for (const account of accounts) {
        AdsManagerApp.select(account);
        results[account.getCustomerId()] = tryProcessAccount();
      }
      AdsManagerApp.select(managerAccount);

      completeExecution(results);
      break;
  }
}

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

/**
 * 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) {
    const 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_SEQUENTIAL;
  } 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 (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.`);
}

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