Perform Search Query Analysis In One Google Doc

search-queries-in-google-docs

/**********************************************************************************************
* AdWords Account Management -- Review Search Queries & Post Adjustments via Google Docs. 
* Version 1.0
* Created By: Derek Martin
* DerekMartinLA.com & MixedMarketingArtist.com 
*********************************************************************************************/

var GOOGLE_DOC_URL = "put your url here";
var START_DATE = '20150401';
var END_DATE = '20150415';

function main() {
  var results = runQueryReport();
  modifySpreadSheet(results); 
}

// check a query for whether the keyword exists in the account
// returns true or false 

function keywordExists(keyword) {
  var kw = keyword;
  
  if (kw != null) {
    kwIter = AdWordsApp.keywords().withCondition("Text = \'"+kw+"\'").withCondition("Status = ENABLED").get();
    
    var exists = kwIter.totalNumEntities() > 0 ? true : false;
    return exists;
  }
}

function runQueryReport() {

    var listOfQueries = [];
  
	 var report = AdWordsApp.report(
	     'SELECT Query, CampaignName, AdGroupName, ConversionsManyPerClick, ConversionValue, Cost, AverageCpc, Clicks, Impressions, Ctr, ConversionRateManyPerClick ' +
	     'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
	     'WHERE CampaignName does_not_contain Shopping ' +
         'DURING ' + START_DATE + ',' + END_DATE +' ');    
	       
     var rows = report.rows();

	 while (rows.hasNext()) {
		   var row = rows.next();
              
           var query = row['Query'];
		   var campaign= row['CampaignName'];
           var adgroup = row['AdGroupName'];     
		   var conversions = row['ConversionsManyPerClick'];
		   var conversionValue = parseFloat(row['ConversionValue']).toPrecision(2);
		   var cost = parseFloat(row['Cost']).toPrecision(2);
           var roas = conversionValue / cost;
		   var averageCpc = row['AverageCpc'];
	       var clicks = row['Clicks'];
           var impressions = row['Impressions'];
		   var ctr = row['Ctr'];
		   var conversionRate = row['ConversionRateManyPerClick'];

		    if (query.length < 20) {
		            var keyword_exists = keywordExists(query);
		       } else {
		            var keyword_exists = false; 
		      }
		
           var queryResult = new queryData(query, campaign, adgroup, conversions, conversionValue,cost, roas,averageCpc, clicks, impressions, ctr, conversionRate, keyword_exists);
       
           listOfQueries.push(queryResult);
           
	 }  // end of report run
    
	 return listOfQueries;
     
} 

function queryData(query, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate, exists ) {
	this.query = query;
	this.campaign = campaign;
	this.adgroup = adgroup;
	this.conversions = conversions;
	this.conversionValue = conversionValue;
	this.cost = cost;
    this.roas = roas;
	this.averageCpc = averageCpc;
	this.clicks = clicks;
	this.impressions = impressions;
	this.ctr = ctr;
	this.conversionRate = conversionRate;
	this.exists = exists;
} // end of productData

function modifySpreadSheet(results) {
  
  var queryResults = results;
  
  var querySS = SpreadsheetApp.openByUrl(GOOGLE_DOC_URL);
  
  var sheet = querySS.getActiveSheet();
  
  var columnNames = ["Query", "In Account", "Campaign", "Ad Group", "Conversions", "Conversion Value", "Cost", "ROAS", "Average CPC","Clicks", "Impressions", "Ctr", "ConversionRate"];
  
  var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
  
  headersRange.setFontWeight("bold");
  headersRange.setFontSize(12);
  headersRange.setBorder(false, false, true, false, false, false);
   
   for (i = 0; i < queryResults.length; i++) {
	headersRange.setValues([columnNames]);
	
	   var query = queryResults[i].query;
       var exists = (queryResults[i].exists == true) ? "Added" : "Not Added";
       var campaign = queryResults[i].campaign;
       var adgroup = queryResults[i].adgroup;
       var conversions  = queryResults[i].conversions;
	   var conversionValue = queryResults[i].conversionValue;
	   var cost = queryResults[i].cost;
       var roas = (isNaN(queryResults[i].roas)) ? 0.00 : queryResults[i].roas;
	   var averageCpc = queryResults[i].averageCpc;
	   var clicks = queryResults[i].clicks;
	   var impressions = queryResults[i].impressions;
	   var ctr = queryResults[i].ctr;
	   var conversionRate = queryResults[i].conversionRate;
	  
      sheet.appendRow([query, exists, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate]);
    }  
  
    sheet.getRange("A2:M").setFontSize(12);
    sheet.getRange("E:E").setNumberFormat("0");
    sheet.getRange("F:G").setNumberFormat("$0.00");
    sheet.getRange("H:H").setNumberFormat("0.00");
    sheet.getRange("I:I").setNumberFormat("$0.00");

    sheet.getRange("J:K").setNumberFormat("0.00");
    sheet.getRange("A2:M")
      .sort({column: 5, ascending: false});   
    sheet.getRange("A:D").setVerticalAlignment("middle");
    sheet.getRange("A:D").setHorizontalAlignment("center");
}

// Helper functions
function warn(msg) {
  Logger.log('WARNING: '+msg);
}
 
function info(msg) {
  Logger.log(msg);
}