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.
Script last updated: February 2023
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:
</pre>
/*****
*
* AdEvolver Automated Keyword Bid Management
*
* @version: 1.2
* 1.1: performance improvements
* 1.2: removed Average Position & AdNetworkType1 from API queries
*
* AdWords script maintained on adevolver.com
*
***************************************************/
var INPUT_SHEET_URL = 'https://docs.google.com/spreadsheets/d/ENTER_YOUR_URL_HERE';
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","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" ,"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', 'CpcBid'];
var reportName = 'ADGROUP_PERFORMANCE_REPORT';
var whereArray = []
whereArray.push(' where ')
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.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.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('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', 'CpcBid'];
var reportName = 'KEYWORDS_PERFORMANCE_REPORT';
var whereArray = []
whereArray.push('where 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.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.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'];
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.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
}
<pre>