|
/* |
|
* 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); |
|
} |
This Adwords Script adds a ROAS-based evaluation to a shopping performance report by ID.