Created
May 10, 2017 13:22
-
-
Save RitwikGA/7599bdf9522e37c207d334100812c340 to your computer and use it in GitHub Desktop.
Scan CPAs across dimensions
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 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 | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment