Adwords Performing vs Underperforming cities - Reduce your CPA - 2

Adwords City Optimizer

/* Adwords City Optimizer
 * Description: Analyze the Adwords City 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+"_City_Performance");
  var spreadsheet = SpreadsheetApp.openByUrl(url)
  
  //Create Sheets
  var sh2=spreadsheet.getSheets()[0].setName("City_Optimizer")
  var sh3=getsheet(spreadsheet,"RAW_D");
  var sh4=getsheet(spreadsheet,"City_Trends")

  CityExport(sh2,sh3,sh4)
  DrawChart(sh2,sh4)
}
function CityExport(sh2,sh3,sh4) 

{
//
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 Date,CountryCriteriaId,CityCriteriaId,Clicks,Impressions,Cost,Conversions,AveragePosition,CampaignName "+ 
   "FROM   GEO_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 Date,CountryCriteriaId,CityCriteriaId,Clicks,Impressions,Cost,Conversions,AveragePosition,CampaignName "+ 
   "FROM   GEO_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();
  
//Create tables
  sh2.getRange(9,1,1,1).setValue("City Performance")
 sh2.getRange(9,1,1,1).setFontSize(14)
 sh2.getRange(9,1,1,1).setFontWeight("bold")
 sh2.getRange(10,1,1,1).setValue("=QUERY("+sh3.getName()+"!A:I,if(B3=\"\",\"select C,sum(F),sum(G),sum(F)/sum(G),sum(F)/sum(D),sum(G)/sum(D) group by C order by sum(G) desc label C 'City',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(F)/sum(D) 'CPC',sum(G)/sum(D) 'Conv Rate'\",CONCATENATE(\"select C,sum(F),sum(G),sum(F)/sum(G),sum(F)/sum(D),sum(G)/sum(D) where I contains '\",B3,\"' group by C order by sum(G) desc label C 'City',sum(F) 'Cost',sum(G) 'Conv',sum(F)/sum(G) 'CPA',sum(F)/sum(D) 'CPC',sum(G)/sum(D) 'Conv. Rate'\")),1)")
  
  sh2.getRange(9,13,1,1).setValue("Cities with 0 conversions")
 sh2.getRange(9,13,1,1).setFontSize(14)
 sh2.getRange(9,13,1,1).setFontWeight("bold")
  sh2.getRange(10,13,1,1).setValue("=QUERY($A$10:$E,\"select A,B,C where C = 0 and B > 0 order by B desc\",1)")
  
  sh2.getRange(3,8,1,1).setValue("Underperforming Cities = CPA > Current CPA")
  sh2.getRange(4,8,1,1).setValue("Performing Cities = CPA < Current CPA")

  
  
  sh2.getRange(10,8,1,1).setValue("=QUERY($A$10:$F,if(H9<>\"Performing\",concatenate(\"select A,D,C,F where D > \",$A$6,\" and C > 0 order by C desc limit 20\"),concatenate(\"select A,D,C,F where D < \",$A$6,\" and C > 0 order by C desc limit 20\")),1)")

var p=[["=QUERY($A$10:$E,\"select sum(B)/sum(C) label sum(B)/sum(C) 'Current CPA'\",1)","=QUERY($A$10:$E,\"select sum(C) label sum(C) 'Total Conv'\",1)","=QUERY($A$10:$E,\"select sum(B) label sum(B) 'Total Cost'\",1)","=QUERY($M$10:$O,\"select sum(N) label sum(N) 'Cost (0 Conv)'\",1)"]]
var q=[["Final CPA"],["=(C6-D6)/B6"]]

sh2.getRange(5,1,1,4).setValues(p);
sh2.getRange(5,5,2,1).setValues(q);
 //sheet.autoResizeColumn(1);

  /////
  var cell = sh2.getRange("H9");
  cell.setValue("Underperforming");
 var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Underperforming', 'Performing']).setAllowInvalid(false).build();
 cell.setDataValidation(rule);
 sh2.getRange("H9").setFontSize(14)
 sh2.getRange("H9").setFontWeight("bold")
 //sh2.autoResizeColumn(8)
  sh2.setColumnWidth(8, 160);
 sh2.setColumnWidth(7, 15);
  sh2.setColumnWidth(12, 15);
  
  ///////set format//////// 
 if(sh2.getRange("I1").getValue()!=="Formatted") {
var format = [
   [ "0,0", "0","0,0","0,0","0,0" ]
 ];
    var rge = sh2.getRange("A6:E6");
 rge.setNumberFormats(format);

    var lastrw = sh2.getRange("A10:A").getValues().filter(String).length+10;
var formats = [
   [ "0,0", "0","0,0","0.0", "%0.00","","","0,0", "0","%0.00","","","0,0","0" ]
 ];
for(var i=11;i<lastrw;i++)
{ var range = sh2.getRange("B"+i+":O"+i);
 range.setNumberFormats(formats);
} 
 sh2.getRange("I1").setValue("Formatted")}  
  /////////////
 //3rd sheet
  sh4.getRange(1,1,1,1).setValue("="+sh2.getName()+"!F1")
  sh4.getRange(7,1,1,1).setValue("=QUERY("+sh3.getName()+"!$A:$I,if($B$3=\"\",concatenate(\"select A,sum(G),sum(F) where C contains '\",$B$4,\"' and C <>'' group by A label A 'Date' , sum(G) 'Conv',sum(F) 'Cost'\"),concatenate(\"select A,sum(G),sum(F) where C contains '\",$B$4,\"'and and C <>'' and I contains '\",$B$3,\"' group by A label A 'Date' , sum(G) 'Conv',sum(F) 'Cost'\")),1)")  
  
  var r=[["Campaign Filter--->"],["City ------->"],["Avg.Daily Conv"]]
  sh4.getRange(3,1,3,1).setValues(r)
sh4.getRange(5,2,1,1).setValue("=AVERAGE(B8:B)")
  sh4.getRange(3,2,1,1).setBackground("#cccccc")
  
//Validation

   var cell = sh4.getRange('B4');
 var range = sh2.getRange('A11:A21');
 var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).setAllowInvalid(false).build();
 cell.setDataValidation(rule);
 sh4.autoResizeColumn(1)
   sh4.set
  

  // Display the Date Range of the Data and Spreadhseet URL
Logger.log("Reports Created for Date Range "+start+" "+end)
Logger.log("URL:"+sh2.getParent().getUrl())
}

function DrawChart(sh2,sh4)
{
  
 //City performance Chart
  var qchart=sh2.newChart();
  qchart.addRange(sh2.getRange("H10:J")).setChartType(Charts.ChartType.COLUMN).asColumnChart().setTitle("Performing / Underperforming Cities")
  .setOption('series',{0:{targetAxisIndex:0},1:{targetAxisIndex:1}}).setOption('vAxes', {
          0: {title: 'CPA'},
          1: {title: 'Conv'}
        }).setXAxisTitle("City").setPosition(10,1,0,0).setOption('width', 1000).setOption('height', 327);
  sh2.insertChart(qchart.build())
  
 //City trend chart
  var qchart=sh4.newChart();
  qchart.addRange(sh4.getRange("A7:C")).setChartType(Charts.ChartType.LINE).asLineChart().setTitle("City Performance")
  .setOption('series',{0:{targetAxisIndex:0},1:{targetAxisIndex:1}}).setOption('vAxes', {
          0: {title: 'Conversions'},
          1: {title: 'Cost'}
        }).setXAxisTitle("Date").setPosition(2,4,10,0).setOption('width', 1000);
  sh4.insertChart(qchart.build())
}




//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 
    }  
}