Automated Keyword Bid Management Script for Google Ads

Making bid adjustments can be a time consuming task, especially over a raft of accounts. Well not any more thanks to our free keyword level automated bid script, make millions of bid adjustments completely automated.

Let’s face it – “Smart Bidding” doesn’t work for every campaign or account.

If you’re getting less than 1-2 conversions a day per campaign, target CPA and target ROAS just seem to struggle. They’re data hungry.

That doesn’t mean you can’t be smart with your bid management, because there’s another option.

Let’s get the automated bids started

Here’s the video that explains how to setup and run the script for your Google Ads MCC.

Then, after watching the video, you’re going to need two files to get this running.

The script code itself and the Google Sheet that controls each account and campaign targets.

Download the files

1) Make a copy of the Google Sheet template:

https://docs.google.com/spreadsheets/d/1U1c1kQdKNXmwRLgDBvErIq1wclYxPRq8aa0Mu6R46sU/

2) Copy and paste the Google Ads script in to your MCC bulk actions area:


/*****
*
* AdEvolver Automated Keyword Bid Management
*
*
* @version: 1.1
* AdWords script maintained on adevolver.com
*
***************************************************/

var INPUT_SHEET_URL = 'https://docs.google.com/spreadsheets/d/1U1c1kQdKNXmwRLgDBvErIq1wclYxPRq8aa0Mu6R46sU/';
var INPUT_TAB_NAME = 'Bid Updater';

// Do not edit anything below this line unless you know what you are doing
var DEBUG = false;
function main() {
  
  var SETTINGS = scanForAccounts();
  var ids = Object.keys(SETTINGS);
  if(DEBUG){ids = ["123-456-7890"]};
  if(ids.length == 0) { Logger.log('No Rules Specified'); return; };
  MccApp.accounts().withIds(ids).withLimit(50).executeInParallel('runRows','callBack',JSON.stringify(SETTINGS));
  
}

function scanForAccounts() {
  log("getting settings...")
  var map = {};
  var controlSheet = SpreadsheetApp.openByUrl(INPUT_SHEET_URL).getSheetByName(INPUT_TAB_NAME)
  var data = SpreadsheetApp.openByUrl(INPUT_SHEET_URL).getSheetByName(INPUT_TAB_NAME).getDataRange().getValues();
  data.shift();
  data.shift();
  data.shift();
  //log(JSON.stringify(data))
  
  var HEADER = ['ID', 'NAME', 'EMAIL', 'FLAG',
                'DATE_RANGE_LITERAL','N','CAMPAIGN_CONTAINS','CAMPAIGN_NOT_CONTAINS','TARGET_CPA','TARGET_ROAS', 'MIN_CLICKS',
                'MIN_ADJUSTMENT','MAX_ADJUSTMENT',"-3SEGMENT","-2SEGMENT","-1SEGMENT","0SEGMENT","1SEGMENT","2SEGMENT","3SEGMENT",
                "-3SEGMENTCHANGE","-2SEGMENTCHANGE","-1SEGMENTCHANGE","0SEGMENTCHANGE","1SEGMENTCHANGE","2SEGMENTCHANGE","3SEGMENTCHANGE",'LOG_SHEET_URL'];
  
  var logsColumn = 0;
  var col = 5
  while(controlSheet.getRange(3, col).getValue()){
    logsColumn = controlSheet.getRange(3, col).getValue() == "Logs" ? col : 0;
    if(logsColumn>0){break;}
    col++;
  }
  
  for(var k in data) {
    //if "run script" is not set to "yes", continue.
    if(data[k][0] == '' || data[k][3].toLowerCase() != 'yes') { continue; }
    var rowNum = parseInt(k,10) + 4;
    var id = data[k][0];
    var rowId = id+"/"+rowNum;
    map[id] = map[id]  || {}
    map[id][rowId] = { 'ROW_NUM': (parseInt(k,10) + 4) };
    for(var j in HEADER) {
      if(HEADER[j] == "LOGS_COLUMN"){
        map[id][rowId][HEADER[j]] = logsColumn;
        continue;
      }
      map[id][rowId][HEADER[j]] = data[k][j];
    }    
  }
  return map;
}

function callBack() {
  // Do something here
  Logger.log('Finished');
}

function runRows(INPUT){
  log("running rows")
  var SETTINGS = JSON.parse(INPUT)[AdWordsApp.currentAccount().getCustomerId().toString()]
  for(var rowId in SETTINGS){
    runScript(SETTINGS[rowId]);
  }
}


