/* Adwords CPA Scanner
 * Description: Scan Campaigns,AdGroups,Devices,AdNetworks for High CPAs.
 * Author:RitwikGA 
 * Copyright 2016 v1.1
 * Digishuffle.com 
 */
function  main()
{
  
  var AccountName=AdWordsApp.currentAccount().getName()
 
  //Create Spreadsheet 
  var url=getSpreadsheetURL("Rutu-"+AccountName+"-CPA-Analyzer2");
  var spreadsheet = SpreadsheetApp.openByUrl(url)
  
  //Create Sheets
  var sh2=spreadsheet.getSheets()[0].setName("CPA-Campaigns");
  var sh3=getsheet(spreadsheet,"RAW_D"); 
  var sh4=getsheet(spreadsheet,"CPA-AdGroups");
  var sh5=getsheet(spreadsheet,"RAW_C");
  var sh6=getsheet(spreadsheet,"CPA-City");
  cpaScanner(sh2,sh3,sh4,sh5,sh6)
  
}

function cpaScanner(sh2,sh3,sh4,sh5,sh6) 

{
//
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")
   
  
  ///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,Device,Clicks,Impressions,Clicks,Cost,Conversions,AdNetworkType2 "+ 
   "FROM   KEYWORDS_PERFORMANCE_REPORT  "+
   " WHERE Clicks>0 "+
   "DURING "+start);
    report.exportToSheet(sh3);
          
  var report1 = AdWordsApp.report(
    "SELECT CampaignName,CountryCriteriaId,CityCriteriaId,Clicks,Impressions,Cost,Conversions "+ 
   "FROM   GEO_PERFORMANCE_REPORT "+
   " WHERE Clicks>0 "+
   "DURING "+start);
    report1.exportToSheet(sh5);

    sh2.getRange(1,6,1,1).setValue("Date Range:Last 7 Days")
    sh4.getRange(1,6,1,1).setValue("Date Range:Last 7 Days")
    sh6.getRange(1,6,1,1).setValue("Date Range:Last 7 Days") 
     
  } else
  {
    
 var report = AdWordsApp.report(
    "SELECT CampaignName,AdGroupName,Device,Clicks,Impressions,Clicks,Cost,Conversions,AdNetworkType2 "+ 
   "FROM   KEYWORDS_PERFORMANCE_REPORT "+
   " WHERE Clicks>0  "+
   "DURING "+start+","+end);
    report.exportToSheet(sh3);
    
    
    var report1 = AdWordsApp.report(
   "SELECT CampaignName,CountryCriteriaId,CityCriteriaId,Clicks,Impressions,Cost,Conversions "+ 
   "FROM   GEO_PERFORMANCE_REPORT "+
   "WHERE Clicks>0 "+
   "DURING "+start+","+end);
    report1.exportToSheet(sh5);
  
    sh2.getRange(1,6,1,1).setValue("Date Range:"+start+" "+end)
    sh4.getRange(1,6,1,1).setValue("Date Range:"+start+" "+end)
    sh6.getRange(1,6,1,1).setValue("Date Range:"+start+" "+end)  
      
  }

  sh3.hideSheet(); 
  sh5.hideSheet();

var Q1="=QUERY(RAW_D!$A:$H,\"select A,sum(E),sum(D),sum(D)/sum(E),sum(F)/sum(D),sum(F),sum(G),sum(F)/sum(G),sum(G)/sum(D) group by A order by sum(F) desc "+ 
       "label A 'Campaigns', sum(E) 'Imp',sum(D) 'Clicks',sum(D)/sum(E) 'CTR',sum(F)/sum(D) 'CPC',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(G)/sum(D) 'Conv. Rate' "+ 
       "format sum(E) '0',sum(D) '0',sum(D)/sum(E) '%0.0',sum(F)/sum(D) '0.0',sum(F) '0',sum(G) '0',sum(F)/sum(G) '0.0',sum(G)/sum(D) '%0.0'\",1)"


var Q2="=QUERY(RAW_D!$A:$H,\"select A,C,sum(E),sum(D),sum(D)/sum(E),sum(F)/sum(D),sum(F),sum(G),sum(F)/sum(G),sum(G)/sum(D) group by A,C order by sum(F) desc "+
       "label A 'Campaigns', sum(E) 'Imp',sum(D) 'Clicks',sum(D)/sum(E) 'CTR',sum(F)/sum(D) 'CPC',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(G)/sum(D) 'Conv. Rate' "+
        "format sum(E) '0',sum(D) '0',sum(D)/sum(E) '%0.0',sum(F)/sum(D) '0.0',sum(F) '0',sum(G) '0',sum(F)/sum(G) '0.0',sum(G)/sum(D) '%0.0'\",1)"

