維護排除關鍵字或排除關鍵字清單是 Google Ads 廣告活動管理中的常見工作。這份清單通常可以用來篩選 能為網站帶來無效流量的關鍵字或刊登位置
Google Ads 支援排除條件清單，可與單一帳戶中的多個廣告活動共用。不過，將這份清單套用至一個大型帳戶中的多個廣告活動，並持續同步清單可能很困難。 「一般排除清單指令碼」可讓您透過試算表管理排除條件，簡化這項工作。
運作方式
此指令碼會讀取 Google 試算表中的排除條件。系統會在 Google Ads 帳戶中建立共用的排除條件清單，並將該清單與試算表中的條件同步處理。系統會維護關鍵字和刊登位置的個別清單。
然後，這個指令碼可確保排除條件清單會套用至帳戶中的所有廣告活動。如有需要，您可以在設定試算表中指定標籤，在處理廣告活動時篩選出納入條件，藉此限制廣告活動清單。
指令碼會選擇性傳送一封電子郵件，摘要說明執行動作至設定試算表中指定的電子郵件地址。
設定
指令碼會使用試算表進行設定。BigQuery 支援的配置設定如下：
- 根據預設，指令碼會處理帳戶中的所有
ENABLED和PAUSED廣告活動。如要限制已處理的廣告活動清單，請按照下列步驟操作：- 在您要處理的每個帳戶中建立標籤。
- 將這個標籤套用至待處理的廣告活動清單。
- 在設定試算表的儲存格 C3 中指定這個標籤。
- 在 C6 儲存格中指定電子郵件地址，以便在指令碼執行完畢後接收摘要電子郵件。
- 指令碼會為處理過的帳戶中的關鍵字和刊登位置建立不同的共用排除條件清單。在設定試算表、儲存格 C4 和 C5 中指定共用排除條件清單的名稱。
- 在刊登位置清單的任何刊登位置網址中，省略任何通訊協定前置字元 (
http://或https://)。 - 確認所有刊登位置網址網址結尾都是正斜線 (
/)。 - 確定試算表中的所有刊登位置網址都為小寫。
排程
排定每天或每小時執行一次。
設定
原始碼
// 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 Common Negative List Script
*
* @overview The Common Negative List script applies negative keywords and
* placements from a spreadsheet to multiple campaigns in your account using
* shared keyword and placement lists. See
* https://developers.google.com/google-ads/scripts/docs/solutions/common-negative-list
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 2.2
*
* @changelog
* - version 2.2
* - Fixed an issue where the match type of keywords in the negative list was
* ignored.
* - version 2.1
* - Split into info, config, and code.
* - version 2.0
* - Updated to use new Google Ads scripts features.
* - 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.
*/
/**
* Configuration to be used for the Common Negative List script.
*/
CONFIG = {
/**
* The URL of the tracking spreadsheet. This should be a copy of
* https://goo.gl/PZGKVn
*/
'spreadsheet_url': 'INSERT_SPREADSHEET_URL_HERE'
};
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
/**
* Keep track of the spreadsheet names for various criteria types, as well as
* the criteria type being processed.
*/
const CriteriaType = {
KEYWORDS: 'Keywords',
PLACEMENTS: 'Placements'
};
/**
* Create a shared negative criteria list in the Google Ads account and
* syncs the list with the criteria from the spreadsheet.
*/
function main() {
let 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 {
const syncSummary = syncCommonLists();
emailParams.PlacementCount = syncSummary.PlacementCount;
emailParams.KeywordCount = syncSummary.KeywordCount;
emailParams.CampaignCount = syncSummary.CampaignCount;
emailParams.Status = 'OK';
} catch (err) {
emailParams.Status = 'ERROR';
}
const config = readConfig();
const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
// Make sure the spreadsheet is using the account's timezone.
spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
spreadsheet.getRangeByName('LastRun').setValue(new Date());
spreadsheet.getRangeByName('CustomerId').setValue(
AdsApp.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) {
const html = [];
let summary = '';
if (emailParams.Status == 'OK') {
summary = `The Common Negative List script successfully processed ` +
`Customer ID: ${AdsApp.currentAccount().getCustomerId()}` +
` and synced a total of ${emailParams.KeywordCount}` +
` keywords and ${emailParams.PlacementCount} placements.`;
} else {
summary = `The Common Negative List script failed to process ` +
`Customer ID: ${AdsApp.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 />Google Ads Scripts Team</p>',
'</td>',
'</tr>',
'</table>',
'</body>',
'</html>'
);
if (config.email != '') {
MailApp.sendEmail({
to: config.email,
subject: 'Common Negative List Script',
htmlBody: html.join('\n')
});
}
}
/**
* Synchronizes the negative criteria list in an account with the common 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 syncCommonLists() {
const config = readConfig();
let syncedCampaignCount = 0;
const keywordListDetails = syncCriteriaInNegativeList(config,
CriteriaType.KEYWORDS);
syncedCampaignCount = syncCampaignList(config, keywordListDetails.SharedList,
CriteriaType.KEYWORDS);
const 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 common list.
*
* @param {Object} config The configuration object.
* @param {AdsApp.NegativeKeywordList|AdsApp.ExcludedPlacementList}
* sharedList The shared negative criterion list to be synced against the
* common list.
* @param {String} criteriaType The criteria type for the shared negative list.
*
* @return {Number} The number of campaigns synced.
*/
function syncCampaignList(config, sharedList, criteriaType) {
const campaignIds = getLabelledCampaigns(config.label);
let totalCampaigns = Object.keys(campaignIds).length;
const listedCampaigns = sharedList.campaigns().get();
const campaignsToRemove = [];
for (const listedCampaign of listedCampaigns) {
if (listedCampaign.getId() in campaignIds) {
delete campaignIds[listedCampaign.getId()];
} else {
campaignsToRemove.push(listedCampaign);
}
}
// Anything left over in campaignIds starts a new list.
const campaignsToAdd = AdsApp.campaigns().withIds(
Object.keys(campaignIds)).get();
for (const campaignToAdd of campaignsToAdd) {
if (criteriaType == CriteriaType.KEYWORDS) {
campaignToAdd.addNegativeKeywordList(sharedList);
} else if (criteriaType == CriteriaType.PLACEMENTS) {
campaignToAdd.addExcludedPlacementList(sharedList);
}
}
for (const campaignToRemove of campaignsToRemove) {
if (criteriaType == CriteriaType.KEYWORDS) {
campaignToRemove.removeNegativeKeywordList(sharedList);
} else if (criteriaType == CriteriaType.PLACEMENTS) {
campaignToRemove.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) {
const campaignIds = {};
let campaigns;
if (labelText != '') {
const label = getLabel(labelText);
campaigns = label.campaigns().withCondition(
'Status in [ENABLED, PAUSED]').get();
} else {
campaigns = AdsApp.campaigns().withCondition(
'Status in [ENABLED, PAUSED]').get();
}
for (const campaign of campaigns) {
campaignIds[campaign.getId()] = 1;
}
return campaignIds;
}
/**
* Gets a label with the specified label text.
*
* @param {String} labelText The label text.
*
* @return {AdsApp.Label} The label text.
*/
function getLabel(labelText) {
let labels = AdsApp.labels().withCondition(
"Name='" + labelText + "'").get();
if (labels.totalNumEntities() == 0) {
const message = Utilities.formatString(`Label named ${labelText} is missing in ' +
'your account. Make sure the label exists in the account, and is ' +
'applied to campaigns and adgroups you wish to process.`);
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) {
let criteriaFromSheet = loadCriteria(criteriaType);
let totalCriteriaCount = Object.keys(criteriaFromSheet).length;
let sharedList = null;
let listName = config.listname[criteriaType];
sharedList = createNegativeListIfRequired(listName, criteriaType);
let negativeCriteria = null;
try {
if (criteriaType == CriteriaType.KEYWORDS) {
negativeCriteria = sharedList.negativeKeywords().get();
} else if (criteriaType == CriteriaType.PLACEMENTS) {
negativeCriteria = sharedList.excludedPlacements().get();
}
} catch (e) {
console.error(`Failed to retrieve shared list. Error says ${e}`);
if (AdsApp.getExecutionInfo().isPreview()) {
const message = Utilities.formatString(`The script cannot create` +
` the negative ${criteriaType} list in preview mode. Either run` +
` the script without preview, or create a negative ` +
` ${criteriaType} list with name "${listName}" ` +
` manually before previewing the script.`);
console.log(message);
}
throw e;
}
const criteriaToDelete = [];
for (const negativeCriterion of negativeCriteria) {
let key = null;
if (criteriaType == CriteriaType.KEYWORDS) {
key = negativeCriterion.getText();
// Since the keyword text in the spreadsheet specifies match types in the
// syntax accepted by the UI, we need to convert our keys to match it.
const matchType = negativeCriterion.getMatchType();
if (matchType === "PHRASE") {
key = `"${key}"`;
} else if (matchType === "EXACT") {
key = `[${key}]`;
}
} 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 (let 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 {AdsApp.NegativeKeywordList|AdsApp.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) {
let negativeListSelector = null;
if (listType == CriteriaType.KEYWORDS) {
negativeListSelector = AdsApp.negativeKeywordLists();
} else if (listType == CriteriaType.PLACEMENTS) {
negativeListSelector = AdsApp.excludedPlacementLists();
}
let negativeListIterator = negativeListSelector.withCondition(
`Name = '${listName}'`).get();
if (negativeListIterator.totalNumEntities() == 0) {
let builder = null;
if (listType == CriteriaType.KEYWORDS) {
builder = AdsApp.newNegativeKeywordListBuilder();
} else if (listType == CriteriaType.PLACEMENTS) {
builder = AdsApp.newExcludedPlacementListBuilder();
}
let 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) {
const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
const sheet = spreadsheet.getSheetByName(sheetName);
const values = sheet.getRange('B4:B').getValues();
const retval = {};
for (const value of values) {
let keyword = value[0].toString().trim();
if (keyword != '') {
retval[keyword] = 1;
}
}
return retval;
}
/**
* Loads a configuration object from the spreadsheet.
*
* @return {Object} A configuration object.
*/
function readConfig() {
const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
let values = spreadsheet.getRangeByName('ConfigurationValues').getValues();
const 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.');
}
return SpreadsheetApp.openByUrl(spreadsheeturl);
}