Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
nrShoppingIdExcludeByRoas.js
/*
* This norisk script creates and exports a report on product id level to a static URL as Google Spreadsheet.
* You can chose a specific Shopping Campaign to be reported or you select all.
* The URL the report will be available at, has to be chosen.
*/
// ********** START OF CONFIGURATION ***************
// README >> EVERY variable needs to be set specific to the project
// SEE THE MARKDOWN DOCUMENTATION below
// COPY THIS DEMO-SHEET: https://docs.google.com/spreadsheets/d/1SLXeVzzWj1JxGEMySWAQc4ZkpXRMlY85lAZM5WUB7IM/edit#gid=383285493
var SPREADSHEET_URL = "$$YOUR COPIED URL FROM DEMO-SHEET ABOVE$$"; // Paste the spreadsheet URL in here
var TIMESPAN = "LAST_30_DAYS"; // Timespan of the report.
var CAMPAIGN_INCLUDE_I = "'PLA'"; // Name of your selected shopping Campaign. "'Name'", @throws InvalidAWQLConditionException
var CAMPAIGN_INCLUDE_II = "'PLA'"; // IF only one is needed, set II the same as I
var CAMPAIGN_EXCLUDE_I = "'AT'"; // Only used as suffix
var CAMPAIGN_EXCLUDE_II = "'AT'"; // IF only one is needed, set II to the same value as I
var COLUMNSUFFIX = "AT";
// I. Price Import
var PRICEFEED_URL = "$$YOUR PRICE FEED URL"; // Custom feed URL with two columns id and price, example: http://transport.productsup.io/3abf070bb070c812df5d/channel/92746/rey_idandprice.csv
var SEPARATOR = '\t'; //CSV seperator of feed
// II. Average Quantity Import from GA (Reference: https://developers.google.com/analytics/solutions/articles/reporting-apps-script)
var GA_PROFILE_ID = 9999999; // << SET Google Analytics PROFILE ID here (NOT property ID), see Admin > View Settings
// III. ROI Calculation Columns
var TARGET_MINROI = 1.5; // The Min ROI, given the next click is a sale. ROI = (Rev + ExpRev - (Cost + CPC)) / (Cost + CPC)
/**************** END CONFIGURATION BLOCK ****************/
var DATASHEET = "adwordsData"; // DON'T CHANGE. Name of the sheet to receive data
function main() {
getAdWordsData();
runRoiCalculator();
}
function getAdWordsData(){
// Log all report data into variable
try{
var report = AdWordsApp.report(
"SELECT OfferId , CampaignName , Cost , Conversions , Clicks , AverageCpc , ConversionValue , Ctr " +
"FROM SHOPPING_PERFORMANCE_REPORT " +
"WHERE Cost > 0 " +
"AND CampaignName CONTAINS_IGNORE_CASE " + CAMPAIGN_INCLUDE_I + " " +
"AND CampaignName CONTAINS_IGNORE_CASE " + CAMPAIGN_INCLUDE_II + " " +
"AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE " + CAMPAIGN_EXCLUDE_I + " " +
"AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE " + CAMPAIGN_EXCLUDE_II + " " +
"DURING " + TIMESPAN + " ");
}
catch(e){
Logger.log("Please mind the format of your Main Shopping Camapaign. The script was stopped because of this InvalidAWQLConditionException.");
Logger.log(e + " . stack: " + e.stack);
return;
}
// Start NEW spreadsheet accessor
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
report.exportToSheet(spreadsheet.getSheetByName(DATASHEET));
COLUMNSUFFIX = COLUMNSUFFIX.replace(/ /g,'_').replace(/'/g,'');
Logger.log(COLUMNSUFFIX);
spreadsheet.getRange("B1").setValue("export[1].statsAdw.CampName_" + COLUMNSUFFIX);
spreadsheet.getRange("C1").setValue("export[1].statsAdW.Cost_" + COLUMNSUFFIX);
spreadsheet.getRange("D1").setValue("export[1].statsAdW.Conversions_" + COLUMNSUFFIX);
spreadsheet.getRange("E1").setValue("export[1].statsAdW.Clicks_" + COLUMNSUFFIX);
spreadsheet.getRange("F1").setValue("export[1].statsAdW.AverageCPC_" + COLUMNSUFFIX);
spreadsheet.getRange("G1").setValue("export[1].statsAdW.ConvValue_" + COLUMNSUFFIX);
spreadsheet.getRange("H1").setValue("export[1].statsAdW.Ctr_" + COLUMNSUFFIX);
var sortRange = spreadsheet.getRange("A:H");
sortRange.sort({column: 3, ascending: false});
Logger.log("Report available at " + spreadsheet.getUrl());
}
function runRoiCalculator() {
// 0. General
var SHEET_NAME = DATASHEET;
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var spreadsheet = ss.getSheetByName(SHEET_NAME);
//////////////////////////////////////////////////////////
////
//// PART I: Importing Prices from Feed
////
//////////////////////////////////////////////////////////
try {
// Parse price feed content into array
var spreadsheeturl = UrlFetchApp.fetch(PRICEFEED_URL);
var spreadsheetContent = Utilities.parseCsv(spreadsheeturl, SEPARATOR);
// Access ID-Tab of spreadsheet, clear content
spreadsheet.getRange("s:t").clear();
var destinationRange = spreadsheet.getRange(1, 19, spreadsheetContent.length, spreadsheetContent[0].length);
destinationRange.setValues(spreadsheetContent);
Logger.log('Feedurl content successfully printed to spreadsheet.');
}
catch (e) {Logger.log('PriceAddError: Price data couldnt be added to sheet: ' + e + '. stack : ' + e.stack);}
//////////////////////////////////////////////////////////
////
//// PART II: Importing Historical Avg Quantities from GA
////
//////////////////////////////////////////////////////////
try {
var today = new Date();
if(today.getDay() == 6){
var results = getReportDataForProfile(GA_PROFILE_ID);
outputToSpreadsheet(results, SPREADSHEET_ID,SHEET_NAME);
} else {
Logger.log("Today is not Saturday. GA Call only needed once a week on Saturday! :-)");
}
} // END TRY STATEMENT
catch(error) {
Logger.log(error.message);
}
function getReportDataForProfile(GA_PROFILE_ID) {
var profileId = GA_PROFILE_ID;
var tableId = 'ga:' + profileId;
var startDate = getLastNdays(180);
var endDate = getLastNdays(0); // Today.
var optArgs = {
'dimensions': 'ga:productSku', // Comma separated list of dimensions.
'sort': '-ga:uniquePurchases,ga:productSku',
// Use segment if necessary 'segment': '',
//'filters': ''
'start-index': '1',
'max-results': '50000'
};
// Make a request to the API.
var results = Analytics.Data.Ga.get(
tableId, // Table id (format ga:xxxxxx).
startDate, // Start-date (format yyyy-MM-dd).
endDate, // End-date (format yyyy-MM-dd).
'ga:uniquePurchases,ga:itemQuantity,ga:itemsPerPurchase', // Comma seperated list of metrics.
optArgs);
if (results.getRows()) {return results;} else {throw new Error('No views (profiles) found');}
}
function getLastNdays(nDaysAgo) {
var today = new Date();
var before = new Date();
before.setDate(today.getDate() - nDaysAgo);
return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');
}
function outputToSpreadsheet(results, SPREADSHEET_ID,SHEET_NAME) {
// Outputting data to a Google Spreadsheet
var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
spreadsheet.getRange("w:z").clear();
// Print the headers.
var headerNames = [];
for (var i = 0, header; header = results.getColumnHeaders()[i]; ++i) {
headerNames.push(header.getName());
}
spreadsheet.getRange(1, 23, 1, headerNames.length).setValues([headerNames]);
// Print the rows of data.
spreadsheet.getRange(2, 23, results.getRows().length, headerNames.length).setValues(results.getRows());
Logger.log("GA-Data successfully transferred to Spreadsheet.");
}
//////////////////////////////////////////////////////////
////
//// PART III: Calculation ROI columns
////
//////////////////////////////////////////////////////////
var colCampSuffix = "_" + COLUMNSUFFIX;
var additionalColumnHeaders = [['export[1].statsAdW.ROICluster'+colCampSuffix, 'export[1].statsAdW.ROI'+colCampSuffix, 'export[1].statsAdW.ROIAction'+colCampSuffix,
'export[1].statsAdW.ItemType'+colCampSuffix,'export[1].statsAdW.Price'+colCampSuffix,'export[1].statsAdW.AvgQt'+colCampSuffix,
'export[1].statsAdW.NexClExpRevHiClDisc'+colCampSuffix,'export[1].statsAdW.NextClickSaleRoi'+colCampSuffix]];
spreadsheet.getRange('i1:p1').setValues(additionalColumnHeaders);
function getLastReportRow(spreadsheet) {
var column = spreadsheet.getRange('A:A');
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct+1);
}
var lastReportRow = getLastReportRow(spreadsheet);
Logger.log('lastReportRow: ' + lastReportRow);
// 1. COLUMN J: Set ROI formula
for (var i=2;i < lastReportRow;i++) {
var cell = spreadsheet.getRange(i,10);
cell.setFormulaR1C1("=(R[-0]C[-3]-R[-0]C[-7])/R[-0]C[-7]");
cell.setNumberFormat("0.00");
}
// 2. Column I: Set ROI Cluster
for (var i=2;i < lastReportRow;i++) {
var cell = spreadsheet.getRange(i,9);
cell.setFormulaR1C1('=IF(R[-0]C[1]>' + TARGET_MINROI + ',"ROI-High",if(R[-0]C[1]<1,"ROI-Low","ROI-Mid"))');
}
// 3. COLUMN M: Vlookup Price
for (var i=2;i < lastReportRow;i++) {
var cell = spreadsheet.getRange(i,13);
cell.setFormula("=IFERROR(VLOOKUP(R[-0]C[-12],adwordsData!s:t,2,false),0)");
cell.setNumberFormat("0.00");
}
// 4. COLUMN N: Vlookup Average Quantity
for (var i=2;i < lastReportRow;i++) {
var cell = spreadsheet.getRange(i,14);
cell.setFormula("=IFERROR(VLOOKUP(R[-0]C[-13],adwordsData!w:z,4,false),0)");
cell.setNumberFormat("0.0");
}
// 5. COLUMN L: SetItemType
/* Column Index Description
* M = Price = R[-0]C[1]
* N = Avg Quant = R[-0]C[2]
*/
for (var i=2;i < lastReportRow;i++) {
var cell = spreadsheet.getRange(i,12);
cell.setFormulaR1C1('=if(and(R[-0]C[1] > 10,R[-0]C[2] = 0),"SingleQt",if(' +
'and(R[-0]C[1] = 0,R[-0]C[2] = 0),"SingleQt",if(' +
'R[-0]C[2] >= 1.5,"MultiQt",if(' +
'and(R[-0]C[2] < 1.5,R[-0]C[2] >= 1),"SingleQt",if(' +
'and(R[-0]C[1] = 0,R[-0]C[2] = 0),"SingleQt","MultiQt")))))');
}
// 6. COLUMN O: SetNexClExpRevHiClDisc = Expected Revenue of Next Converted Click with High click Revenue Discount
/* Column Index Description
* Expected Variable values: Avg Quant = 10, Min Click Discount Thresh = 50, DiscountFactor = 1/800
* E = Clicks = R[-0]C[-10]
* F = CPC = R[-0]C[-9]
* G = Revenue = R[-0]C[-8]
* L = ItemType = R[-0]C[-3]
* M = Price = R[-0]C[-2]
* N = Quantity = R[-0]C[-1]
*/
for (var i=2;i < lastReportRow;i++) {
var cell = spreadsheet.getRange(i,15);
cell.setFormulaR1C1('= if(or(R[-0]C[-8] > 0,and(R[-0]C[-8] = 0,R[-0]C[-10] < 50)),if(' +
'R[-0]C[-2] = 0,"noprice",if(and(R[-0]C[-3] = "SingleQt",R[-0]C[-2] > 0.01),(R[-0]C[-8] + R[-0]C[-2]),if(' +
'and(R[-0]C[-3] = "MultiQt",R[-0]C[-2] > 0.01,R[-0]C[-1] > 0.1),(R[-0]C[-8] + R[-0]C[-2] * R[-0]C[-1]),if(' +
'and(R[-0]C[-3] = "MultiQt",R[-0]C[-2] > 0.01,R[-0]C[-1] = 0),(R[-0]C[-8] + R[-0]C[-2] * 10),"0")))),if(' +
'and(R[-0]C[-8] = 0,R[-0]C[-10] >= 50),if(' +
'R[-0]C[-2] = 0,"noprice",if(' +
'and(R[-0]C[-3] = "SingleQt",R[-0]C[-2] > 0.01),(1.05 - (R[-0]C[-10] / 800))*(R[-0]C[-8] + R[-0]C[-2]),if(' +
'and(R[-0]C[-3] = "MultiQt",R[-0]C[-2] > 0.01,R[-0]C[-1] > 0.1),(1.05 - (R[-0]C[-10] / 800))*(R[-0]C[-8] + R[-0]C[-2] * R[-0]C[-1]),if(' +
'and(R[-0]C[-3] = "MultiQt",R[-0]C[-2] > 0.01,R[-0]C[-1] = 0),(1.05 - (R[-0]C[-10] / 800))*(R[-0]C[-8] + R[-0]C[-2] * 10),"0"))))))');
cell.setNumberFormat("0.00");
}
// 7. COLUMN P: NextClickSaleRoi = ROI if Next Click converted and generated the revenue of column O
/* Column Index Description
* C = Cost = R[-0]C[-13]
* E = Clicks = R[-0]C[-11]
* F = CPC = R[-0]C[-10]
* G = Revenue = R[-0]C[-9]
* L = ItemType = R[-0]C[-4]
* M = Price = R[-0]C[-3]
* N = Quantity = R[-0]C[-2]
* 0 = NexClExpRev = R[-0]C[-1]
*/
for (var i=2;i < lastReportRow;i++) {
var cell = spreadsheet.getRange(i,16);
cell.setFormulaR1C1('=if(R[-0]C[-3] = 0,"noprice",if(and(R[-0]C[-4] = "SingleQt",R[-0]C[-3] > 0.01),' +
'(R[-0]C[-1] - (R[-0]C[-13] + R[-0]C[-10]))/(R[-0]C[-13] + R[-0]C[-10]),if(' +
'and(R[-0]C[-4] = "MultiQt",R[-0]C[-3] > 0.01,R[-0]C[-2] > 0.01),(R[-0]C[-1] - (R[-0]C[-13] + R[-0]C[-10]))/(R[-0]C[-13] + R[-0]C[-10]),if(' +
'and(R[-0]C[-4] = "MultiQt",R[-0]C[-3] > 0.01,R[-0]C[-2] = 0),((R[-0]C[-1] - (R[-0]C[-13] + R[-0]C[-10]))/(R[-0]C[-13] + R[-0]C[-10]))))))');
cell.setNumberFormat("0.00");
}
// 8. COLUMN K: ROI Status
for (var i=2;i < lastReportRow;i++) {
var cell = spreadsheet.getRange(i,11);
cell.setFormula('=if(R[-0]C[5] >' + TARGET_MINROI + ',"keep", "exclude")&" | "&R[-0]C[-2]');
}
// Reset import column headers
var priceColumnHeaders = [['export[1].statsAdW.id'+colCampSuffix, 'export[1].statsAdW.price'+colCampSuffix]];
spreadsheet.getRange('s1:t1').setValues(priceColumnHeaders);
// Reset imported GA data column headers
var gaDataColumnHeaders = [['export[1].statsAdW.ga:productSku'+colCampSuffix, 'export[1].statsAdW.ga:uniquePurchases'+colCampSuffix,
'export[1].statsAdW.ga:itemQuantity'+colCampSuffix, 'export[1].statsAdW.ga:itemsPerPurchase'+colCampSuffix]];
spreadsheet.getRange('w1:z1').setValues(gaDataColumnHeaders);
}

