http://remkovanderzwaag.nl/blog/adwords-script-auto-optimalisatie-zoektermen

search_query_optimizer.js

//------------------------------------------------
// Auto-optimize Search Terms
// Created by: Remko van der Zwaag & pdds
// remkovanderzwaag.nl & pdds.nl
// More info (Dutch): http://goo.gl/f1Iioa
// Based on a Google example script: http://goo.gl/aunUKV
// 05-04-2016: Big update!
//             - Hybrid (MCC & Account level)
//             - Settings by Spreadsheet
//             - Can filter both on Search Query 
//               _and_ keyword performance
//             - Far more configuration settings
//             - Reporting function (email)
//------------------------------------------------

var spreadsheetId = '15jZRRSql_BbdnnvVLrIhZKC14Tb51dJdlhO57UkfQPI';

// Email address to receive the report (leave empty for no report)
var emailAddr = '[email protected]'; 
// Subject of the report email
var emailSubject = 'SQO results';
// Always send an email, even if the script made no changes.
var alwaysReport = true;

// Description for tag label
var LABEL_DESC = {description: 'Label created by script: Search Query Optimizer', color: '#FF0000'};

// Minimum number of impressions to consider "enough data" (default)
var IMPRESSIONS_THRESHOLD = 50;

var LOG_LEVEL = 'error';

// We try to build a list of existing keywords to prevent
// evaluating doubles, but this becomes complex
// when the number of keywords gets above 50k due to scripting limits
// Should probably remain true in most cases
var SKIP_KEYWORD_EXISTS = true;

// ADVANCED!
// --------------------------------------------------------
// Map spreadsheet info to fields we need
// Here you can ajust what column translates to a property
// 0 = A, 1 = B, etc.
// We only work with the first sheet,
// and only look at the first 26 columns (A-Z)
// ONLY USE IF YOU WANT TO USE A DIFFERENT FORMAT THAN THAT
// ONE CREATED BY COPYING THE EXAMPLE SHEET
function mapRowToInfo(row) {
  return {
    cust: row[0],
    custId: row[1].trim(),
    impressionsThreshold: row[17],
    campaignLabel: row[2],
    keywordLabel: row[3],
    type: row[4],
    exclusionLevel: row[5],
    matchType: row[6],
    numDays: row[7],
    andOr: row[8],
    lt: {
      Conversions: row[10],
      CostPerConversion: row[12],
      ConversionValueCost: row[14],
      Clicks: null,
      Ctr: null,
      ClickAssistedConversions: row[19],
    },
    gt: {
      Conversions: row[9],
      CostPerConversion: row[11],
      ConversionValueCost: row[13],
      Clicks: row[15],
      Ctr: row[16],
      ClickAssistedConversions: row[18],
    }
  };
}

// PLEASE DON'T TOUCH BELOW THIS LINE

function main() {
  try {
      MccApp.accounts()
      .withIds(getSpreadsheetIds())
      .withLimit(50)
      .executeInParallel("processAccount", "processReports");
  } catch (ex) {
    processReport(processAccount());
  }
}

function processAccount() {
  var results = [];
  var multiAccountInfo = getAccountInfo();
  for (var i in multiAccountInfo) {
    var accountInfo = multiAccountInfo[i];
    debug(accountInfo);
    addLabelToAccount(accountInfo.keywordLabel);
    // All the exact keywords in the account
    var allKeywordsMap = {},
      newKeywords = {},
      allElementIds = {};

    if (accountInfo.type === 'positive' && !SKIP_KEYWORD_EXISTS) {
      // Fill the allKeywordsMap with all keywords
      var options = { includeZeroImpressions : true }; // Include keywords that aren't used
      // AWQL query to find all keywords in the account
      var query = "SELECT Criteria, KeywordMatchType FROM KEYWORDS_PERFORMANCE_REPORT WHERE KeywordMatchType = " + accountInfo.matchType + " DURING LAST_7_DAYS";
      var reportIter = AdWordsApp.report(query, options).rows();
      while(reportIter.hasNext()) {
        var row = reportIter.next();
        // Save as key, for easy lookup
        allKeywordsMap[row.Criteria.toLowerCase()] = true;
      }
    }

    // Check if keyword exists, only works if getAllKeywords has been run.
    function keywordExists(keyword) {
      return (allKeywordsMap[keyword.toLowerCase()] !== undefined);
    }

    var campaignFilter = '';
    if (accountInfo.campaignLabel !== null) {
      campaignFilter = ' AND CampaignId IN [' +
        getCampaignIds(accountInfo.campaignLabel).join(', ')
        + ']';
    }
    var report = AdWordsApp.report(
        "SELECT Query,Clicks,Cost,Ctr,ConversionRate,ConversionValue,CostPerConversion,Conversions,CampaignId,AdGroupId,Impressions,KeywordId " +
        " FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
        " WHERE " +
            " Impressions >= " + accountInfo.impressionsThreshold +
            " AND AdGroupStatus = ENABLED " +
            " AND CampaignStatus = ENABLED " +
            campaignFilter +
        " DURING " + last_n_days(accountInfo.numDays));
    var rows = report.rows();


    var elementfield = (accountInfo.type === 'negative' && accountInfo.exclusionLevel == 'Campaign') ? 'CampaignId' : 'AdGroupId';
    var keywords = [];
    var activeKeyword = {};
    
    // Iterate through search query and decide whether to
    // add them as positive or negative keywords (or ignore).
    while (rows.hasNext()) {
      var row = rows.next();
      // If query exists as keyword, we don't need to process; report and move on
      if (!SKIP_KEYWORD_EXISTS && keywordExists(row['Query'])) {
        debug([row['Query'], 'exists'].join(': '));
        continue;
      }
      debug([row['Query'], 'doesn\'t exist'].join(': '));
      row['ConversionValueCost'] = row['ConversionValue'] / row['Cost'];
      row['Ctr'] = parseFloat(row['Ctr']) / 100;

      // If the keyword doesn't exist, check if query meets criteria for
      // for addition as exact keyword
      if (keywordMatches(row, accountInfo)) {
        // Prepare for keyword level check
        info(row['Query'] + ' passes SQPR level');
        addToMultiMultiMap(activeKeyword, row[elementfield], row['KeywordId'], row['Query']);
        keywords.push(row['KeywordId']);
      }
    }
    
    // Get keyword performance for all keywords found
    var report = getKeywords(accountInfo, keywords);
    while (report.hasNext()) {
      var row = report.next();
      // Check if keyword is in same context as initially found
      if (!activeKeyword[row[elementfield]] || !activeKeyword[row[elementfield]][row['Id']]) {
        continue;
      }
      // Check keyword stats against check
      if (keywordMatches(row, accountInfo)) {
        info(activeKeyword[row[elementfield]][row['Id']] + ' passes keyword level');
        addToMultiMap(newKeywords, row[elementfield], activeKeyword[row[elementfield]][row['Id']]);
        allElementIds[row[elementfield]] = true;
      }
    }

    // Copy all the adGroupIds from the object into an array to allow bulkprocessing of groups
    var elementIdList = [];
    for (var elementId in allElementIds) {
      elementIdList.push(elementId);
    }

    // Fetch all touched AdWords elements and process relevant keywords
    var elements = (elementfield === 'CampaignId') ? AdWordsApp.campaigns() : AdWordsApp.adGroups();
    var kbResult;
    elements = elements.withIds(elementIdList).get();
    while (elements.hasNext()) {
      var element = elements.next();
      var campaignName = '-';
      var adGroupName = '-';
      if (element.getEntityType() == 'AdGroup') {
        adGroupName = element.getName();
        campaignName = element.getCampaign().getName();
      } else if (element.getEntityType() == 'Campaign') {
        campaignName = element.getName();
      }

      for (var i = 0; i < newKeywords[element.getId()].length; i++) {
        var keyword = newKeywords[element.getId()][i];
        if (accountInfo.matchType === 'EXACT') keyword = '[' + keyword + ']';
        if (accountInfo.matchType === 'PHRASE') keyword = '"' + keyword + '"';
        debug(['Adding', keyword, 'as', accountInfo.type].join(' '));
        if (accountInfo.type === 'positive') {
          kbResult = element.newKeywordBuilder().withText(keyword).build();
          if (kbResult.isSuccessful()) {
            kbResult.getResult().applyLabel(accountInfo.keywordLabel);
            results.push({
              cust: accountInfo.cust,
              custId: accountInfo.custId,
              campaign: campaignName,
              adGroup: adGroupName,
              keyword: keyword,
              type: 'positive'
            });
          }
        } else {
          element.createNegativeKeyword(keyword);
          results.push({
              cust: accountInfo.cust,
              custId: accountInfo.custId,
              campaign: campaignName,
              adGroup: adGroupName,
              keyword: keyword,
              type: 'negative'
            });
        }
      }
    }
  }
  return JSON.stringify(results);
}

