Search Query Report - Manager Account

MCC Search Query Report extends Search Query Report to run for multiple accounts under a single manager (MCC) account. The script uses the Search Query Performance report to generate positive and negative (exact) keywords based on search term performance.

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.

Iterating over all managed accounts

Initially the script runs in the manager account's context. It first invokes

var managerAccount = AdsApp.currentAccount();

to store the manager account, then invokes

var accountIterator = AdsManagerApp.accounts().get();

to get an iterator of all advertiser (managed) accounts under the manager account.

In each iteration, the script invokes

AdsManagerApp.select(account);

to switch to the managed account's context to perform operations on that account. After processing all managed accounts, the script invokes

AdsManagerApp.select(managerAcount);

to restore the manager account's context.

Request a report with AWQL

You can request a report using AWQL like this:

var report = AdsApp.report(
   "SELECT Query,Clicks,Cost,Ctr,ConversionRate,CostPerConversion,Conversions,CampaignId,AdGroupId " +
   " FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
   " WHERE " +
       " Conversions > 0" +
       " AND Impressions > " + IMPRESSIONS_THRESHOLD +
       " AND AverageCpc > " + AVERAGE_CPC_THRESHOLD +
   " DURING LAST_7_DAYS");
var rows = report.rows();

This sample AWQL query is requesting performance metrics from SEARCH_QUERY_PERFORMANCE_REPORT that are above certain thresholds for the last seven days.

Making decisions with report data

Data from reports is cached temporarily on the Google Ads scripts servers allowing your script to iterate over the rows:

while(rows.hasNext()) {
  var row = rows.next();
  if (parseFloat(row['Ctr']) < CTR_THRESHOLD) {
    addToMultiMap(negativeKeywords, row['AdGroupId'], row['Query']);
    allAdGroupIds[row['AdGroupId']] = true;
  } else if (parseFloat(row['CostPerConversion']) < costPerConvThrsh) {
    addToMultiMap(positiveKeywords, row['AdGroupId'], row['Query']);
    allAdGroupIds[row['AdGroupId']] = true;
  }
}

Each row in the report is a JavaScript object—an associative array where the key is the field name you requested and the value is a string representation of that field's value. The script then parses the field's value (which will always be a string) into a number and compares it to the CTR threshold. If a keyword's CTR is below the threshold, the keyword is considered poor and added to the negative keywords list (to be created later). The script also records the ad group IDs in a separate object so ad groups can be loaded in bulk.

The script keeps track of keywords that are above the CTR threshold but below the CPC (CostPerConversion) limit. The script later adds these keywords as positive keywords.

Loading ad groups in bulk

Using the ad group IDs recorded earlier, the script loads all the ad groups in a single request:

var adGroupIdList = [];
for (var adGroupId in allAdGroupIds) {
  adGroupIdList.push(adGroupId);
}

var adGroups = AdsApp.adGroups().withIds(adGroupIdList).get();

This is significantly more efficient than loading them one-by-one when adding keywords. We strongly encourage the use of IDs to load objects in a batch when working with reports.

Adding keywords to ad groups

The script then iterates over the ad groups that were loaded in bulk and adds the appropriate keywords:

if (negativeKeywords[adGroup.getId()]) {
  for (var i = 0; i < negativeKeywords[adGroup.getId()].length; i++) {
    adGroup.createNegativeKeyword('[' + negativeKeywords[adGroup.getId()][i] + ']');
  }
}

This code checks to see if we have negative keywords to add for this group, loops over each one and adds it as an exact match negative keyword to make sure we won't show ads if this exact text is searched for.

Setup

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

1. Open the template spreadsheet
2. Make a copy of the spreadsheet and save it:
   • In the spreadsheet, menu, select "File -> Make a copy".
   • Enter the new spreadsheet name, click "OK".
