Марат-блог
Услуги по продвижению и разработке сайта
Отправить заявку
Заказать обратный звонок

Спасибо, Ваша заявка принята.

В ближайшее время менеджер свяжется с Вами.

Главная » Контекстная реклама » 5 скриптов AdWords, упрощающих жизнь
5 скриптов AdWords, упрощающих жизнь
Контекстная реклама
465
05 апреля 2018

5 скриптов AdWords, упрощающих жизнь

Сегодня мы поговорим о пяти бесплатных скриптах Адвордс, что предназначены для отслеживания показателей качестве, выявления битых ссылок, группировки и конфликта ключевых слов.

Скрипт отслеживания показателя качества Quality Score Tracker

Показатели качества оказывают влияние на многих вещи – как на позицию рекламного объявления, так и на цену клика.

Данный инструмент поможет сохранить информацию, что касается показателей качества аккаунта, кампании и ключевых фраз, которые входят в группу первых 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;

}

Скрипт битых ссылок Brocken Links Script

Когда происходит удаление старых страниц, нередко появляется ошибка 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.');
  }
}

Скрипт выявления конфликта ключевиков Keyword Conflict Detection

Негативные слова являются минус-фразами, которые придумывают, чтобы произвести защиту кампании от нерелевантных поисковых запросов. Подобные слова могут по ошибке блокировать и релевантные запросы. А это снижает эффективность рекламы. Скрипт способен обнаруживать такие конфликты и сохранять данные в таблицу.

Скрипт:

// 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.');
  }
}

 

Скрипт группировки ключевиков Keyword Labeler

Данный скрипт производит группировку ключевых слов согласно заданному признаку. Так, он способен выбрать все фразы, которые включают названия брендов либо как-то связаны с конкурентами. Можно задать одновременно несколько признаков.

Необходимый код для работы:

// 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.');
  }
}

 

Скрипт выявления аномалий в аккаунте Performance Anomalies

Данный скрипт способен работать с более сложной операцией, нежели остальные. Он осуществляет проверку наличия отклонений среди ключевых слов. Если они есть, им будут присвоены ярлыки.

Аномалиями называют ключевики либо группы ключевиков, которые работают по- другому, чем остальные.

Стоит тщательно отслеживать актуальность скриптов, чтобы не происходило ошибок.

Скрипт:

/**************************************

* 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;

}