var Q3="=QUERY(RAW_D!$A:$H,\"select A,H,sum(E),sum(D),sum(D)/sum(E),sum(F)/sum(D),sum(F),sum(G),sum(F)/sum(G),sum(G)/sum(D) group by A,H order by sum(F) desc "+
       "label A 'Campaigns',H 'AdNetwork', sum(E) 'Imp',sum(D) 'Clicks',sum(D)/sum(E) 'CTR',sum(F)/sum(D) 'CPC',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(G)/sum(D) 'Conv. Rate' "+
        "format sum(E) '0',sum(D) '0',sum(D)/sum(E) '%0.0',sum(F)/sum(D) '0.0',sum(F) '0',sum(G) '0',sum(F)/sum(G) '0.0',sum(G)/sum(D) '%0.0'\",1)"      

var Q4="=QUERY(RAW_D!$A:$H,\"select A,B,sum(E),sum(D),sum(D)/sum(E),sum(F)/sum(D),sum(F),sum(G),sum(F)/sum(G),sum(G)/sum(D) group by A,B order by sum(F) desc "+
       "label A 'Campaigns',B 'AdGroup', sum(E) 'Imp',sum(D) 'Clicks',sum(D)/sum(E) 'CTR',sum(F)/sum(D) 'CPC',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(G)/sum(D) 'Conv. Rate' "+
        "format sum(E) '0',sum(D) '0',sum(D)/sum(E) '%0.0',sum(F)/sum(D) '0.0',sum(F) '0',sum(G) '0',sum(F)/sum(G) '0.0',sum(G)/sum(D) '%0.0'\",1)"
       
var Q5="=QUERY(RAW_D!$A:$H,\"select A,B,C,sum(E),sum(D),sum(D)/sum(E),sum(F)/sum(D),sum(F),sum(G),sum(F)/sum(G),sum(G)/sum(D) group by A,B,C order by sum(F) desc "+
       "label A 'Campaigns',B 'AdGroup', sum(E) 'Imp',sum(D) 'Clicks',sum(D)/sum(E) 'CTR',sum(F)/sum(D) 'CPC',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(G)/sum(D) 'Conv. Rate' "+
        "format sum(E) '0',sum(D) '0',sum(D)/sum(E) '%0.0',sum(F)/sum(D) '0.0',sum(F) '0',sum(G) '0',sum(F)/sum(G) '0.0',sum(G)/sum(D) '%0.0'\",1)"       

       
sh3.getRange("L2").setValue(Q1)
sh3.getRange("V2").setValue(Q2)
sh3.getRange("AG2").setValue(Q3)
sh3.getRange("AR2").setValue(Q4)
sh3.getRange("BC2").setValue(Q5)


  sh2.getRange(4,1,1,1).setValue("Enter CPA ---->")
  sh2.getRange("B4").setBackground("#FFFF33");
  
  sh2.getRange("A6").setValue("Campaign - CPA Scanner") 
  sh2.getRange("A6").setFontSize(14);
  sh2.getRange("A6").setFontWeight("bold")
  sh2.getRange("A6").setHorizontalAlignment("center")
  sh2.getRange("A6:I7").setBorder(true, true, true, true, true, true)
  sh2.getRange("A6").setBackground("#336DCB");
  sh2.getRange("A7:I7").setBackground("#336DCB");
  sh2.getRange("A6").setFontColor("#FFFFFF"); 
  sh2.getRange("A7:I7").setFontColor("#FFFFFF"); 
  sh2.getRange("A6:I6").merge()
  sh2.setRowHeight(6, 40);


  
  sh2.getRange("K6").setValue("Campaign - > Device - CPA Scanner "); 
  sh2.getRange("K6").setFontSize(14);
  sh2.getRange("K6").setFontWeight("bold")
  sh2.getRange("K6").setHorizontalAlignment("center")
  sh2.getRange("K6:T7").setBorder(true, true, true, true, true, true)
  sh2.getRange("K6").setBackground("#336DCB");
  sh2.getRange("K7:T7").setBackground("#336DCB");
  sh2.getRange("K6").setFontColor("#FFFFFF"); 
  sh2.getRange("K7:T7").setFontColor("#FFFFFF"); 
  sh2.getRange("K6:T6").merge()
 
  
  sh2.getRange("V6").setValue("Campaign - > AdNetworks - CPA Scanner") 
  sh2.getRange("V6").setFontSize(14);
  sh2.getRange("V6").setFontWeight("bold")
  sh2.getRange("V6").setHorizontalAlignment("center")
  sh2.getRange("V6:AE7").setBorder(true, true, true, true, true, true)
  sh2.getRange("V6").setBackground("#336DCB");
  sh2.getRange("V7:AE7").setBackground("#336DCB");
  sh2.getRange("V6").setFontColor("#FFFFFF"); 
  sh2.getRange("V7:AE7").setFontColor("#FFFFFF"); 
  sh2.getRange("V6:AE6").merge()
 
  

var Q6="=if($B$4=\"\",QUERY(RAW_D!$L:$T,\"select L,M,N,O,P,Q,R,S,T format M '0',N '0',O '%0.0',P '0.0',Q '0',R '0',S '0.0',T '%0.0'\",2),"+
       "QUERY(RAW_D!$L:$T,CONCATENATE(\"select L,M,N,O,P,Q,R,S,T where ( S > \",$B$4,\" OR (R=0 AND Q > \",$B$4,\" )) format M '0',N '0',O '%0.0',P '0.0',Q '0',R '0',S '0.0',T '%0.0'\"),2))"


var Q7="=if($B$4=\"\",QUERY(RAW_D!$V:$AE,\"select V,W,X,Y,Z,AA,AB,AC,AD,AE format X '0',Y '0',Z '%0.0',AA '0.0',AB '0',AC '0',AD '0.0',AE '%0.0'\",2),"+
       "QUERY(RAW_D!$V:$AE,CONCATENATE(\"select V,W,X,Y,Z,AA,AB,AC,AD,AE where (AD >  \",$B$4,\" OR (AC = 0 AND AB > \",$B$4,\") ) format X '0',Y '0',Z '%0.0',AA '0.0',AB '0',AC '0',AD '0.0',AE '%0.0'\"),2))"

var Q8="=if($B$4=\"\",QUERY(RAW_D!$AG:$AP,\"select AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP format AI '0',AJ '0',AK '%0.0',AL '0.0',AM '0',AN '0',AO '0.0',AP '%0.0'\",2),"+
       "QUERY(RAW_D!$AG:$AP,CONCATENATE(\"select AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP where ( AO >  \",$B$4,\" OR ( AN = 0 AND AM > \",$B$4,\" )) format AI '0',AJ '0',AK '%0.0',AL '0.0',AM '0',AN '0',AO '0.0',AP '%0.0'\"),2))"


sh2.getRange("A7").setValue(Q6)       
sh2.getRange("K7").setValue(Q7)
sh2.getRange("V7").setValue(Q8)
       

  sh4.getRange(4,1,1,1).setValue("Enter CPA ---->")
  sh4.getRange("B4").setBackground("#FFFF33");
  sh4.getRange(4,2,1,1).setValue("='"+sh2.getSheetName()+"'!B4")
  
  sh4.getRange("A6").setValue("Campaign -> AdGroup - CPA Scanner") 
  sh4.getRange("A6").setFontSize(14);
  sh4.getRange("A6").setFontWeight("bold")
  sh4.getRange("A6").setHorizontalAlignment("center")
  sh4.getRange("A6:J7").setBorder(true, true, true, true, true, true)
  sh4.getRange("A6").setBackground("#336DCB");
  sh4.getRange("A7:J7").setBackground("#336DCB");
  sh4.getRange("A6").setFontColor("#FFFFFF"); 
  sh4.getRange("A7:J7").setFontColor("#FFFFFF"); 
  sh4.getRange("A6:J6").merge()
  sh4.setRowHeight(6, 40);


  
  sh4.getRange("L6").setValue("Campaign -> AdGroup -> Device - CPA Scanner "); 
  sh4.getRange("L6").setFontSize(14);
  sh4.getRange("L6").setFontWeight("bold")
  sh4.getRange("L6").setHorizontalAlignment("center")
  sh4.getRange("L6:V7").setBorder(true, true, true, true, true, true)
  sh4.getRange("L6").setBackground("#336DCB");
  sh4.getRange("L7:V7").setBackground("#336DCB");
  sh4.getRange("L6").setFontColor("#FFFFFF"); 
  sh4.getRange("L7:V7").setFontColor("#FFFFFF"); 
  sh4.getRange("L6:V6").merge()
 



 
var Q9="=if($B$4=\"\",QUERY(RAW_D!$AR:$BA,\"select AR,AS,AT,AU,AV,AW,AX,AY,AZ,BA format AT '0',AU '0',AV '%0.0',AW '0.0',AX '0',AY '0',AZ '0.0',BA '%0.0'\",2),"+
       "QUERY(RAW_D!$AR:$BA,CONCATENATE(\"select AR,AS,AT,AU,AV,AW,AX,AY,AZ,BA where ( AZ >  \",$B$4,\" OR ( AY = 0 AND AX > \",$B$4,\" )) format AT '0',AU '0',AV '%0.0',AW '0.0',AX '0',AY '0',AZ '0.0',BA '%0.0'\"),2))"