function getKeywords(accountInfo, kids) {
  var report = AdWordsApp.report(
        "SELECT Id, AdGroupId, CampaignId, ClickAssistedConversions " +
        " FROM KEYWORDS_PERFORMANCE_REPORT " +
        " WHERE " +
            " Id IN [" + kids.join(', ') + "]" +
        " DURING " + last_n_days(accountInfo.numDays), { includeZeroImpressions : true });
  return report.rows();
}

function getCampaignIds(labels) {
  labels = labels.split(',');
  var campaignIterator = AdWordsApp.campaigns();
  if (labels.length !== 0) {
    for (var j in labels) {
      labels[j] = '\'' + labels[j].trim() + '\''
    }
    campaignIterator = campaignIterator.withCondition("LabelNames CONTAINS_ANY [" + labels.join(', ') + "]");
  }
  campaignIterator = campaignIterator.get();
  var ids = [];
  while (campaignIterator.hasNext()) {
    ids.push(campaignIterator.next().getId());
  }
  return ids;
}

function keywordMatches(keyword, criteria) {
  var and = (criteria.andOr === 'AND');
  var dirs = ['lt', 'gt'];
  var i = 0;
  for (var diri in dirs) {
    var dir = dirs[diri];
    for (var metric in criteria[dir]) {
      if (!keyword[metric]) {
        continue;
      }
      if (criteria[dir][metric] !== null) {
        i++;
        var comp = (dir === 'lt') ?
          (keyword[metric] <= criteria[dir][metric]) :
          (keyword[metric] >= criteria[dir][metric]);
        debug([metric, criteria[dir][metric], keyword[metric], comp]);
        if (and && !comp) {
          return false;
        } else if (!and && comp) {
          return true
        }
      }
    }
  }
  if (i == 0) {
    // If no comparissons were done, none could have failed, and so the test always passes.
    // This already works for AND, but is a special case for OR (given A vs E semantics).
    return true;
  }
  return and;
}

function addLabelToAccount(label) {
  try {
    AdWordsApp.createLabel(label, LABEL_DESC.description, LABEL_DESC.color);
  } catch (e) {
    // Already exists
  }
}

function getSpreadsheetIds() {
  var ids = [],
      ss,
      reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;

  try {
    ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
    return ids;
  }
  ss = ss.getSheets()[0];
  var rows = parseInt(ss.getLastRow());
  var range = ss.getRange("A1:Z" + rows).getValues();
  for (var i = 0; i < rows; i++) {
    var account = mapRowToInfo(range[i]);
    if (!reAWId.test(account.custId)) {
      continue;
    }
    ids.push(account.custId);
  }
  return ids;
}

// Fetch info for current account from the spreadsheet
// MCC scripts don't seem to support shared state between
// Parallel executions, so we need to do this fresh for every account

