Search Query Optimization - Manager Account

Reports icon

This solution runs a search query report to find search terms that don't have a high enough Clickthrough rate, and adds them as negative keywords so that they won't trigger your ad. The script also looks for keywords that have a high Clickthrough rate as well as a low cost per conversion, and adds them as positive keywords.

This script extends the Search Query Optimization script to run for multiple accounts under a single manager account. This script compares search term performance in the Search term view report with thresholds you supply to generate lists of positive and negative (exact) keywords.

You supply a spreadsheet that has threshold conditions, with each row representing an individual advertiser account. You can optionally have a row that contains default thresholds to be used by the script for accounts without their own configuration row.

How it works

The script works similarly to the single account Search Query script, but adds support for multiple accounts through a user-supplied spreadsheet.

The first column in the spreadsheet holds account IDs of advertiser accounts (not manager accounts). You can specify default thresholds through the first row by assigning "Default" as the account ID.

When the script is processing an account, account-specific thresholds are used if they exist (in a row in the spreadsheet). Otherwise, default thresholds are used if a default row exists. If neither account-specific nor default thresholds exist, the script skips the account.

To familiarize yourself with how reporting works in Google Ads scripts, check out the dedicated reporting guide and the reporting samples page.

Setup

Set up a spreadsheet-based script with the source code below. Use the Search Query template spreadsheet.
Update the SPREADSHEET_URL in the code to reflect your spreadsheet's URL.
Schedule the script to run Weekly.

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 Search Query Report
 *
 * @overview The MCC Search Query Report script uses the Search Query Performance
 *     Report to find undesired search terms in accounts under an MCC account
 *     and add them as negative (or positive) exact keywords. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/adsmanagerapp-search-query
 *     for more details.
 *
 * @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.3
 *   - Upgrade to API version v201609.
 * - version 1.0.2
 *   - Added validation for external spreadsheet setup.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */

const 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.
const COLUMN = {
  accountId: 2,
  impressionsThreshold: 3,
  averageCpcThreshold: 4,
  ctrThreshold: 5,
  costPerConvThreshold: 6
};

// Start row/column numbers and total columns of actual config
// (without header and margin)
const CONFIG = {
  startRow: 6,
  startColumn: 2,
  totalColumns: 5
};

// One currency unit is one million micro amount.
const MICRO_AMOUNT_MULTIPLIER = 1000000;

/**
 * Configuration to be used for running reports.
 */
const REPORTING_OPTIONS = {
  // Comment out the following line to default to the latest reporting version.
  apiVersion: 'v11'
};

function main() {
  // Read config data from the spreadsheet
  console.log(`Using spreadsheet - ${SPREADSHEET_URL}.`);
  const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);

  // Make sure the spreadsheet is using the account's timezone.
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  const sheet = spreadsheet.getSheets()[0];

  const endRow = sheet.getLastRow();
  const rows = endRow - CONFIG.startRow + 1;
  if (rows <= 0) {
    console.log('Empty config, abort!');
    return;
  }
  const config = sheet.getRange(CONFIG.startRow, CONFIG.startColumn,
    rows, CONFIG.totalColumns).getValues();

  const mccAccount = AdsApp.currentAccount();
  sheet.getRange(2, 6).setValue(mccAccount.getCustomerId());

  const accountIterator = AdsManagerApp.accounts().get();
  for (const account of accountIterator) {
    processAccount(account, config);
  }

  // Update "Last execution" timestamp
  const today = new Date();
  sheet.getRange(1, 3).setValue(today);
  AdsManagerApp.select(mccAccount);
}

