이 스크립트는 관리자 계정의 여러 계정을 대상으로 작동할 수 있도록 공통 제외 목록의 단일 계정 버전의 기능을 확장합니다. 이 스크립트는 여러 계정의 캠페인에 대한 공통 제외 목록을 관리하는 작업을 간소화합니다.
사용 방법
스크립트는 Google 스프레드시트에서 제외 기준을 읽습니다. Google Ads 계정에 공유된 제외 기준 목록을 만들고 이 목록을 스프레드시트의 기준과 동기화합니다. 키워드와 게재위치에 대해서는 별도의 목록이 필요합니다. 목록은 각 관리 계정에서 개별적으로 생성되며 스프레드시트에 제공된 구성과 일치하도록 각 계정에 동기화됩니다.
그런 다음 스크립트는 제외 기준 목록이 계정의 모든 캠페인에 적용되도록 합니다. 필요한 경우 캠페인을 처리할 때 포함하도록 필터링하도록 구성 스프레드시트에 라벨을 지정하여 캠페인 목록을 제한할 수 있습니다.
스크립트는 선택적으로 작업을 요약하는 이메일을 구성 스프레드시트에 지정된 이메일 주소로 전송합니다.
설정
아래 버튼을 클릭하여 Google Ads 계정에서 스프레드시트 기반 스크립트를 만들 수 있습니다.
템플릿 스프레드시트의 사본을 만들려면 아래 버튼을 클릭하세요.
스크립트에서
spreadsheet_url를 업데이트합니다.스크립트는 기본적으로 계정의 모든
ENABLED및PAUSED캠페인을 처리합니다. 처리되는 캠페인 목록을 제한하려면 다음 단계를 따르세요.- 처리할 각 계정에서 라벨을 만듭니다.
- 이 라벨을 처리할 캠페인 목록에 적용합니다.
- 구성 스프레드시트의 C3 셀에 이 라벨을 지정합니다.
스크립트는 기본적으로 관리자 계정에서 액세스할 수 있는 모든 캠페인을 처리합니다. 계정의 특정 하위 집합으로 제한하려면 C7 셀에 쉼표로 구분된 고객 ID 목록 (
XXX-XXX-XXXX형식)을 지정합니다.스크립트 실행이 완료되면 요약 이메일을 받을 이메일 주소를 C6 셀에 지정합니다.
스크립트는 처리하는 계정의 키워드 및 게재위치에 대해 별도의 공유 제외 기준 목록을 만듭니다. 구성 스프레드시트, C4 및 C5 셀에 공유 제외 기준 목록의 이름을 지정합니다.
게재위치 목록의 게재위치 URL에서 프로토콜 접두어 (
http://또는https://)를 생략합니다.모든 게재위치 URL에 후행 슬래시 (
/)가 없어야 합니다.스프레드시트에 모든 게재위치 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 for Google Ads manager accounts
*
* @overview The Master Negative List script for Google Ads manager accounts
* 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 Google Ads accounts in parallel. See
* https://developers.google.com/google-ads/scripts/docs/solutions/manager-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 Master Negative List Script for Google Ads
* manager accounts.
*/
CONFIG = {
// The URL of the tracking spreadsheet. This should be a copy of
// https://goo.gl/rwnCbF
// Make sure the sheet is owned by or shared with same Google user executing the script
'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'
};
/**
* The code to execute when running the script.
*/
function main() {
const config = readConfig();
const accountSelector = AdsManagerApp.accounts();
if (config.customerids.length > 0) {
accountSelector.withIds(config.customerids);
}
accountSelector.executeInParallel('processAccounts', 'postProcess');
}
/**
* Process an account when processing multiple accounts under a Google Ads
* manager account in parallel.
*
* @return {string} A JSON string that summarizes the number of keywords and
* placements synced, and the number of campaigns processed.
*/
function processAccounts() {
return JSON.stringify(syncMasterLists());
}
/**
* Callback method after processing accounts, when processing multiple accounts
* under a Google Ads manager account in parallel.
*
* @param {Array.<AdsManagerApp.ExecutionResult>} results The execution results
* from the accounts that were processed by this script.
*/
function postProcess(results) {
const config = readConfig();
const emailParams = {
// Number of placements that were synced.
PlacementCount: 0,
// Number of keywords that were synced.
KeywordCount: 0,
// Summary of customers who were synced.
Customers: {
// How many customers were synced?
Success: 0,
// How many customers failed to sync?
Failure: 0,
// Details of each account processed. Contains 3 properties:
// CustomerId, CampaignCount, Status.
Details: []
}
};
for (const result of results) {
const customerResult = {
// The customer ID that was processed.
CustomerId: result.getCustomerId(),
// Number of campaigns that were synced.
CampaignCount: 0,
// Status of processing this account - OK / ERROR / TIMEOUT
Status: result.getStatus()
};
if (result.getStatus() == 'OK') {
let retval = JSON.parse(result.getReturnValue());
customerResult.CampaignCount = retval.CampaignCount;
if (emailParams.Customers.Success == 0) {
emailParams.KeywordCount = retval.KeywordCount;
emailParams.PlacementCount = retval.PlacementCount;
}
emailParams.Customers.Success++;
} else {
emailParams.Customers.Failure++;
}
emailParams.Customers.Details.push(customerResult);
}
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 = [];
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>The Master Negative List script synced a total ' +
'of <b>' + emailParams.KeywordCount + '</b> ' +
'keywords and <b>' + emailParams.PlacementCount +
'</b> placements. <b>' +
(emailParams.Customers.Success +
emailParams.Customers.Failure) +
'</b> accounts were processed, of which <b>' +
emailParams.Customers.Success + '</b> ' +
'succeeded, and <b>' +
emailParams.Customers.Failure + '</b> failed. ' +
'See the table below' +
' for details.</p>',
"<table border='1' width='100%' " +
"style='border-collapse: collapse; " +
"border: solid 1px #dddddd;font-size:10pt;'>",
'<tr>',
'<th>CustomerId</th>',
'<th>Synced Campaigns</th>',
'<th>Status</th>',
'</tr>'
);
for (const detail of emailParams.Customers.Details) {
html.push('<tr>',
'<td>' + detail.CustomerId + '</td>',
'<td>' + detail.CampaignCount + '</td>',
'<td>' + detail.Status + '</td>',
'</tr>'
);
}
html.push('</table>',
'<p>Cheers<br />Google Ads 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() {
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);
syncedCampaignCount = 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 {AdsApp.NegativeKeywordList|AdsApp.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) {
const campaignIds = getLabelledCampaigns(config.label);
const 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 = null;
if (labelText != '') {
const label = getLabel(labelText);
campaigns = label.campaigns().withCondition(
'campaign.status in (ENABLED, PAUSED)').get();
} else {
campaigns = AdsApp.campaigns().withCondition(
'campaign.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) {
const labels = AdsApp.labels().withCondition(
`label.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) {
const criteriaFromSheet = loadCriteria(criteriaType);
const 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()) {
let 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 (const criterionToDelete of criteriaToDelete) {
criterionToDelete.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(
`shared_set.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],
'customerids': extractCustomerIds(values[4][0])
};
config.listname[CriteriaType.KEYWORDS] = values[1][0];
config.listname[CriteriaType.PLACEMENTS] = values[2][0];
return config;
}
/**
* Extracts customerIds from a comma separated list.
*
* @param {string} data the input.
* @return {Array.<number>} A list of customer IDs.
*/
function extractCustomerIds(data) {
const retval = [];
const splits = data.split(',');
for (let split of splits) {
split = split.trim().replace(/-/g, '').replace(/^\s+|\s+$/g, '');
if (split) {
if (isNaN(split)) {
console.log(`Invalid customer ID found in spreadsheet: ${split}`);
} else {
const customerId = parseInt(split).toFixed(0);
retval.push(customerId);
}
}
}
return retval;
}
/**
* 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);
}