Analyze the Adwords Dayparting Performance Through Automated Scripts

Hour&DayOfWeek_Script

/* Adwords Hour&DayOfWeek Analysis
 * Description: Analyze the Adwords Dayparting performance.
 * Author:RitwikGA
 * Version 1.1
 * DigiShuffle.com
 */
function  main()
{
  
  
  var AccountName=AdWordsApp.currentAccount().getName()
 
  //Create Spreadsheet 
  var url=getSpreadsheetURL("Rutu-"+AccountName+"_Hour_DayOfWeek");
  var spreadsheet = SpreadsheetApp.openByUrl(url)
  
  //Create Sheets
  var sh2=spreadsheet.getSheets()[0].setName("Hour_DayOfWeek")
  var sh3=getsheet(spreadsheet,"RAW_D");
  DayPartingExport(sh2,sh3)
}

function DayPartingExport(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(6,1,1,1).setValue("Metric-->");
  sh2.getRange(5,1,1,1).setValue("Device-->");
  sh2.getRange(4,2,1,1).setBackground("#cccccc")
  sh2.getRange(4,1,1,1).setValue("Campaign contains ---->")
var cell = sh2.getRange("B5");
  cell.setValue("All");
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['All', 'Computer','Mobile','Tablet']).setAllowInvalid(false).build();
 cell.setDataValidation(rule);

var cell = sh2.getRange("B6");
  cell.setValue("Conv");
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Imp', 'Clicks','Cost','Conv','CPA','CPC']).setAllowInvalid(false).build();
 cell.setDataValidation(rule);
  
  
  ///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,Device,DayOfWeek,HourOfDay,Clicks,Impressions,Cost,Conversions "+ 
   "FROM   CAMPAIGN_PERFORMANCE_REPORT "+
   " WHERE Clicks>0 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,Device,DayOfWeek,HourOfDay,Clicks,Impressions,Cost,Conversions "+ 
   "FROM   CAMPAIGN_PERFORMANCE_REPORT "+
   " WHERE Clicks>0 AND CampaignStatus = 'ENABLED' "+
   "DURING "+start+","+end);
    report.exportToSheet(sh3);
    sh2.getRange(1,6,1,1).setValue("Date Range:"+start+" "+end)
  }
sh3.hideSheet(); 
 
  //// Dayparting Query ///////
 var p="=if(B4=\"\", if(B6=\"Cost\",if(B5=\"All\",QUERY(RAW_D!A:H,\"select D,sum(G) group by D pivot C format sum(G) '0'\",1),if(B5=\"Computer\",QUERY(RAW_D!A:H,\"select D,sum(G) where B contains 'Computers' group by D pivot C format sum(G) '0'\",1),"+
 "if(B5=\"Mobile\",QUERY(RAW_D!A:H,\"select D,sum(G) where B contains 'Mobile' group by D pivot C format sum(G) '0'\",1),IF(B5=\"Tablet\",QUERY(RAW_D!A:H,\"select D,sum(G) where B contains 'Tablets' group by D pivot C format sum(G) '0'\",1),0)))),"+
  "if(B6=\"Imp\",if(B5=\"All\",QUERY(RAW_D!A:H,\"select D,sum(F) group by D pivot C\",1),if(B5=\"Computer\",QUERY(RAW_D!A:H,\"select D,sum(F) where B contains 'Computers' group by D pivot C\",1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,\"select D,sum(F) where B contains 'Mobile' group by D pivot C\",1),IF(B5=\"Tablet\",QUERY(RAW_D!A:H,\"select D,sum(F) where B contains 'Tablets' group by D pivot C\",1),0)))),"+
