主排除列表 - 单一帐号

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

维护关键字和展示位置的主排除列表是 AdWords 广告系列管理中的一项常见任务。此主列表通常用来滤除那些为您的网站带来无效流量的关键字和展示位置。在某些情况下,广告客户可能认为不需要某个关键字;另一些情况下,可能为了遵守法律要求(例如,法院命令)而需要排除某些关键字。

AdWords 支持使用排除标准列表,并且这些列表可由单个帐号中的多个广告系列共享。不过,如果要将列表应用于大型帐号中的多个广告系列,并且长时间保持同步,却并非易事。使用“主排除列表”脚本,您可以通过电子表格管理排除标准,从而大大简化了这项任务。

工作原理

脚本会从 Google 电子表格中读取排除标准。它将在 AdWords 帐号中创建一个共享的排除标准列表,并使该列表与电子表格中的条件保持同步。将分别维护用于关键字和展示位置的列表。然后,脚本会确保将排除标准列表应用到帐号中的所有广告系列。如果需要,您可以限制广告系列的列表,方法是通过在配置电子表格中指定标签,在处理广告系列时过滤出要包含的广告系列。

脚本可以向配置电子表格中所指定的电子邮件地址发送邮件,摘要说明其所执行的操作。

配置

脚本使用电子表格进行配置。支持以下配置设置:

  • 默认情况下,脚本将处理帐号中的所有“已启用”和“已暂停”的广告系列。要限制接受处理的广告系列的列表,可以执行以下操作:
    1. 在您要处理的每个帐号中创建一个标签。
    2. 将该标签应用到要处理的广告系列的列表。
    3. 在配置电子表格的 C3 单元格中指定该标签。
  • 在 C6 单元格中指定要在脚本完成运行后接收摘要电子邮件的邮件地址。
  • 脚本将分别为它所处理的帐号中的关键字和展示位置创建共享的排除标准列表。在配置电子表格的 C4 和 C5 单元格中指定共享的排除标准列表的名称。

运行时间设置

将脚本设为每天或每小时运行。

设置

  • 使用下面的源代码设置基于电子表格的脚本。使用此 电子表格模板
  • 切勿忘记更新代码中的 SPREADSHEET_URL

源代码

// 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 Master Negative List Script
 *
 * @overview The Master Negative List script applies negative keywords and
 *     placements from a spreadsheet to multiple campaigns in your account using
 *     shared keyword and placement lists. The script can process multiple
 *     AdWords accounts in parallel. See
 *     https://developers.google.com/adwords/scripts/docs/solutions/master-negative-list
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.0.2
 *
 * @changelog
 * - 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.
 */

/**
 * The URL of the tracking spreadsheet. This should be a copy of
 * https://goo.gl/IC3JIz
 */
var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';

/**
 * Keep track of the spreadsheet names for various criteria types, as well as
 * the criteria type being processed.
 */
var CriteriaType = {
  KEYWORDS: 'Keywords',
  PLACEMENTS: 'Placements'
};

function main() {
  var emailParams = {
    // Number of placements that were synced.
    PlacementCount: 0,
    // Number of keywords that were synced.
    KeywordCount: 0,
    // Number of campaigns that were synced.
    CampaignCount: 0,
    // Status of processing this account - OK / ERROR.
    Status: ''
  };

  try {
    var syncSummary = syncMasterLists();

    emailParams.PlacementCount = syncSummary.PlacementCount;
    emailParams.KeywordCount = syncSummary.KeywordCount;
    emailParams.CampaignCount = syncSummary.CampaignCount;
    emailParams.Status = 'OK';
  } catch (err) {
    emailParams.Status = 'ERROR';
  }
  var config = readConfig();

  var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);

  // Make sure the spreadsheet is using the account's timezone.
  spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
  spreadsheet.getRangeByName('LastRun').setValue(new Date());
  spreadsheet.getRangeByName('CustomerId').setValue(
      AdWordsApp.currentAccount().getCustomerId());

  sendEmail(config, emailParams);
}