// Uses default info from 'defaults' set in script, and replaces with
// values from spreadsheet where possible
function getAccountInfo() {
  var ss;
  var reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;
  var protoAccount = {
    cust: AdWordsApp.currentAccount().getName(),
    custId: AdWordsApp.currentAccount().getCustomerId(),
    impressionsThreshold: IMPRESSIONS_THRESHOLD,
    campaignLabel: null,
    keywordLabel: null,
    type: 'positive',
    exclusionLevel: null,
    matchType: 'EXACT',
    numDays: 90,
    andOr: 'AND',
    lt: {
      Conversions: null,
      CostPerConversion: null,
      ConversionValueCost: null,
      Clicks: null,
      Ctr: null,
      ClickAssistedConversions: null,
    },
    gt: {
      Conversions: null,
      CostPerConversion: null,
      ConversionValueCost: null,
      Clicks: null,
      Ctr: null,
      ClickAssistedConversions: null,
    }
  };

  try {
    ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
    return [protoAccount];
  }
  ss = ss.getSheets()[0];
  var rows = parseInt(ss.getLastRow());
  var range = ss.getRange("A1:Z" + rows).getValues();
  var accounts = [];

  for (var i = 0; i < rows; i++) {
    var account = mapRowToInfo(range[i]);
    if (!reAWId.test(account.custId) || account.custId !== protoAccount.custId) {
      continue;
    }

    for(var key in account) {
      if (key === 'gt' || key === 'lt') {
        for (var subkey in account[key]) {
          if (account[key][subkey] === '' || account[key][subkey] === null || account[key][subkey] === '-') {
            account[key][subkey] = protoAccount[key][subkey];
          }
        }
      } else {
        if (account[key] === '' || account[key] === null || account[key] === '-') {
          account[key] = protoAccount[key];
        }
      }
    }
    accounts.push(account);
  }
  if (accounts.length === 0) {
    return [protoAccount];
  } else {
    return accounts;
  }
}

function addToMultiMap(map, key, value) {
  if (!map[key]) {
    map[key] = [];
  }
  map[key].push(value);
}

function addToMultiMultiMap(map, key, sndkey, value) {
  if (!map[key]) {
    map[key] = [];
  }
  map[key][sndkey] = value;
}

// Convenience function to generate a date range based on the current date.
function last_n_days(n) {
	var	from = new Date(),
		to = new Date();
	to.setUTCDate(from.getUTCDate() - n);
	return google_date_range(from, to);
}

// Convenience function to generate a google formatted date range based on js Date objects
function google_date_range(from, to) {
	function google_format(date) {
		var date_array = [date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate()];
		if (date_array[1] < 10) date_array[1] = '0' + date_array[1];
		if (date_array[2] < 10) date_array[2] = '0' + date_array[2];
		return date_array.join('');
	}
	var inverse = (from > to);
	from = google_format(from);
	to = google_format(to);
	var result = [from, to];
	if (inverse) {
		result = [to, from];
	}
	return result.join(',');
}

// Process the results of a single
// Creates table, exports as html and sends to set emailaddress
function processReport(report) {
  if (emailAddr == '') {
		return;
	}
  // Define table(headers)
  var table = buildTable();
  rep = JSON.parse(report);
  for (var j in rep) {
    add_row(table, rep[j], {});
  }
  sendEmail(table);
}

// Process the results of all the accounts
// Creates table, exports as html and sends to set emailaddress
function processReports(reports) {
	if (emailAddr == '') {
		return;
	}
  // Define table(headers)
  var table = buildTable();
  for (var i in reports) {
    if(!reports[i].getReturnValue()) { continue; }
    var rep = JSON.parse(reports[i].getReturnValue());
    for (var j in rep) {
      add_row(table, rep[j], {});
    }
  }
  sendEmail(table);
}