"if(B6=\"Clicks\",if(B5=\"All\",QUERY(RAW_D!A:H,\"select D,sum(E) group by D pivot C\",1),if(B5=\"Computer\",QUERY(RAW_D!A:H,\"select D,sum(E) where B contains 'Computers' group by D pivot C\",1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,\"select D,sum(E) where B contains 'Mobile' group by D pivot C\",1),IF(B5=\"Tablet\",QUERY(RAW_D!A:H,\"select D,sum(E) where B contains 'Tablets' group by D pivot C\",1),0)))),"+
"if(B6=\"Conv\",if(B5=\"All\",QUERY(RAW_D!A:H,\"select D,sum(H) group by D pivot C\",1),if(B5=\"Computer\",QUERY(RAW_D!A:H,\"select D,sum(H) where B contains 'Computers' group by D pivot C\",1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,\"select D,sum(H) where B contains 'Mobile' group by D pivot C\",1),IF(B5=\"Tablet\",QUERY(RAW_D!A:H,\"select D,sum(H) where B contains 'Tablets' group by D pivot C\",1),0)))),"+
"if(B6=\"CPA\",if(B5=\"All\",QUERY(RAW_D!A:H,\"select D,sum(G)/sum(H) group by D pivot C label sum(G)/sum(H) 'CPA' format sum(G)/sum(H) '0'\",1),"+
"if(B5=\"Computer\",QUERY(RAW_D!A:H,\"select D,sum(G)/sum(H) where B contains 'Computers' group by D pivot C label sum(G)/sum(H) 'CPA' format sum(G)/sum(H) '0'\",1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,\"select D,sum(G)/sum(H) where B contains 'Mobile' group by D pivot C label sum(G)/sum(H) 'CPA' format sum(G)/sum(H) '0'\",1),"+
"IF(B5=\"Tablet\",QUERY(RAW_D!A:H,\"select D,sum(G)/sum(H) where B contains 'Tablets' group by D pivot C label sum(G)/sum(H) 'CPA' format sum(G)/sum(H) '0'\",1),0)))),"+
"if(B6=\"CPC\",if(B5=\"All\",QUERY(RAW_D!A:H,\"select D,sum(G)/sum(E) group by D pivot C label sum(G)/sum(E) 'CPC' format sum(G)/sum(E) '0' \",1),"+
"if(B5=\"Computer\",QUERY(RAW_D!A:H,\"select D,sum(G)/sum(E) where B contains 'Computers' group by D pivot C label sum(G)/sum(E) 'CPC' format sum(G)/sum(E) '0'\",1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,\"select D,sum(G)/sum(E) where B contains 'Mobile' group by D pivot C label sum(G)/sum(E) 'CPC' format sum(G)/sum(E) '0'\",1),"+
"IF(B5=\"Tablet\",QUERY(RAW_D!A:H,\"select D,sum(G)/sum(E) where B contains 'Tablets' group by D pivot C label sum(G)/sum(E) 'CPC' format sum(G)/sum(E) '0'\",1),0)))),0)))))),"+
"if(B6=\"Cost\",if(B5=\"All\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G) where A contains'\",B4,\"'group by D pivot C format sum(G) '0'\"),1),if(B5=\"Computer\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G) where B contains 'Computers' and A contains'\",B4,\"' group by D pivot C format sum(G) '0'\"),1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G) where B contains 'Mobile' and A contains'\",B4,\"' group by D pivot C format sum(G) '0'\"),1),"+
"IF(B5=\"Tablet\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G) where B contains 'Tablets' and A contains'\",B4,\"' group by D pivot C format sum(G) '0'\"),1),0)))),"+
"if(B6=\"Imp\",if(B5=\"All\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(F) where A contains'\",B4,\"'group by D pivot C format sum(F) '0'\"),1),if(B5=\"Computer\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(F) where B contains 'Computers' and A contains'\",B4,\"' group by D pivot C format sum(F) '0'\"),1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(F) where B contains 'Mobile' and A contains'\",B4,\"' group by D pivot C format sum(F) '0'\"),1),"+
"IF(B5=\"Tablet\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(F) where B contains 'Tablets' and where A contains'\",B4,\"' group by D pivot C format sum(F) '0'\"),1),0)))),"+
"if(B6=\"Clicks\",if(B5=\"All\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(E) where A contains'\",B4,\"'group by D pivot C format sum(E) '0'\"),1),if(B5=\"Computer\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(E) where B contains 'Computers' and A contains '\",B4,\"' group by D pivot C format sum(E) '0'\"),1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(E) where B contains 'Mobile' and A contains '\",B4,\"' group by D pivot C format sum(E) '0'\"),1),"+
"IF(B5=\"Tablet\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(E) where B contains 'Tablets' and A contains '\",B4,\"' group by D pivot C format sum(E) '0'\"),1),0)))),"+
"if(B6=\"Conv\",if(B5=\"All\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(H) where A contains'\",B4,\"'group by D pivot C format sum(H) '0'\"),1),if(B5=\"Computer\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(H) where B contains 'Computers' and A contains '\",B4,\"' group by D pivot C format sum(H) '0'\"),1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(H) where B contains 'Mobile' and A contains '\",B4,\"' group by D pivot C format sum(H) '0'\"),1),IF(B5=\"Tablet\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(H) where B contains 'Tablets' and A contains '\",B4,\"' group by D pivot C format sum(H) '0'\"),1),0)))),"+
"if(B6=\"CPA\",if(B5=\"All\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G)/sum(H) where A contains'\",B4,\"'group by D pivot C label sum(G)/sum(H) 'CPA' format sum(G)/sum(H) '0'\"),1),if(B5=\"Computer\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G)/sum(H) where B contains 'Computers' and A contains '\",B4,\"' group by D pivot C label sum(G)/sum(H) 'CPA' format sum(G)/sum(H) '0'\"),1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G)/sum(H) where B contains 'Mobile' and A contains '\",B4,\"' group by D pivot C label sum(G)/sum(H) 'CPA' format sum(G)/sum(H) '0'\"),1),"+
"IF(B5=\"Tablet\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G)/sum(H) where B contains 'Tablets' and A contains '\",B4,\"' group by D pivot C label sum(G)/sum(H) 'CPA' format sum(G)/sum(H) '0'\"),1),0)))),"+
"if(B6=\"CPC\",if(B5=\"All\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G)/sum(E) where A contains'\",B4,\"'group by D pivot C label sum(G)/sum(E) 'CPC' format sum(G)/sum(E) '0'\"),1),"+
"if(B5=\"Computer\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G)/sum(E) where B contains 'Computers' and A contains '\",B4,\"' group by D pivot C label sum(G)/sum(E) 'CPC' format sum(G)/sum(E) '0'\"),1),"+
"if(B5=\"Mobile\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G)/sum(E) where B contains 'Mobile' and A contains '\",B4,\"' group by D pivot C label sum(G)/sum(E) 'CPC' format sum(G)/sum(E) '0'\"),1),"+
"IF(B5=\"Tablet\",QUERY(RAW_D!A:H,CONCATENATE(\"select D,sum(G)/sum(E) where B contains 'Tablets' and A contains '\",B4,\"' group by D pivot C label sum(G)/sum(E) 'CPC' format sum(G)/sum(E) '0'\"),1),0)))),0)))))))" 
  ///////////////////////////////
 
sh2.getRange(4,4,1,1).setValue(p);
    
  sh2.getRange("D4:K4").setFontWeight("bold");
  sh2.getRange("D4:K4").setFontSize(12);
  sh2.getRange("D4:K28").setBorder(true, true, true, true , true , true);// 
  sh2.getRange("D4:K28").setHorizontalAlignment("center")
  
  
  /////Campaign Filter///////////
  var q="=if(B4=\"\",QUERY(RAW_D!A:H,\"select A label A 'Campaign Filtered'\",1),QUERY(RAW_D!A:H,CONCATENATE(\"select A where A contains '\",B4,\"' label A 'Campaign Filtered'\")))";
  sh2.getRange(10,1,1,1).setValue(q);
   sh2.setColumnWidth(1, 200);
   sh2.getRange(10,1,1,1).setFontSize(12)
 sh2.getRange(10,1,1,1).setFontWeight("bold")
 sh2.getRange("D5:D28").setFontWeight("bold")
 sh2.getRange("D5:D28").setFontSize(11) 
 


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