Skip to content

Instantly share code, notes, and snippets.

@RitwikGA

RitwikGA/AdwordsCPAScanner

Created May 10, 2017 13:22
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save RitwikGA/7599bdf9522e37c207d334100812c340 to your computer and use it in GitHub Desktop.
Save RitwikGA/7599bdf9522e37c207d334100812c340 to your computer and use it in GitHub Desktop.
Download ZIP
Scan CPAs across dimensions
Raw
AdwordsCPAScanner
/* 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