This is the legacy documentation for Google Ads scripts. Go to the current docs.

Search Query Report - Single Account

This script compares search term performance in the Search Query Performance report with thresholds you supply to generate lists of positive and negative (exact) keywords.

How it works

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']) < COST_PER_CONVERSION_THRESHOLD) {
    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 load 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

  • Create a new script with the source code below.
  • When Previewing, the script will probably be able to complete within the 30 second preview window. If not, try changing the condition DURING LAST_7_DAYS to DURING YESTERDAY.
  • After the script is done previewing, you'll see a list of keywords that would be added as negative (and in some cases positive) keywords.

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 Search Query Report
 *
 * @overview The Search Query Report script uses the Search Query Performance
 *     Report to find undesired search terms and add them as negative (or
 *     positive) exact keywords. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/search-query
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.0.1
 *
 * @changelog
 * - version 1.0.1
 *   - Upgrade to API version v201609.
 * - version 1.0
 *   - Released initial version.
 */

// Minimum number of impressions to consider "enough data"
var IMPRESSIONS_THRESHOLD = 100;
// Cost-per-click (in account currency) we consider an expensive keyword.
var AVERAGE_CPC_THRESHOLD = 1; // $1
// Threshold we use to decide if a keyword is a good performer or bad.
var CTR_THRESHOLD = 0.5; // 0.5%
// If ctr is above threshold AND our conversion cost isn’t too high,
// it’ll become a positive keyword.
var COST_PER_CONVERSION_THRESHOLD = 10; // $10
// 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() {
  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 * 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']) < CTR_THRESHOLD) {
      addToMultiMap(negativeKeywords, row['AdGroupId'], row['Query']);
      allAdGroupIds[row['AdGroupId']] = true;
    } else if (parseFloat(row['CostPerConversion']) <
        COST_PER_CONVERSION_THRESHOLD) {
      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();
    if (negativeKeywords[adGroup.getId()]) {
      for (var i = 0; i < negativeKeywords[adGroup.getId()].length; i++) {
        adGroup.createNegativeKeyword(
            '[' + negativeKeywords[adGroup.getId()][i] + ']');
      }
    }
    if (positiveKeywords[adGroup.getId()]) {
      for (var i = 0; i < positiveKeywords[adGroup.getId()].length; i++) {
        var keywordOperation = adGroup.newKeywordBuilder()
            .withText('[' + positiveKeywords[adGroup.getId()][i] + ']')
            .build();
      }
    }
  }
}

function addToMultiMap(map, key, value) {
  if (!map[key]) {
    map[key] = [];
  }
  map[key].push(value);
}

Looking for the Ads Manager (MCC) version? Click here