除外キーワードによる競合 - 単一アカウント

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

除外キーワードは、関連性の低い検索語句に対して広告を表示しないようにするためのものですが、関連性の高い検索語句と通常のキーワードとの一致も誤ってブロックされ、キャンペーンの成果が悪化してしまう場合があります。除外キーワードが通常のキーワードと競合してしまう原因としてよくあるのは、除外キーワードを設定する際に、目標より広範囲の検索語句に該当するマッチタイプを指定してしまっていることです。また、同じアカウントで別のユーザーがキーワードを更新した場合も競合が発生することがあります。

除外キーワードの競合スクリプトは、アカウント内の除外キーワードが通常のキーワードをブロックしていないか確認します。このスクリプトは、競合しているすべてのキーワードを検出してスプレッドシートに保存し、メールでアラートを送信します。このアラートを参考にすることで、競合の原因となっている除外キーワードを削除するなど、適切な対応を行うことができます。

スケジュール設定

スクリプトのスケジュールを、キーワードの更新と同程度の頻度に設定します。たとえば、キーワードを頻繁に更新する場合は、スクリプトを毎時間実行するように設定します。キーワードをあまり更新しない場合は毎日か、それより低い頻度に設定します。新しく競合が発生するのはキーワードの変更時に限られるため、スクリプトの実行頻度をキーワードの更新よりも多く設定しても意味がありません。

仕組み

スクリプトはレポートを使用して、アカウント全体のすべての除外キーワードと通常のキーワードを効率的に取得し、通常のキーワードがブロックされていないか確認します。キャンペーン単位や広告グループ単位の除外キーワードに加え、キャンペーンに追加されている除外キーワード リストを含む、すべての除外キーワードが対象となります。

除外キーワードが通常のキーワードをブロックしてしまうかどうかは、マッチタイプによって異なります。

  • 除外キーワードのマッチタイプが通常のキーワードよりも狭い場合、通常のキーワードと一致する検索語句の範囲が除外キーワードより広いため、通常のキーワードはブロックされません。たとえば、完全一致の除外キーワード「シルク スカーフ」は、部分一致の通常のキーワード「シルク スカーフ」をブロックしません。これは通常のキーワードが「スカーフ シルク」や「女性用 シルク スカーフ」などの検索語句と一致するのに対し、除外キーワードは一致しないからです。
  • [シルク スカーフ] などの完全一致の除外キーワードは、完全一致の通常のキーワードだけをブロックします。
  • シルク スカーフ」などのフレーズ一致の除外キーワードは、「シルク スカーフ」というフレーズを含む、フレーズ一致や完全一致の通常のキーワードをブロックします。
  • シルク スカーフ」などの部分一致の除外キーワードは、順序を問わず「シルク」と「スカーフ」を含むすべての通常のキーワードを(マッチタイプに関係なく)ブロックします。

スクリプトが競合を検出すると、詳細をスプレッドシートに出力して、受信者リストの宛先にメールアラートを送信します。競合がなければ、スプレッドシートの作成も、メールの送信も行われません。

アカウントのキーワードの数が 25 万より多かったり、除外キーワードの数が 5 万を超えていたりすると、スクリプトがタイムアウトになる場合があります。こうしたアカウントに対しては、複数のスクリプト インスタンスを作成して、各インスタンスがキャンペーン内の異なるアカウントに実行されるように、CAMPAIGN_LABEL を設定します。

設定方法

  • 下記のソースコードを使ってスクリプトを作成します。こちらのテンプレート スプレッドシートのコピーを使用します。
  • スクリプトの SPREADSHEET_URLRECIPIENT_EMAILS を必ず変更してください。
  • 必要に応じて CAMPAIGN_LABEL を設定して、スクリプトがキャンペーンの一部のアカウントだけに実行されるようにします。
  • スクリプト実行のスケジュールを設定します。

ソースコード

// 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 Negative Keyword Conflicts
 *
 * @overview The Negative Keyword Conflicts script generates a spreadsheet
 *     and email alert if an AdWords account has positive keywords which are
 *     blocked by negative keywords. See
 *     https://developers.google.com/adwords/scripts/docs/solutions/negative-keyword-conflicts
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.3.3
 *
 * @changelog
 * - version 1.3.3
 *   - Added column for negative keyword list name.
 * - version 1.3.2
 *   - Added validation for external spreadsheet setup.
 * - version 1.3.1
 *   - Fix bug where campaigns with multiple shared negative keyword lists were
 *     not handled correctly.
 * - version 1.3.0
 *   - Fix bug where in certain cases phrase match negatives were incorrectly
 *     reported as blocking positive keywords.
 * - version 1.2.1
 *   - Improvements to time zone handling.
 * - version 1.2
 *   - Improved compatibility with Large Manager Hierarchy template.
 *   - Add option for reusing the spreadsheet or making a copy.
 * - version 1.1
 *   - Bug fixes.
 * - version 1.0
 *   - Released initial version.
 */