nrShoppingIdExcludeByRoas.js

Simple AdWords Script that generates a predictive-ROAS based "keep" or "exclude" value for Shopping products

Installation

Copy the complete source code into an empty script in your account.

Copy your version from the demosheet

Demo-Sheet: https://docs.google.com/spreadsheets/d/1SLXeVzzWj1JxGEMySWAQc4ZkpXRMlY85lAZM5WUB7IM/edit#gid=383285493

Required values

You will need a few global variables in order for the script to work properly.

Modify these variables:
var SPREADSHEET_URL = "URL" // Your copied URL

var TIMESPAN = "LAST_30_DAYS"; // Timespan of the report.

var CAMPAIGN_INCLUDE_I = "'PLA'"; // Name of your selected shopping Campaign. "'Name'", @throws InvalidAWQLConditionException

var CAMPAIGN_INCLUDE_II = "'PLA'"; // IF only one is needed, set II the same as I

var CAMPAIGN_EXCLUDE_I = "'AT'"; // Use to exclude campaigns

var CAMPAIGN_EXCLUDE_II = "'AT'"; // IF only one is needed, set II to the same value as I

var COLUMNSUFFIX = "AT";

var PRICEFEED_URL = "http://transport.productsup.io/3abf070bb070c812df5d/channel/92746/rey_idandprice.csv"; // Custom feed URL with two columns id and price

var SEPARATOR = '\t'; //CSV seperator of feed

var GA_PROFILE_ID = 9999999; // << SET Google Analytics PROFILE ID here (NOT property ID), see Admin > View Settings

var TARGET_MINROI = 1.5; // The Min ROI, given the next click is a sale. ROI = (Rev + ExpRev - (Cost + CPC)) / (Cost + CPC)

Don't modify those:
var DATASHEET = "adwordsData"; // Name of the sheet to receive data, don't change

Wrapper methods

Public void

getAdWordsData()

Writes Shopping product performance data to the sheet.

runRoiCalculator()

Adds the calculation layer by invcorporating price and quantity data to generate a keep / exclude recommendation per product ID.

@norisk-marketing

This comment has been minimized.

Copy link
Owner Author

commented Mar 13, 2018

This Adwords Script adds a ROAS-based evaluation to a shopping performance report by ID.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.