function runScript(SETTINGS) {
  log('Script Started');
  //NOTE: The min clicks rule needs adding to products/adgroups if used in future
  
  //****   1: Get settings from the sheet
  var controlSheet = SpreadsheetApp.openByUrl(INPUT_SHEET_URL).getSheetByName(INPUT_TAB_NAME)
  var logsColumn = getLogsColumn(controlSheet)
  
  parseDateRange(SETTINGS);
  
  var now = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMM dd, yyyy HH:mm:ss')
  SETTINGS.CAMPAIGN_CONTAINS = SETTINGS.CAMPAIGN_CONTAINS=="" ? [] : SETTINGS.CAMPAIGN_CONTAINS.split(",")
  SETTINGS.CAMPAIGN_NOT_CONTAINS = SETTINGS.CAMPAIGN_NOT_CONTAINS == "" ? [] : SETTINGS.CAMPAIGN_NOT_CONTAINS.split(",")
  SETTINGS.RUN_KEYWORDS = true;
  SETTINGS.RUN_PRODUCT_GROUPS = false;
  SETTINGS.RUN_ADGROUPS = false;
  SETTINGS.EMAIL = SETTINGS.EMAIL=="" ? [] : SETTINGS.EMAIL.split(",")
  SETTINGS.MAX_MIN_CLICKS = 100
  
  if(SETTINGS.TARGET_ROAS=="" && SETTINGS.TARGET_CPA != ""){
    SETTINGS.CPA_ROAS = "CPA" 
  }else if(SETTINGS.TARGET_ROAS!="" && SETTINGS.TARGET_CPA == ""){
    SETTINGS.CPA_ROAS = "ROAS" 
  }else{
    var msg = "Can't determine if the target is ROAS or CPA, check the settings"
    log(msg)
    throw(msg) 
  }
  
  log(JSON.stringify(SETTINGS))
  
  if(!SETTINGS.RUN_KEYWORDS && !SETTINGS.RUN_PRODUCT_GROUPS && !SETTINGS.RUN_ADGROUPS){
    log("All three areas (keywords, products and ad groups) have been excluded. No changes will be made.")
    log("Finished")
    return;
  }
  
  //****   2: Check keywords, ad groups and product groups. Return changes
    var tabNames = []
  //Keywords
  if(SETTINGS.RUN_KEYWORDS){
    tabNames.push("Keywords")
  var keywordChanges  = SETTINGS.INCLUDE_TEXT == "No" ? {} : checkKeywords(SETTINGS);
  var keywordChangeRows = keywordChanges[1];
  var keywordNumChanges = keywordChangeRows.length;
  updateKeyords(keywordChanges[0])
  }else{
   keywordNumChanges = 0 
  }

  //Product Groups
  if(SETTINGS.RUN_PRODUCT_GROUPS){
    tabNames.push("Product Groups")
  var productGroupChanges = SETTINGS.INCLUDE_SHOPPING == "No" ? {} : getProductGroupChanges(SETTINGS)
  var productGroupChangeRows =  productGroupChanges[1];
  var productGroupNumChanges = productGroupChangeRows.length;  
  updateProducts(productGroupChanges[0])
  }else{
   productGroupNumChanges = 0 
  }
  
  //Ad Groups
  if(SETTINGS.RUN_ADGROUPS){
    tabNames.push("Ad Groups")
    var adGroupChanges = getAdGroupChanges(SETTINGS)
    var adGroupChangeRows = adGroupChanges[1];
    var adGroupNumChanges = adGroupChangeRows.length;
    updateAdGroups(adGroupChanges[0])
  }else{
    adGroupNumChanges = 0
  }
  
  var numChanges = keywordNumChanges + productGroupNumChanges + adGroupNumChanges
  
  //****   3: Update the keywords, products, ad groups
  
  var logSS = createSheet(SETTINGS, controlSheet, logsColumn)

  
  //create the tabs
  //or rename if they exist (may want to rename in future, then it's just a case of changing the array)

  createTabs(tabNames, logSS)
 
  var previewMessage = AdWordsApp.getExecutionInfo().isPreview() ? " (Preview Mode)" : "";
  if(DEBUG){previewMessage+=" (Debug Mode)"}
  var tab = SpreadsheetApp.openByUrl(INPUT_SHEET_URL).getSheetByName(INPUT_TAB_NAME);
  if(numChanges == 0) { 
    log('No Changes'); 
    tab.getRange(SETTINGS.ROW_NUM, logsColumn, 1, 2).setValues([['No New Changes '+previewMessage, now]]);
    log('Finished');
  }
  
  var headers = [ ["Date",  "Campaign","Ad Group","Keyword","Match Type", "Cost", "Clicks", 
                "Impressions", "Conversions", "Avg. Position" ,"Conv. Value", "ROAS", 
                "CPA","Previous Bid", "New Bid", "% Change","Preview Mode?"], ["Date",  "Campaign","Ad Group","Product Group", "Cost", "Clicks", 
                "Impressions", "Conversions", "Conv. Value", "ROAS", 
                "CPA","Previous Bid", "New Bid", "% Change","Preview Mode?"], ["Date",  "Campaign","Ad Group", "Cost", "Clicks", 
                "Impressions", "Conversions", "Avg. Position" ,"Conv. Value", "ROAS", 
                "CPA","Previous Bid", "New Bid", "% Change","Preview Mode?"]]  
     
  for(var t in tabNames){
    var header = headers[t]
    var tabName = tabNames[t]
    var outputTab = logSS.getSheetByName(tabName)
    outputTab.clear()
    outputTab.setFrozenRows(1);
    outputTab.getRange(1, 1, 1, header.length).setValues([header]).setFontWeight('bold').setBackground('#efefef');
  }

  var results = [SETTINGS.LOG_SHEET_URL, numChanges + ' Changes'+previewMessage, now ];
  tab.getRange(SETTINGS.ROW_NUM, logsColumn-1, 1, results.length).setValues([results]);
 
  for(var t in tabNames){
    var header = headers[t]
    var tabName = tabNames[t]
    var outputTab = logSS.getSheetByName(tabName)
    outputTab.clear()
    outputTab.setFrozenRows(1);
    outputTab.getRange(1, 1, 1, header.length).setValues([header]).setFontWeight('bold').setBackground('#efefef');
  }  
  
  var outputData = [keywordChangeRows, productGroupChangeRows, adGroupChangeRows]
  
  for(var t in tabNames){
    var header = headers[t]
    var tabName = tabNames[t]
    var outputTab = logSS.getSheetByName(tabName)
    var changeRows = outputData[t]
    if(typeof changeRows=="undefined" || changeRows.length==0){continue;}
    outputTab.getRange(2, 1, changeRows.length, changeRows[0].length).setValues(changeRows);
    outputTab.getDataRange().setFontFamily('Calibri');
  }
 
  //lastly, send email
  if(!DEBUG){
    var SUB = SETTINGS.NAME + ': '+INPUT_TAB_NAME+' Changes';
    var MSG = 'Hi,\n\nBased on performance, some Cpc Bids have been updated in your AdWords account.';
    MSG += '\n\nPlease follow the link below to see the latest changes:\n' + SETTINGS.LOG_SHEET_URL;
    MSG += '\n\nThanks.'

      for(var e in SETTINGS.EMAIL){
        MailApp.sendEmail(SETTINGS.EMAIL[e], SUB, MSG);
      }
    
  }
  
  log('Finished');
  //*****************************************************   END OF MAIN   *****************************************************//
}

function updateAdGroups(adGroupChanges){
  log("updating adgroups...")

  var types = ["Search", "Shopping"]
  for(var type in types){
    if(types[type]=="Search"){
      var adGroups = AdWordsApp.adGroups().withIds(adGroupChanges['ids']).get();
    }else if(types[type]=="Shopping"){
      var adGroups =  AdWordsApp.shoppingAdGroups().withIds(adGroupChanges['ids']).get();
    }
    
    while(adGroups.hasNext()){
      var adGroup = adGroups.next();
      var oldBid = adGroup.bidding().getCpc();
      var adGroupId = adGroup.getId();
      adGroup.bidding().setCpc(adGroupChanges['rows'][adGroupId].change)
    }
    
  }
}

function updateProducts(productGroupChanges){
    log("updating product groups...")
    var productGroups = AdWordsApp.productGroups().withIds(productGroupChanges['ids']).get();
    while (productGroups.hasNext()) {      
      var productGroup = productGroups.next();
      var productGroupId = productGroup.getId();
      var adGroupId = productGroup.getAdGroup().getId();
      var idPair = [adGroupId, productGroupId]
      productGroup.setMaxCpc(productGroupChanges['rows'][idPair].change)
    }
}

function updateKeyords(keywordChanges){
    log("updating keywords...")
  var keywords = AdWordsApp.keywords().withIds(keywordChanges['ids']).get();
  while(keywords.hasNext()){
    var keyword = keywords.next();
    var keywordId = keyword.getId();
    var adGroupId = keyword.getAdGroup().getId()
    var idPair = [adGroupId, keywordId]
    keyword.bidding().setCpc(keywordChanges['rows'][idPair].change)
  }
}

