Increase Target ROAS Script
When you want your ads to be effective but fit into a tighter budget than originally planned, you need to ensure you are still reaching quality customers. When budget or other restraints impact your ad spend, be sure your revenue volume is not significantly impacted with the increase target ROAS script.
/* README BEFORE EXECUTING THIS SCRIPT. Note the line numbers may change if you delete a line.
* Recommend to leave the line blank to avoid confusion. Modify this script only after copying to your local account.
* Modify the lines 9-18 as per the requirement of the account.
*
* FUNCTIONALITY: Smart Bidding and Smart Shopping_Controlling Spend by Adjusting ROAS Targets
*/
//SPREADSHEET_URL of the google sheet created from the respective account
var SPREADSHEET_URL = 'Enter the Spreadsheet url of the account';
//Change this variable to true if you want to receive email notification after the script is run and change it to false if email notification is not required
var RECEIVE_EMAIL_NOTIFICATION=true;
//Email address to be edited by the user, for multiple emails addresess give them in comma separated
var RECIPIENT_EMAIL = 'Enter your email id';
//Replace with one of these values: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH. Do not change the format.
var dataRangeDuration = 'LAST_30_DAYS';
//DO NOT CHANGE (To be changed by the developer on any change request)
var VERSION_NUMBER=1.1;
function main() {
getRoasReport();
}
function getRoasReport() {
var campMap= getCampaigns();
var idList=campMap["idList"];
var wasterSpendMap=searchTermData(idList);
var map=campMap["campaignTRoasMap"];
var newCampMap=getAdGroups(map,wasterSpendMap);
var now = new Date();
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet1 = ss.getSheetByName("Analysis Report");
if(sheet1==null){
sheet1=ss.insertSheet("Analysis Report");
}
var percent=sheet1.getRange('B2').getValue()>0?sheet1.getRange('B2').getValue():80;
var percent2=sheet1.getRange('B3').getValue()>0?sheet1.getRange('B3').getValue():120;
var percent3=sheet1.getRange('B4').getValue()>0?sheet1.getRange('B4').getValue():130;
sheet1.getRange("A2").setValue("Normalization Percentage 1 (Default:80)").setFontWeight('bold');
sheet1.getRange("A3").setValue("Normalization Percentage 2 (Default:120)").setFontWeight('bold');
sheet1.getRange("A4").setValue("Normalization Percentage 3 (Default:130)").setFontWeight('bold');
sheet1.getRange("B2").setValue(percent);
sheet1.getRange("B3").setValue(percent2);
sheet1.getRange("B4").setValue(percent3);
var row=sheet1.getLastRow() + 2;
sheet1.getRange("A"+row).setValue("Date").setFontWeight('bold');
sheet1.getRange("B"+row).setValue(now).setFontWeight('bold');
var row=sheet1.getLastRow() + 1;
sheet1.getRange("A"+row).setValue("Date Range").setFontWeight('bold');
sheet1.getRange("B"+row).setValue("Last 30 Days").setFontWeight('bold');
var row=sheet1.getLastRow() + 2;
sheet1.getRange("A"+row).setValue("Target ROAS Increasing Rules").setFontWeight('bold');
var row=sheet1.getLastRow() + 2;
sheet1.getRange("A"+row).setValue("Campaign Name").setFontWeight('bold');
sheet1.getRange("B"+row).setValue("Campaign Type").setFontWeight('bold');
sheet1.getRange("C"+row).setValue("Campaign Benchmark KPI Value").setFontWeight('bold');
sheet1.getRange("D"+row).setValue("RoAS of Top 80% AdGroups (V1)").setFontWeight('bold');
sheet1.getRange("E"+row).setValue("RoAS of All Active AdGroups (V2)").setFontWeight('bold');
sheet1.getRange("F"+row).setValue("Normalized Value (V3)").setFontWeight('bold');
sheet1.getRange("G"+row).setValue("RoAS excluding NCSQ (V4)").setFontWeight('bold');
sheet1.getRange("H"+row).setValue("Normalized Value (V5)").setFontWeight('bold');
sheet1.getRange("I"+row).setValue("Current RoAS Target (V6)").setFontWeight('bold');
sheet1.getRange("J"+row).setValue("Adjusted RoAS Target").setFontWeight('bold');
sheet1.getRange("K"+row).setValue("Conversion Value").setFontWeight('bold');
sheet1.getRange("L"+row).setValue("Cost").setFontWeight('bold');
sheet1.getRange("M"+row).setValue("Impressions").setFontWeight('bold');
sheet1.getRange("N"+row).setValue("Clicks").setFontWeight('bold');
sheet1.getRange("O"+row).setValue("Orders").setFontWeight('bold');
var benchmark=newCampMap["avgRC"];
for(var key in newCampMap["campMap"]){
var row=sheet1.getLastRow() + 1;
var myformula_placeholder = '';
var campaignCalData=newCampMap["campMap"][key];
var campaignData=map[key];
var details=[[campaignData['campaign.name'], campaignData['campaign.advertising_channel_type'],benchmark,
campaignCalData['rcTopAdGrp'], campaignCalData['rcCamp'], myformula_placeholder,
campaignCalData['rcWithoutWastedSpend'], myformula_placeholder, campaignCalData['targetRoas'],
myformula_placeholder,(campaignData["metrics.conversions_value"]).toFixed(2),
(campaignData["metrics.cost_micros"]/1000000).toFixed(2),campaignData["metrics.impressions"],
campaignData["metrics.clicks"],campaignData["metrics.conversions"]]];
var range=sheet1.getRange(row,1,1,15);
range.setValues(details);
var cell=sheet1.getRange(row,6);
cell.setFormula('=if(D' +row+ '>= E' +row+ ',D' +row+ ',E' +row+ ')');
cell.setNumberFormat('.00');
var cell=sheet1.getRange(row,8);
cell.setFormula('=if(E'+row+'>=C'+row+',if(F' +row+ '>= G' +row+ '*B2%,F' +row+ ',G' +row+ '*B2%),if(F' +row+ '*B3%>= G' +row+ '*B2%,F' +row+ '*B3%,G' +row+ '*B2%))');
cell.setNumberFormat('.00');
var cell=sheet1.getRange(row,10);
cell.setFormula('=if(E'+row+'>=C'+row+',if(H' +row+ '>= I' +row+ '*B3%,H' +row+ ',I' +row+ '*B3%),if(H' +row+ '>= I' +row+ '*B4%,H' +row+ ',I' +row+ '*B4%))');
cell.setNumberFormat('.00');
}
var campMap= getSmartShoppingCampaigns();
var map=campMap["campaignTRoasMap"];
var benchmark=campMap["avgRC"];
var row=sheet1.getLastRow() + 3;
sheet1.getRange("A"+row).setValue("Campaign Name").setFontWeight('bold');
sheet1.getRange("B"+row).setValue("Campaign Type").setFontWeight('bold');
sheet1.getRange("C"+row).setValue("Campaign Benchmark KPI Value").setFontWeight('bold');
sheet1.getRange("D"+row).setValue("Actual RoAS (V1)").setFontWeight('bold');
sheet1.getRange("E"+row).setValue("Current RoAS Target (V2)").setFontWeight('bold');
sheet1.getRange("F"+row).setValue("Adjusted RoAS Target").setFontWeight('bold');
sheet1.getRange("G"+row).setValue("Conversion Value").setFontWeight('bold');
sheet1.getRange("H"+row).setValue("Cost").setFontWeight('bold');
sheet1.getRange("I"+row).setValue("Impressions").setFontWeight('bold');
sheet1.getRange("J"+row).setValue("Clicks").setFontWeight('bold');
sheet1.getRange("K"+row).setValue("Orders").setFontWeight('bold');
for(var key in map){
var campaignData=map[key];
var row=sheet1.getLastRow() + 1;
var myformula_placeholder = '';
var totalConv=(campaignData["metrics.conversions_value"]).toFixed(2);
var totalCost=(campaignData["metrics.cost_micros"]/1000000).toFixed(2);
var targetRoas=campaignData["campaign.maximize_conversion_value.target_roas"];
var rcCamp=totalCost>0?(totalConv/totalCost).toFixed(2):0;
var details=[[campaignData['campaign.name'], campaignData['campaign.advertising_channel_type'],benchmark,
rcCamp, targetRoas,
myformula_placeholder,(campaignData["metrics.conversions_value"]).toFixed(2),
(campaignData["metrics.cost_micros"]/1000000).toFixed(2),campaignData["metrics.impressions"],
campaignData["metrics.clicks"],campaignData["metrics.conversions"]]];
var range=sheet1.getRange(row,1,1,11);
range.setValues(details);
var cell=sheet1.getRange(row,6);
cell.setFormula('=if(D'+row+'>=C'+row+',if(D' +row+ '*B3%>= E' +row+ '*B3%,D' +row+ '*B3%,E' +row+ '*B3%),if(D' +row+ '*B3%>= E' +row+ '*B4%,D' +row+ '*B3%,E' +row+ '*B4%))');
cell.setNumberFormat('.00');
}
var ZERO_SHEET_NAME="Rules";
var sheet0 = ss.getSheetByName(ZERO_SHEET_NAME);
if(sheet0!=null){
sheet0.clearContents();
sheet0.clearFormats();
}else{
sheet0=ss.insertSheet(ZERO_SHEET_NAME);
}
sheet0.getRange("A1").setValue("Report Run On:").setFontWeight('bold');
sheet0.getRange("B1").setValue(now).setFontWeight('bold');
sheet0.getRange("A2").setValue("Target ROAS Increasing Rules").setFontWeight('bold').setFontSize(18);
sheet0.getRange("A3").setValue("For Any Campaign with ROAS better than Benchmark:").setFontWeight('bold').setFontSize(16);
sheet0.getRange("A5").setValue("Option 1:").setFontWeight('bold');
sheet0.getRange("A6").setValue("Use Simulator and try to adjust by at least 20%-30% after checking on the potential impacts on spend and revenue.");
sheet0.getRange("A8").setValue("Option 2:").setFontWeight('bold');
sheet0.getRange("A9").setValue("If simulator is not available or you need more data driven approach.");
sheet0.getRange("A11").setValue("Condition 1: Campaigns on tROAS smart bidding:").setFontWeight('bold');
sheet0.getRange("A12").setValue("V1: ROAS of top 80% Ad Groups: ");
sheet0.getRange("A13").setValue("V2: ROAS of all active ad groups");
sheet0.getRange("A14").setValue("V3: IF(V1>=V2,V1,V2)");
sheet0.getRange("A15").setValue("V4: ROAS after excluding non converting search queries");
sheet0.getRange("A16").setValue("V5: IF(V3>=V4*80%,V3,V4*80%)");
sheet0.getRange("A17").setValue("V6: Current tROAS target");
sheet0.getRange("A18").setValue("Adjusted tROAS target: IF(V5>=V6x120%, V5, V6x120%)");
sheet0.getRange("A19").setValue("Final ROAS Target: Adjusted ROAS target or rounded off value of Adjusted ROAS target");
sheet0.getRange("A21").setValue("Condition 2: Smart Shopping campaigns:").setFontWeight('bold');
sheet0.getRange("A22").setValue("V1: Actual ROAS");
sheet0.getRange("A23").setValue("V2: Current ROAS Target");
sheet0.getRange("A24").setValue("Adjusted ROAS Target: IF(V1x120%>=V2x120%, V1x120%, V2x120%)");
sheet0.getRange("A25").setValue("Final ROAS Target: Adjusted ROAS target or rounded off value of Adjusted ROAS target");
sheet0.getRange("A27").setValue("For Any Campaign with ROAS lower than Benchmark:").setFontWeight('bold').setFontSize(16);
sheet0.getRange("A29").setValue("Option 1:").setFontWeight('bold');
sheet0.getRange("A30").setValue("Use Simulator and try to adjust by at least 20%-30% after checking on the potential impacts on spend and revenue.");
sheet0.getRange("A32").setValue("Option 2:").setFontWeight('bold');
sheet0.getRange("A33").setValue("If simulator is not available or you need more data driven approach.");
sheet0.getRange("A35").setValue("Condition 1: Campaigns on tROAS smart bidding").setFontWeight('bold');
sheet0.getRange("A36").setValue("V1: ROAS of top 80% Ad Groups:");
sheet0.getRange("A37").setValue("V2: ROAS of all active ad groups");
sheet0.getRange("A38").setValue("V3: IF(V1>=V2,V1,V2)");
sheet0.getRange("A39").setValue("V4: ROAS after excluding non converting search queries");
sheet0.getRange("A40").setValue("V5: IF(V3*120%>=V4*80%,V3*120%,V4*80%)");
sheet0.getRange("A41").setValue("V6: Current tROAS target");
sheet0.getRange("A42").setValue("Adjusted tROAS target: IF(V5>=V6x130%, V5, V6x130%)");
sheet0.getRange("A43").setValue("Final ROAS Target: Adjusted ROAS target or rounded off value of Adjusted ROAS target");
sheet0.getRange("A45").setValue("Condition 2: Smart Shopping campaigns").setFontWeight('bold');
sheet0.getRange("A46").setValue("V1: Actual ROAS");
sheet0.getRange("A47").setValue("V2: Current ROAS Target");
sheet0.getRange("A48").setValue("Adjusted ROAS Target: IF(V1x120%>=V2x130%, V1x120%, V2x130%)");
sheet0.getRange("A49").setValue("Final ROAS Target: Adjusted ROAS target or rounded off value of Adjusted ROAS target");
sheet0.getRange("A52").setValue("Note:").setFontWeight('bold');
sheet0.getRange("A53").setValue("The automated template should give the values of all variables");
sheet0.getRange("A54").setValue("The above options would give the teams more flexibilty when using the template.");
sheet0.getRange("A55").setValue("For e.g. the team could sometimes directly use V3 as the ROAS target before comparing it with actual ROAS if the account has a lot of budget.");
sheet0.getRange("A56").setValue("We may also want to provide the account team with the options to define the percentages.");
if (RECEIVE_EMAIL_NOTIFICATION) {
if(RECIPIENT_EMAIL && RECIPIENT_EMAIL !="Enter your email id"){
MailApp.sendEmail(RECIPIENT_EMAIL,'Target ROAS Increasing Rules Script Run Completed',SPREADSHEET_URL);
}
}
}
function getAdGroups(map,wasterSpendMap){
var campData={};
var campMap={};
var idList=[];
var count=0;
var sumRC=0.0;
var avgRC=0.0;
for (var key in map) {
var campaign=map[key];
var report = AdsApp.report("select ad_group.campaign, ad_group.id, ad_group.name, ad_group.status,"+
"ad_group.target_roas, ad_group.type, metrics.conversions_value, metrics.clicks,"+
"metrics.cost_micros, metrics.conversions FROM ad_group WHERE campaign.id ="+key+
" AND metrics.conversions_value>0 AND ad_group.status = 'ENABLED' AND "+
"segments.date DURING "+dataRangeDuration+" ORDER BY metrics.conversions_value DESC");
var rows = report.rows();
var totalConv=(campaign["metrics.conversions_value"]).toFixed(2);
var totalCost=(campaign["metrics.cost_micros"]/1000000).toFixed(2);
var targetRoas=campaign["campaign.target_roas.target_roas"];
var rcCamp=(totalConv/totalCost).toFixed(2);
sumRC=sumRC+(totalConv/totalCost);
var conversions_value_sum=0;
var cost_sum=0;
while (rows.hasNext()) {
var row = rows.next();
conversions_value_sum += row["metrics.conversions_value"];
cost_sum+= row["metrics.cost_micros"]/1000000;
if(conversions_value_sum<=totalConv*0.8){
break;
}
}
var rcTopAdGrp=(conversions_value_sum/cost_sum).toFixed(2);
var wasterSpend=wasterSpendMap[key];
var rcWithoutWastedSpend=(cost_sum-wasterSpend)>0?
(conversions_value_sum/(cost_sum-wasterSpend)).toFixed(2):
(conversions_value_sum/cost_sum).toFixed(2);
var roasMap= {
"campId":key,"rcTopAdGrp":rcTopAdGrp,"rcCamp":rcCamp,"rcWithoutWastedSpend":rcWithoutWastedSpend,"targetRoas":targetRoas,"wasterSpend":wasterSpend
}
if(campMap[key]==null){
campMap[key]={
"campId":key,"rcTopAdGrp":0,"rcCamp":0,"targetRoas":0,"wasterSpend":0
}
}
campMap[key]=roasMap;
count++;
}
avgRC=(sumRC/count).toFixed(2);
campData["campMap"]=campMap;
campData["avgRC"]=avgRC;
return campData;
}
function searchTermData(idList){
var report= AdsApp.report("SELECT campaign.id,search_term_view.search_term, segments.search_term_match_type,metrics.cost_micros,"+
"metrics.conversions_value, metrics.conversions FROM search_term_view WHERE "+
" campaign.id IN ("+idList+") AND "+
" metrics.cost_micros > 0 AND metrics.conversions = 0 AND "+
"segments.date DURING "+dataRangeDuration+"");
var rows=report.rows();
var campMap={};
var wastedCost=0.0;
while(rows.hasNext()){
var row=rows.next();
var cmpId=row["campaign.id"];
var sTerm= row["search_term_view.search_term"];
var mType=row["segments.search_term_match_type"];
if(campMap[cmpId]==null){
campMap[cmpId]=0.0;
}
campMap[cmpId]= campMap[cmpId]+(row["metrics.cost_micros"]/1000000);
}
return campMap;
}
function getCampaigns() {
var report = AdsApp.report("SELECT campaign.id, campaign.name, campaign.status,campaign.advertising_channel_type,"+
"campaign.target_roas.target_roas, campaign.bidding_strategy_type,"+
"metrics.impressions,metrics.clicks,metrics.conversions,"+
"metrics.conversions_value,metrics.cost_micros "+
" FROM campaign WHERE campaign.status = 'ENABLED' "+
" AND campaign.bidding_strategy_type IN ('TARGET_ROAS') AND segments.date DURING "+dataRangeDuration+"");
var rows = report.rows();
var campaignTRoasMap = {};
var idList=[];
var campData={};
while (rows.hasNext()) {
var campaign = rows.next();
var campaignId= campaign["campaign.id"];
idList.push(campaignId);
if(campaignTRoasMap[campaignId]==null){
campaignTRoasMap[campaignId]=[];
}
campaignTRoasMap[campaignId]=campaign;
}
campData["campaignTRoasMap"]=campaignTRoasMap;
campData["idList"]=idList;
return campData;
}
function getSmartShoppingCampaigns() {
var report = AdsApp.report("SELECT campaign.id, campaign.name, campaign.status,campaign.advertising_channel_type,"+
"campaign.maximize_conversion_value.target_roas, campaign.bidding_strategy_type,"+
"metrics.impressions,metrics.clicks,metrics.conversions,"+
"metrics.conversions_value,metrics.cost_micros "+
" FROM campaign WHERE campaign.advertising_channel_sub_type='SHOPPING_SMART_ADS'"+
" AND campaign.status = 'ENABLED' AND segments.date DURING "+dataRangeDuration+"");
var rows = report.rows();
var campaignTRoasMap = {};
var campData={};
var sumRC=0;
var avgRC=0;
var count=0;
while (rows.hasNext()) {
var campaign = rows.next();
var campaignId= campaign["campaign.id"];
var totalConv=(campaign["metrics.conversions_value"]).toFixed(2);
var totalCost=(campaign["metrics.cost_micros"]/1000000).toFixed(2);
var targetRoas=campaign["campaign.target_roas.target_roas"];
sumRC=sumRC+(totalConv/totalCost);
if(campaignTRoasMap[campaignId]==null){
campaignTRoasMap[campaignId]=[];
}
campaignTRoasMap[campaignId]=campaign;
count++;
}
campData["campaignTRoasMap"]=campaignTRoasMap;
avgRC=(sumRC/count).toFixed(2);
campData["avgRC"]=avgRC;
return campData;
}
How does the script work?
Don’t lose out on significant revenue volume just because your ad budget is restricted. Identify high-quality targets to ensure your campaign is still fully optimized and running effectively.
The increase target ROAS script filters out data that is not meaningful to the end goal. The script takes into account factors like search query level performance and ad group level 80:20 analysis, as well as ROAS benchmarking by brand, non-brand, and shopping. Smart bidding accounts for some of these factors when optimizing campaigns, but maybe using weak data points to get there. The script ensures that smart bidding algorithms are running on high-quality data to run fully optimized campaigns that further your brand’s end goal while fitting into your budget.
How do I use it?
All the scripts can be customized to run for MCC / Non-MCC accounts. To run the script:
- Copy the script code
- Open the Adwords Account (MCC / Non MCC)
- Navigate to Bulk Operations > Scripts > Click New > Paste the code
- Give a name to the script (we typically recommend using the same name we provided so you can better track the code and its results)
Make sure to edit the Output File Path point to your account drive. Ensure all authorizations that are required are in place so the script can run smoothly. Schedule the script as per the need of your account.