var CONFIG = {
  // URL of the spreadsheet template.
  // This should be a copy of https://goo.gl/yNwOIV.
  SPREADSHEET_URL: 'YOUR_SPREADSHEET_URL',

  // Whether to output results to a copy of the above spreadsheet (true) or to
  // the spreadsheet directly, overwriting previous results (false).
  COPY_SPREADSHEET: false,

  // Array of addresses to be alerted via email if conflicts are found.
  RECIPIENT_EMAILS: [
    'YOUR_EMAIL_HERE'
  ],

  // Label on the campaigns to be processed.
  // Leave blank to include all campaigns.
  CAMPAIGN_LABEL: '',

  // Limits on the number of keywords in an account the script can process.
  MAX_POSITIVES: 250000,
  MAX_NEGATIVES: 50000
};

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

function main() {
  var spreadsheet = validateAndGetSpreadsheet(CONFIG.SPREADSHEET_URL);
  validateEmailAddresses();

  var conflicts = findAllConflicts();

  if (CONFIG.COPY_SPREADSHEET) {
    spreadsheet = spreadsheet.copy('Negative Keyword Conflicts');
  }
  initializeSpreadsheet(spreadsheet);

  var hasConflicts = outputConflicts(spreadsheet,
    AdWordsApp.currentAccount().getCustomerId(), conflicts);

  if (hasConflicts && CONFIG.RECIPIENT_EMAILS) {
    sendEmail(spreadsheet);
  }
}

/**
 * Finds all negative keyword conflicts in an account.
 *
 * @return {Array.<Object>} An array of conflicts.
 */
function findAllConflicts() {
  var campaignIds;
  if (CONFIG.CAMPAIGN_LABEL) {
    campaignIds = getCampaignIdsWithLabel(CONFIG.CAMPAIGN_LABEL);
  } else {
    campaignIds = getAllCampaignIds();
  }

  var campaignCondition = '';
  if (campaignIds.length > 0) {
    campaignCondition = 'AND CampaignId IN [' + campaignIds.join(',') + ']';
  }

  Logger.log('Downloading keywords performance report');
  var query =
    'SELECT CampaignId, CampaignName, AdGroupId, AdGroupName, ' +
    '       Criteria, KeywordMatchType, IsNegative ' +
    'FROM KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE CampaignStatus = "ENABLED" AND AdGroupStatus = "ENABLED" AND ' +
    '      Status = "ENABLED" AND IsNegative IN [true, false] ' +
    '      ' + campaignCondition + ' ' +
    'DURING YESTERDAY';
  var report = AdWordsApp.report(query, REPORTING_OPTIONS);

  Logger.log('Building cache and populating with keywords');
  var cache = {};
  var numPositives = 0;
  var numNegatives = 0;

  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();

    var campaignId = row['CampaignId'];
    var campaignName = row['CampaignName'];
    var adGroupId = row['AdGroupId'];
    var adGroupName = row['AdGroupName'];
    var keywordText = row['Criteria'];
    var keywordMatchType = row['KeywordMatchType'];
    var isNegative = row['IsNegative'];

    if (!cache[campaignId]) {
      cache[campaignId] = {
        campaignName: campaignName,
        adGroups: {},
        negatives: [],
        negativesFromLists: [],
      };
    }

    if (!cache[campaignId].adGroups[adGroupId]) {
      cache[campaignId].adGroups[adGroupId] = {
        adGroupName: adGroupName,
        positives: [],
        negatives: [],
      };
    }

    if (isNegative == 'true') {
      cache[campaignId].adGroups[adGroupId].negatives
        .push(normalizeKeyword(keywordText, keywordMatchType));
      numNegatives++;
    } else {
      cache[campaignId].adGroups[adGroupId].positives
        .push(normalizeKeyword(keywordText, keywordMatchType));
      numPositives++;
    }

    if (numPositives > CONFIG.MAX_POSITIVES ||
        numNegatives > CONFIG.MAX_NEGATIVES) {
      throw 'Trying to process too many keywords. Please restrict the ' +
            'script to a smaller subset of campaigns.';
    }
  }

  Logger.log('Downloading campaign negatives report');
  var query =
    'SELECT CampaignId, Criteria, KeywordMatchType ' +
    'FROM CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE CampaignStatus = "ENABLED" ' +
    '      ' + campaignCondition;
  var report = AdWordsApp.report(query, REPORTING_OPTIONS);

  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();

    var campaignId = row['CampaignId'];
    var keywordText = row['Criteria'];
    var keywordMatchType = row['KeywordMatchType'];

    if (cache[campaignId]) {
      cache[campaignId].negatives
        .push(normalizeKeyword(keywordText, keywordMatchType));
    }
  }

  Logger.log('Populating cache with negative keyword lists');
  var negativeKeywordLists =
    AdWordsApp.negativeKeywordLists().withCondition('Status = ACTIVE').get();

  while (negativeKeywordLists.hasNext()) {
    var negativeKeywordList = negativeKeywordLists.next();

    var negativeList = {name: negativeKeywordList.getName(), negatives: []};
    var negativeKeywords = negativeKeywordList.negativeKeywords().get();

    while (negativeKeywords.hasNext()) {
      var negative = negativeKeywords.next();
      negativeList.negatives.push(
          normalizeKeyword(negative.getText(), negative.getMatchType()));
    }

    var campaigns = negativeKeywordList.campaigns()
        .withCondition('Status = ENABLED').get();

    while (campaigns.hasNext()) {
      var campaign = campaigns.next();
      var campaignId = campaign.getId();

      if (cache[campaignId]) {
        cache[campaignId].negativesFromLists =
            cache[campaignId].negativesFromLists.concat(negativeList);
      }
    }
  }

  Logger.log('Finding negative conflicts');
  var conflicts = [];

  // Adds context about the conflict.
  var enrichConflict = function(
      conflict, campaignId, adGroupId, level, opt_listName) {
    conflict.campaignId = campaignId;
    conflict.adGroupId = adGroupId;
    conflict.campaignName = cache[campaignId].campaignName;
    conflict.adGroupName = cache[campaignId].adGroups[adGroupId].adGroupName;
    conflict.level = level;
    conflict.listName = opt_listName || '-';
  };

  for (var campaignId in cache) {
    for (var adGroupId in cache[campaignId].adGroups) {
      var positives = cache[campaignId].adGroups[adGroupId].positives;

      var negativeLevels = {
        'Campaign': cache[campaignId].negatives,
        'Ad Group': cache[campaignId].adGroups[adGroupId].negatives
      };

      for (var level in negativeLevels) {
        var newConflicts =
          checkForConflicts(negativeLevels[level], positives);

        for (var i = 0; i < newConflicts.length; i++) {
          enrichConflict(newConflicts[i], campaignId, adGroupId, level);
        }
        conflicts = conflicts.concat(newConflicts);
      }

      var negativeLists = cache[campaignId].negativesFromLists;
      var level = 'Negative list';
      for (var k = 0; k < negativeLists.length; k++) {
        var negativeList = negativeLists[k];
        var newConflicts = checkForConflicts(negativeList.negatives, positives);

        for (var j = 0; j < newConflicts.length; j++) {
          enrichConflict(
              newConflicts[j], campaignId, adGroupId, level, negativeList.name);
        }
        conflicts = conflicts.concat(newConflicts);
      }
    }
  }

  return conflicts;
}

/**
 * Saves conflicts to a spreadsheet if present.
 *
 * @param {Object} spreadsheet The spreadsheet object.
 * @param {string} customerId The account the conflicts are for.
 * @param {Array.<Object>} conflicts A list of conflicts.
 * @return {boolean} True if there were conflicts and false otherwise.
 */
function outputConflicts(spreadsheet, customerId, conflicts) {
  if (conflicts.length > 0) {
    saveConflictsToSpreadsheet(spreadsheet, customerId, conflicts);
    Logger.log('Conflicts were found for ' + customerId +
               '. See ' + spreadsheet.getUrl());
    return true;
  } else {
    Logger.log('No conflicts were found for ' + customerId + '.');
    return false;
  }
}

/**
 * Sets up the spreadsheet to receive output.
 *
 * @param {Object} spreadsheet The spreadsheet object.
 */
function initializeSpreadsheet(spreadsheet) {
  // Make sure the spreadsheet is using the account's timezone.
  spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());

  // Clear the last run date on the spreadsheet.
  spreadsheet.getRangeByName('RunDate').clearContent();

  // Clear all rows in the spreadsheet below the header row.
  var outputRange = spreadsheet.getRangeByName('Headers')
    .offset(1, 0, spreadsheet.getSheetByName('Conflicts')
        .getDataRange().getLastRow())
    .clearContent();
}

/**
 * Saves conflicts for a particular account to the spreadsheet starting at the
 * first unused row.
 *
 * @param {Object} spreadsheet The spreadsheet object.
 * @param {string} customerId The account that the conflicts are for.
 * @param {Array.<Object>} conflicts A list of conflicts.
 */
function saveConflictsToSpreadsheet(spreadsheet, customerId, conflicts) {
  // Find the first open row on the Report tab below the headers and create a
  // range large enough to hold all of the failures, one per row.
  var lastRow = spreadsheet.getSheetByName('Conflicts')
    .getDataRange().getLastRow();
  var headers = spreadsheet.getRangeByName('Headers');
  var outputRange = headers
    .offset(lastRow - headers.getRow() + 1, 0, conflicts.length);

  // Build each row of output values in the order of the columns.
  var outputValues = [];
  for (var i = 0; i < conflicts.length; i++) {
    var conflict = conflicts[i];
    outputValues.push([
      customerId,
      conflict.negative,
      conflict.level,
      conflict.positives.join(', '),
      conflict.campaignName,
      conflict.adGroupName,
      conflict.listName
    ]);
  }
  outputRange.setValues(outputValues);

  spreadsheet.getRangeByName('RunDate').setValue(new Date());

  for (var i = 0; i < CONFIG.RECIPIENT_EMAILS.length; i++) {
    spreadsheet.addEditor(CONFIG.RECIPIENT_EMAILS[i]);
  }
}

/**
 * Sends an email to a list of email addresses with a link to the spreadsheet.
 *
 * @param {Object} spreadsheet The spreadsheet object.
 */
function sendEmail(spreadsheet) {
  MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
      'Negative Keyword Conflicts Found',
      'Negative keyword conflicts were found in your ' +
      'AdWords account(s). See ' +
      spreadsheet.getUrl() + ' for details. You may wish ' +
      'to delete the negative keywords causing the ' +
      'the conflicts.');
}

/**
 * Retrieves the campaign IDs of a campaign iterator.
 *
 * @param {Object} campaigns A CampaignIterator object.
 * @return {Array.<Integer>} An array of campaign IDs.
 */
function getCampaignIds(campaigns) {
  var campaignIds = [];
  while (campaigns.hasNext()) {
    campaignIds.push(campaigns.next().getId());
  }

  return campaignIds;
}

/**
 * Retrieves all campaign IDs in an account.
 *
 * @return {Array.<Integer>} An array of campaign IDs.
 */
function getAllCampaignIds() {
  return getCampaignIds(AdWordsApp.campaigns().get());
}

/**
 * Retrieves the campaign IDs with a given label.
 *
 * @param {string} labelText The text of the label.
 * @return {Array.<Integer>} An array of campaign IDs, or null if the
 *     label was not found.
 */
function getCampaignIdsWithLabel(labelText) {
  var labels = AdWordsApp.labels()
    .withCondition('Name = "' + labelText + '"')
    .get();

  if (!labels.hasNext()) {
    return null;
  }
  var label = labels.next();

  return getCampaignIds(label.campaigns().get());
}

/**
 * Compares a set of negative keywords and positive keywords to identify
 * conflicts where a negative keyword blocks a positive keyword.
 *
 * @param {Array.<Object>} negatives A list of objects with fields
 *     display, raw, and matchType.
 * @param {Array.<Object>} positives A list of objects with fields
 *     display, raw, and matchType.
 * @return {Array.<Object>} An array of conflicts, each an object with
 *     the negative keyword display text causing the conflict and an array
 *     of blocked positive keyword display texts.
 */
function checkForConflicts(negatives, positives) {
  var conflicts = [];

  for (var i = 0; i < negatives.length; i++) {
    var negative = negatives[i];
    var anyBlock = false;
    var blockedPositives = [];

    for (var j = 0; j < positives.length; j++) {
      var positive = positives[j];

      if (negativeBlocksPositive(negative, positive)) {
        anyBlock = true;
        blockedPositives.push(positive.display);
      }
    }

    if (anyBlock) {
      conflicts.push({
        negative: negative.display,
        positives: blockedPositives
      });
    }
  }

  return conflicts;
}

/**
 * Removes leading and trailing match type punctuation from the first and
 * last character of a keyword's text, if any.
 *
 * @param {string} text A keyword's text to remove punctuation from.
 * @param {string} open The character that may be the first character.
 * @param {string} close The character that may be the last character.
 * @return {Object} The same text, trimmed of open and close if present.
 */
function trimKeyword(text, open, close) {
  if (text.substring(0, 1) == open &&
      text.substring(text.length - 1) == close) {
    return text.substring(1, text.length - 1);
  }

  return text;
}

/**
 * Normalizes a keyword by returning a raw and display version and consistent
 * match type. The raw version has no leading and trailing punctuation for
 * phrase and exact match keywords, no consecutive whitespace, is all
 * lowercase, and removes broad match qualifiers. The display version has no
 * consecutive whitespace and is all lowercase. The match type is uppercase.
 *
 * @param {string} text A keyword's text that should be normalized.
 * @param {string} matchType The keyword's match type.
 * @return {Object} An object with fields display, raw, and matchType.
 */
function normalizeKeyword(text, matchType) {
  var display;
  var raw = text;
  matchType = matchType.toUpperCase();

  // Replace leading and trailing "" for phrase match keywords and [] for
  // exact match keywords, if it is there.
  if (matchType == 'PHRASE') {
    raw = trimKeyword(raw, '"', '"');
  } else if (matchType == 'EXACT') {
    raw = trimKeyword(raw, '[', ']');
  }

  // Collapse any runs of whitespace into single spaces.
  raw = raw.replace(new RegExp('\\s+', 'g'), ' ');

  // Keywords are not case sensitive.
  raw = raw.toLowerCase();

  // Set display version.
  display = raw;
  if (matchType == 'PHRASE') {
    display = '"' + display + '"';
  } else if (matchType == 'EXACT') {
    display = '[' + display + ']';
  }

  // Remove broad match modifier '+' sign.
  raw = raw.replace(new RegExp('\\s\\+', 'g'), ' ');

  return {display: display, raw: raw, matchType: matchType};
}

/**
 * Tests whether all of the tokens in one keyword's raw text appear in
 * the tokens of a second keyword's text.
 *
 * @param {string} keywordText1 the raw keyword text whose tokens may
 *     appear in the other keyword text.
 * @param {string} keywordText2 the raw keyword text which may contain
 *     the tokens of the other keyword.
 * @return {boolean} Whether all tokens in keywordText1 appear among
 *     the tokens of keywordText2.
 */
function hasAllTokens(keywordText1, keywordText2) {
  var keywordTokens1 = keywordText1.split(' ');
  var keywordTokens2 = keywordText2.split(' ');

  for (var i = 0; i < keywordTokens1.length; i++) {
    if (keywordTokens2.indexOf(keywordTokens1[i]) == -1) {
      return false;
    }
  }

  return true;
}

/**
 * Tests whether all of the tokens in one keyword's raw text appear in
 * order in the tokens of a second keyword's text.
 *
 * @param {string} keywordText1 the raw keyword text whose tokens may
 *     appear in the other keyword text.
 * @param {string} keywordText2 the raw keyword text which may contain
 *     the tokens of the other keyword in order.
 * @return {boolean} Whether all tokens in keywordText1 appear in order
 *     among the tokens of keywordText2.
 */
function isSubsequence(keywordText1, keywordText2) {
  return (' ' + keywordText2 + ' ').indexOf(' ' + keywordText1 + ' ') >= 0;
}

/**
 * Tests whether a negative keyword blocks a positive keyword, taking into
 * account their match types.
 *
 * @param {Object} negative An object with fields raw and matchType.
 * @param {Object} positive An object with fields raw and matchType.
 * @return {boolean} Whether the negative keyword blocks the positive keyword.
 */
function negativeBlocksPositive(negative, positive) {
  var isNegativeStricter;

  switch (positive.matchType) {
    case 'BROAD':
      isNegativeStricter = negative.matchType != 'BROAD';
      break;

    case 'PHRASE':
      isNegativeStricter = negative.matchType == 'EXACT';
      break;

    case 'EXACT':
      isNegativeStricter = false;
      break;
  }

  if (isNegativeStricter) {
    return false;
  }

  switch (negative.matchType) {
    case 'BROAD':
      return hasAllTokens(negative.raw, positive.raw);
      break;

    case 'PHRASE':
      return isSubsequence(negative.raw, positive.raw);
      break;

    case 'EXACT':
      return positive.raw === negative.raw;
      break;
  }
}

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

/**
 * Validates the provided email address to make sure it's not the default.
 * Throws a descriptive error message if validation fails.
 *
 * @throws {Error} If the list of email addresses is still the default
 */
function validateEmailAddresses() {
  if (CONFIG.RECIPIENT_EMAILS &&
      CONFIG.RECIPIENT_EMAILS[0] == 'YOUR_EMAIL_HERE') {
    throw new Error('Please either specify a valid email address or clear' +
        ' the RECIPIENT_EMAILS field.');
  }
}

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。