Adwords - Search Query Optimizer

/* Adwords Search Query Optimizer
 * Description: Analyze the Adwords Search Query performance.
 * Author:RitwikGA
 * Version 1.1
 * Copyright (c) 2016 Licensed under GPL licenses.
 * Mail: ritwikga@gmail.com
 */
function  main()
{
  
  var AccountName=AdWordsApp.currentAccount().getName()
 
  //Create Spreadsheet 
  var url=getSpreadsheetURL("Rutu-"+AccountName+"_Query_Optimizer");
  var spreadsheet = SpreadsheetApp.openByUrl(url)
  
  //Create Sheets
  var sh2=spreadsheet.getSheets()[0].setName("Search_Query_Optimizer")
  var sh3=getsheet(spreadsheet,"RAW_D");

 
QueryExport(sh2,sh3)
}

function QueryExport(sh2,sh3) 

{
//
var Tzone=AdWordsApp.currentAccount().getTimeZone();  
  sh2.getRange(1,1,1,1).setValue("Start Date ---->")
  sh2.getRange(1,4,1,1).setValue("<-------End Date")
  sh2.getRange("B1:C1").setBackground("#cccccc")
  sh2.getRange(3,1,1,1).setValue("Query Word Include->")
  sh2.getRange(4,1,1,1).setValue("Query Word Exclude->")
  sh2.getRange(3,2,1,1).setBackground("#cccccc")
  sh2.getRange(4,2,1,1).setBackground("#cccccc")
  
  
  
  ///Fetch Date (if Date is entered)
  var start=typeof(sh2.getRange(1,2,1,1).getValue())=="object"?Utilities.formatDate(sh2.getRange(1,2,1,1).getValue(), Tzone, "yyyyMMdd"):"LAST_7_DAYS";
  var end = typeof(sh2.getRange(1,3,1,1).getValue())=="object"?Utilities.formatDate(sh2.getRange(1,3,1,1).getValue(), Tzone, "yyyyMMdd"):""; 
  
  //Default Last 7 Days Data (if Date not Entered)  
  
  if(start=="LAST_7_DAYS")
  {
 var report = AdWordsApp.report(
    "SELECT CampaignName,AdGroupName,KeywordTextMatchingQuery,Query,Device,AdNetworkType2,Clicks,Impressions,Cost,Conversions,AveragePosition "+ 
   "FROM   SEARCH_QUERY_PERFORMANCE_REPORT "+
   " WHERE Clicks>0 AND AdGroupStatus = 'ENABLED' AND CampaignStatus = 'ENABLED' "+
   "DURING "+start);
    report.exportToSheet(sh3);
     sh2.getRange(1,6,1,1).setValue("Date Range:Last 7 Days")
  } else
  {
 var report = AdWordsApp.report(
    "SELECT CampaignName,AdGroupName,KeywordTextMatchingQuery,Query,Device,AdNetworkType2,Clicks,Impressions,Cost,Conversions,AveragePosition "+ 
   "FROM   SEARCH_QUERY_PERFORMANCE_REPORT "+
   " WHERE Clicks>0  "+
   "DURING "+start+","+end);
    report.exportToSheet(sh3);
    sh2.getRange(1,6,1,1).setValue("Date Range:"+start+" "+end)
  }

sh3.hideSheet(); 
 
 

  sh2.getRange(2,4,1,1).setValue("Query Word Contribution");
   sh2.getRange(2,4,1,1).setFontSize(12)
 sh2.getRange(2,4,1,1).setFontWeight("bold")
 sh2.getRange("D2:E2").merge();
 
  
  
  sh2.getRange(2,6,1,1).setValue("After Excluding");
   sh2.getRange(2,6,1,1).setFontSize(12)
 sh2.getRange(2,6,1,1).setFontWeight("bold")
 sh2.getRange("F2:G2").merge();
 
  
 
 
var rw1=[["%CPA Change","%Conv Change"],["=iferror($F$10/$F$7-1,0)","=iferror($C$10/$C$7-1,0)"]];
sh2.getRange(3,6,2,2).setValues(rw1);

var rw2=[["%Cost/Total","%Conv/Total"],["=iferror($B$13/$B$7,0)","=iferror($C$13/$C$7,0)"]];
sh2.getRange(3,4,2,2).setValues(rw2);
  
sh2.getRange(4,4,1,1).setBackground("#ffef6e")
sh2.getRange(4,5,1,1).setBackground("#ffef6e")
  
sh2.getRange(4,6,1,1).setBackground("#ffef6e")
sh2.getRange(4,7,1,1).setBackground("#ffef6e")

sh2.getRange("B6:H6").setBackground("#79726c");
sh2.getRange("B9:H9").setBackground("#79726c");
  sh2.getRange("B12:H12").setBackground("#79726c");
  
  sh2.getRange("B6:H6").setFontColor("#ffffff");
sh2.getRange("B9:H9").setFontColor("#ffffff");
  sh2.getRange("B12:H12").setFontColor("#ffffff");
  
sh2.setColumnWidth(1, 200);
sh2.setRowHeight(2, 40);  
sh2.setRowHeight(5, 40);  
sh2.setRowHeight(8, 40);  
sh2.setRowHeight(11, 40);  

  
sh2.getRange(6,1,1,1).setValue("Current Performance->");
  sh2.getRange(6,1,1,1).setFontSize(12)
 sh2.getRange(6,1,1,1).setFontWeight("bold")
var rw2=[["=QUERY("+sh3.getName()+"!$A:$K,\"select sum(I) label sum(I)'Total Cost'\",1)","=QUERY("+sh3.getName()+"!$A:$K,\"select sum(J) label sum(J)'Total Conv'\",1)",
          "=QUERY("+sh3.getName()+"!$A:$K,\"select sum(G) label sum(G)'Total Clicks'\",1)","=QUERY("+sh3.getName()+"!$A:$K,\"select sum(H) label sum(H)'Total Imp'\",1)"]]
sh2.getRange(6,2,1,4).setValues(rw2);

var rw22=[["Current CPA","Current CTR","Current CPC"],["=iferror(B7/C7,B7)","=iferror(D7/E7,0)","=iferror(B7/D7,0)"]]  
sh2.getRange(6,6,2,3).setValues(rw22);

//////Excluded Query///////////////
sh2.getRange(9,1,1,1).setValue("After Excluding Query->");
  sh2.getRange(9,1,1,1).setFontSize(12)
 sh2.getRange(9,1,1,1).setFontWeight("bold")
var rw3=[["Final Cost","Final Conv","Final Clicks","Final Imp","Final CPA","Final CTR","Final CPC"],["=B7-B13","=C7-C13","=D7-D13","=E7-E13","=iferror(B10/C10,B10)","=iferror(D10/E10,0)","=iferror(B10/D10,0)"]]
sh2.getRange(9,2,2,7).setValues(rw3);
//////////////
  ///////Filtered Query///////
  sh2.getRange(12,1,1,1).setValue("Filtered Query->");
  sh2.getRange(12,1,1,1).setFontSize(12)
 sh2.getRange(12,1,1,1).setFontWeight("bold")
var rw4=[["=QUERY($A$15:$E,\"select sum(B) label sum(B) 'Cost'\",1)","=QUERY($A$15:$E,\"select sum(C) label sum(C) 'Conv'\",1)",
          "=QUERY($A$15:$E,\"select sum(D) label sum(D) 'Clicks'\",1)","=QUERY($A$15:$E,\"select sum(E) label sum(E) 'Imp'\",1)"]]  
sh2.getRange(12,2,1,4).setValues(rw4);

var rw44=[["CPA","CTR","CPC"],["=iferror(B13/C13,B13)","=iferror(D13/E13,0)","=iferror(B13/D13,0)"]]  
sh2.getRange(12,6,2,3).setValues(rw44);  
/////////  
  
var rw5="=QUERY("+sh3.getName()+"!$A:$K,IF(AND($B$3=\"\",$B$4=\"\"),\"select D,sum(I),sum(J),sum(G),sum(H) group by D order by sum(I) desc label D 'Search Term' ,sum(I) 'Cost ',sum(J) ' Conv' ,sum(G) 'Clicks',sum(H) 'Imp'\",if(AND($B$3<>\"\",$B$4<>\"\"),"+
"CONCATENATE(\"select D,sum(I),sum(J),sum(G),sum(H) where D matches '.*\",$B$3,\".*' and D matches '^((?!.*\",$B$4,\".*).)*$' group by D order by sum(I) desc  label D 'Search Term' ,sum(I) 'Cost ',sum(J) ' Conv' ,sum(G) 'Clicks',sum(H) 'Imp'\"),if($B$4<>\"\","+
"CONCATENATE(\"select D,sum(I),sum(J),sum(G),sum(H) where D matches '^((?!.*\",$B$4,\".*).)*$' group by D order by sum(I) desc label D 'Search Term' ,sum(I) 'Cost ',sum(J) ' Conv' ,sum(G) 'Clicks',sum(H) 'Imp'\"),CONCATENATE(\"select D,sum(I),sum(J),sum(G),sum(H) where D matches '.*\",$B$3,\".*' group by D order by sum(I) desc  label D 'Search Term' ,sum(I) 'Cost ',sum(J) ' Conv' ,sum(G) 'Clicks',sum(H) 'Imp'\")))),1)"

sh2.getRange(15,1,1,1).setValue(rw5);
  
  
/////Format////////
  
   if(sh2.getRange("I1").getValue()!=="Formatted") {

    var formattop=[[ "%0.00", "%0.00","%0.00", "%0.00"]] 
     var leftAligntop = [[ "left", "left","left", "left"]]
  var rget = sh2.getRange("D4:G4");
rget.setNumberFormats(formattop);
rget.setHorizontalAlignments(leftAligntop);
     
     
     var format = [
   [ "0,0", "0,0","0,0","0,0","0,0","%0.00","0.00"]
 ];
      var leftAlign1 = [
   [ "left", "left", "left", "left", "left", "left", "left"]]
var rge = sh2.getRange("B7:H7");
rge.setNumberFormats(format);
rge.setHorizontalAlignments(leftAlign1);

var rge = sh2.getRange("B10:H10");
rge.setNumberFormats(format);
rge.setHorizontalAlignments(leftAlign1);

var rge = sh2.getRange("B13:H13");
rge.setNumberFormats(format);
rge.setHorizontalAlignments(leftAlign1);     
     
sh2.getRange("I1").setValue("Formatted")}  

/////
Logger.log("Reports Created for Date Range "+start+" "+end)
Logger.log("URL:"+sh2.getParent().getUrl())

}


//Get Spreadhsheet 
function getSpreadsheetURL(name)
{
  var files = DriveApp.searchFiles('title contains "'+name+'"');
  if(files.hasNext()){
     var file = files.next();
 return file.getUrl();
  } else 
 {
var sh_new=SpreadsheetApp.create(name)
return sh_new.getUrl()
 }
}

//Get Sheet
function getsheet(sht,name){
  var sh2 =sht.getSheetByName(name);
  if(sh2)
   {
    return sht.getSheetByName(name)}
    else 
    { var sh2=sht.insertSheet(name)
           return sh2 
    }  
}