/**
 * Sends a summary email about the changes that this script made.
 *
 * @param {Object} config The configuration object.
 * @param {Object} emailParams Contains details required to create the email
 *     body.
 */
function sendEmail(config, emailParams) {
  var html = [];
  var summary = '';

  if (emailParams.Status == 'OK') {
    summary = 'The Master Negative List script successfully processed ' +
        'Customer ID: ' + AdWordsApp.currentAccount().getCustomerId() +
        ' and synced a total of ' + emailParams.KeywordCount +
        ' keywords and ' + emailParams.PlacementCount + ' placements.';
  } else {
    summary = 'The Master Negative List script failed to process ' +
        'Customer ID: ' + AdWordsApp.currentAccount().getCustomerId() +
        ' and synced a total of ' + emailParams.KeywordCount +
        ' keywords and ' + emailParams.PlacementCount + ' placements.';
  }
  html.push('<html>',
              '<head></head>',
                 '<body>',
                  '<table style="font-family:Arial,Helvetica; ' +
                       'border-collapse:collapse;font-size:10pt; ' +
                       'color:#444444; border: solid 1px #dddddd;" ' +
                       'width="600" cellpadding=20>',
                     '<tr>',
                       '<td>',
                         '<p>Hello,</p>',
                         '<p>' + summary + '</p>',
                         '<p>Cheers<br />AdWords Scripts Team</p>',
                       '</td>',
                     '</tr>',
                   '</table>',
                 '</body>',
             '</html>'
           );

  if (config.email != '') {
    MailApp.sendEmail({
      to: config.email,
      subject: 'Master Negative List Script',
      htmlBody: html.join('\n')
    });
  }
}

/**
 * Synchronizes the negative criteria list in an account with the master list
 * in the user spreadsheet.
 *
 * @return {Object} A summary of the number of keywords and placements synced,
 *     and the number of campaigns to which these lists apply.
 */
function syncMasterLists() {
  var config = readConfig();
  var syncedCampaignCount = 0;

  var keywordListDetails = syncCriteriaInNegativeList(config,
      CriteriaType.KEYWORDS);
  syncedCampaignCount = syncCampaignList(config, keywordListDetails.SharedList,
      CriteriaType.KEYWORDS);

  var placementListDetails = syncCriteriaInNegativeList(config,
      CriteriaType.PLACEMENTS);
  syncCampaignList(config, placementListDetails.SharedList,
      CriteriaType.PLACEMENTS);

  return {
    'CampaignCount': syncedCampaignCount,
    'PlacementCount': placementListDetails.CriteriaCount,
    'KeywordCount': keywordListDetails.CriteriaCount
  };
}

/**
 * Synchronizes the list of campaigns covered by a negative list against the
 * desired list of campaigns to be covered by the master list.
 *
 * @param {Object} config The configuration object.
 * @param {AdWordsApp.NegativeKeywordList|AdWordsApp.ExcludedPlacementList}
 *    sharedList The shared negative criterion list to be synced against the
 *    master list.
 * @param {String} criteriaType The criteria type for the shared negative list.
 *
 * @return {Number} The number of campaigns synced.
 */
