Skrip ini memperluas fungsi Daftar Negatif Umum versi tunggal versi akun untuk berfungsi pada beberapa akun dalam akun pengelola. Skrip ini menyederhanakan tugas pengelolaan daftar negatif umum untuk kampanye di beberapa akun.
Cara kerjanya
Skrip membaca kriteria negatif dari Google Spreadsheet. Tindakan ini akan membuat daftar kriteria negatif bersama di akun Google Ads dan menyinkronkan daftar tersebut dengan kriteria dari spreadsheet. Daftar terpisah dikelola untuk kata kunci dan penempatan. Daftar dibuat satu per satu di setiap akun terkelola, dan disinkronkan di setiap akun agar sesuai dengan konfigurasi yang diberikan dalam spreadsheet.
Skrip tersebut kemudian memastikan daftar kriteria negatif diterapkan ke semua kampanye di akun. Jika diperlukan, Anda dapat membatasi daftar kampanye dengan menentukan label dalam spreadsheet konfigurasi untuk memfilter penyertaan saat memproses kampanye.
Secara opsional, skrip ini mengirimkan email yang berisi rangkuman tindakannya ke alamat email yang ditentukan dalam spreadsheet konfigurasi.
Penyiapan
Klik tombol di bawah untuk membuat skrip berbasis spreadsheet di akun Google Ads Anda.
Klik tombol di bawah ini untuk membuat salinan {i>spreadsheet<i} {i>template<i}.
Mengupdate
spreadsheet_urldalam skrip Anda.Skrip ini memproses semua kampanye
ENABLEDdanPAUSEDdi dalam akun secara default. Untuk membatasi daftar kampanye yang diproses, lakukan langkah-langkah berikut:- Buat label di setiap akun yang ingin Anda proses.
- Terapkan label ini ke daftar kampanye yang akan diproses.
- Tentukan label ini di sel C3 pada {i>spreadsheet<i} konfigurasi.
Skrip ini secara default memproses semua kampanye yang dapat diakses dari akun pengelola Anda. Untuk membatasi subkumpulan akun tertentu, tentukan daftar ID Pelanggan yang dipisahkan koma (dalam format
XXX-XXX-XXXX) di Sel C7.Tentukan alamat email di sel C6 untuk menerima email ringkasan setelah skrip selesai dijalankan.
Skrip ini membuat daftar kriteria negatif bersama yang terpisah untuk kata kunci dan penempatan di akun yang diproses. Tentukan nama daftar kriteria negatif bersama dalam {i>spreadsheet<i} konfigurasi, sel C4 dan C5.
Hapus semua awalan protokol (
http://atauhttps://) dari URL Penempatan apa pun di daftar penempatan Anda.Pastikan bahwa semua URL Penempatan tidak memiliki garis miring di akhir (
/).Pastikan bahwa semua URL Penempatan menggunakan huruf kecil di spreadsheet Anda.
Penjadwalan
Jadwalkan skrip untuk dijalankan setiap hari atau setiap jam.
Kode sumber
// 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);
}