Analyze your keyword potential , pause low performing keywords ,reduce your cpc & increase your conversions,etc

Adwords-Keywords-Optimizer

/* Adwords keywords Optimizer - Part 1
 * Description: Analyze the Adwords Keyword 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+"_Keywords_Performance");
  var spreadsheet = SpreadsheetApp.openByUrl(url)
  
  //Create Sheets
  var sh2=spreadsheet.getSheets()[0].setName("Keywords_Optimizer")
  var sh3=getsheet(spreadsheet,"RAW_D");

 
                                      
KeywordExport(sh2,sh3)
}

function KeywordExport(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("Campaign Filter->")
  sh2.getRange(3,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,Criteria,Device,Clicks,Impressions,SearchImpressionShare,Cost,Conversions,CostPerConversion,AveragePosition,QualityScore,EstimatedAddClicksAtFirstPositionCpc,EstimatedAddCostAtFirstPositionCpc "+ 
   "FROM   KEYWORDS_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,Criteria,Device,Clicks,Impressions,SearchImpressionShare,Cost,Conversions,CostPerConversion,AveragePosition,QualityScore "+ 
   "FROM   KEYWORDS_PERFORMANCE_REPORT "+
   " WHERE Clicks>0 AND AdGroupStatus = 'ENABLED' AND CampaignStatus = 'ENABLED' "+
   "DURING "+start+","+end);
    report.exportToSheet(sh3);
    sh2.getRange(1,6,1,1).setValue("Date Range:"+start+" "+end)
  }
  sh3.hideSheet();
  
 ///////////////New column////////////
  sh3.getRange("N1").setValue("=query(A:M,\"select E/F label E/F 'Immpression(100% Share)'\",1)")
     
sh3.getRange("AK3").setValue("=if(ISNUMBER(SEARCH(\"All\","+sh2.getName()+"!$B$4)),\"(C contains 'Computers' or C contains 'Mobile' or C contains 'Tablets')\",if(ISNUMBER(SEARCH(\"Desktop\","+sh2.getName()+"!$B$4)),\"(C contains 'Computers')\",if(ISNUMBER(SEARCH(\"Mobile\","+sh2.getName()+"!$B$4)),\"(C contains 'Mobile')\",if(ISNUMBER(SEARCH(\"Tablet\","+sh2.getName()+"!$B$4)),\"(C contains 'Tablets')\",\"\"))))")


//Validation
sh2.getRange(4,1,1,1).setValue("Devices ---->")
var cell = sh2.getRange("B4");
  cell.setValue("All");
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['All', 'Desktop','Mobile','Tablet']).setAllowInvalid(false).build();
 cell.setDataValidation(rule);


var rw1=[["=Query($A10:$H,\"select sum(C) label sum(C) 'Impressions'\",1)","=Query($A10:$H,\"select sum(B) label sum(B) 'Clicks'\",1)","=Query($A10:$H,\"select sum(B)/sum(C) label sum(B)/sum(C) 'CTR'\",1)",
          "=Query($A10:$H,\"select sum(F)/sum(B) label sum(F)/sum(B) 'Conv.Rate'\",1)","=Query($A10:$H,\"select sum(F) label sum(F) 'Conv'\",1)","=Query($A10:$H,\"select sum(H) where F > 0 label sum(H) 'Imp. Possible'\",1)",
 "=Query($O10:$Q,\"select sum(O) label sum(O) 'Conv. Possible'\",1)"]]

sh2.getRange(6,1,1,7).setValues(rw1);
sh2.getRange(6,8,1,1).setValue("%Conv Change");
sh2.getRange(7,8,1,1).setValue("=G7/E7-1");
sh2.getRange(7,8,1,1).setBackground("#ffef6e")
  
var tb1="=QUERY("+sh3.getName()+"!$A:$N,if(B3=\"\",CONCATENATE(\"select B,sum(D),sum(E),sum(G),sum(G)/sum(D),sum(H),sum(G)/sum(H),sum(N) where \","+sh3.getName()+"!AK3,\" group by B order by sum(H) desc label B 'Keywords',sum(D) 'Clicks' ,sum(E) 'Imp.', sum(G) 'Cost', sum(G)/sum(D) 'CPC', sum(H) 'Conv', sum(G)/sum(H) 'CPA' ,sum(N) 'Imp. Possible'\"),CONCATENATE(\"select B,sum(D),sum(E),sum(G),sum(G)/sum(D),sum(H),sum(G)/sum(H),sum(N) where \","+sh3.getName()+"!AK3,\" and A contains '\",B3,\"' group by B order by sum(H) desc label B 'Keywords',sum(D) 'Clicks' ,sum(E) 'Imp.', sum(G) 'Cost', sum(G)/sum(D) 'CPC', sum(H) 'Conv', sum(G)/sum(H) 'CPA' ,sum(N) 'Imp. Possible'\")),1)"  
sh2.getRange(11,1,1,1).setValue(tb1);

var tb2="=QUERY($A11:$H,\"select A,sum(F),sum(C),sum(C)/sum(H),sum(H),sum(F)*sum(H)/sum(C),sum(D)/sum(C),sum(F)/sum(B) where F > 0 group by A label A 'Keywords',sum(F) 'Conv.',sum(C) 'Imp.',sum(C)/sum(H) 'Imp.Share',sum(H) 'Imp. Possible',sum(F)*sum(H)/sum(C) 'Conv. Possible',sum(D)/sum(C) 'CPA',sum(F)/sum(B) 'Conv.Rate'\",1)"  
sh2.getRange(11,10,1,1).setValue(tb2);

sh2.getRange(10,10,1,1).setValue("Scalable Keywords")  
sh2.getRange(10,10,1,1).setFontSize(14)
 sh2.getRange(10,10,1,1).setFontWeight("bold")

 sh2.getRange(10,1,1,1).setValue("Keywords Data")  
sh2.getRange(10,1,1,1).setFontSize(14)
 sh2.getRange(10,1,1,1).setFontWeight("bold")
sh2.setColumnWidth(9, 15);

/////Format////////
  
   if(sh2.getRange("I1").getValue()!=="Formatted") {
var format = [
   [ "0,0", "0,0","%0.00","%0.00","0","0","0","%0.00"]
 ];
      var leftAlign1 = [
   [ "left", "left", "left", "left", "left", "left", "left", "left"]]
    var rge = sh2.getRange("A7:H7");
 rge.setNumberFormats(format);
rge.setHorizontalAlignments(leftAlign1);

    var lastrw = sh2.getRange("A11:A").getValues().filter(String).length+11;
var formats = [
   [ "0,0", "0,0","0,0","0.0", "0","0","0.0","","","0","0","%0.00","0.0","0.0","0","%0.00"]
 ];
 var leftAlign = [
   [ "left", "left", "left", "left", "left", "left", "left", "left", "left", "left", "left", "left", "left", "left", "left", "left" ]
 ];    
for(var i=12;i<lastrw;i++)
{ var range = sh2.getRange("B"+i+":Q"+i);
 range.setNumberFormats(formats);
range.setHorizontalAlignments(leftAlign);
 
} 
 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 
    }  
}