function getAdGroupChanges(SETTINGS){
  log("checking ad groups...")
  //get campaigns and types, for checking INCLUDE_settings  
  var OPTIONS = { includeZeroImpressions : false };
  var cols = ['CampaignId','AdGroupId','CampaignName','AdGroupName','ConversionValue',
              'Impressions','Clicks','Cost','Conversions', 'AveragePosition', 'CpcBid'];

  var reportName = 'ADGROUP_PERFORMANCE_REPORT';
  
  var whereArray = []
  whereArray.push(' where ')
  whereArray.push( ' AdNetworkType1 IN [SEARCH,CONTENT] ')
  whereArray.push('and CampaignStatus = ENABLED and AdGroupStatus = ENABLED')
  for(var i in SETTINGS.CAMPAIGN_CONTAINS){
    whereArray.push("and CampaignName CONTAINS_IGNORE_CASE '" + SETTINGS.CAMPAIGN_CONTAINS[i].trim()+"'")
  }
  
  for(var i in SETTINGS.CAMPAIGN_NOT_CONTAINS){
    whereArray.push("and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + SETTINGS.CAMPAIGN_NOT_CONTAINS[i].trim()+"'")
  }
  
  var where = whereArray.join(" ")
  
  var query = ['select',cols.join(','),'from',reportName,
               where,
               'during',SETTINGS.DATE_RANGE].join(' ');
  //log(query)
  var map = { 'ids': [], 'rows': {}};
  var reportIter = AdWordsApp.report(query, OPTIONS).rows();
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    //log(row.CampaignName)
    var toChange = false;
    //remove automatic bids
    if(row.CpcBid=="--" || row.CpcBid.indexOf("auto")>-1){
     continue; 
    }
    
    row.Impressions = parseInt(row.Impressions,10);
    row.Clicks = parseInt(row.Clicks,10);
    row.Conversions = parseFloat(row.Conversions) < 1 && parseFloat(row.Conversions) > 0 ? Math.ceil(parseFloat(row.Conversions.toString().replace(/,/g,''),10)) : Math.round(parseFloat(row.Conversions.toString().replace(/,/g,''),10));
    row.CTR = row.Impressions == 0 ? 0 : round((row.Clicks / row.Impressions),4);
    row.AveragePosition = parseFloat(row.AveragePosition);
    row.Cost = parseFloat(row.Cost.toString().replace(/,/g,''));
    row.CPA = row.Conversions == 0 ? 100000 : round((row.Cost / row.Conversions),2);
    row.ConversionValue = parseFloat(row.ConversionValue.toString().replace(/,/g, ''));
    row.vpc = row.ConversionValue == 0 ? -100 : round((row.ConversionValue / row.Cost),2);
    row.ConversionRate = row.Conversions > 0 ? row.Conversions/row.Clicks : 0;
    
    //check if a new bid is needed, store it if so
    
var idPair = [row.AdGroupId]
      map['ids'].push(idPair);
      map['rows'][idPair] = {}
      map['rows'][idPair]  = row;
    
  }
  
  //now loop through the rows and add CPA, ROAS, and the bid adjustment
  var changeRows = []
  var previewMode = AdWordsApp.getExecutionInfo().isPreview() == true ? "Yes" : "No";
  var now = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMM dd, yyyy HH:mm:ss')
  
  for(var row in map['rows']){
    var stats =  map['rows'][row]
    
    stats.ROAS = stats.ConversionValue == 0 ? -100000 : round((stats.ConversionValue / stats.Cost),2);
    stats.CPA = stats.Conversions == 0 ? 100000 : round((stats.Cost / stats.Conversions),2);
    
    var newBidAndSegment = returnBid(SETTINGS, stats.CPA, stats.ROAS, stats.Clicks, stats.CpcBid)
    
    stats["change"] = newBidAndSegment[0]
    
    if(stats["change"] == "No Change"){
      row = row.split(",")
      delete map['rows'][row] 
      for(var i in map['ids']){
        if(map['ids'][i][0]==row[0] && map['ids'][i][1]==row[1]){
          map['ids'].splice(i, 1);
          break 
        }
      }
    
      continue
    }
    stats["change"] = parseFloat(stats["change"] )
    var segment = newBidAndSegment[1]
    stats.ROAS = stats.ConversionValue == 0 ? "--" : round((stats.ConversionValue / stats.Cost),2);
    stats.CPA = stats.Conversions == 0 ? "--"  : round((stats.Cost / stats.Conversions),2);
      changeRows.push([now,stats.CampaignName,stats.AdGroupName,stats.Cost,stats.Clicks,
                       stats.Impressions,stats.Conversions,stats.AveragePosition,stats.ConversionValue,stats.ROAS,
                       stats.CPA,stats.CpcBid, stats["change"],segment,previewMode]);
    
  }
  
  //log(JSON.stringify(map))
  //log(changeRows)
  //log("Num of keyword changes: " + map['ids'].length)
  return [map, changeRows];
}