function syncCampaignList(config, sharedList, criteriaType) {
  var campaignIds = getLabelledCampaigns(config.label);
  var totalCampaigns = Object.keys(campaignIds).length;

  var listedCampaigns = sharedList.campaigns().get();

  var campaignsToRemove = [];

  while (listedCampaigns.hasNext()) {
    var listedCampaign = listedCampaigns.next();
    if (listedCampaign.getId() in campaignIds) {
      delete campaignIds[listedCampaign.getId()];
    } else {
      campaignsToRemove.push(listedCampaign);
    }
  }

  // Anything left over in campaignIds starts a new list.
  var campaignsToAdd = AdWordsApp.campaigns().withIds(
      Object.keys(campaignIds)).get();
  while (campaignsToAdd.hasNext()) {
    var campaignToAdd = campaignsToAdd.next();

    if (criteriaType == CriteriaType.KEYWORDS) {
      campaignToAdd.addNegativeKeywordList(sharedList);
    } else if (criteriaType == CriteriaType.PLACEMENTS) {
      campaignToAdd.addExcludedPlacementList(sharedList);
    }
  }

  for (var i = 0; i < campaignsToRemove.length; i++) {
    if (criteriaType == CriteriaType.KEYWORDS) {
      campaignsToRemove[i].removeNegativeKeywordList(sharedList);
    } else if (criteriaType == CriteriaType.PLACEMENTS) {
      campaignsToRemove[i].removeExcludedPlacementList(sharedList);
    }
  }

  return totalCampaigns;
}

/**
 * Gets a list of campaigns having a particular label.
 *
 * @param {String} labelText The label text.
 *
 * @return {Array.<Number>} An array of campaign IDs having the specified
 *     label.
 */
function getLabelledCampaigns(labelText) {
  var campaignIds = {};

  if (labelText != '') {
    var label = getLabel(labelText);
    var campaigns = label.campaigns().withCondition(
        'Status in [ENABLED, PAUSED]').get();
  } else {
    var campaigns = AdWordsApp.campaigns().withCondition(
        'Status in [ENABLED, PAUSED]').get();
  }

  while (campaigns.hasNext()) {
    var campaign = campaigns.next();
    campaignIds[campaign.getId()] = 1;
  }
  return campaignIds;
}

/**
 * Gets a label with the specified label text.
 *
 * @param {String} labelText The label text.
 *
 * @return {AdWordsApp.Label} The label text.
 */
function getLabel(labelText) {
  var labels = AdWordsApp.labels().withCondition(
      "Name='" + labelText + "'").get();
  if (labels.totalNumEntities() == 0) {
    var message = Utilities.formatString('Label named %s is missing in your ' +
        'account. Make sure the label exists in the account, and is applied ' +
        'to campaigns and adgroups you wish to process.', labelText);
    throw (message);
  }

  return labels.next();
}

/**
 * Synchronizes the criteria in a shared negative criteria list with the user
 * spreadsheet.
 *
 * @param {Object} config The configuration object.
 * @param {String} criteriaType The criteria type for the shared negative list.
 *
 * @return {Object} A summary of the synced negative list, and the number of
 *     criteria that were synced.
 */
function syncCriteriaInNegativeList(config, criteriaType) {
  var criteriaFromSheet = loadCriteria(criteriaType);
  var totalCriteriaCount = Object.keys(criteriaFromSheet).length;

  var sharedList = null;
  var listName = config.listname[criteriaType];

  sharedList = createNegativeListIfRequired(listName, criteriaType);

  var negativeCriteria = null;

  try {
    if (criteriaType == CriteriaType.KEYWORDS) {
      negativeCriteria = sharedList.negativeKeywords().get();
    } else if (criteriaType == CriteriaType.PLACEMENTS) {
      negativeCriteria = sharedList.excludedPlacements().get();
    }
  } catch (e) {
    Logger.log('Failed to retrieve shared list. Error says ' + e);
    if (AdWordsApp.getExecutionInfo().isPreview()) {
      var message = Utilities.formatString('The script cannot create the ' +
          'negative %s list in preview mode. Either run the script without ' +
          'preview, or create a negative %s list with name "%s" manually ' +
          'before previewing the script.', criteriaType, criteriaType,
          listName);
      Logger.log(message);
    }
    throw e;
  }

  var criteriaToDelete = [];

  while (negativeCriteria.hasNext()) {
    var negativeCriterion = negativeCriteria.next();
    var key = null;

    if (criteriaType == CriteriaType.KEYWORDS) {
      key = negativeCriterion.getText();
    } else if (criteriaType == CriteriaType.PLACEMENTS) {
      key = negativeCriterion.getUrl();
    }

    if (key in criteriaFromSheet) {
      // Nothing to do with this criteria. Remove it from loaded list.
      delete criteriaFromSheet[key];
    } else {
      // This criterion is not in the sync list. Mark for deletion.
      criteriaToDelete.push(negativeCriterion);
    }
  }

  // Whatever left in the sync list are new items.
  if (criteriaType == CriteriaType.KEYWORDS) {
    sharedList.addNegativeKeywords(Object.keys(criteriaFromSheet));
  } else if (criteriaType == CriteriaType.PLACEMENTS) {
    sharedList.addExcludedPlacements(Object.keys(criteriaFromSheet));
  }

  for (var i = 0; i < criteriaToDelete.length; i++) {
    criteriaToDelete[i].remove();
  }

  return {
    'SharedList': sharedList,
    'CriteriaCount': totalCriteriaCount,
    'Type': criteriaType
  };
}

