Directions:

1.) Find var reportUrl = ‘SPREADSHEET URL GOES HERE’;
Replace SPREADSHEET URL GOES HERE with a link to a blank Google spreadsheet. Link must go in between quotes.
2.) (Optional) Find var LAST_N_DAYS = 14;

If you wish, change var LAST_N_DAYS = 14 to the number of days back you would like to see data. Right now it’s set to the previous 14 days.

3.) Load script to your account and preview before running. f you’re not sure how to load the script, you can find directions here.

 

var reportUrl = 'SPREADSHEET URL GOES HERE';
var sheetName = 'First & Top of Page Bid Gap'
var reportSheetName = 'Keyword Cpc Report'
var LAST_N_DAYS = 14;
var includeZeroImpressions = false;

function main() {
  
  var to = getAdWordsFormattedDate(1, 'yyyyMMdd');
  var from = getAdWordsFormattedDate(LAST_N_DAYS, 'yyyyMMdd');
  
  var dateTo = getAdWordsFormattedDate(1, 'MMM dd, yyyy');
  var dateFrom = getAdWordsFormattedDate(LAST_N_DAYS, 'MMM dd, yyyy');
  
  var reportHead = 'First & Top of Page Bid Gap report (' + dateFrom + ' - ' + dateTo + ')';
  
  //var cpcMap = getAdGroupCpc();
  var cpcMap = {};
  
  //Header Row
  var header = ['First Page Bid Gap','# of KWs','Converted Clicks Past ' + LAST_N_DAYS + ' Days','Cost Past 30 Days','Cost / converted click','','',
                'Top of Page Bid Gap','# of KWs','Converted Clicks Past ' + LAST_N_DAYS + ' Days','Cost Past 30 Days','Cost / converted click'];
  var data = [header];
  
  var columns = ['CampaignId','CampaignName','AdGroupId','AdGroupName','Id','KeywordText','KeywordMatchType','Impressions',
                 'Clicks','Cost','Conversions','MaxCpc','FirstPageCpc','TopOfPageCpc'];
  var query = 'select ' + columns.join(',') + ' from KEYWORDS_PERFORMANCE_REPORT during ' + from + ',' + to;
  var report = AdWordsApp.report(query, { includeZeroImpressions: includeZeroImpressions }).rows();
  
  var firstPageGap = {};
  var topPageGap = {};
  var rangeArray = ['< $.10', '$.10-$.25', '$.26-$.50', '$.51-$1.00', '$1.01-$1.50', '$1.51-$2.00','> $2.00'];
  initializeMaps(rangeArray, firstPageGap, topPageGap);  
  var reportData = [['Campaign', 'AdGroup', 'Keyword', 'Match Type', 'Impressions', 'Clicks', 'Cost', 'Conversions', 
					 'Max Cpc', 'First Page Cpc', 'Top of Page Cpc', 'Bid Increase to Reach First Page', 'Bid Increase to Reach Top Of Page']];
  
  while(report.hasNext()){
    var row = report.next();
    
    var rowData = new Object();
    rowData.conversions = parseInt(row['Conversions']);
    rowData.cost = parseFloat(row['Cost']);
    
    var maxCpc = parseFloat(row['MaxCpc'].replace('auto: ',''));
    
    if(isNaN(maxCpc)) {
      if(!cpcMap[row.AdGroupId]) {
        var ag = AdWordsApp.adGroups().withIds([row.AdGroupId]).get().next();
        cpcMap[row.AdGroupId] = ag.getKeywordMaxCpc();
      }
      maxCpc = cpcMap[row.AdGroupId];
    }
    
    if(!maxCpc) { continue; }
        
    var firstPageDiff = parseFloat(row['FirstPageCpc']) - maxCpc;
    var topPageDiff = parseFloat(row['TopOfPageCpc']) - maxCpc;
    
	if(firstPageDiff > 0 || topPageDiff > 0) {
		reportData.push([row.CampaignName, row.AdGroupName, row.KeywordText, row.KeywordMatchType, row.Impressions, row.Clicks, rowData.cost, 
                         rowData.conversions, row.MaxCpc, row.FirstPageCpc, row.TopOfPageCpc, firstPageDiff, topPageDiff]);
	}
    if(firstPageDiff > 0) {	addToMap(rowData, firstPageDiff, firstPageGap);	}	
    if(topPageDiff > 0) { addToMap(rowData, topPageDiff, topPageGap); }
  }  
  
  for(var i in rangeArray){
    var key = rangeArray[i];
    var map1 = firstPageGap[key];
    var cpa1 = (map1.conversions == 0 ) ? 0 : ( map1.cost / map1.conversions).toFixed(2);
    var map2 = topPageGap[key];
    var cpa2 = (map2.conversions == 0 ) ? 0 : ( map2.cost / map2.conversions).toFixed(2);
    
    data.push([key, map1.count, map1.conversions, map1.cost, cpa1, '','',
               key, map2.count, map2.conversions, map2.cost, cpa2]);
  }
  
  //Connect to Report Spreadsheet
  var spreadsheet = SpreadsheetApp.openByUrl(reportUrl);
  
  var sheet = spreadsheet.getSheetByName(sheetName);
  if(!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  var reportSheet = spreadsheet.getSheetByName(reportSheetName);
  if(!reportSheet) {
    reportSheet = spreadsheet.insertSheet(reportSheetName);
  }
  
  var trash = spreadsheet.getSheetByName('Sheet1');
  if(trash) { spreadsheet.deleteSheet(trash); }
  
  //Push data to spreadhseet
  reportSheet.clear();
  reportSheet.getRange(1,1,1,1).setValue(reportHead);
  reportSheet.getRange(1,1,1,1).setFontWeight('bold');
  reportSheet.setFrozenRows(2);
  reportSheet.getRange(2,1,reportData.length,reportData[0].length).setValues(reportData);
    
  //Format Rows	
  var numRows = reportSheet.getDataRange().getNumRows();
  var lastCol = reportSheet.getLastColumn();	
  reportSheet.getRange(2,1,1,reportData[0].length).setVerticalAlignment("middle").setHorizontalAlignment("center").setBackground('#b6d7a8').setFontWeight('bold').setWrap(true);
  reportSheet.getRange(3, 5, numRows, 1).setNumberFormat("#,##0"); //Impressions
  reportSheet.getRange(3, 6, numRows, 1).setNumberFormat("#,##0"); //Clicks
  reportSheet.getRange(3, 7, numRows, 1).setNumberFormat("$#,##0.00"); //Cost
  reportSheet.getRange(3, 8, numRows, 1).setNumberFormat("#,##0"); //Conversions
  reportSheet.getRange(3, 10, numRows, 1).setNumberFormat("$#,##0.00"); // First Page Cpc
  reportSheet.getRange(3, 11, numRows, 1).setNumberFormat("$#,##0.00"); // Top Of Page Cpc
  reportSheet.getRange(3, 12, numRows, 1).setNumberFormat("#,##0.00"); // Diff
  reportSheet.getRange(3, 13, numRows, 1).setNumberFormat("#,##0.00"); // Diff
  
  if((reportSheet.getMaxColumns() - reportSheet.getLastColumn()) > 0) {
    reportSheet.deleteColumns(reportSheet.getLastColumn()+1, reportSheet.getMaxColumns() - reportSheet.getLastColumn());
  }
  
  sheet.clear();
  sheet.getRange(1,1,1,1).setValue(reportHead);
  sheet.getRange(1,1,1,1).setFontWeight('bold');
  sheet.getRange(2,2,data.length, data[0].length).setValues(data);
  
  sheet.setFrozenRows(2);
  
  //Format Rows	
  var numRows = sheet.getDataRange().getNumRows();
  var lastCol = sheet.getLastColumn();	
  sheet.getRange(2,2,1,5).setVerticalAlignment("middle").setHorizontalAlignment("center").setBackground('#b6d7a8').setFontWeight('bold').setWrap(true);
  sheet.getRange(2,9,1,5).setVerticalAlignment("middle").setHorizontalAlignment("center").setBackground('#b6d7a8').setFontWeight('bold').setWrap(true);
  
  sheet.getRange(3, 3, numRows, 1).setNumberFormat("#,##0"); //Count of Kws
  sheet.getRange(3, 4, numRows, 1).setNumberFormat("#,##0"); //Conversions
  sheet.getRange(3, 5, numRows, 1).setNumberFormat("$#,##0.00"); //Cost
  sheet.getRange(3, 6, numRows, 1).setNumberFormat("$#,##0.00"); // CPA
  
  sheet.getRange(3, 10, numRows, 1).setNumberFormat("#,##0"); //Count of Kws
  sheet.getRange(3, 11, numRows, 1).setNumberFormat("#,##0"); //Conversions
  sheet.getRange(3, 12, numRows, 1).setNumberFormat("$#,##0.00"); //Cost
  sheet.getRange(3, 13, numRows, 1).setNumberFormat("$#,##0.00"); // CPA
  
  //Remove all the embedded charts from the spreadsheet
  var charts = sheet.getCharts();
  for (var i in charts) {
    sheet.removeChart(charts[i]);
  }
  
  //Build Range	1
  var range1 = sheet.getRange('B2:B9');
  var range2 = sheet.getRange('C2:C9');
  var range3 = sheet.getRange('F2:F9');  
  
  //Build Chart	 1
  var chart = sheet.newChart()
  .asColumnChart()
  .addRange(range1)
  .addRange(range2)
  .addRange(range3)
  .setOption('useFirstColumnAsDomain', 'true')
  .setOption('legend', {position: 'bottom'})
  .setOption('vAxes', {0: {title:'Keywords', minValue:0}, 1: {title:'Cost/Converted Click', minValue:0}})
  .setOption('series', {0: {targetAxisIndex:0}, 1:{targetAxisIndex:1}, 2:{targetAxisIndex:1}})
  .setOption('hAxis.title', 'First Page Bid Gap')
  .setTitle('# of KWs & Cost per Converted Click vs First Page Bid Gap')
  .setPosition(11, 1, 0, 0)
  .build();
  
  //Insert Chart 1	
  sheet.insertChart(chart);
  
  //Build Range	2
  var range1 = sheet.getRange('B2:B9');
  var range2 = sheet.getRange('C2:C9');
  var range3 = sheet.getRange('D2:D9');  
  
  //Build Chart	2
  var chart = sheet.newChart()
  .asColumnChart()
  .addRange(range1)
  .addRange(range2)
  .addRange(range3)
  .setOption('useFirstColumnAsDomain', 'true')
  .setOption('legend', {position: 'bottom'})
  .setOption('vAxes', {0: {title:'Keywords', minValue:0}, 1: {title:'Converted Clicks', minValue:0}})
  .setOption('series', {0: {targetAxisIndex:0}, 1:{targetAxisIndex:1}, 2:{targetAxisIndex:1}})
  .setOption('hAxis.title', 'First Page Bid Gap')
  .setTitle('# of KWs & Converted Clicks vs First Page Bid Gap')
  .setPosition(30, 1, 0, 0)
  .build();
  
  //Insert Chart 2
  sheet.insertChart(chart);
  
  //Build Range	3
  var range1 = sheet.getRange('I2:I9');
  var range2 = sheet.getRange('J2:J9');
  var range3 = sheet.getRange('M2:M9');  
  
  //Build Chart	3
  var chart = sheet.newChart()
  .asColumnChart()
  .addRange(range1)
  .addRange(range2)
  .addRange(range3)
  .setOption('useFirstColumnAsDomain', 'true')
  .setOption('legend', {position: 'bottom'})
  .setOption('vAxes', {0: {title:'Keywords', minValue:0}, 1: {title:'Cost/Converted Click', minValue:0}})
  .setOption('series', {0: {targetAxisIndex:0}, 1:{targetAxisIndex:1}, 2:{targetAxisIndex:1}})
  .setOption('hAxis.title', 'Top Of Page Bid Gap')
  .setTitle('# of KWs & Cost per Converted Click vs Top of Page Bid Gap')
  .setPosition(11, 9, 0, 0)
  .build();
  
  //Insert Chart 3	
  sheet.insertChart(chart);
  
  //Build Range	4
  var range1 = sheet.getRange('I2:I9');
  var range2 = sheet.getRange('J2:J9');
  var range3 = sheet.getRange('K2:K9');   
  
  //Build Chart	4 
  var chart = sheet.newChart()
  .asColumnChart()
  .addRange(range1)
  .addRange(range2)
  .addRange(range3)
  .setOption('useFirstColumnAsDomain', 'true')
  .setOption('legend', {position: 'bottom'})
  .setOption('vAxes', {0: {title:'Keywords', minValue:0}, 1: {title:'Converted Clicks', minValue:0}})
  .setOption('series', {0: {targetAxisIndex:0}, 1:{targetAxisIndex:1}, 2:{targetAxisIndex:1}})
  .setOption('hAxis.title', 'Top Of Page Bid Gap')
  .setTitle('# of KWs & Converted Clicks vs Top of Page Bid Gap')
  .setPosition(30, 9, 0, 0)
  .build();
  
  //Insert Chart 4	
  sheet.insertChart(chart);
  
  if((sheet.getMaxColumns() - sheet.getLastColumn() - 1) > 0) {
    sheet.deleteColumns(sheet.getLastColumn()+2, sheet.getMaxColumns() - sheet.getLastColumn() - 1);
  }
}

function addToMap(row,diff,map) {
  
  if(diff < 0.10) {
    var range = '< $.10';
  } else if(diff >= 0.10 && diff <= 0.25) {
    var range = '$.10-$.25';
  } else if(diff >= 0.26 && diff <= 0.5) {
    var range = '$.26-$.50';
  } else if(diff >= 0.51 && diff <= 1) {
    var range = '$.51-$1.00';
  } else if(diff >= 1.01 && diff <= 1.5) {
    var range = '$1.01-$1.50';
  } else if(diff >= 1.51 && diff <= 2) {
    var range = '$1.51-$2.00';
  } else {
    var range = '> $2.00';
  }
  
  map[range].count += 1;
  map[range].conversions += row.conversions;
  map[range].cost += row.cost;	
}

function initializeMaps(rangeArray, map1, map2){
  for(var i in rangeArray) {
    if(!map1[rangeArray[i]]) {
      map1[rangeArray[i]] = { count: 0, conversions: 0, cost: 0 }
    } 
    if(!map2[rangeArray[i]]) {
      map2[rangeArray[i]] = { count: 0, conversions: 0, cost: 0 }
    }
  }	
}

/*function getAdGroupCpc() {
var map = {};
var adGroups = AdWordsApp.adGroups().get();
while(adGroups.hasNext()){
var ag = adGroups.next();
map[ag.getId()] = ag.getKeywordMaxCpc();
}
return map;
}*/

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