Search Query Report - Single Account

This script is for a single account. For operating on multiple accounts in a Manager Account, use the Manager Account version of the script.

A good source of both negative and positive keywords is the Search Query Report. This report shows the actual search queries that trigger your ads. For example, if your broad match keyword is "pet store", your ads may show when users search for "bad pet store". This type of negative search is not something you want to advertise for. Negative exact keywords allow you to exclude undesired search queries. By looking at performance of various search terms, you can decide which search terms to exclude.

The script uses the Search Query Performance Report to find undesired search terms and add them as negative (or positive) exact keywords.

How it works

Request a report with AWQL

You can request a report using AWQL like this:

var report = AdWordsApp.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();

When you start iterating over the rows in the report, AdWords scripts servers will download the data corresponding to the report you asked for and store it temporarily. As you iterate over the rows, more information is sent to your script.

In the above AWQL, we're requesting the Search Query Performance Report (SEARCH_QUERY_PERFORMANCE_REPORT) and asking for a number of performance fields with data for the last seven days.

Making decisions with report data

Next, the script needs to iterate over the rows in the report and decide what to do with our report data:

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). We also record the AdGroupId in a separate object so we can load the ad groups in bulk.

We also keep track of keywords that are above our Ctr threshold and have a reasonable CostPerConversion—these will be added as positive keywords.

Loading ad groups in bulk

Using the AdGroupIds we recorded earlier, we can load all the ad groups in a single request:

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

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

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

Adding keywords to ad groups

Next we iterate over all the ad groups (that we loaded in bulk) and add 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/adwords/scripts/docs/solutions/search-query
 *     for more details.
 *
 * @author AdWords 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: 'v201609'
};

function main() {
  var report = AdWordsApp.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 = AdWordsApp.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 Manager Account (MCC) version? Click here

Send feedback about...

AdWords Scripts
AdWords Scripts
Need help? Visit our support page.