function getProductGroupChanges(SETTINGS){
 log("checking product groups...")
  var OPTIONS = { includeZeroImpressions : false };
  var cols = ['AdGroupId','CampaignId','Id','CampaignName','AdGroupName','ProductGroup','ConversionValue',
              'Impressions','Clicks','Cost','Conversions', 'CpcBid'];
  var labels = AdWordsApp.labels().withCondition(
      "Name = 'OR'").get();
  var reportName = 'PRODUCT_PARTITION_REPORT';
   var whereArray = []
  whereArray.push(' where ')
  whereArray.push( ' AdNetworkType1 IN [SEARCH] ')
  whereArray.push('and CampaignStatus = ENABLED and AdGroupStatus = ENABLED')
  for(var i in SETTINGS.CAMPAIGN_CONTAINS){
    whereArray.push("and CampaignName CONTAINS_IGNORE_CASE '" + SETTINGS.CAMPAIGN_CONTAINS[i].trim()+"'")
  }
  
  for(var i in SETTINGS.CAMPAIGN_NOT_CONTAINS){
    whereArray.push("and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + SETTINGS.CAMPAIGN_NOT_CONTAINS[i].trim()+"'")
  }
  
  var where = whereArray.join(" ")
  
  var query = ['select',cols.join(','),'from',reportName,
               where,
               'during',SETTINGS.DATE_RANGE].join(' ');
 
  var map = { 'ids': [], 'rows': {}};
  var reportIter = AdWordsApp.report(query, OPTIONS).rows();
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    
    var toChange = false;
    row.Impressions = parseInt(row.Impressions,10);
    row.Clicks = parseInt(row.Clicks,10);
    row.Conversions = parseFloat(row.Conversions) < 1 && parseFloat(row.Conversions) > 0 ? Math.ceil(parseFloat(row.Conversions.toString().replace(/,/g,''),10)) : Math.round(parseFloat(row.Conversions.toString().replace(/,/g,''),10));
    row.CTR = row.Impressions == 0 ? 0 : round((row.Clicks / row.Impressions),4);
    row.Cost = parseFloat(row.Cost.toString().replace(/,/g,''));
   
    row.CPA = row.Conversions == 0 ? 100000 : round((row.Cost / row.Conversions),2);
    row.ConversionValue = parseFloat(row.ConversionValue.toString().replace(/,/g, ''));
    row.vpc = row.ConversionValue == 0 ? -100 : round((row.ConversionValue / row.Cost),2);
    row.ConversionRate = row.Conversions > 0 ? row.Conversions/row.Clicks : 0;
    
    //remove automatic bids
    if(row.CpcBid=="--" || row.CpcBid.indexOf("auto")>-1){
     continue; 
    }
    
   var idPair = [row.AdGroupId, row.Id]
      map['ids'].push(idPair);
      map['rows'][idPair] = {}
      map['rows'][idPair]  = row;
    
  }
  
  //now loop through the rows and add CPA, ROAS, and the bid adjustment
  var changeRows = []
  var previewMode = AdWordsApp.getExecutionInfo().isPreview() == true ? "Yes" : "No";
  var now = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMM dd, yyyy HH:mm:ss')
  
  for(var row in map['rows']){
    var stats =  map['rows'][row]
    
    stats.ROAS = stats.ConversionValue == 0 ? -100000 : round((stats.ConversionValue / stats.Cost),2);
    stats.CPA = stats.Conversions == 0 ? 100000 : round((stats.Cost / stats.Conversions),2);
    
    var newBidAndSegment = returnBid(SETTINGS, stats.CPA, stats.ROAS, stats.Clicks, stats.CpcBid)
    
    stats["change"] = newBidAndSegment[0]
    
    if(stats["change"] == "No Change"){
      row = row.split(",")
      delete map['rows'][row] 
      for(var i in map['ids']){
        if(map['ids'][i][0]==row[0] && map['ids'][i][1]==row[1]){
          map['ids'].splice(i, 1);
          break 
        }
      }
    
      continue
    }
    stats["change"] = parseFloat(stats["change"] )
    var segment = newBidAndSegment[1]
        stats.ROAS = stats.ConversionValue == 0 ? "--" : round((stats.ConversionValue / stats.Cost),2);
    stats.CPA = stats.Conversions == 0 ? "--"  : round((stats.Cost / stats.Conversions),2);
      changeRows.push([now,stats.CampaignName,stats.AdGroupName,stats.ProductGroup,stats.Cost,stats.Clicks,
                       stats.Impressions,stats.Conversions,stats.ConversionValue,stats.ROAS,
                       stats.CPA,stats.CpcBid, stats["change"],segment,previewMode]);
  }
  
  //log("map: " + JSON.stringify(map))
  //log("changeRows: " + changeRows)
  //log("Num of keyword changes: " + map['ids'].length)
  return [map, changeRows];

}

function checkKeywords(SETTINGS){
  log("checking keywords...")
  var OPTIONS = { includeZeroImpressions : false };
  var cols = ['AdGroupId','CampaignId','Id','CampaignName','AdGroupName','Criteria','KeywordMatchType','ConversionValue',
              'Impressions','Clicks','Cost','AllConversions', 'AveragePosition', 'CpcBid'];

  var reportName = 'KEYWORDS_PERFORMANCE_REPORT';
     var whereArray = []
  whereArray.push(' where ')
  whereArray.push( ' AdNetworkType1 IN [SEARCH] ')
  whereArray.push('and CampaignStatus = ENABLED and AdGroupStatus = ENABLED')
  for(var i in SETTINGS.CAMPAIGN_CONTAINS){
    whereArray.push("and CampaignName CONTAINS_IGNORE_CASE '" + SETTINGS.CAMPAIGN_CONTAINS[i].trim()+"'")
  }
  
  for(var i in SETTINGS.CAMPAIGN_NOT_CONTAINS){
    whereArray.push("and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + SETTINGS.CAMPAIGN_NOT_CONTAINS[i].trim()+"'")
  }
  
  whereArray.push(" and Clicks > 0 ")
  
  var where = whereArray.join(" ")
  
  var query = ['select',cols.join(','),'from',reportName,
               where,
               'during',SETTINGS.DATE_RANGE].join(' ');

  var map = { 'ids': [], 'rows': {}};
  //log("Keyword query: " + query)
  var reportIter = AdWordsApp.report(query, OPTIONS).rows();
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    
    //rows 3000006 and 3000000 are AutomaticContent and Content, respectively - more info here: https://groups.google.com/forum/#!topic/adwords-api/qcskfkalb3g
    //AutomaticContent: stats from Display Optimiser. Content: All Display Stats combined
    if(row.Id.indexOf("300000")>-1){continue;}
      //remove automatic bids
    if(row.CpcBid=="--" || row.CpcBid.indexOf("auto")>-1){
     continue; 
    }
    
    row.Impressions = parseInt(row.Impressions,10);
    row.Clicks = parseInt(row.Clicks,10);
    row.AllConversions = parseFloat(row.AllConversions) < 1 && parseFloat(row.AllConversions) > 0 ? Math.ceil(parseFloat(row.AllConversions.toString().replace(/,/g,''),10)) : Math.round(parseFloat(row.AllConversions.toString().replace(/,/g,''),10));
    row.CTR = row.Impressions == 0 ? 0 : round((row.Clicks / row.Impressions),4);
    row.AveragePosition = parseFloat(row.AveragePosition);
    row.Cost = parseFloat(row.Cost.toString().replace(/,/g,''));
    row.CPA = row.AllConversions == 0 ? 100000 : round((row.Cost / row.AllConversions),2);
    row.ConversionValue = parseFloat(row.ConversionValue.toString().replace(/,/g, ''));
    row.vpc = row.ConversionValue == 0 ? -100 : round((row.ConversionValue / row.Cost),2);
    row.ConversionRate = row.AllConversions > 0 ? row.AllConversions/row.Clicks : 0;
    
    //Check we've hit the min clicks (as defined by 100/conv. rate
    var minClicks = 10/(row.ConversionRate*10) > SETTINGS.MAX_MIN_CLICKS ? SETTINGS.MAX_MIN_CLICKS : 10/(row.ConversionRate*10)
    var variableMinClicks = row.AllConversions==0 ? SETTINGS.MIN_CLICKS : minClicks
    
    if(row.Clicks < variableMinClicks){
      continue;
    }
    
    var idPair = [row.AdGroupId, row.Id]
    map['ids'].push(idPair);
    map['rows'][idPair] = {}
    map['rows'][idPair]  = row;
    
  }
  
  //now loop through the rows and add CPA, ROAS, and the bid adjustment
  var changeRows = []
  var previewMode = AdWordsApp.getExecutionInfo().isPreview() == true ? "Yes" : "No";
  var now = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMM dd, yyyy HH:mm:ss')
  
  for(var row in map['rows']){
    var stats =  map['rows'][row]
    
    stats.ROAS = stats.ConversionValue == 0 ? -100000 : round((stats.ConversionValue / stats.Cost),2);
    stats.CPA = stats.AllConversions == 0 ? 100000 : round((stats.Cost / stats.AllConversions),2);
    
    var newBidAndSegment = returnBid(SETTINGS, stats.CPA, stats.ROAS, stats.Clicks, stats.CpcBid)
    
    stats["change"] = newBidAndSegment[0]
    
    if(stats["change"] == "No Change"){
      row = row.split(",")
      delete map['rows'][row] 
      for(var i in map['ids']){
        if(map['ids'][i][0]==row[0] && map['ids'][i][1]==row[1]){
          map['ids'].splice(i, 1);
          break 
        }
      }
    
      continue
    }
    stats["change"] = parseFloat(stats["change"] )
    var segment = newBidAndSegment[1]
    stats.ROAS = stats.ConversionValue == 0 ? "--" : round((stats.ConversionValue / stats.Cost),2);
    stats.CPA = stats.AllConversions == 0 ? "--"  : round((stats.Cost / stats.AllConversions),2);
      changeRows.push([now,stats.CampaignName,stats.AdGroupName,stats.Criteria, stats.KeywordMatchType,stats.Cost,stats.Clicks,
                       stats.Impressions,stats.AllConversions,stats.AveragePosition,stats.ConversionValue,stats.ROAS,
                       stats.CPA,stats.CpcBid, stats["change"],segment,previewMode]);
    
  }
  
  //log(JSON.stringify(map))
  //log(changeRows)
  //log("Num of keyword changes: " + map['ids'].length)
  return [map, changeRows];
}

function returnBid(SETTINGS, CPA, ROAS, Clicks, currentModifier){
  //if(!DEBUG){if(Clicks < SETTINGS.MIN_CLICKS){return "No Change";}}
  //if(Clicks < SETTINGS.MIN_CLICKS){return ["No Change", ""];}
  currentModifier = parseFloat(currentModifier)
  //first determine the % vs target
  //then determine the bucket
  //then update and return the currentModifier
  
  if(SETTINGS.CPA_ROAS=="CPA"){
    //(target-actual)/actual
    var vsTarget = CPA==0 ? 0 : ((SETTINGS.TARGET_CPA-CPA)/CPA);

  }else if(SETTINGS.CPA_ROAS=="ROAS"){
    //(actual-target)/target

    var vsTarget = ROAS==0 ? 0 : ((ROAS-SETTINGS.TARGET_ROAS)/SETTINGS.TARGET_ROAS);
    
  }else{
   log("CPA or ROAS field not recognised, please check the control sheet");
   
  }
  
  var adjustmentChange = parseFloat(returnChange(SETTINGS,vsTarget))
  var adjustment = parseFloat(currentModifier+(adjustmentChange*currentModifier));
  adjustment = adjustment > SETTINGS.MAX_ADJUSTMENT ? SETTINGS.MAX_ADJUSTMENT : adjustment;
  adjustment = adjustment < SETTINGS.MIN_ADJUSTMENT ? SETTINGS.MIN_ADJUSTMENT : adjustment;
  if(adjustmentChange==0 || adjustmentChange =="0"){adjustment="No Change";}
  return [adjustment, adjustmentChange];
  
}


function returnChange(SETTINGS,vsTarget){
  var toReturn = 0;
  if(vsTarget <= SETTINGS["-3SEGMENT"]){
    toReturn =  SETTINGS["-3SEGMENTCHANGE"]
  }else if(vsTarget > SETTINGS["-3SEGMENT"] && vsTarget <= SETTINGS["-2SEGMENT"]){
     toReturn =  SETTINGS["-2SEGMENTCHANGE"]
  }else if(vsTarget > SETTINGS["-2SEGMENT"] && vsTarget <= SETTINGS["-1SEGMENT"]){
     toReturn =  SETTINGS["-1SEGMENTCHANGE"]
  }else if(vsTarget > SETTINGS["-1SEGMENT"] && vsTarget < SETTINGS["1SEGMENT"]){
     toReturn =  SETTINGS["0SEGMENTCHANGE"]
  }else if(vsTarget >= SETTINGS["1SEGMENT"] && vsTarget < SETTINGS["2SEGMENT"]){
     toReturn =  SETTINGS["1SEGMENTCHANGE"]
  }else if(vsTarget >= SETTINGS["2SEGMENT"] && vsTarget < SETTINGS["3SEGMENT"]){
     toReturn =  SETTINGS["2SEGMENTCHANGE"]
  }else if(vsTarget >= SETTINGS["3SEGMENT"]){
     toReturn =  SETTINGS["3SEGMENTCHANGE"]
  }
  return toReturn;
}




function getCampaignsAndTypes(SETTINGS){
  var map = {}
  var OPTIONS = { includeZeroImpressions : false };
  var cols = ['CampaignName','AdvertisingChannelType'];
  var labels = AdWordsApp.labels().withCondition(
      "Name = 'OR'").get();  

  var reportName = 'CAMPAIGN_PERFORMANCE_REPORT';
  var query = ['select',cols.join(','),'from',reportName,
               'during',SETTINGS.DATE_RANGE].join(' ');
  

  var reportIter = AdWordsApp.report(query, OPTIONS).rows();
  while(reportIter.hasNext()) {
    var row = reportIter.next()
    map[row.CampaignName] = row.AdvertisingChannelType
  }
  
  return map
  
}

function checkAdGroups(SETTINGS){
  
  //get campaigns and types, for checking INCLUDE_settings
  if(SETTINGS.INCLUDE_SHOPPING=="Yes" &&SETTINGS.INCLUDE_TEXT=="Yes" &&SETTINGS.INCLUDE_DISPLAY=="Yes" ){
   //no checks to run, just continue 
  }else{
    //we'll need the campaign types
    var campaignTypes = getCampaignsAndTypes(SETTINGS);
    log(JSON.stringify(campaignTypes))
  }
  
  var OPTIONS = { includeZeroImpressions : false };
  var cols = ['CampaignId','AdGroupId','CampaignName','AdGroupName','ConversionValue',
              'Impressions','Clicks','Cost','Conversions', 'AveragePosition'];
  var labels = AdWordsApp.labels().withCondition(
      "Name = 'OR'").get();  

  var reportName = 'ADGROUP_PERFORMANCE_REPORT';
  var query = ['select',cols.join(','),'from',reportName,
               'where CampaignStatus = ENABLED and AdGroupStatus = ENABLED',
               // 'and AdNetworkType1 = SEARCH',
               SETTINGS.NAME_CONTAINS ? 'and CampaignName CONTAINS_IGNORE_CASE "' + SETTINGS.NAME_CONTAINS + '"' : '',
               'during',SETTINGS.DATE_RANGE].join(' ');
  
  var map = { 'ids': [], 'rows': {}};
  var reportIter = AdWordsApp.report(query, OPTIONS).rows();
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    var toChange = false;
    
    //INCLUDE_X check
    if(SETTINGS.INCLUDE_TEXT == "No" && campaignTypes[row.CampaignName] == "Search"){continue;}
    if(SETTINGS.INCLUDE_SHOPPING == "No" && campaignTypes[row.CampaignName] == "Shopping"){continue;}
    if(SETTINGS.INCLUDE_DISPLAY == "No" && campaignTypes[row.CampaignName] == "Display"){continue;}
    
    row.Impressions = parseInt(row.Impressions,10);
    row.Clicks = parseInt(row.Clicks,10);
    row.Conversions = parseFloat(row.Conversions) < 1 && parseFloat(row.Conversions) > 0 ? Math.ceil(parseFloat(row.Conversions.toString().replace(/,/g,''),10)) : Math.round(parseFloat(row.Conversions.toString().replace(/,/g,''),10));
    row.CTR = row.Impressions == 0 ? 0 : round((row.Clicks / row.Impressions),4);
    row.AveragePosition = parseFloat(row.AveragePosition);
    row.Cost = parseFloat(row.Cost.toString().replace(/,/g,''));
    row.CPA = row.Conversions == 0 ? 0 : round((row.Cost / row.Conversions),2);
    row.ConversionValue = parseFloat(row.ConversionValue.toString().replace(/,/g, ''));
    row.vpc = row.Cost == 0 ? 0 : round((row.ConversionValue / row.Cost),2);
    row.ConversionRate = row.Conversions > 0 ? row.Conversions/row.Clicks : 0;
    
    
    var minClicks = 10/(row.ConversionRate*10) > SETTINGS.MAX_MIN_CLICKS ? SETTINGS.MAX_MIN_CLICKS : 10/(row.ConversionRate*10)
    var variableMinClicks = row.AllConversions==0 ? SETTINGS.MIN_CLICKS : minClicks
    
    if(row.Clicks < variableMinClicks){
      continue;
    }
    
    //check if a new bid is needed, store it if so
    
    if(SETTINGS.MIN_CONVERSIONS == "" || row.Conversions >= SETTINGS.MIN_CONVERSIONS){

      var newBid = 0;
      if(SETTINGS.CPA_ROAS == "ROAS"){
        newBid = (row.ConversionValue/row.Clicks) * (1/SETTINGS.TARGET_ROAS);
      }else if(SETTINGS.CPA_ROAS == "CPA"){
         newBid = SETTINGS.TARGET_CPA * row.ConversionRate;
      }
      
      if((newBid > SETTINGS.MIN_BID || SETTINGS.MIN_BID=="") && (newBid < SETTINGS.MAX_BID|| SETTINGS.MAX_BID =="")){
        toChange = true;	
      }
    }
    
    
    if(toChange){
      map['ids'].push([row.CampaignId, row.AdGroupId]);
      map['rows'][row.AdGroupId] = {}
      row.newBid = newBid;
      map['rows'][row.AdGroupId]  = row;
    }
    
  }
  
  //log(JSON.stringify(map))
  //log("Num of AdGroup changes: " + map['ids'].length)
  return map;
}


