-
-
Save RitwikGA/7f3ef1e906eea05fa9e94b7829984db3 to your computer and use it in GitHub Desktop.
Adwords Advanced Automated Reports - Hourly/Daily/Weekly/Monthly
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* Adwords Daily Reporting | |
| * Description: Analyze daily stats of Adwords. | |
| * Author:RitwikGA | |
| * Copyright 2016 v1.1 | |
| * Digishuffle.com | |
| */ | |
| function main() | |
| { | |
| var AccountName=AdWordsApp.currentAccount().getName() | |
| //Create Spreadsheet | |
| var url=getSpreadsheetURL("Rutu-"+AccountName+"_Daily_Report"); | |
| var spreadsheet = SpreadsheetApp.openByUrl(url) | |
| //Create Sheets | |
| var sh2=spreadsheet.getSheets()[0].setName("Daily_Report"); | |
| var sh3=getsheet(spreadsheet,"RAW_D"); | |
| var sh4=getsheet(spreadsheet,"Monthly_Report"); | |
| var sh5=getsheet(spreadsheet,"DayOfWeek_AdNetwork_Report"); | |
| DailyReportExport(sh2,sh3,sh4,sh5) | |
| } | |
| function DailyReportExport(sh2,sh3,sh4,sh5) | |
| { | |
| // | |
| 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 Date,DayOfWeek,Month,Device,Clicks,Impressions,Clicks,Cost,Conversions,AdNetworkType2 "+ | |
| "FROM CAMPAIGN_PERFORMANCE_REPORT "+ | |
| " WHERE Clicks>0 "+ | |
| "DURING "+start); | |
| report.exportToSheet(sh3); | |
| sh2.getRange(1,6,1,1).setValue("Date Range:Last 7 Days") | |
| sh5.getRange(1,6,1,1).setValue("Date Range:Last 7 Days") | |
| } else | |
| { | |
| var report = AdWordsApp.report( | |
| "SELECT Date,DayOfWeek,Month,Device,Clicks,Impressions,Clicks,Cost,Conversions,AdNetworkType2 "+ | |
| "FROM CAMPAIGN_PERFORMANCE_REPORT "+ | |
| " WHERE Clicks>0 "+ | |
| "DURING "+start+","+end); | |
| report.exportToSheet(sh3); | |
| sh2.getRange(1,6,1,1).setValue("Date Range:"+start+" "+end) | |
| sh5.getRange(1,6,1,1).setValue("Date Range:"+start+" "+end) | |
| } | |
| sh3.hideSheet(); | |
| sh2.getRange(4,1,1,1).setValue("Devices ---->") | |
| sh2.getRange("B4").setBackground("#FFFF33"); | |
| var cell = sh2.getRange("B4"); | |
| cell.setValue("All"); | |
| var rule = SpreadsheetApp.newDataValidation().requireValueInList(['All', 'Computers','Mobile','Tablet']).setAllowInvalid(false).build(); | |
| cell.setDataValidation(rule); | |
| sh2.getRange("A6").setValue("DAILY REPORT") | |
| 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); | |
| var DQuery="=if($B$4=\"All\",QUERY(RAW_D!$A:$I,\"select A,sum(F),sum(E),sum(E)/sum(F),sum(G)/sum(E),sum(G),sum(H),sum(G)/sum(H),sum(H)/sum(E) group by A order by A desc label A 'Date',sum(F) 'Imp',sum(E) 'Clicks',sum(E)/sum(F) 'Ctr',sum(G)/sum(E) 'CPC',sum(G) 'Cost',sum(H) 'Conv',sum(G)/sum(H) 'CPA',sum(H)/sum(E) 'Conv. Rate' format sum(E)/sum(F) '%0.0' ,sum(H)/sum(E) '%0.0',sum(G) '0',sum(G)/sum(E) '0.0',sum(G)/sum(H) '0'\",1),"+ | |
| "QUERY(RAW_D!$A:$I,CONCATENATE(\"select A,sum(F),sum(E),sum(E)/sum(F),sum(G)/sum(E),sum(G),sum(H),sum(G)/sum(H),sum(H)/sum(E) where D contains '\",$B$4,\"' group by A order by A desc label A 'Date',sum(F) 'Imp',sum(E) 'Clicks',sum(E)/sum(F) 'Ctr',sum(G)/sum(E) 'CPC',sum(G) 'Cost',sum(H) 'Conv',sum(G)/sum(H) 'CPA',sum(H)/sum(E) 'Conv. Rate' format sum(E)/sum(F) '%0.0' ,sum(H)/sum(E) '%0.0',sum(G) '0',sum(G)/sum(E) '0.0',sum(G)/sum(H) '0' \"),1))" | |
| sh2.getRange("A7").setValue(DQuery); | |
| ////Monthly _ Reports_Sheet | |
| sh4.getRange(5,1,1,1).setValue("Devices ---->") | |
| sh4.getRange("B5").setBackground("#FFFF33"); | |
| var cell = sh4.getRange("B5"); | |
| cell.setValue("All"); | |
| var rule = SpreadsheetApp.newDataValidation().requireValueInList(['All', 'Computers','Mobile','Tablet']).setAllowInvalid(false).build(); | |
| cell.setDataValidation(rule); | |
| sh4.getRange("A6").setValue("MONTHLY REPORT") | |
| sh4.getRange("A6").setFontSize(14); | |
| sh4.getRange("A6").setFontWeight("bold") | |
| sh4.getRange("A6").setHorizontalAlignment("center") | |
| sh4.getRange("A6:I7").setBorder(true, true, true, true, true, true) | |
| sh4.getRange("A6").setBackground("#336DCB"); | |
| sh4.getRange("A7:I7").setBackground("#336DCB"); | |
| sh4.getRange("A6").setFontColor("#FFFFFF"); | |
| sh4.getRange("A7:I7").setFontColor("#FFFFFF"); | |
| sh4.getRange("A6:I6").merge() | |
| sh4.setRowHeight(6, 40); | |
| //Prediction | |
| sh4.getRange("B1").setValue("=CONCATENATE(\"Prediction - \",text($A$8,\"mmmm-yy\"))") | |
| sh4.getRange("B1").setFontSize(14); | |
| sh4.getRange("B1").setFontWeight("bold") | |
| sh4.getRange("B1").setHorizontalAlignment("center") | |
| sh4.getRange("B1:I3").setBorder(true, true, true, true, true, true) | |
| sh4.getRange("B1").setBackground("#336DCB"); | |
| sh4.getRange("B2:I2").setBackground("#336DCB"); | |
| sh4.getRange("B1").setFontColor("#FFFFFF"); | |
| sh4.getRange("B2:I2").setFontColor("#FFFFFF"); | |
| sh4.getRange("B1:I1").merge() | |
| sh4.setRowHeight(1, 40); | |
| sh4.getRange("B2:I2").setValues([["Imp","Clicks","Ctr","CPC","Cost","Conv","CPA","Conv. Rate"]]) | |
| sh4.getRange("B3:I3").setValues([["=$C$3/$D$3","=$F$3/$E$3","=$D$8","=$E$8","=$F$8*(1+(text(EOMONTH(Daily_Report!$A$8,0),\"d\")-TEXT(Daily_Report!$A$8,\"d\"))/TEXT(Daily_Report!$A$8,\"d\"))", | |
| "=$C$3*$I$3","=$F$3/$G$3","=$I$8"]]) | |
| var format = [[ "0","0","%0.0","0.0","0","0","0","%0.0"]]; | |
| sh4.getRange("B3:I3").setNumberFormats(format); | |
| ///Monthly Query/// | |
| var MQuery="=if($B$5=\"All\",QUERY(RAW_D!$A:$I,\"select C,sum(F),sum(E),sum(E)/sum(F),sum(G)/sum(E),sum(G),sum(H),sum(G)/sum(H),sum(H)/sum(E) group by C order by C desc label C 'Month',sum(F) 'Imp',sum(E) 'Clicks',sum(E)/sum(F) 'Ctr',sum(G)/sum(E) 'CPC',sum(G) 'Cost',sum(H) 'Conv',sum(G)/sum(H) 'CPA',sum(H)/sum(E) 'Conv. Rate' format sum(E)/sum(F) '%0.0' ,sum(H)/sum(E) '%0.0',sum(G) '0',sum(G)/sum(E) '0.0',sum(G)/sum(H) '0' \",1)"+ | |
| ",QUERY(RAW_D!$A:$I,CONCATENATE(\"select C,sum(F),sum(E),sum(E)/sum(F),sum(G)/sum(E),sum(G),sum(H),sum(G)/sum(H),sum(H)/sum(E) where D contains '\",$B$5,\"' group by C order by C desc label C 'Month',sum(F) 'Imp',sum(E) 'Clicks',sum(E)/sum(F) 'Ctr',sum(G)/sum(E) 'CPC',sum(G) 'Cost',sum(H) 'Conv',sum(G)/sum(H) 'CPA',sum(H)/sum(E) 'Conv. Rate' format sum(E)/sum(F) '%0.0' ,sum(H)/sum(E) '%0.0',sum(G) '0',sum(G)/sum(E) '0.0',sum(G)/sum(H) '0' \"),1))" | |
| sh4.getRange("A7").setValue(MQuery); | |
| ////DayOfWeek_AdNetwork_Report////// | |
| sh5.getRange(4,1,1,1).setValue("Devices ---->") | |
| sh5.getRange("B4").setBackground("#FFFF33"); | |
| var cell = sh5.getRange("$B$4"); | |
| cell.setValue("All"); | |
| var rule = SpreadsheetApp.newDataValidation().requireValueInList(['All', 'Computers','Mobile','Tablet']).setAllowInvalid(false).build(); | |
| cell.setDataValidation(rule); | |
| sh5.getRange("A6").setValue("Day Of Week Report") | |
| sh5.getRange("A6").setFontSize(14); | |
| sh5.getRange("A6").setFontWeight("bold") | |
| sh5.getRange("A6").setHorizontalAlignment("center") | |
| sh5.getRange("A6:I7").setBorder(true, true, true, true, true, true) | |
| sh5.getRange("A6").setBackground("#336DCB"); | |
| sh5.getRange("A7:I7").setBackground("#336DCB"); | |
| sh5.getRange("A6").setFontColor("#FFFFFF"); | |
| sh5.getRange("A7:I7").setFontColor("#FFFFFF"); | |
| sh5.getRange("A6:I6").merge() | |
| sh5.setRowHeight(6, 40); | |
| var DOWQuery="=if($B$4=\"All\",QUERY(RAW_D!$A:$I,\"select B,sum(F),sum(E),sum(E)/sum(F),sum(G)/sum(E),sum(G),sum(H),sum(G)/sum(H),sum(H)/sum(E) group by B order by B desc label B 'Day Of Week',sum(F) 'Imp',sum(E) 'Clicks',sum(E)/sum(F) 'Ctr',sum(G)/sum(E) 'CPC',sum(G) 'Cost',sum(H) 'Conv',sum(G)/sum(H) 'CPA',sum(H)/sum(E) 'Conv. Rate' format sum(E)/sum(F) '%0.0' ,sum(H)/sum(E) '%0.0',sum(G) '0',sum(G)/sum(E) '0.0',sum(G)/sum(H) '0' \",1),"+ | |
| "QUERY(RAW_D!$A:$I,CONCATENATE(\"select B,sum(F),sum(E),sum(E)/sum(F),sum(G)/sum(E),sum(G),sum(H),sum(G)/sum(H),sum(H)/sum(E) where D contains '\",$B$4,\"' group by B label B 'Day Of Week',sum(F) 'Imp',sum(E) 'Clicks',sum(E)/sum(F) 'Ctr',sum(G)/sum(E) 'CPC',sum(G) 'Cost',sum(H) 'Conv',sum(G)/sum(H) 'CPA',sum(H)/sum(E) 'Conv. Rate' format sum(E)/sum(F) '%0.0' ,sum(H)/sum(E) '%0.0',sum(G) '0',sum(G)/sum(E) '0.0',sum(G)/sum(H) '0' \"),1))" | |
| sh5.getRange("A7").setValue(DOWQuery); | |
| ////Adnetwork///////// | |
| sh5.getRange("A16").setValue("Ad Network Report") | |
| sh5.getRange("A16").setFontSize(14); | |
| sh5.getRange("A16").setFontWeight("bold") | |
| sh5.getRange("A16").setHorizontalAlignment("center") | |
| sh5.getRange("A16:I17").setBorder(true, true, true, true, true, true) | |
| sh5.getRange("A16").setBackground("#336DCB"); | |
| sh5.getRange("A17:I17").setBackground("#336DCB"); | |
| sh5.getRange("A16").setFontColor("#FFFFFF"); | |
| sh5.getRange("A17:I17").setFontColor("#FFFFFF"); | |
| sh5.getRange("A16:I16").merge() | |
| sh5.setRowHeight(16, 40); | |
| var ADQuery="=if($B$4=\"All\",QUERY(RAW_D!$A:$I,\"select I,sum(F),sum(E),sum(E)/sum(F),sum(G)/sum(E),sum(G),sum(H),sum(G)/sum(H),sum(H)/sum(E) group by I order by I desc label I 'Ad Networks',sum(F) 'Imp',sum(E) 'Clicks',sum(E)/sum(F) 'Ctr',sum(G)/sum(E) 'CPC',sum(G) 'Cost',sum(H) 'Conv',sum(G)/sum(H) 'CPA',sum(H)/sum(E) 'Conv. Rate' format sum(E)/sum(F) '%0.0' ,sum(H)/sum(E) '%0.0',sum(G) '0',sum(G)/sum(E) '0.0',sum(G)/sum(H) '0' \",1),"+ | |
| "QUERY(RAW_D!$A:$I,CONCATENATE(\"select I,sum(F),sum(E),sum(E)/sum(F),sum(G)/sum(E),sum(G),sum(H),sum(G)/sum(H),sum(H)/sum(E) where D contains '\",$B$4,\"' group by I label I 'Ad Networks',sum(F) 'Imp',sum(E) 'Clicks',sum(E)/sum(F) 'Ctr',sum(G)/sum(E) 'CPC',sum(G) 'Cost',sum(H) 'Conv',sum(G)/sum(H) 'CPA',sum(H)/sum(E) 'Conv. Rate' format sum(E)/sum(F) '%0.0' ,sum(H)/sum(E) '%0.0',sum(G) '0',sum(G)/sum(E) '0.0',sum(G)/sum(H) '0' \"),1))" | |
| sh5.getRange("A17").setValue(ADQuery) | |
| ///// | |
| 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 | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment