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

アラート アイコン

除外キーワードは、関連性の低い検索語句に対して広告が表示されないようにするためのものですが、通常のキーワードによって関連する検索語句が除外され、キャンペーンの効果が低下することがあります。除外キーワードが通常のキーワードと競合する最も一般的な理由は、除外キーワードが想定よりも広範なマッチタイプで作成されている場合です。また、複数のユーザーがアカウント内のキーワードを同時に更新した場合にも、競合が発生することがあります。

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

スケジュール設定

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

仕組み

このスクリプトは、レポートを使用してアカウント全体のすべての除外キーワードと通常のキーワードを取得し、通常のキーワードがブロックされているかどうかを確認します。キャンペーン レベルの除外キーワード、広告グループレベルの除外キーワード、キャンペーンに関連付けられている除外キーワード リストなど、すべての除外キーワードが対象となります。

除外キーワードが通常のキーワードをブロックするかどうかは、マッチタイプによって異なります。一般的に、通常のキーワードよりも厳密なマッチタイプを使用している除外キーワードではブロックできません。これは、通常のキーワードの方が幅広い検索語句に一致するためです。

たとえば、完全一致の除外キーワード [silk scarves] は、通常の部分一致キーワード silk scarves をブロックしません。後者は、除外キーワードによって除外されない scarves silkwomen's silk scarves などのクエリと一致するためです。

ルールは次のとおりです。

  • [silk scarves] のような完全一致の除外キーワードは、同一の完全一致の通常のキーワードだけをブロックします。
  • "silk scarves" のようなフレーズ一致の除外キーワードは、silk scarves というフレーズを含むフレーズ一致または完全一致の通常のキーワードをブロックします。
  • silk scarves のような部分一致の除外キーワードでは、silkscarves という単語を任意の順序で含む通常のキーワードがブロックされます(マッチタイプは問いません)。

エラー

競合が見つかると、詳細がスプレッドシートに出力され、受信者のリストにメール通知アラートが送信されます。このスクリプトは、競合がない場合にスプレッドシートを作成したり、メールを送信したりしません。

タイムアウト

25 万個を超えるキーワードと 50,000 個を超える除外キーワードを含むアカウントでは、スクリプトがタイムアウトする場合があります。これらのアカウントに対しては、複数のスクリプト インスタンスを作成して 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 a Google Ads account has positive keywords which are
 *     blocked by negative keywords. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/negative-keyword-conflicts
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.1
 *
 * @changelog
 * - version 2.1
 *   - Fix bug where negative keywords were not categorized correctly.
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - 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.
 */

const CONFIG = {
  // URL of the spreadsheet template.
  // This should be a copy of https://goo.gl/M4HjaH.
  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.
 */
const REPORTING_OPTIONS = {
  // Comment out the following line to default to the latest reporting version.
  apiVersion: 'v11'
};

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

  const conflicts = findAllConflicts();

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

  const hasConflicts = outputConflicts(spreadsheet,
    AdsApp.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() {
  let campaignIds;
  if (CONFIG.CAMPAIGN_LABEL) {
    campaignIds = getCampaignIdsWithLabel(CONFIG.CAMPAIGN_LABEL);
  } else {
    campaignIds = getAllCampaignIds();
  }

  let campaignCondition = '';
  if (campaignIds.length > 0) {
    campaignCondition = `AND campaign.id IN (${campaignIds.join(',')})`;
  }

  console.log('Downloading keywords performance report');
  let query =
      `SELECT campaign.id, campaign.name, ad_group.id, ad_group.name, ` +
      `ad_group_criterion.keyword.text, ` +
      `ad_group_criterion.keyword.match_type, ` +
      `ad_group_criterion.negative FROM keyword_view ` +
      `WHERE campaign.status = "ENABLED" AND ad_group.status = "ENABLED" `+
      `AND ad_group_criterion.status = "ENABLED" ` +
      `${campaignCondition} AND segments.date DURING YESTERDAY`;
  let report = AdsApp.report(query, REPORTING_OPTIONS);

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

  let rows = report.rows();
  for (const row of rows) {

    const campaignId = row['campaign.id'];
    const campaignName = row['campaign.name'];
    const adGroupId = row['ad_group.id'];
    const adGroupName = row['ad_group.name'];
    const keywordText = row['ad_group_criterion.keyword.text'];
    const keywordMatchType = row['ad_group_criterion.keyword.match_type'];
    const isNegative = row['ad_group_criterion.negative'];

    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) {
      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.';
    }
  }

  console.log('Downloading campaign negatives report');
  query =
      `SELECT campaign.id, campaign_criterion.keyword.text, ` +
      `campaign_criterion.keyword.match_type FROM campaign_criterion ` +
      `WHERE campaign_criterion.negative = true AND ` +
      `campaign_criterion.type = "KEYWORD" AND ` +
      `campaign.status = "ENABLED" ${campaignCondition}`;
  report = AdsApp.report(query, REPORTING_OPTIONS);

  rows = report.rows();
  for (const row of rows) {

    const campaignId = row['campaign.id'];
    const keywordText = row['campaign_criterion.keyword.text'];
    const keywordMatchType = row['campaign_criterion.keyword.match_type'];

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

  console.log('Populating cache with negative keyword lists');
  const negativeKeywordLists =
    AdsApp.negativeKeywordLists().withCondition('Status = ACTIVE').get();

  for (const negativeKeywordList of negativeKeywordLists) {
    const negativeList = {name: negativeKeywordList.getName(), negatives: []};
    const negativeKeywords = negativeKeywordList.negativeKeywords().get();

    for (const negative of negativeKeywords) {
      negativeList.negatives.push(
          normalizeKeyword(negative.getText(), negative.getMatchType()));
    }

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

    for (const campaign of campaigns) {
      const campaignId = campaign.getId();

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

  console.log('Finding negative conflicts');
  let conflicts = [];

  // Adds context about the conflict.
  const 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 (const campaignId in cache) {
    for (const adGroupId in cache[campaignId].adGroups) {
      const positives = cache[campaignId].adGroups[adGroupId].positives;

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

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

        for (const newConflict of newConflicts) {
          enrichConflict(newConflict, campaignId, adGroupId, level);
        }
        conflicts = conflicts.concat(newConflicts);
      }

      const negativeLists = cache[campaignId].negativesFromLists;
      const level = 'Negative list';
      for (const negativeList of negativeLists) {
        const newConflicts = checkForConflicts(
            negativeList.negatives, positives);

        for (const newConflict of newConflicts) {
          enrichConflict(
              newConflict, 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);
    console.log(`Conflicts were found for ${customerId}` +
               `. See ${spreadsheet.getUrl()}`);
    return true;
  } else {
    console.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(AdsApp.currentAccount().getTimeZone());

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

  // Clear all rows in the spreadsheet below the header row.
  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.
  const lastRow = spreadsheet.getSheetByName('Conflicts')
    .getDataRange().getLastRow();
  const headers = spreadsheet.getRangeByName('Headers');
  const outputRange = headers
    .offset(lastRow - headers.getRow() + 1, 0, conflicts.length);

  // Build each row of output values in the order of the columns.
  const outputValues = [];
  for (const conflict of conflicts) {
    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 (const recipientEmail of CONFIG.RECIPIENT_EMAILS) {
    spreadsheet.addEditor(recipientEmail);
  }
}

/**
 * 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 ` +
      `Google Ads account(s). See ` +
      `${spreadsheet.getUrl()} for details. You may wish ` +
      `to delete the negative keywords causing 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) {
  const campaignIds = [];
  for (const campaign of campaigns) {
    campaignIds.push(campaign.getId());
  }

  return campaignIds;
}

/**
 * Retrieves all campaign IDs in an account.
 *
 * @return {Array.<Integer>} An array of campaign IDs.
 */
function getAllCampaignIds() {
  return getCampaignIds(AdsApp.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) {
  const labels = AdsApp.labels()
    .withCondition('Name = "' + labelText + '"')
    .get();

  if (!labels.hasNext()) {
    return null;
  }
  const 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) {
  const conflicts = [];

  for (const negative of negatives) {
    let anyBlock = false;
    const blockedPositives = [];

    for (const positive of positives) {

      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) {
  let display;
  let 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) {
  const keywordTokens1 = keywordText1.split(' ');
  const keywordTokens2 = keywordText2.split(' ');

  for (const keywordToken of keywordTokens1) {
    if (keywordTokens2.indexOf(keywordToken) == -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) {
  let 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.');
  }
}