function sendEmail(table) {
   // Only send if there is something to report, or alwaysReport is set.
  if (alwaysReport || table.rows.length > 0) {
    var htmlBody = '<' + 'h1>' + emailSubject + '<' + '/h1>' + render_table(table, {border: 1, width: "95%", style: "border-collapse:collapse;"});
    MailApp.sendEmail(emailAddr, emailSubject, emailSubject, { htmlBody: htmlBody });
  }
}

function buildTable() {
  var tableCols = {
    cust: 'Customer',
    custId: 'Customer-ID',
    campaign: 'Campaign',
    adGroup: 'AdGroup',
    keyword: 'Keyword',
    type: 'Type',
  };
  return create_table(tableCols);
}

// Instantiate a table object with given column names
// Either as array or object/hash
function create_table(cols) {
  var table = { head: [], rows: [], row_attrs: [], row_names: undefined};
  if (cols instanceof Array) {
    table.head = cols;
  } else if (cols instanceof Object) {
    var i = 0;
    table.row_names = {};
    for (var key in cols) {
      table.head.push(cols[key]);
      table.row_names[key] = i;
      i++;
    }
  }
  return table;
}

// Add a row to the table object
// Either an clean array or an object
// with correct parameter names
function add_row(table, row, attrs) {
  if (row instanceof Array) {
    table.rows.push(row);
    return;
  }
  if (table.row_names === undefined) {
    return;
  }
  var new_row = [];
  for (var key in row) {
    if (table.row_names[key] === undefined) {
      continue;
    }
    new_row[table.row_names[key]] = row[key];
  }
  table.rows.push(new_row);
  table.row_attrs.push(attrs);
}

// Log the contents of the table object in a semi readable format
function log_table(table) {
  Logger.log('----------------------------------');
  Logger.log(table.head.join(' | '));
  Logger.log('----------------------------------');
  for (var i in table.rows) {
    Logger.log(table.rows[i].join(' | '));
  }
  Logger.log('----------------------------------');
}

// Turn the table object into an HTML table
// Add attributes to the table tag with the attrs param
// Takes an object/hash
function render_table(table, attrs) {
  function render_tag(content, tag_name, attrs) {
    var attrs_str = '';
    if (attrs instanceof Object) {
      for (var attr in attrs) {
        attrs_str += [' ',attr,'="', attrs[attr], '"'].join('');
      }
    }
    var tag = ['<' + tag_name + attrs_str + '>'];
    tag.push(content);
    tag.push('<!--' + tag_name + '-->');
    return tag.join('');
  }
  function render_row(row, field, row_attrs) {
    if (field === undefined) {
      field = 'td';
    }
    var row_ar = new Array(table.head.length);
    for (var col in row) {
      row_ar.push(render_tag(row[col], field, row_attrs));
    }
    return render_tag(row_ar.join(''), 'tr');
  }
  var table_ar = [];
  table_ar.push(render_row(table.head, 'th'));
  for (var row in table.rows) {
    table_ar.push(render_row(table.rows[row], 'td', table.row_attrs[row]));
  }
  return render_tag(table_ar.join(''), 'table', attrs);
}

// Some functions to help with logging - gracefully borrowed from http://www.freeadwordsscripts.com
var LOG_LEVELS = { 'error':1, 'warn':2, 'info':3, 'debug':4 };
function error(msg) { if(LOG_LEVELS['error'] <= LOG_LEVELS[LOG_LEVEL]) { log('ERROR',msg); } }
function warn(msg)  { if(LOG_LEVELS['warn']  <= LOG_LEVELS[LOG_LEVEL]) { log('WARN' ,msg); } }
function info(msg)  { if(LOG_LEVELS['info']  <= LOG_LEVELS[LOG_LEVEL]) { log('INFO' ,msg); } }
function debug(msg) { if(LOG_LEVELS['debug'] <= LOG_LEVELS[LOG_LEVEL]) { log('DEBUG',msg); } }
function log(type,msg) { Logger.log([type, msg]); }