// Core logic for processing each account
function processAccount(account, config) {
  // Swith to current Google Ads account
  AdsManagerApp.select(account);
  const accountId = account.getCustomerId();

  const accountIdCol = COLUMN.accountId - CONFIG.startColumn;
  const impsThrshCol = COLUMN.impressionsThreshold - CONFIG.startColumn;
  const avgCpcThrshCol = COLUMN.averageCpcThreshold - CONFIG.startColumn;
  const ctrThrshCol = COLUMN.ctrThreshold - CONFIG.startColumn;
  const costPerConvThrshCol = COLUMN.costPerConvThreshold - CONFIG.startColumn;

  // Get config for this account, if not found use default entry,
  // if no default entry just skip
  let configIndex = -1;
  const hasDefault = (config[0][accountIdCol].toLowerCase() == 'default');
  const configStartRow = hasDefault ? 1 : 0;
  for (let i = configStartRow; i < config.length; i++) {
    if (config[i][accountIdCol] === accountId) {
      configIndex = i;
      break;
    }
  }

  if (configIndex == -1) {
    if (hasDefault) {
      console.log(`Processing account ${accountId} with default config.`);
      configIndex = 0;
    }
    else {
      console.log(`Skipping account ${accountId}: no config found.`);
      return;
    }
  }
  else {
    console.log(`Processing account ${accountId} with ` +
                `account-specific config.`);
  }

  const impsThrsh = config[configIndex][impsThrshCol];
  const avgCpcThrsh = config[configIndex][avgCpcThrshCol];
  const ctrThrsh = config[configIndex][ctrThrshCol];
  const costPerConvThrsh = config[configIndex][costPerConvThrshCol];

  const report = AdsApp.report(
      `SELECT search_term_view.search_term, ` +
      `metrics.clicks, ` +
      `metrics.cost_micros, ` +
      `metrics.ctr, ` +
      `metrics.conversions_from_interactions_rate, ` +
      `metrics.cost_per_conversion, ` +
      `metrics.conversions, ` +
      `campaign.id, ` +
      `ad_group.id ` +
      `FROM search_term_view ` +
      `WHERE metrics.conversions > 0 ` +
      `AND metrics.impressions > ${impsThrsh} ` +
      `AND metrics.average_cpc > ` +
      `${(avgCpcThrsh * MICRO_AMOUNT_MULTIPLIER)} ` +
      `AND segments.date DURING LAST_7_DAYS`, REPORTING_OPTIONS);

  const rows = report.rows();
  const negativeKeywords = {};
  const positiveKeywords = {};
  const allAdGroupIds = {};
  // Iterate through search query and decide whether to add them as positive
  // or negative keywords (or ignore).
  for (const row of rows) {
    if (parseFloat(row['metrics.ctr']) < ctrThrsh) {
      addToMultiMap(negativeKeywords, row['ad_group.id'],
                    row['search_term_view.search_term']);
      allAdGroupIds[row['ad_group.id']] = true;
    } else if (parseFloat(row['metrics.cost_per_conversion']) <
               costPerConvThrsh ||
              !(parseFloat(row['metrics.cost_per_conversion']))) {
      addToMultiMap(positiveKeywords, row['ad_group.id'],
                    row['search_term_view.search_term']);
      allAdGroupIds[row['ad_group.id']] = true;
    }
  }

  // Copy all the adGroupIds from the object into an array.
  const adGroupIdList = [];
  for (const adGroupId of Object.keys(allAdGroupIds)) {
    adGroupIdList.push(adGroupId);
  }

  // Add the keywords as negative or positive to the applicable ad groups.
  const adGroups = AdsApp.adGroups().withIds(adGroupIdList).get();
  for (const adGroup of adGroups) {
    const adGroupId = adGroup.getId();
    if (negativeKeywords[adGroupId]) {
      for (const curNegativeKeyword of negativeKeywords[adGroupId]) {
        adGroup.createNegativeKeyword(`[${curNegativeKeyword}]`);
        console.log(`Update adGroup "${adGroupId}": add negative keyword ` +
                    `"${curNegativeKeyword}".`);
      }
    }
    if (positiveKeywords[adGroupId]) {
      for (const curPositiveKeyword of positiveKeywords[adGroupId]) {
        adGroup.newKeywordBuilder()
            .withText(`[${curPositiveKeyword}]`)
            .build();
        console.log(`Update adGroup "${adGroupId}": add positive keyword ` +
                    `"${curPositiveKeyword}".`);
      }
    }
  }
}

// Helper function that stores queries with AdGroupId
function addToMultiMap(map, key, value) {
  if (!map[key]) {
    map[key] = [];
  }
  map[key].push(value);
}

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