Lower Target ROAS Script
Artificial intelligence and machine learning algorithms are only as good as the data they have access to. Optimize your data to ensure your smart bidding and smart shopping campaigns are only using quality data with the lower target ROAS Google Ads 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 Pushing 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 Relaxation 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 All Active AdGroups").setFontWeight('bold');
sheet1.getRange("E"+row).setValue("RoAS of Top 80% AdGroups (V1)").setFontWeight('bold');
sheet1.getRange("F"+row).setValue("RoAS excluding NCSQ (V2)").setFontWeight('bold');
sheet1.getRange("G"+row).setValue("Normalized Value (V3)").setFontWeight('bold');
sheet1.getRange("H"+row).setValue("Current RoAS Target (V4)").setFontWeight('bold');
sheet1.getRange("I"+row).setValue("Adjusted RoAS Target").setFontWeight('bold');
sheet1.getRange("J"+row).setValue("Conversion Value").setFontWeight('bold');
sheet1.getRange("K"+row).setValue("Cost").setFontWeight('bold');
sheet1.getRange("L"+row).setValue("Impressions").setFontWeight('bold');
sheet1.getRange("M"+row).setValue("Clicks").setFontWeight('bold');
sheet1.getRange("N"+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['rcCamp'], campaignCalData['rcTopAdGrp'],
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,14);
range.setValues(details);
var cell=sheet1.getRange(row,7);
cell.setFormula('=if(D' +row+ '>= C' +row+ ',if(E' +row+ '<= F' +row+ ',E' +row+ '*B2%,F' +row+ '*B2%),if(E' +row+ '>= F' +row+ ',E' +row+ '*B2%,F' +row+ '*B2%))');
cell.setNumberFormat('.00');
var cell=sheet1.getRange(row,9);
cell.setFormula('=if(D' +row+ '>= C' +row+ ',if(G' +row+ '<= H' +row+ '*B2%,G' +row+ ',H' +row+ '*B2%),if(G' +row+ '<= H' +row+ '*B2%,G' +row+ ',H' +row+ '*B2%))');
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+ '*B2%<= E' +row+ '*B2%,D' +row+ '*B2%,E' +row+ '*B2%),if(D' +row+ '*B2%>= E' +row+ '*B2%,D' +row+ '*B2%,E' +row+ '*B2%))');
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 Relaxation 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%.");
sheet0.getRange("A8").setValue("Option 2:").setFontWeight('bold');
sheet0.getRange("A9").setValue("If simulator is not avaiable 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 after excluding non converting search queries");
sheet0.getRange("A14").setValue("V3: IF(V1<=V2,V1x80%,V2x80%)");
sheet0.getRange("A15").setValue("V4: Current tROAS target");
sheet0.getRange("A16").setValue("Adjusted tROAS target: IF(V3<=V4x80%, V3, V4x80%)");
sheet0.getRange("A17").setValue("Final ROAS Target: Adjusted ROAS target or rounded off value of Adjusted ROAS target");
sheet0.getRange("A19").setValue("Condition 2: Smart Shopping campaigns:").setFontWeight('bold');
sheet0.getRange("A20").setValue("V1: Actual ROAS");
sheet0.getRange("A21").setValue("V2: Current ROAS Target");
sheet0.getRange("A22").setValue("Adjusted ROAS Target: IF(V1x80%<=V2x80%, V1x80%, V2x80%)");
sheet0.getRange("A23").setValue("Final ROAS Target: Adjusted ROAS target or rounded off value of Adjusted ROAS target");
sheet0.getRange("A25").setValue("For Any Campaign with ROAS lower than Benchmark:").setFontWeight('bold').setFontSize(16);
sheet0.getRange("A27").setValue("Option 1:").setFontWeight('bold');
sheet0.getRange("A28").setValue("Use Simulator and try to adjust. ");
sheet0.getRange("A30").setValue("Option 2:").setFontWeight('bold');
sheet0.getRange("A31").setValue("If simulator is not available or you need more data driven approach.");
sheet0.getRange("A33").setValue("Condition 1: Campaigns on tROAS smart bidding").setFontWeight('bold');
sheet0.getRange("A34").setValue("V1: ROAS of top 80% Ad Groups: ");
sheet0.getRange("A35").setValue("V2: ROAS after excluding non converting search queries");
sheet0.getRange("A36").setValue("V3: IF(V1<=V2,V1x80%,V2x80%)");
sheet0.getRange("A37").setValue("V4: Current tROAS target");
sheet0.getRange("A38").setValue("Adjusted tROAS target: IF(V3<=V4x80%, V3, V4x80%)");
sheet0.getRange("A39").setValue("Final ROAS Target: Adjusted ROAS target or rounded off value of Adjusted ROAS target");
sheet0.getRange("A41").setValue("Condition 2: Smart Shopping campaigns").setFontWeight('bold');
sheet0.getRange("A42").setValue("V1: Actual ROAS");
sheet0.getRange("A43").setValue("V2: Current ROAS Target");
sheet0.getRange("A44").setValue("Adjusted ROAS Target: IF(V1x80%>=V2x80%, V1x80%, V2x80%)");
sheet0.getRange("A45").setValue("Final ROAS Target: Adjusted ROAS target or rounded off value of Adjusted ROAS target");
sheet0.getRange("A47").setValue("Note: ").setFontWeight('bold');
sheet0.getRange("A48").setValue("The automated template should give the values of all variables ");
sheet0.getRange("A49").setValue("The above options would give the teams more flexibilty when using the template.");
sheet0.getRange("A50").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("A51").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 Relaxation 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?
The script shows keywords that should have their exact match variants present based on the words in prior search queries. Add these exact matches to your campaign for increased reach.
Our script helps minimize cross-serving by showing the keywords wrongly triggered by search queries, even if those keywords exactly match the search queries that are present in the setup. Adding the search queries as negatives to irrelevant ad groups would minimize such cross-serving.
Finally, the script identifies new keywords from converting search queries, as well as identifies negative keywords from search queries that are not converting and thus costing you clicks and ad budget.
The script’s automated keyword research can help inform future ads, as well as copy for your product’s landing page, ads, and description to ensure the right customers are finding you.
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.