/**
 * Creates a shared negative criteria list if required.
 *
 * @param {string} listName The name of shared negative criteria list.
 * @param {String} listType The criteria type for the shared negative list.
 *
 * @return {AdWordsApp.NegativeKeywordList|AdWordsApp.ExcludedPlacementList} An
 *     existing shared negative criterion list if it already exists in the
 *     account, or the newly created list if one didn't exist earlier.
 */
function createNegativeListIfRequired(listName, listType) {
  var negativeListSelector = null;
  if (listType == CriteriaType.KEYWORDS) {
    negativeListSelector = AdWordsApp.negativeKeywordLists();
  } else if (listType == CriteriaType.PLACEMENTS) {
    negativeListSelector = AdWordsApp.excludedPlacementLists();
  }
  var negativeListIterator = negativeListSelector.withCondition(
      "Name = '" + listName + "'").get();

  if (negativeListIterator.totalNumEntities() == 0) {
    var builder = null;

    if (listType == CriteriaType.KEYWORDS) {
      builder = AdWordsApp.newNegativeKeywordListBuilder();
    } else if (listType == CriteriaType.PLACEMENTS) {
      builder = AdWordsApp.newExcludedPlacementListBuilder();
    }

    var negativeListOperation = builder.withName(listName).build();
    return negativeListOperation.getResult();
  } else {
    return negativeListIterator.next();
  }
}

/**
 * Loads a list of criteria from the user spreadsheet.
 *
 * @param {string} sheetName The name of shared negative criteria list.
 *
 * @return {Object} A map of the list of criteria loaded from the spreadsheet.
 */
function loadCriteria(sheetName) {
  var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName(sheetName);
  var values = sheet.getRange('B4:B').getValues();

  var retval = {};
  for (var i = 0; i < values.length; i++) {
    var keyword = values[i][0].toString().trim();
    if (keyword != '') {
      retval[keyword] = 1;
    }
  }
  return retval;
}

/**
 * Loads a configuration object from the spreadsheet.
 *
 * @return {Object} A configuration object.
 */
function readConfig() {
  var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  var values = spreadsheet.getRangeByName('ConfigurationValues').getValues();

  var config = {
    'label': values[0][0],
    'listname': {
    },
    'email': values[3][0],
  };
  config.listname[CriteriaType.KEYWORDS] = values[1][0];
  config.listname[CriteriaType.PLACEMENTS] = values[2][0];
  return config;
}

/**
 * DO NOT EDIT ANYTHING BELOW THIS LINE.
 * Please modify your spreadsheet URL at the top of the file only.
 */

/**
 * Validates the provided spreadsheet URL and email address
 * to make sure that they're 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 or email hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'INSERT_SPREADSHEET_URL_HERE') {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
  return spreadsheet;
}

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

发送以下问题的反馈:

此网页
AdWords Scripts
AdWords Scripts
需要帮助?请访问我们的支持页面