var Q10="=if($B$4=\"\",QUERY(RAW_D!$BC:$BM,\"select BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM format BF '0',BG '0',BH '%0.0',BI '0.0',BJ '0',BK '0',BL '0.0',BM '%0.0'\",2),"+
        "QUERY(RAW_D!$BC:$BM,CONCATENATE(\"select BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM where ( BL >  \",$B$4,\" OR ( BK = 0 AND BJ > \",$B$4,\" )) format BF '0',BG '0',BH '%0.0',BI '0.0',BJ '0',BK '0',BL '0.0',BM '%0.0'\"),2))"


sh4.getRange("A7").setValue(Q9)       
sh4.getRange("L7").setValue(Q10)  


  
  var Q11="=QUERY($A:$G,\"select B,C,sum(E),sum(D),sum(D)/sum(E),sum(F)/sum(D),sum(F),sum(G),sum(F)/sum(G),sum(G)/sum(D) group by B,C order by sum(F) desc "+
          "label B 'Country',C 'City',sum(E) 'Imp',sum(D) 'Clicks',sum(D)/sum(E) 'CTR',sum(F)/sum(D) 'CPC',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(G)/sum(D) 'Conv. Rate'\",1)"

 
  var Q12="=QUERY($A:$G,\"select A,C,sum(E),sum(D),sum(D)/sum(E),sum(F)/sum(D),sum(F),sum(G),sum(F)/sum(G),sum(G)/sum(D) group by A,C order by sum(F) desc "+
          "label A 'Campaign',C 'City',sum(E) 'Imp',sum(D) 'Clicks',sum(D)/sum(E) 'CTR',sum(F)/sum(D) 'CPC',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(G)/sum(D) 'Conv. Rate'\",1)"


  sh5.getRange("J2").setValue(Q11)
  sh5.getRange("U2").setValue(Q12)
  
  
  sh6.getRange(4,1,1,1).setValue("Enter CPA ---->")
  sh6.getRange("B4").setBackground("#FFFF33");
  sh6.getRange(4,2,1,1).setValue("='"+sh2.getSheetName()+"'!B4")
  
  sh6.getRange("A6").setValue("Country -> City - CPA Scanner") 
  sh6.getRange("A6").setFontSize(14);
  sh6.getRange("A6").setFontWeight("bold")
  sh6.getRange("A6").setHorizontalAlignment("center")
  sh6.getRange("A6:J7").setBorder(true, true, true, true, true, true)
  sh6.getRange("A6").setBackground("#336DCB");
  sh6.getRange("A7:J7").setBackground("#336DCB");
  sh6.getRange("A6").setFontColor("#FFFFFF"); 
  sh6.getRange("A7:J7").setFontColor("#FFFFFF"); 
  sh6.getRange("A6:J6").merge()
  sh6.setRowHeight(6, 40);


  
  sh6.getRange("L6").setValue("Campaign -> City - CPA Scanner"); 
  sh6.getRange("L6").setFontSize(14);
  sh6.getRange("L6").setFontWeight("bold")
  sh6.getRange("L6").setHorizontalAlignment("center")
  sh6.getRange("L6:U7").setBorder(true, true, true, true, true, true)
  sh6.getRange("L6").setBackground("#336DCB");
  sh6.getRange("L7:U7").setBackground("#336DCB");
  sh6.getRange("L6").setFontColor("#FFFFFF"); 
  sh6.getRange("L7:U7").setFontColor("#FFFFFF"); 
  sh6.getRange("L6:U6").merge()
 
  
  var Q13="=if($B$4=\"\",QUERY(RAW_C!$J:$S,\"select J,K,L,M,N,O,P,Q,R,S format L '0',M '0',N '%0.0',O '0.0',P '0',Q '0',R '0.0',S '%0.0'\",2),"+
          "QUERY(RAW_C!$J:$S,CONCATENATE(\"select J,K,L,M,N,O,P,Q,R,S where ( R >  \",$B$4,\" OR ( Q = 0 AND P > \",$B$4,\" )) format L '0',M '0',N '%0.0',O '0.0',P '0',Q '0',R '0.0',S '%0.0'\"),2))"
  
  
  var Q14="=if($B$4=\"\",QUERY(RAW_C!$U:$AD,\"select U,V,W,X,Y,Z,AA,AB,AC,AD format W '0',X '0',Y '%0.0',Z '0.0',AA '0',AB '0',AC '0.0',AD '%0.0'\",2),"+
          "QUERY(RAW_C!$U:$AD,CONCATENATE(\"select U,V,W,X,Y,Z,AA,AB,AC,AD where ( AC >  \",$B$4,\" OR ( AB = 0 AND AA > \",$B$4,\" )) format W '0',X '0',Y '%0.0',Z '0.0',AA '0',AB '0',AC '0.0',AD '%0.0'\"),2))"
  
  
  sh6.getRange("A7").setValue(Q13)
  sh6.getRange("L7").setValue(Q14)
  
  /////////////////////////////
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 
    }  
}