function changeIsMoreThanHalfPercent(before,after){
  //check if the change is > 1% difference return true/false
  if(before/after<.995){
    return true 
  }else{
    return false
  }
}

function parseDateRange(SETTINGS) {
  var YESTERDAY = getAdWordsFormattedDate(1, 'yyyyMMdd');
  SETTINGS.DATE_RANGE = '20000101,' + YESTERDAY;
  
  if(SETTINGS.DATE_RANGE_LITERAL == 'LAST_N_DAYS') {
    SETTINGS.DATE_RANGE =  getAdWordsFormattedDate(SETTINGS.N, 'yyyyMMdd') + ',' + YESTERDAY;
  }
  
  if(SETTINGS.DATE_RANGE_LITERAL == 'LAST_N_MONTHS') {
    var now = new Date(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMM dd, yyyy HH:mm:ss'));
    now.setHours(12);
    now.setDate(0);
    
    var TO = Utilities.formatDate(now, 'PST', 'yyyyMMdd');
    now.setDate(1);
    
    var counter = 1;
    while(counter < SETTINGS.N) {
      now.setMonth(now.getMonth()-1);
      counter++;
    }
    
    var FROM = Utilities.formatDate(now, 'PST', 'yyyyMMdd');
    SETTINGS.DATE_RANGE =  FROM + ',' + TO;
  }
  
}

/**
* Get AdWords Formatted date for n days back
* @param {int} d - Numer of days to go back for start/end date
* @return {String} - Formatted date yyyyMMdd
**/
function getAdWordsFormattedDate(d, format){
  var date = new Date();
  date.setDate(date.getDate() - d);
  return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format);
}

function log(msg) {
  Logger.log(AdWordsApp.currentAccount().getName() + ' - ' + msg);
}

function round(num,n) {    
  return +(Math.round(num + "e+"+n)  + "e-"+n);
}


function getLogsColumn(controlSheet){
  var col = 5
  var logsColumn = 0;
  while(String(controlSheet.getRange(3, col).getValue())){
    logsColumn = controlSheet.getRange(3, col).getValue() == "Logs" ? col : 0;
    if(logsColumn>0){break;}
    col++;
  } 
  return logsColumn;
}


function addEditors(spreadsheet, editors){
  
  //check current editors, add if they don't exist
  var currentEditors = spreadsheet.getEditors()
  var currentEditorEmails = []
  for(var c in currentEditors){
   currentEditorEmails.push(currentEditors.getEmail().trim().toLowerCase()); 
  }
  
  if(editors==""){return;}
  if(editors.indexOf(",")>-1){
   editors = editors.split(",") 
   for(var e in editors){
    editors[e] = editors[e].trim().toLowerCase()
   }
  }else{
   editors = [editors.trim().toLowerCase()] 
  }

  for(var e in editors){
    var index = currentEditorEmails.indexOf(editors[e])
    if(currentEditorEmails.indexOf(editors[e])==-1){
      spreadsheet.addEditor(editors[e])
    }
  }
  
}

function createTabs(tabNames, logSS){
  //attempt to rename
  var logSheets = logSS.getSheets()
  for( var l in logSheets){
    var logSheet = logSheets[l]
    try{
      logSheet.setName(tabNames[l])
    }catch(e){
      
    }
  }
  //attempt to create
  for(var t in tabNames){
    var tabName = tabNames[t]
    
    try{
      logSS.insertSheet(tabName)
    }catch(e){
      
    }
    
  }
  
}

function createSheet(SETTINGS, controlSheet, logsColumn){
  var reportName = SETTINGS.NAME + ': '+INPUT_TAB_NAME;
  if(!SETTINGS.LOG_SHEET_URL) {
    var ss = SpreadsheetApp.create(reportName);
    SETTINGS.LOG_SHEET_URL = ss.getUrl();
  }
  var logSS = SpreadsheetApp.openByUrl(SETTINGS.LOG_SHEET_URL);
  if(logSS.getName()!=reportName){
    logSS.rename(reportName) 
  }
  
  //grab the editors from the sheet and add them if they don't already exist
  SETTINGS.EDITORS = controlSheet.getRange(1,logsColumn+1).getValue();
  //addEditors(logSS, SETTINGS.EDITORS)
  return logSS
}