3. In your Google Ads manager account, navigate to Scripts
4. Create a new script by clicking the "+ SCRIPT" button and give it a name; authorize the script.
5. Copy-paste source code into the script (see below)
6. Replace
   

   var SPREADSHEET_URL = "[YOUR_URL]";

   with

   var SPREADSHEET_URL = "https://docs.google.com/...";

   Use the URL of the spreadsheet created in step 2.
7. Save the script, click "Close".
8. If necessary, schedule the script
   

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 1.0.3
 *
 * @changelog
 * - 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.
 */

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,
  impressionsThreshold: 3,
  averageCpcThreshold: 4,
  ctrThreshold: 5,
  costPerConvThreshold: 6
};

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

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

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

function main() {
  // Read config data from the spreadsheet
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);

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

  var endRow = sheet.getLastRow();
  var rows = endRow - CONFIG.startRow + 1;
  var config = [];
  if (rows > 0) {
    config = sheet.getRange(CONFIG.startRow, CONFIG.startColumn,
               rows, CONFIG.totalColumns).getValues();
  }
  else {
    Logger.log('Empty config, abort!');
    return;
  }

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

  var accountIterator = AdsManagerApp.accounts().get();
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    processAccount(account, config);
  }

  // Update "Last execution" timestamp
  var 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);
  var accountId = account.getCustomerId();

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

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

  if (configIndex == -1) {
    if (hasDefault) {
      Logger.log('Processing account %s with default config.', accountId);
      configIndex = 0;
    }
    else {
      Logger.log('Skipping account %s: no config found.', accountId);
      return;
    }
  }
  else {
    Logger.log('Processing account %s with account-specific config.',
               accountId);
  }

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

  var report = AdsApp.report(
      'SELECT Query, Clicks, Cost, Ctr, ConversionRate,' +
      ' CostPerConversion, Conversions, CampaignId, AdGroupId' +
      ' FROM SEARCH_QUERY_PERFORMANCE_REPORT' +
      ' WHERE ' +
          ' Conversions > 0' +
          ' AND Impressions > ' + impsThrsh +
          ' AND AverageCpc > ' + (avgCpcThrsh * MICRO_AMOUNT_MULTIPLIER) +
      ' DURING LAST_7_DAYS', REPORTING_OPTIONS);
  var rows = report.rows();

  var negativeKeywords = {};
  var positiveKeywords = {};
  var allAdGroupIds = {};
  // Iterate through search query and decide whether to add them as positive
  // or negative keywords (or ignore).
  while (rows.hasNext()) {
    var row = rows.next();
    if (parseFloat(row['Ctr']) < ctrThrsh) {
      addToMultiMap(negativeKeywords, row['AdGroupId'], row['Query']);
      allAdGroupIds[row['AdGroupId']] = true;
    } else if (parseFloat(row['CostPerConversion']) < costPerConvThrsh) {
      addToMultiMap(positiveKeywords, row['AdGroupId'], row['Query']);
      allAdGroupIds[row['AdGroupId']] = true;
    }
  }

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

  // Add the keywords as negative or positive to the applicable ad groups.
  var adGroups = AdsApp.adGroups().withIds(adGroupIdList).get();
  while (adGroups.hasNext()) {
    var adGroup = adGroups.next();
    var adGroupId = adGroup.getId();
    if (negativeKeywords[adGroupId]) {
      for (var i = 0; i < negativeKeywords[adGroupId].length; i++) {
        var curNegativeKeyword = '[' + negativeKeywords[adGroupId][i] + ']';
        adGroup.createNegativeKeyword(curNegativeKeyword);
        Logger.log('Update adGroup "%s": add negative keyword "%s".',
                   adGroupId, curNegativeKeyword);
      }
    }
    if (positiveKeywords[adGroupId]) {
      for (var i = 0; i < positiveKeywords[adGroupId].length; i++) {
        var curPositiveKeyword = '[' + positiveKeywords[adGroupId][i] + ']';
        var keywordOperation = adGroup.newKeywordBuilder()
            .withText(curPositiveKeyword)
            .build();
        Logger.log('Update adGroup "%s": add positive keyword "%s".',
                   adGroupId, 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);
}