Сегодня мы поговорим о пяти бесплатных скриптах Адвордс, что предназначены для отслеживания показателей качестве, выявления битых ссылок, группировки и конфликта ключевых слов.
Показатели качества оказывают влияние на многих вещи – как на позицию рекламного объявления, так и на цену клика.
Данный инструмент поможет сохранить информацию, что касается показателей качества аккаунта, кампании и ключевых фраз, которые входят в группу первых 50 000 слов в виде CSV.
Скрипт:
/************************************
* Store Account, Campaign, and AdGroup Level Quality Score
* Version 2.3
* ChangeLog v2.3
* - Solved #NUM! issue by filtering out -- values
* ChangeLog v2.2
* - Updated KeywordText to Criteria
* ChangeLog v2.1
* - Ignore negatives
* ChangeLog v2.0
* - Rewrote for speed using the reporting api
* - Added ability to store data in .csv file
* - Added the ability for custom date ranges
* - Added the ability for Spreadsheet Names
* ChangeLog v1.3
* - Updated writeToSpreadsheet function
* - Added keyword level reporting
* ChangeLog v1.2
* - Changed status to ENABLED
* ChangeLog v1.1
* - Added APPEND option
* - Added ability to create spreadsheet sheets
* - Updated logic for faster spreadsheet insertion
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************/
var DECIMALS = 4; //this will give you 4 decimal places of accuracy
//You can set this to anything in this list: TODAY, YESTERDAY, LAST_7_DAYS,
// THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS,
// LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH
var DATE_RANGE = 'LAST_30_DAYS';
// Or you can set this to any number of days you like. it overrides the DATE_RANGE set above
var LAST_N_DAYS = 0;
var CSV_FILE_PREFIX = ""; //Set this if you want to write to a set of CSV files, one for each account level.
var SPREADSHEET_URL = ""; //Set this if you have the url of a spreadsheet you want to update
var SPREADSHEET_NAME = ""; //Set this if you want to write to the name of a spreadsheet instead
function main() {
var isCSV = (CSV_FILE_PREFIX !== "");
var allData = getKeywordsReport();
var tabs = ['Account','Campaign','AdGroup','Keyword'];
for(var i in tabs) {
var tab = tabs[i];
var dataToWrite = [];
var cols = getCols(tab);
var rowKeys = getRowKeys(tab,Object.keys(allData));
for(var x in rowKeys) {
var rowArray = [];
var key = rowKeys[x];
var row = allData[key];
for(var y in cols) {
rowArray.push(row[cols[y]]);
}
dataToWrite.push(rowArray);
}
if(isCSV) {
writeDataToCSV(tab,dataToWrite);
} else {
writeDataToSpreadsheet(tab,dataToWrite);
}
}
}
function getRowKeys(tab,allKeys) {
return allKeys.filter(function(e) { return (e.indexOf(tab) >= 0); });
}
function getCols(tab) {
return {
'Account' : ['Date','Account','ImpsWeightedQS'],
'Campaign': ['Date','Account','Campaign','ImpsWeightedQS'],
'AdGroup' : ['Date','Account','Campaign','AdGroup','ImpsWeightedQS'],
'Keyword' : ['Date','Account','Campaign','AdGroup','Keyword','QS','ImpsWeightedQS']
}[tab];
}
// Super fast spreadsheet insertion
function writeDataToSpreadsheet(tab,toWrite) {
//This is where i am going to store all my data
var spreadsheet;
if(SPREADSHEET_NAME) {
var fileIter = DriveApp.getFilesByName(SPREADSHEET_NAME);
if(fileIter.hasNext()) {
var file = fileIter.next();
spreadsheet = SpreadsheetApp.openById(file.getId());
} else {
spreadsheet = SpreadsheetApp.create(SPREADSHEET_NAME);
}
} else if(SPREADSHEET_URL) {
spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
} else {
throw 'You need to set at least one of the SPREADSHEET_URL or SPREADSHEET_NAME variables.';
}
var sheet = spreadsheet.getSheetByName(tab);
if(!sheet) {
sheet = spreadsheet.insertSheet(tab);
sheet.appendRow(getCols(tab));
}
var lastRow = sheet.getLastRow();
var numRows = sheet.getMaxRows();
if((numRows-lastRow) < toWrite.length) {
sheet.insertRowsAfter((lastRow == 0) ? 1 : lastRow,toWrite.length-numRows+lastRow);
}
var range = sheet.getRange(lastRow+1,1,toWrite.length,toWrite[0].length);
range.setValues(toWrite);
}
function writeDataToCSV(tab,toWrite) {
if(!toWrite) { return; }
var fileName = CSV_FILE_PREFIX + '_' + tab + '.csv';
var file;
var fileIter = DriveApp.getFilesByName(fileName);
if(fileIter.hasNext()) {
file = fileIter.next();
} else {
file = DriveApp.createFile(fileName, formatCsvRow(getCols(tab)));
}
var fileData = file.getBlob().getDataAsString();
for(var i in toWrite) {
fileData += formatCsvRow(toWrite[i]);
}
file.setContent(fileData);
return file.getUrl();
}
function formatCsvRow(row) {
for(var i in row) {
if(row[i].toString().indexOf('"') == 0) {
row[i] = '""'+row[i]+'""';
}
if(row[i].toString().indexOf('+') == 0) {
row[i] = "'"+row[i];
}
if(row[i].toString().indexOf(',') >= 0 &&
row[i].toString().indexOf('"""') != 0)
{
row[i] = ('"'+row[i]+'"');
}
}
return row.join(',')+'\n';
}
function getKeywordsReport() {
var theDate = DATE_RANGE;
if(LAST_N_DAYS != 0) {
theDate = getDateDaysAgo(LAST_N_DAYS)+','+getDateDaysAgo(1);
}
Logger.log('Using date range: '+theDate);
var OPTIONS = { includeZeroImpressions : true };
var cols = ['ExternalCustomerId',
'CampaignId','CampaignName',
'AdGroupId','AdGroupName',
'Id','Criteria','KeywordMatchType',
'IsNegative','Impressions', 'QualityScore'];
var report = 'KEYWORDS_PERFORMANCE_REPORT';
var query = ['select',cols.join(','),'from',report,
'where AdNetworkType1 = SEARCH',
'and CampaignStatus = ENABLED',
'and AdGroupStatus = ENABLED',
'and Status = ENABLED',
'during',theDate].join(' ');
var results = {};
var reportIter = AdWordsApp.report(query, OPTIONS).rows();
while(reportIter.hasNext()) {
var row = reportIter.next();
if(row.QualityScore == "--") { continue; }
if(row.IsNegative == true || row.IsNegative === 'true') { continue; }
loadHashEntry('Account:'+row.ExternalCustomerId,row,results);
loadHashEntry('Campaign:'+row.CampaignId,row,results);
loadHashEntry('AdGroup:'+[row.CampaignId,row.AdGroupId].join('-'),row,results);
loadHashEntry('Keyword:'+[row.CampaignId,row.AdGroupId,row.Id].join('-'),row,results);
}
var dateStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
for(var i in results) {
results[i]['Date'] = dateStr;
results[i]['ImpsWeightedQS'] = (results[i]['totalImps'] === 0) ? 0 : round(results[i]['ImpsWeightedQS']/results[i]['totalImps']);
}
return results;
}
function loadHashEntry(key,row,results) {
if(!results[key]) {
results[key] = {
QS : 0,
ImpsWeightedQS : 0,
totalImps : 0,
Account : null,
Campaign : null,
AdGroup : null,
Keyword : null
};
}
results[key].QS = parseFloat(row.QualityScore);
results[key].ImpsWeightedQS += (parseFloat(row.QualityScore)*parseFloat(row.Impressions));
results[key].totalImps += parseFloat(row.Impressions);
results[key].Account = row.ExternalCustomerId;
results[key].Campaign = row.CampaignName;
results[key].AdGroup = row.AdGroupName;
results[key].Keyword = (row.KeywordMatchType === 'Exact') ? '['+row.Criteria+']' :
(row.KeywordMatchType === 'Phrase') ? '"'+row.Criteria+'"' : row.Criteria;
}
//A helper function to return the number of days ago.
function getDateDaysAgo(days) {
var thePast = new Date();
thePast.setDate(thePast.getDate() - days);
return Utilities.formatDate(thePast, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
}
function round(val) {
var divisor = Math.pow(10,DECIMALS);
return Math.round(val*divisor)/divisor;
}
Когда происходит удаление старых страниц, нередко появляется ошибка 404. Она может сократить целевой трафик и слить ваш бюджет.
Для того, чтобы находить ошибки адресов в нужное время, стоит включить скрипт проверки ссылок. С помощью данного инструмента можно уточнить наличие целевых страниц для всей рекламы и ключевиков аккаунта. Если ошибки будут обнаружены, то вам на электронную почту придет оповещение.
Для того, чтоб работать с данным скриптом, стоит пользоваться панелью управления, которая имеет вид электронной таблицы.
Код:
// Copyright 2016, 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 Link Checker
*
* @overview The Link Checker script iterates through the ads, keywords, and
* sitelinks in your account and makes sure their URLs do not produce "Page
* not found" or other types of error responses. See
* https://developers.google.com/adwords/scripts/docs/solutions/link-checker
* for more details.
*
* @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 2.1
*
* @changelog
* - version 2.1
* - Added expansion of conditional ValueTrack parameters (e.g. ifmobile).
* - Added expanded text ad and other ad format support.
* - version 2.0.3
* - Added validation for external spreadsheet setup.
* - version 2.0.2
* - Allow the custom tracking label to include spaces.
* - version 2.0.1
* - Catch and output all UrlFetchApp exceptions.
* - version 2.0
* - Completely revised the script to work on larger accounts.
* - Check URLs in campaign and ad group sitelinks.
* - version 1.2
* - Released initial version.
*/
var CONFIG = {
// URL of the spreadsheet template.
// This should be a copy of https://goo.gl/8YLeMj.
SPREADSHEET_URL: 'YOUR_SPREADSHEET_URL',
// Array of addresses to be alerted via email if issues are found.
RECIPIENT_EMAILS: [
'YOUR_EMAIL_HERE'
],
// Label to use when a link has been checked.
LABEL: 'LinkChecker_Done',
// Number of milliseconds to sleep after each URL request. If your URLs are
// all on one or a few domains, use this throttle to reduce the load that the
// script imposes on your web server(s).
THROTTLE: 0,
// Number of seconds before timeout that the script should stop checking URLs
// to make sure it has time to output its findings.
TIMEOUT_BUFFER: 120
};
/**
* Parameters controlling the script's behavior after hitting a UrlFetchApp
* QPS quota limit.
*/
var QUOTA_CONFIG = {
INIT_SLEEP_TIME: 250,
BACKOFF_FACTOR: 2,
MAX_TRIES: 5
};
/**
* Exceptions that prevent the script from finishing checking all URLs in an
* account but allow it to resume next time.
*/
var EXCEPTIONS = {
QPS: 'Reached UrlFetchApp QPS limit',
LIMIT: 'Reached UrlFetchApp daily quota',
TIMEOUT: 'Approached script execution time limit'
};
/**
* Named ranges in the spreadsheet.
*/
var NAMES = {
CHECK_AD_URLS: 'checkAdUrls',
CHECK_KEYWORD_URLS: 'checkKeywordUrls',
CHECK_SITELINK_URLS: 'checkSitelinkUrls',
CHECK_PAUSED_ADS: 'checkPausedAds',
CHECK_PAUSED_KEYWORDS: 'checkPausedKeywords',
CHECK_PAUSED_SITELINKS: 'checkPausedSitelinks',
VALID_CODES: 'validCodes',
EMAIL_EACH_RUN: 'emailEachRun',
EMAIL_NON_ERRORS: 'emailNonErrors',
EMAIL_ON_COMPLETION: 'emailOnCompletion',
SAVE_ALL_URLS: 'saveAllUrls',
FREQUENCY: 'frequency',
DATE_STARTED: 'dateStarted',
DATE_COMPLETED: 'dateCompleted',
DATE_EMAILED: 'dateEmailed',
NUM_ERRORS: 'numErrors',
RESULT_HEADERS: 'resultHeaders',
ARCHIVE_HEADERS: 'archiveHeaders'
};
function main() {
var spreadsheet = validateAndGetSpreadsheet(CONFIG.SPREADSHEET_URL);
validateEmailAddresses(CONFIG.RECIPIENT_EMAILS);
spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
var options = loadOptions(spreadsheet);
var status = loadStatus(spreadsheet);
if (!status.dateStarted) {
// This is the very first execution of the script.
startNewAnalysis(spreadsheet);
} else if (status.dateStarted > status.dateCompleted) {
Logger.log('Resuming work from a previous execution.');
} else if (dayDifference(status.dateStarted, new Date()) <
options.frequency) {
Logger.log('Waiting until ' + options.frequency +
' days have elapsed since the start of the last analysis.');
return;
} else {
// Enough time has passed since the last analysis to start a new one.
removeLabels([CONFIG.LABEL]);
startNewAnalysis(spreadsheet);
}
var results = analyzeAccount(options);
outputResults(results, options);
}
/**
* Checks as many new URLs as possible that have not previously been checked,
* subject to quota and time limits.
*
* @param {Object} options Dictionary of options.
* @return {Object} An object with fields for the URLs checked and an indication
* if the analysis was completed (no remaining URLs to check).
*/
function analyzeAccount(options) {
// Ensure the label exists before attempting to retrieve already checked URLs.
ensureLabels([CONFIG.LABEL]);
var checkedUrls = getAlreadyCheckedUrls(options);
var urlChecks = [];
var didComplete = false;
try {
// If the script throws an exception, didComplete will remain false.
didComplete = checkUrls(checkedUrls, urlChecks, options);
} catch(e) {
if (e == EXCEPTIONS.QPS ||
e == EXCEPTIONS.LIMIT ||
e == EXCEPTIONS.TIMEOUT) {
Logger.log('Stopped checking URLs early because: ' + e);
Logger.log('Checked URLs will still be output.');
} else {
throw e;
}
}
return {
urlChecks: urlChecks,
didComplete: didComplete
};
}
/**
* Outputs the results to a spreadsheet and sends emails if appropriate.
*
* @param {Object} results An object with fields for the URLs checked and an
* indication if the analysis was completed (no remaining URLs to check).
* @param {Object} options Dictionary of options.
*/
function outputResults(results, options) {
var spreadsheet = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
var numErrors = countErrors(results.urlChecks, options);
Logger.log('Found ' + numErrors + ' this execution.');
saveUrlsToSpreadsheet(spreadsheet, results.urlChecks, options);
// Reload the status to get the total number of errors for the entire
// analysis, which is calculated by the spreadsheet.
status = loadStatus(spreadsheet);
if (results.didComplete) {
spreadsheet.getRangeByName(NAMES.DATE_COMPLETED).setValue(new Date());
Logger.log('Found ' + status.numErrors + ' across the entire analysis.');
}
if (CONFIG.RECIPIENT_EMAILS) {
if (!results.didComplete && options.emailEachRun &&
(options.emailNonErrors || numErrors > 0)) {
sendIntermediateEmail(spreadsheet, numErrors);
}
if (results.didComplete &&
(options.emailEachRun || options.emailOnCompletion) &&
(options.emailNonErrors || status.numErrors > 0)) {
sendFinalEmail(spreadsheet, status.numErrors);
}
}
}
/**
* Loads data from a spreadsheet based on named ranges. Strings 'Yes' and 'No'
* are converted to booleans. One-dimensional ranges are converted to arrays
* with blank cells omitted. Assumes each named range exists.
*
* @param {Object} spreadsheet The spreadsheet object.
* @param {Array.<string>} names A list of named ranges that should be loaded.
* @return {Object} A dictionary with the names as keys and the values
* as the cell values from the spreadsheet.
*/
function loadDatabyName(spreadsheet, names) {
var data = {};
for (var i = 0; i < names.length; i++) {
var name = names[i];
var range = spreadsheet.getRangeByName(name);
if (range.getNumRows() > 1 && range.getNumColumns() > 1) {
// Name refers to a 2d range, so load it as a 2d array.
data[name] = range.getValues();
} else if (range.getNumRows() == 1 && range.getNumColumns() == 1) {
// Name refers to a single cell, so load it as a value and replace
// Yes/No with boolean true/false.
data[name] = range.getValue();
data[name] = data[name] === 'Yes' ? true : data[name];
data[name] = data[name] === 'No' ? false : data[name];
} else {
// Name refers to a 1d range, so load it as an array (regardless of
// whether the 1d range is oriented horizontally or vertically).
var isByRow = range.getNumRows() > 1;
var limit = isByRow ? range.getNumRows() : range.getNumColumns();
var cellValues = range.getValues();
data[name] = [];
for (var j = 0; j < limit; j++) {
var cellValue = isByRow ? cellValues[j][0] : cellValues[0][j];
if (cellValue) {
data[name].push(cellValue);
}
}
}
}
return data;
}
/**
* Loads options from the spreadsheet.
*
* @param {Object} spreadsheet The spreadsheet object.
* @return {Object} A dictionary of options.
*/
function loadOptions(spreadsheet) {
return loadDatabyName(spreadsheet,
[NAMES.CHECK_AD_URLS, NAMES.CHECK_KEYWORD_URLS,
NAMES.CHECK_SITELINK_URLS, NAMES.CHECK_PAUSED_ADS,
NAMES.CHECK_PAUSED_KEYWORDS, NAMES.CHECK_PAUSED_SITELINKS,
NAMES.VALID_CODES, NAMES.EMAIL_EACH_RUN,
NAMES.EMAIL_NON_ERRORS, NAMES.EMAIL_ON_COMPLETION,
NAMES.SAVE_ALL_URLS, NAMES.FREQUENCY]);
}
/**
* Loads state information from the spreadsheet.
*
* @param {Object} spreadsheet The spreadsheet object.
* @return {Object} A dictionary of status information.
*/
function loadStatus(spreadsheet) {
return loadDatabyName(spreadsheet,
[NAMES.DATE_STARTED, NAMES.DATE_COMPLETED,
NAMES.DATE_EMAILED, NAMES.NUM_ERRORS]);
}
/**
* Saves the start date to the spreadsheet and archives results of the last
* analysis to a separate sheet.
*
* @param {Object} spreadsheet The spreadsheet object.
*/
function startNewAnalysis(spreadsheet) {
Logger.log('Starting a new analysis.');
spreadsheet.getRangeByName(NAMES.DATE_STARTED).setValue(new Date());
// Helper method to get the output area on the results or archive sheets.
var getOutputRange = function(rangeName) {
var headers = spreadsheet.getRangeByName(rangeName);
return headers.offset(1, 0, headers.getSheet().getDataRange().getLastRow());
};
getOutputRange(NAMES.ARCHIVE_HEADERS).clearContent();
var results = getOutputRange(NAMES.RESULT_HEADERS);
results.copyTo(getOutputRange(NAMES.ARCHIVE_HEADERS));
getOutputRange(NAMES.RESULT_HEADERS).clearContent();
}
/**
* Counts the number of errors in the results.
*
* @param {Array.<Object>} urlChecks A list of URL check results.
* @param {Object} options Dictionary of options.
* @return {number} The number of errors in the results.
*/
function countErrors(urlChecks, options) {
var numErrors = 0;
for (var i = 0; i < urlChecks.length; i++) {
if (options.validCodes.indexOf(urlChecks[i].responseCode) == -1) {
numErrors++;
}
}
return numErrors;
}
/**
* Saves URLs for a particular account to the spreadsheet starting at the first
* unused row.
*
* @param {Object} spreadsheet The spreadsheet object.
* @param {Array.<Object>} urlChecks A list of URL check results.
* @param {Object} options Dictionary of options.
*/
function saveUrlsToSpreadsheet(spreadsheet, urlChecks, options) {
// Build each row of output values in the order of the columns.
var outputValues = [];
for (var i = 0; i < urlChecks.length; i++) {
var urlCheck = urlChecks[i];
if (options.saveAllUrls ||
options.validCodes.indexOf(urlCheck.responseCode) == -1) {
outputValues.push([
urlCheck.customerId,
new Date(urlCheck.timestamp),
urlCheck.url,
urlCheck.responseCode,
urlCheck.entityType,
urlCheck.campaign,
urlCheck.adGroup,
urlCheck.ad,
urlCheck.keyword,
urlCheck.sitelink
]);
}
}
if (outputValues.length > 0) {
// Find the first open row on the Results tab below the headers and create a
// range large enough to hold all of the output, one per row.
var headers = spreadsheet.getRangeByName(NAMES.RESULT_HEADERS);
var lastRow = headers.getSheet().getDataRange().getLastRow();
var outputRange = headers.offset(lastRow - headers.getRow() + 1,
0, outputValues.length);
outputRange.setValues(outputValues);
}
for (var i = 0; i < CONFIG.RECIPIENT_EMAILS.length; i++) {
spreadsheet.addEditor(CONFIG.RECIPIENT_EMAILS[i]);
}
}
/**
* Sends an email to a list of email addresses with a link to the spreadsheet
* and the results of this execution of the script.
*
* @param {Object} spreadsheet The spreadsheet object.
* @param {boolean} numErrors The number of errors found in this execution.
*/
function sendIntermediateEmail(spreadsheet, numErrors) {
spreadsheet.getRangeByName(NAMES.DATE_EMAILED).setValue(new Date());
MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
'Link Checker Results',
'The Link Checker script found ' + numErrors + ' URLs with errors in ' +
'an execution that just finished. See ' +
spreadsheet.getUrl() + ' for details.');
}
/**
* Sends an email to a list of email addresses with a link to the spreadsheet
* and the results across the entire account.
*
* @param {Object} spreadsheet The spreadsheet object.
* @param {boolean} numErrors The number of errors found in the entire account.
*/
function sendFinalEmail(spreadsheet, numErrors) {
spreadsheet.getRangeByName(NAMES.DATE_EMAILED).setValue(new Date());
MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
'Link Checker Results',
'The Link Checker script found ' + numErrors + ' URLs with errors ' +
'across its entire analysis. See ' +
spreadsheet.getUrl() + ' for details.');
}
/**
* Retrieves all final URLs and mobile final URLs in the account across ads,
* keywords, and sitelinks that were checked in a previous run, as indicated by
* them having been labeled.
*
* @param {Object} options Dictionary of options.
* @return {Object} A map of previously checked URLs with the URL as the key.
*/
function getAlreadyCheckedUrls(options) {
var urlMap = {};
var addToMap = function(items) {
for (var i = 0; i < items.length; i++) {
var urls = expandUrlModifiers(items[i]);
urls.forEach(function(url) {
urlMap[url] = true;
});
}
};
if (options.checkAdUrls) {
addToMap(getUrlsBySelector(AdWordsApp.ads().
withCondition(labelCondition(true))));
}
if (options.checkKeywordUrls) {
addToMap(getUrlsBySelector(AdWordsApp.keywords().
withCondition(labelCondition(true))));
}
if (options.checkSitelinkUrls) {
addToMap(getAlreadyCheckedSitelinkUrls());
}
return urlMap;
}
/**
* Retrieves all final URLs and mobile final URLs for campaign and ad group
* sitelinks.
*
* @return {Array.<string>} An array of URLs.
*/
function getAlreadyCheckedSitelinkUrls() {
var urls = [];
// Helper method to get campaign or ad group sitelink URLs.
var addSitelinkUrls = function(selector) {
var iterator = selector.withCondition(labelCondition(true)).get();
while (iterator.hasNext()) {
var entity = iterator.next();
var sitelinks = entity.extensions().sitelinks();
urls = urls.concat(getUrlsBySelector(sitelinks));
}
};
addSitelinkUrls(AdWordsApp.campaigns());
addSitelinkUrls(AdWordsApp.adGroups());
return urls;
}
/**
* Retrieves all URLs in the entities specified by a selector.
*
* @param {Object} selector The selector specifying the entities to use.
* The entities should be of a type that has a urls() method.
* @return {Array.<string>} An array of URLs.
*/
function getUrlsBySelector(selector) {
var urls = [];
var entities = selector.get();
// Helper method to add the url to the list if it exists.
var addToList = function(url) {
if (url) {
urls.push(url);
}
};
while (entities.hasNext()) {
var entity = entities.next();
addToList(entity.urls().getFinalUrl());
addToList(entity.urls().getMobileFinalUrl());
}
return urls;
}
/**
* Retrieves all final URLs and mobile final URLs in the account across ads,
* keywords, and sitelinks, and checks their response code. Does not check
* previously checked URLs.
*
* @param {Object} checkedUrls A map of previously checked URLs with the URL as
* the key.
* @param {Array.<Object>} urlChecks An array into which the results of each URL
* check will be inserted.
* @param {Object} options Dictionary of options.
* @return {boolean} True if all URLs were checked.
*/
function checkUrls(checkedUrls, urlChecks, options) {
var didComplete = true;
// Helper method to add common conditions to ad group and keyword selectors.
var addConditions = function(selector, includePaused) {
var statuses = ['ENABLED'];
if (includePaused) {
statuses.push('PAUSED');
}
var predicate = ' IN [' + statuses.join(',') + ']';
return selector.withCondition(labelCondition(false)).
withCondition('Status' + predicate).
withCondition('CampaignStatus' + predicate).
withCondition('AdGroupStatus' + predicate);
};
if (options.checkAdUrls) {
didComplete = didComplete && checkUrlsBySelector(checkedUrls, urlChecks,
addConditions(AdWordsApp.ads().withCondition('CreativeFinalUrls != ""'),
options.checkPausedAds));
}
if (options.checkKeywordUrls) {
didComplete = didComplete && checkUrlsBySelector(checkedUrls, urlChecks,
addConditions(AdWordsApp.keywords().withCondition('FinalUrls != ""'),
options.checkPausedKeywords));
}
if (options.checkSitelinkUrls) {
didComplete = didComplete &&
checkSitelinkUrls(checkedUrls, urlChecks, options);
}
return didComplete;
}
/**
* Retrieves all final URLs and mobile final URLs in a selector and checks them
* for a valid response code. Does not check previously checked URLs. Labels the
* entity that it was checked, if possible.
*
* @param {Object} checkedUrls A map of previously checked URLs with the URL as
* the key.
* @param {Array.<Object>} urlChecks An array into which the results of each URL
* check will be inserted.
* @param {Object} selector The selector specifying the entities to use.
* The entities should be of a type that has a urls() method.
* @return {boolean} True if all URLs were checked.
*/
function checkUrlsBySelector(checkedUrls, urlChecks, selector) {
var customerId = AdWordsApp.currentAccount().getCustomerId();
var iterator = selector.get();
var entities = [];
// Helper method to check a URL.
var checkUrl = function(entity, url) {
if (!url) {
return;
}
var urlsToCheck = expandUrlModifiers(url);
for (var i = 0; i < urlsToCheck.length; i++) {
var expandedUrl = urlsToCheck[i];
if (checkedUrls[expandedUrl]) {
continue;
}
var responseCode = requestUrl(expandedUrl);
var entityType = entity.getEntityType();
urlChecks.push({
customerId: customerId,
timestamp: new Date(),
url: expandedUrl,
responseCode: responseCode,
entityType: entityType,
campaign: entity.getCampaign ? entity.getCampaign().getName() : '',
adGroup: entity.getAdGroup ? entity.getAdGroup().getName() : '',
ad: entityType == 'Ad' ? getAdAsText(entity) : '',
keyword: entityType == 'Keyword' ? entity.getText() : '',
sitelink: entityType.indexOf('Sitelink') != -1 ?
entity.getLinkText() : ''
});
checkedUrls[expandedUrl] = true;
}
};
while (iterator.hasNext()) {
entities.push(iterator.next());
}
for (var i = 0; i < entities.length; i++) {
var entity = entities[i];
checkUrl(entity, entity.urls().getFinalUrl());
checkUrl(entity, entity.urls().getMobileFinalUrl());
// Sitelinks do not have labels.
if (entity.applyLabel) {
entity.applyLabel(CONFIG.LABEL);
checkTimeout();
}
}
// True only if we did not breach an iterator limit.
return entities.length == iterator.totalNumEntities();
}
/**
* Retrieves a text representation of an ad, casting the ad to the appropriate
* type if necessary.
*
* @param {Ad} ad The ad object.
* @return {string} The text representation.
*/
function getAdAsText(ad) {
// There is no AdTypeSpace method for textAd
if (ad.getType() === 'TEXT_AD') {
return ad.getHeadline();
} else if (ad.isType().expandedTextAd()) {
var eta = ad.asType().expandedTextAd();
return eta.getHeadlinePart1() + ' - ' + eta.getHeadlinePart2();
} else if (ad.isType().gmailImageAd()) {
return ad.asType().gmailImageAd().getName();
} else if (ad.isType().gmailMultiProductAd()) {
return ad.asType().gmailMultiProductAd().getHeadline();
} else if (ad.isType().gmailSinglePromotionAd()) {
return ad.asType().gmailSinglePromotionAd().getHeadline();
} else if (ad.isType().html5Ad()) {
return ad.asType().html5Ad().getName();
} else if (ad.isType().imageAd()) {
return ad.asType().imageAd().getName();
} else if (ad.isType().responsiveDisplayAd()) {
return ad.asType().responsiveDisplayAd().getLongHeadline();
}
return 'N/A';
}
/**
* Retrieves all final URLs and mobile final URLs for campaign and ad group
* sitelinks and checks them for a valid response code. Does not check
* previously checked URLs. Labels the containing campaign or ad group that it
* has been checked.
*
* @param {Object} checkedUrls A map of previously checked URLs with the URL as
* the key.
* @param {Array.<Object>} urlChecks An array into which the results of each URL
* check will be inserted.
* @param {Object} options Dictionary of options.
* @return {boolean} True if all URLs were checked.
*/
function checkSitelinkUrls(checkedUrls, urlChecks, options) {
var didComplete = true;
// Helper method to check URLs for sitelinks in a campaign or ad group
// selector.
var checkSitelinkUrls = function(selector) {
var iterator = selector.withCondition(labelCondition(false)).get();
var entities = [];
while (iterator.hasNext()) {
entities.push(iterator.next());
}
for (var i = 0; i < entities.length; i++) {
var entity = entities[i];
var sitelinks = entity.extensions().sitelinks();
if (sitelinks.get().hasNext()) {
didComplete = didComplete &&
checkUrlsBySelector(checkedUrls, urlChecks, sitelinks);
entity.applyLabel(CONFIG.LABEL);
checkTimeout();
}
}
// True only if we did not breach an iterator limit.
didComplete = didComplete &&
entities.length == iterator.totalNumEntities();
};
var statuses = ['ENABLED'];
if (options.checkPausedSitelinks) {
statuses.push('PAUSED');
}
var predicate = ' IN [' + statuses.join(',') + ']';
checkSitelinkUrls(AdWordsApp.campaigns().
withCondition('Status' + predicate));
checkSitelinkUrls(AdWordsApp.adGroups().
withCondition('Status' + predicate).
withCondition('CampaignStatus' + predicate));
return didComplete;
}
/**
* Expands a URL that contains ValueTrack parameters such as {ifmobile:mobile}
* to all the combinations, and returns as an array. The following pairs of
* ValueTrack parameters are currently expanded:
* 1. {ifmobile:<...>} and {ifnotmobile:<...>} to produce URLs simulating
* clicks from either mobile or non-mobile devices.
* 2. {ifsearch:<...>} and {ifcontent:<...>} to produce URLs simulating
* clicks on either the search or display networks.
* Any other ValueTrack parameters or customer parameters are stripped out from
* the URL entirely.
*
* @param {string} url The URL which may contain ValueTrack parameters.
* @return {!Array.<string>} An array of one or more expanded URLs.
*/
function expandUrlModifiers(url) {
var ifRegex = /({(if\w+):([^}]+)})/gi;
var modifiers = {};
var matches;
while (matches = ifRegex.exec(url)) {
// Tags are case-insensitive, e.g. IfMobile is valid.
modifiers[matches[2].toLowerCase()] = {
substitute: matches[0],
replacement: matches[3]
};
}
if (Object.keys(modifiers).length) {
if (modifiers.ifmobile || modifiers.ifnotmobile) {
var mobileCombinations =
pairedUrlModifierReplace(modifiers, 'ifmobile', 'ifnotmobile', url);
} else {
var mobileCombinations = [url];
}
// Store in a map on the offchance that there are duplicates.
var combinations = {};
mobileCombinations.forEach(function(url) {
if (modifiers.ifsearch || modifiers.ifcontent) {
pairedUrlModifierReplace(modifiers, 'ifsearch', 'ifcontent', url)
.forEach(function(modifiedUrl) {
combinations[modifiedUrl] = true;
});
} else {
combinations[url] = true;
}
});
var modifiedUrls = Object.keys(combinations);
} else {
var modifiedUrls = [url];
}
// Remove any custom parameters
return modifiedUrls.map(function(url) {
return url.replace(/{[0-9a-zA-Z\_\+\:]+}/g, '');
});
}
/**
* Return a pair of URLs, where each of the two modifiers is mutually exclusive,
* one for each combination. e.g. Evaluating ifmobile and ifnotmobile for a
* mobile and a non-mobile scenario.
*
* @param {Object} modifiers A map of ValueTrack modifiers.
* @param {string} modifier1 The modifier to honour in the URL.
* @param {string} modifier2 The modifier to remove from the URL.
* @param {string} url The URL potentially containing ValueTrack parameters.
* @return {Array.<string>} A pair of URLs, as a list.
*/
function pairedUrlModifierReplace(modifiers, modifier1, modifier2, url) {
return [
urlModifierReplace(modifiers, modifier1, modifier2, url),
urlModifierReplace(modifiers, modifier2, modifier1, url)
];
}
/**
* Produces a URL where the first {if...} modifier is set, and the second is
* deleted.
*
* @param {Object} mods A map of ValueTrack modifiers.
* @param {string} mod1 The modifier to honour in the URL.
* @param {string} mod2 The modifier to remove from the URL.
* @param {string} url The URL potentially containing ValueTrack parameters.
* @return {string} The resulting URL with substitions.
*/
function urlModifierReplace(mods, mod1, mod2, url) {
var modUrl = mods[mod1] ?
url.replace(mods[mod1].substitute, mods[mod1].replacement) :
url;
return mods[mod2] ? modUrl.replace(mods[mod2].substitute, '') : modUrl;
}
/**
* Requests a given URL. Retries if the UrlFetchApp QPS limit was reached,
* exponentially backing off on each retry. Throws an exception if it reaches
* the maximum number of retries. Throws an exception if the UrlFetchApp daily
* quota limit was reached.
*
* @param {string} url The URL to test.
* @return {number|string} The response code received when requesting the URL,
* or an error message.
*/
function requestUrl(url) {
var responseCode;
var sleepTime = QUOTA_CONFIG.INIT_SLEEP_TIME;
var numTries = 0;
while (numTries < QUOTA_CONFIG.MAX_TRIES && !responseCode) {
try {
// If UrlFetchApp.fetch() throws an exception, responseCode will remain
// undefined.
responseCode =
UrlFetchApp.fetch(url, {muteHttpExceptions: true}).getResponseCode();
if (CONFIG.THROTTLE > 0) {
Utilities.sleep(CONFIG.THROTTLE);
}
} catch(e) {
if (e.message.indexOf('Service invoked too many times in a short time:')
!= -1) {
Utilities.sleep(sleepTime);
sleepTime *= QUOTA_CONFIG.BACKOFF_FACTOR;
} else if (e.message.indexOf('Service invoked too many times:') != -1) {
throw EXCEPTIONS.LIMIT;
} else {
return e.message;
}
}
numTries++;
}
if (!responseCode) {
throw EXCEPTIONS.QPS;
} else {
return responseCode;
}
}
/**
* Throws an exception if the script is close to timing out.
*/
function checkTimeout() {
if (AdWordsApp.getExecutionInfo().getRemainingTime() <
CONFIG.TIMEOUT_BUFFER) {
throw EXCEPTIONS.TIMEOUT;
}
}
/**
* Returns the number of days between two dates.
*
* @param {Object} from The older Date object.
* @param {Object} to The newer (more recent) Date object.
* @return {number} The number of days between the given dates (possibly
* fractional).
*/
function dayDifference(from, to) {
return (to.getTime() - from.getTime()) / (24 * 3600 * 1000);
}
/**
* Builds a string to be used for withCondition() filtering for whether the
* label is present or not.
*
* @param {boolean} hasLabel True if the label should be present, false if the
* label should not be present.
* @return {string} A condition that can be used in withCondition().
*/
function labelCondition(hasLabel) {
return 'LabelNames ' + (hasLabel ? 'CONTAINS_ANY' : 'CONTAINS_NONE') +
' ["' + CONFIG.LABEL + '"]';
}
/**
* Retrieves an entity by name.
*
* @param {Object} selector A selector for an entity type with a Name field.
* @param {string} name The name to retrieve the entity by.
* @return {Object} The entity, if it exists, or null otherwise.
*/
function getEntityByName(selector, name) {
var entities = selector.withCondition('Name = "' + name + '"').get();
if (entities.hasNext()) {
return entities.next();
} else {
return null;
}
}
/**
* Retrieves a Label object by name.
*
* @param {string} labelName The label name to retrieve.
* @return {Object} The Label object, if it exists, or null otherwise.
*/
function getLabel(labelName) {
return getEntityByName(AdWordsApp.labels(), labelName);
}
/**
* Checks that the account has all provided labels and creates any that are
* missing. Since labels cannot be created in preview mode, throws an exception
* if a label is missing.
*
* @param {Array.<string>} labelNames An array of label names.
*/
function ensureLabels(labelNames) {
for (var i = 0; i < labelNames.length; i++) {
var labelName = labelNames[i];
var label = getLabel(labelName);
if (!label) {
if (!AdWordsApp.getExecutionInfo().isPreview()) {
AdWordsApp.createLabel(labelName);
} else {
throw 'Label ' + labelName + ' is missing and cannot be created in ' +
'preview mode. Please run the script or create the label manually.';
}
}
}
}
/**
* Removes all provided labels from the account. Since labels cannot be removed
* in preview mode, throws an exception in preview mode.
*
* @param {Array.<string>} labelNames An array of label names.
*/
function removeLabels(labelNames) {
if (AdWordsApp.getExecutionInfo().isPreview()) {
throw 'Cannot remove labels in preview mode. Please run the script or ' +
'remove the labels manually.';
}
for (var i = 0; i < labelNames.length; i++) {
var label = getLabel(labelNames[i]);
if (label) {
label.remove();
}
}
}
/**
* 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 addresses to make sure it's not the default.
* Throws a descriptive error message if validation fails.
*
* @param {Array.<string>} recipientEmails The list of email adresses.
* @throws {Error} If the list of email addresses is still the default
*/
function validateEmailAddresses(recipientEmails) {
if (recipientEmails &&
recipientEmails[0] == 'YOUR_EMAIL_HERE') {
throw new Error('Please either specify a valid email address or clear' +
' the RECIPIENT_EMAILS field.');
}
}
Негативные слова являются минус-фразами, которые придумывают, чтобы произвести защиту кампании от нерелевантных поисковых запросов. Подобные слова могут по ошибке блокировать и релевантные запросы. А это снижает эффективность рекламы. Скрипт способен обнаруживать такие конфликты и сохранять данные в таблицу.
Скрипт:
// 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 an AdWords account has positive keywords which are
* blocked by negative keywords. See
* https://developers.google.com/adwords/scripts/docs/solutions/negative-keyword-conflicts
* for more details.
*
* @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 1.3.3
*
* @changelog
* - 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.
*/
var CONFIG = {
// URL of the spreadsheet template.
// This should be a copy of https://goo.gl/yNwOIV.
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.
*/
var REPORTING_OPTIONS = {
// Comment out the following line to default to the latest reporting version.
apiVersion: 'v201705'
};
function main() {
var spreadsheet = validateAndGetSpreadsheet(CONFIG.SPREADSHEET_URL);
validateEmailAddresses();
var conflicts = findAllConflicts();
if (CONFIG.COPY_SPREADSHEET) {
spreadsheet = spreadsheet.copy('Negative Keyword Conflicts');
}
initializeSpreadsheet(spreadsheet);
var hasConflicts = outputConflicts(spreadsheet,
AdWordsApp.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() {
var campaignIds;
if (CONFIG.CAMPAIGN_LABEL) {
campaignIds = getCampaignIdsWithLabel(CONFIG.CAMPAIGN_LABEL);
} else {
campaignIds = getAllCampaignIds();
}
var campaignCondition = '';
if (campaignIds.length > 0) {
campaignCondition = 'AND CampaignId IN [' + campaignIds.join(',') + ']';
}
Logger.log('Downloading keywords performance report');
var query =
'SELECT CampaignId, CampaignName, AdGroupId, AdGroupName, ' +
' Criteria, KeywordMatchType, IsNegative ' +
'FROM KEYWORDS_PERFORMANCE_REPORT ' +
'WHERE CampaignStatus = "ENABLED" AND AdGroupStatus = "ENABLED" AND ' +
' Status = "ENABLED" AND IsNegative IN [true, false] ' +
' ' + campaignCondition + ' ' +
'DURING YESTERDAY';
var report = AdWordsApp.report(query, REPORTING_OPTIONS);
Logger.log('Building cache and populating with keywords');
var cache = {};
var numPositives = 0;
var numNegatives = 0;
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignId = row['CampaignId'];
var campaignName = row['CampaignName'];
var adGroupId = row['AdGroupId'];
var adGroupName = row['AdGroupName'];
var keywordText = row['Criteria'];
var keywordMatchType = row['KeywordMatchType'];
var isNegative = row['IsNegative'];
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 == 'true') {
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.';
}
}
Logger.log('Downloading campaign negatives report');
var query =
'SELECT CampaignId, Criteria, KeywordMatchType ' +
'FROM CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT ' +
'WHERE CampaignStatus = "ENABLED" ' +
' ' + campaignCondition;
var report = AdWordsApp.report(query, REPORTING_OPTIONS);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignId = row['CampaignId'];
var keywordText = row['Criteria'];
var keywordMatchType = row['KeywordMatchType'];
if (cache[campaignId]) {
cache[campaignId].negatives
.push(normalizeKeyword(keywordText, keywordMatchType));
}
}
Logger.log('Populating cache with negative keyword lists');
var negativeKeywordLists =
AdWordsApp.negativeKeywordLists().withCondition('Status = ACTIVE').get();
while (negativeKeywordLists.hasNext()) {
var negativeKeywordList = negativeKeywordLists.next();
var negativeList = {name: negativeKeywordList.getName(), negatives: []};
var negativeKeywords = negativeKeywordList.negativeKeywords().get();
while (negativeKeywords.hasNext()) {
var negative = negativeKeywords.next();
negativeList.negatives.push(
normalizeKeyword(negative.getText(), negative.getMatchType()));
}
var campaigns = negativeKeywordList.campaigns()
.withCondition('Status = ENABLED').get();
while (campaigns.hasNext()) {
var campaign = campaigns.next();
var campaignId = campaign.getId();
if (cache[campaignId]) {
cache[campaignId].negativesFromLists =
cache[campaignId].negativesFromLists.concat(negativeList);
}
}
}
Logger.log('Finding negative conflicts');
var conflicts = [];
// Adds context about the conflict.
var 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 (var campaignId in cache) {
for (var adGroupId in cache[campaignId].adGroups) {
var positives = cache[campaignId].adGroups[adGroupId].positives;
var negativeLevels = {
'Campaign': cache[campaignId].negatives,
'Ad Group': cache[campaignId].adGroups[adGroupId].negatives
};
for (var level in negativeLevels) {
var newConflicts =
checkForConflicts(negativeLevels[level], positives);
for (var i = 0; i < newConflicts.length; i++) {
enrichConflict(newConflicts[i], campaignId, adGroupId, level);
}
conflicts = conflicts.concat(newConflicts);
}
var negativeLists = cache[campaignId].negativesFromLists;
var level = 'Negative list';
for (var k = 0; k < negativeLists.length; k++) {
var negativeList = negativeLists[k];
var newConflicts = checkForConflicts(negativeList.negatives, positives);
for (var j = 0; j < newConflicts.length; j++) {
enrichConflict(
newConflicts[j], 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);
Logger.log('Conflicts were found for ' + customerId +
'. See ' + spreadsheet.getUrl());
return true;
} else {
Logger.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(AdWordsApp.currentAccount().getTimeZone());
// Clear the last run date on the spreadsheet.
spreadsheet.getRangeByName('RunDate').clearContent();
// Clear all rows in the spreadsheet below the header row.
var outputRange = 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.
var lastRow = spreadsheet.getSheetByName('Conflicts')
.getDataRange().getLastRow();
var headers = spreadsheet.getRangeByName('Headers');
var outputRange = headers
.offset(lastRow - headers.getRow() + 1, 0, conflicts.length);
// Build each row of output values in the order of the columns.
var outputValues = [];
for (var i = 0; i < conflicts.length; i++) {
var conflict = conflicts[i];
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 (var i = 0; i < CONFIG.RECIPIENT_EMAILS.length; i++) {
spreadsheet.addEditor(CONFIG.RECIPIENT_EMAILS[i]);
}
}
/**
* 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 ' +
'AdWords account(s). See ' +
spreadsheet.getUrl() + ' for details. You may wish ' +
'to delete the negative keywords causing the ' +
'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) {
var campaignIds = [];
while (campaigns.hasNext()) {
campaignIds.push(campaigns.next().getId());
}
return campaignIds;
}
/**
* Retrieves all campaign IDs in an account.
*
* @return {Array.<Integer>} An array of campaign IDs.
*/
function getAllCampaignIds() {
return getCampaignIds(AdWordsApp.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) {
var labels = AdWordsApp.labels()
.withCondition('Name = "' + labelText + '"')
.get();
if (!labels.hasNext()) {
return null;
}
var 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) {
var conflicts = [];
for (var i = 0; i < negatives.length; i++) {
var negative = negatives[i];
var anyBlock = false;
var blockedPositives = [];
for (var j = 0; j < positives.length; j++) {
var positive = positives[j];
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) {
var display;
var 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) {
var keywordTokens1 = keywordText1.split(' ');
var keywordTokens2 = keywordText2.split(' ');
for (var i = 0; i < keywordTokens1.length; i++) {
if (keywordTokens2.indexOf(keywordTokens1[i]) == -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) {
var 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.');
}
}
Данный скрипт производит группировку ключевых слов согласно заданному признаку. Так, он способен выбрать все фразы, которые включают названия брендов либо как-то связаны с конкурентами. Можно задать одновременно несколько признаков.
Необходимый код для работы:
// 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 Keyword Labeler
*
* @overview The Keyword Labeler script labels keywords based on rules that
* you define. For example, you can create a rule to label keywords that
* are underperforming. Later, you can filter for this label in AdWords
* to decide whether to pause or remove those keywords. Rules don't have
* to be based solely on a keyword's performance. They can also be based
* on properties of a keyword such as its text or match type. For example,
* you could define "branded" keywords as those containing proper nouns
* associated with your brand, then label those keywords based on
* different performance thresholds versus "non-branded" keywords.
* Finally, the script sends an email linking to a spreadsheet when new
* keywords have been labeled. See
* https://developers.google.com/adwords/scripts/docs/solutions/labels
* for more details.
*
* @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 1.1.2
*
* @changelog
* - version 1.1.2
* - Added validation for external spreadsheet setup.
* - version 1.1.1
* - Improvements to time zone handling.
* - version 1.1
* - Modified to allow generic rules and labeling.
* - version 1.0
* - Released initial version.
*/
var CONFIG = {
// URL of the spreadsheet template.
// This should be a copy of https://goo.gl/uhK6nS.
SPREADSHEET_URL: 'YOUR_SPREADSHEET_URL',
// Array of addresses to be alerted via email if labels are applied.
RECIPIENT_EMAILS: [
'YOUR_EMAIL_HERE'
],
// Selector conditions to apply for all rules.
GLOBAL_CONDITIONS: [
'CampaignStatus = ENABLED',
'AdGroupStatus = ENABLED',
'Status = ENABLED'
],
// Default date range over which statistics fields are retrieved.
// Used when fetching keywords if a rule doesn't specify a date range.
DEFAULT_DATE_RANGE: 'LAST_7_DAYS'
};
/**
* Defines the rules by which keywords will be labeled.
* The labelName field is required. Other fields may be null.
* @type {Array.<{
* conditions: Array.<string>,
* dateRange: string,
* filter: function(Object): boolean,
* labelName: string,
* }>
* }
*/
var RULES = [
{
conditions: [
'Ctr < 0.02',
'AverageCpc > 1',
],
filter: function(keyword) {
var brands = ['Product A', 'Product B', 'Product C'];
var text = keyword.getText();
for (var i = 0; i < brands.length; i++) {
if (text.indexOf(brand[i]) >= 0) {
return true;
}
}
return false;
},
labelName: 'Underperforming Branded'
},
{
conditions: [
'Ctr < 0.01',
'AverageCpc > 2',
],
labelName: 'Underperforming'
}
];
function main() {
validateEmailAddresses();
var results = processAccount();
processResults(results);
}
/**
* Processes the rules on the current account.
*
* @return {Array.<Object>} An array of changes made, each having
* a customerId, campaign name, ad group name, label name,
* and keyword text that the label was applied to.
*/
function processAccount() {
ensureAccountLabels();
var changes = applyLabels();
return changes;
}
/**
* Processes the results of the script.
*
* @param {Array.<Object>} changes An array of changes made, each having
* a customerId, campaign name, ad group name, label name,
* and keyword text that the label was applied to.
*/
function processResults(changes) {
if (changes.length > 0) {
var spreadsheetUrl = saveToSpreadsheet(changes, CONFIG.RECIPIENT_EMAILS);
sendEmail(spreadsheetUrl, CONFIG.RECIPIENT_EMAILS);
} else {
Logger.log('No labels were applied.');
}
}
/**
* Retrieves the names of all labels in the account.
*
* @return {Array.<string>} An array of label names.
*/
function getAccountLabelNames() {
var labelNames = [];
var iterator = AdWordsApp.labels().get();
while (iterator.hasNext()) {
labelNames.push(iterator.next().getName());
}
return labelNames;
}
/**
* Checks that the account has a label for each rule and
* creates the rule's label if it does not already exist.
* Throws an exception if a rule does not have a labelName.
*/
function ensureAccountLabels() {
var labelNames = getAccountLabelNames();
for (var i = 0; i < RULES.length; i++) {
var labelName = RULES[i].labelName;
if (!labelName) {
throw 'Missing labelName for rule #' + i;
}
if (labelNames.indexOf(labelName) == -1) {
AdWordsApp.createLabel(labelName);
labelNames.push(labelName);
}
}
}
/**
* Retrieves the keywords in an account satisfying a rule
* and that do not already have the rule's label.
*
* @param {Object} rule An element of the RULES array.
* @return {Array.<Object>} An array of keywords.
*/
function getKeywordsForRule(rule) {
var selector = AdWordsApp.keywords();
// Add global conditions.
for (var i = 0; i < CONFIG.GLOBAL_CONDITIONS.length; i++) {
selector = selector.withCondition(CONFIG.GLOBAL_CONDITIONS[i]);
}
// Add selector conditions for this rule.
if (rule.conditions) {
for (var i = 0; i < rule.conditions.length; i++) {
selector = selector.withCondition(rule.conditions[i]);
}
}
// Exclude keywords that already have the label.
selector.withCondition('LabelNames CONTAINS_NONE ["' + rule.labelName + '"]');
// Add a date range.
selector = selector.forDateRange(rule.dateRange || CONFIG.DEFAULT_DATE_RANGE);
// Get the keywords.
var iterator = selector.get();
var keywords = [];
// Check filter conditions for this rule.
while (iterator.hasNext()) {
var keyword = iterator.next();
if (!rule.filter || rule.filter(keyword)) {
keywords.push(keyword);
}
}
return keywords;
}
/**
* For each rule, determines the keywords matching the rule and which
* need to have a label newly applied, and applies it.
*
* @return {Array.<Object>} An array of changes made, each having
* a customerId, campaign name, ad group name, label name,
* and keyword text that the label was applied to.
*/
function applyLabels() {
var changes = [];
var customerId = AdWordsApp.currentAccount().getCustomerId();
for (var i = 0; i < RULES.length; i++) {
var rule = RULES[i];
var keywords = getKeywordsForRule(rule);
var labelName = rule.labelName;
for (var j = 0; j < keywords.length; j++) {
var keyword = keywords[j];
keyword.applyLabel(labelName);
changes.push({
customerId: customerId,
campaignName: keyword.getCampaign().getName(),
adGroupName: keyword.getAdGroup().getName(),
labelName: labelName,
keywordText: keyword.getText(),
});
}
}
return changes;
}
/**
* Outputs a list of applied labels to a new spreadsheet and gives editor access
* to a list of provided emails.
*
* @param {Array.<Object>} changes An array of changes made, each having
* a customerId, campaign name, ad group name, label name,
* and keyword text that the label was applied to.
* @param {Array.<Object>} emails An array of email addresses.
* @return {string} The URL of the spreadsheet.
*/
function saveToSpreadsheet(changes, emails) {
var template = validateAndGetSpreadsheet(CONFIG.SPREADSHEET_URL);
var spreadsheet = template.copy('Keyword Labels Applied');
// Make sure the spreadsheet is using the account's timezone.
spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
Logger.log('Saving changes to spreadsheet at ' + spreadsheet.getUrl());
var headers = spreadsheet.getRangeByName('Headers');
var outputRange = headers.offset(1, 0, changes.length);
var outputValues = [];
for (var i = 0; i < changes.length; i++) {
var change = changes[i];
outputValues.push([
change.customerId,
change.campaignName,
change.adGroupName,
change.keywordText,
change.labelName
]);
}
outputRange.setValues(outputValues);
spreadsheet.getRangeByName('RunDate').setValue(new Date());
for (var i = 0; i < emails.length; i++) {
spreadsheet.addEditor(emails[i]);
}
return spreadsheet.getUrl();
}
/**
* Sends an email to a list of email addresses with a link to a spreadsheet.
*
* @param {string} spreadsheetUrl The URL of the spreadsheet.
* @param {Array.<Object>} emails An array of email addresses.
*/
function sendEmail(spreadsheetUrl, emails) {
MailApp.sendEmail(emails.join(','), 'Keywords Newly Labeled',
'Keywords have been newly labeled in your' +
'AdWords account(s). See ' +
spreadsheetUrl + ' for details.');
}
/**
* DO NOT EDIT ANYTHING BELOW THIS LINE.
* Please modify your spreadsheet URL and email addresses 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 == '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.');
}
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
return spreadsheet;
}
/**
* 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 specify a valid email address.');
}
}
Данный скрипт способен работать с более сложной операцией, нежели остальные. Он осуществляет проверку наличия отклонений среди ключевых слов. Если они есть, им будут присвоены ярлыки.
Аномалиями называют ключевики либо группы ключевиков, которые работают по- другому, чем остальные.
Стоит тщательно отслеживать актуальность скриптов, чтобы не происходило ошибок.
Скрипт:
/**************************************
* Find the Anomalies
* Created By: Russ Savage
* Version: 1.2
* Changelog v1.2
* - Fixed divide by 0 errors
* - Changed SIG_FIGS to DECIMAL_PLACES
* Changelog v1.1
* - Added ability to tag ad anomalies as well
* FreeAdWordsScripts.com
**************************************/
var DATE_RANGE = 'LAST_30_DAYS';
var DECIMAL_PLACES = 3;
var STANDARD_DEVIATIONS = 2;
var TO = ['you@your_domain.com'];
function main() {
// This will add labels to and send emails about adgroups, keywords and ads. Remove any if you like.
var levels_to_tag = ['adgroup','keyword','ad'];
for(var x in levels_to_tag) {
var report = getContentRows(levels_to_tag[x]);
var entity_map = buildEntityMap(levels_to_tag[x]);
for(var parent_id in entity_map) {
var child_list = entity_map[parent_id];
var stats_list = Object.keys(child_list[0].stats);
for(var i in stats_list) {
var mean = getMean(child_list,stats_list[i]);
var stand_dev = getStandardDev(child_list,mean,stats_list[i]);
var label_name = stats_list[i]+"_anomaly";
report += addLabelToAnomalies(child_list,mean,stand_dev,stats_list[i],label_name,levels_to_tag[x]);
}
}
sendResultsViaEmail(report,levels_to_tag[x]);
}
}
//Takes a report and the level of reporting and sends and email
//with the report as an attachment.
function sendResultsViaEmail(report,level) {
var rows = report.match(/\n/g).length - 1;
if(rows == 0) { return; }
var options = { attachments: [Utilities.newBlob(report, 'text/csv', level+"_anomalies_"+_getDateString()+'.csv')] };
var email_body = "There are " + rows + " " + level + "s that have abnormal performance. See attachment for details.";
var subject = 'Abnormal ' + _initCap(level) + ' Entities Report - ' + _getDateString();
for(var i in TO) {
MailApp.sendEmail(TO[i], subject, email_body, options);
}
}
//Helper function to return a single row of the report formatted correctly
function toReportRow(entity,level,label_name) {
var ret_val = [AdWordsApp.currentAccount().getCustomerId(),
entity.getCampaign().getName()];
ret_val.push( (level == 'adgroup') ? entity.getName() : entity.getAdGroup().getName() );
if(level == 'keyword') {
ret_val = ret_val.concat([entity.getText(),entity.getMatchType()]);
} else if(level == 'ad') {
ret_val = ret_val.concat([entity.getHeadline(),entity.getDescription1(),entity.getDescription2(),entity.getDisplayUrl()]);
}
ret_val.push(label_name);
return '"' + ret_val.join('","') + '"\n';
}
//Helper function to return the column headings for the report
function getContentRows(level) {
var ret_val = ['AccountId','CampaignName','AdGroupName'];
if(level == 'keyword') {
ret_val = ret_val.concat(['KeywordText','MatchType']);
} else if(level == 'ad') {
ret_val = ret_val.concat(['Headline','Description1','Description2','DisplayUrl']);
}
ret_val.push('LabelName');
return '"' + ret_val.join('","') + '"\n';
}
//Function to add the labels to the entities based on the standard deviation and mean.
//It returns a csv formatted string for reporting
function addLabelToAnomalies(entites,mean,sd,stat_key,label_name,level) {
createLabelIfNeeded(label_name);
var report = '';
for(var i in entites) {
var entity = entites[i]['entity'];
var deviation = Math.abs(entites[i]['stats'][stat_key] - mean);
if(sd != 0 && deviation/sd >= STANDARD_DEVIATIONS) {
entity.applyLabel(label_name);
report += toReportRow(entity,level,label_name);
} else {
entity.removeLabel(label_name);
}
}
return report;
}
//This is a helper function to create the label if it does not already exist
function createLabelIfNeeded(name) {
if(!AdWordsApp.labels().withCondition("Name = '"+name+"'").get().hasNext()) {
AdWordsApp.createLabel(name);
}
}
//This function returns the standard deviation for a set of entities
//The stat key determines which stat to calculate it for
function getStandardDev(entites,mean,stat_key) {
var total = 0;
for(var i in entites) {
total += Math.pow(entites[i]['stats'][stat_key] - mean,2);
}
if(Math.sqrt(entites.length-1) == 0) {
return 0;
}
return round(Math.sqrt(total)/Math.sqrt(entites.length-1));
}
//Returns the mean (average) for the set of entities
//Again, stat key determines which stat to calculate this for
function getMean(entites,stat_key) {
var total = 0;
for(var i in entites) {
total += entites[i]['stats'][stat_key];
}
if(entites.length == 0) {
return 0;
}
return round(total/entites.length);
}
//This function returns a map of the entities that I am processing.
//The format for the map can be found on the first line.
//It is meant to work on AdGroups and Keywords
function buildEntityMap(entity_type) {
var map = {}; // { parent_id : [ { entity : entity, stats : entity_stats } ], ... }
var iter = getIterator(entity_type);
while(iter.hasNext()) {
var entity = iter.next();
var stats = entity.getStatsFor(DATE_RANGE);
var stats_map = getStatsMap(stats);
var parent_id = getParentId(entity_type,entity);
if(map[parent_id]) {
map[parent_id].push({entity : entity, stats : stats_map});
} else {
map[parent_id] = [{entity : entity, stats : stats_map}];
}
}
return map;
}
//Given an entity type (adgroup or keyword) this will return the parent id
function getParentId(entity_type,entity) {
switch(entity_type) {
case 'adgroup' :
return entity.getCampaign().getId();
case 'keyword':
return entity.getAdGroup().getId();
case 'ad':
return entity.getAdGroup().getId();
}
}
//Given an entity type this will return the iterator for that.
function getIterator(entity_type) {
switch(entity_type) {
case 'adgroup' :
return AdWordsApp.adGroups().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
case 'keyword' :
return AdWordsApp.keywords().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
case 'ad' :
return AdWordsApp.ads().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
}
}
//This returns a map of all the stats for a given entity.
//You can comment out the things you don't really care about.
function getStatsMap(stats) {
return { // You can comment these out as needed.
avg_cpc : stats.getAverageCpc(),
avg_cpm : stats.getAverageCpm(),
avg_pv : stats.getAveragePageviews(),
avg_pos : stats.getAveragePosition(),
avg_tos : stats.getAverageTimeOnSite(),
bounce : stats.getBounceRate(),
clicks : stats.getClicks(),
cv : stats.getConversionRate(),
conv : stats.getConversions(),
cost : stats.getCost(),
ctr : stats.getCtr(),
imps : stats.getImpressions()
};
}
//Helper function to format todays date
function _getDateString() {
return Utilities.formatDate((new Date()), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
}
//Helper function to capitalize the first letter of a string.
function _initCap(str) {
return str.replace(/(?:^|\s)\S/g, function(a) { return a.toUpperCase(); });
}
// A helper function to make rounding a little easier
function round(value) {
var decimals = Math.pow(10,DECIMAL_PLACES);
return Math.round(value*decimals)/decimals;
}
