-
-
Save santosonit/431ad1ffc09a9c7368d1cedc409855ec to your computer and use it in GitHub Desktop.
some demo here
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"timeZone": "America/New_York", | |
"dependencies": { | |
}, | |
"webapp": { | |
"access": "ANYONE", | |
"executeAs": "USER_ACCESSING" | |
}, | |
"exceptionLogging": "STACKDRIVER", | |
"runtimeVersion": "V8", | |
"sheets": { | |
"macros": [{ | |
"menuName": "SP prepare sheet", | |
"functionName": "SPpreparesheet", | |
"defaultShortcut": "Ctrl+Alt+Shift+1" | |
}] | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// test for antonio | |
// test 2 | |
// test3 | |
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); | |
// Or DocumentApp or FormApp. | |
ui.createMenu('Bids') | |
.addItem('Optimize Bids', 'optimizeBid') | |
.addToUi(); | |
} | |
// global configuration | |
var configSheetName = "config"; | |
/* | |
-Filter to add as overview critera: Column B = Record Type. Make sure it's both Keywords and Product Targeting | |
-Create functions for repetitive comparison, getting values as parameters and spitting confirmations; | |
-Refactor de code so as to users can easily read it and understand it; | |
-Add comments to each line, explaining/translating it so that non-programmers can understand it; | |
-Make sure that the file sctructure, data types are correct, data format is correct so that the code runs. If not, highlight it to | |
the user for them to correct it; | |
-After the code has run, check the result to see if it passes tests for integrity; | |
-Check for existence of needed columns/data prior to running, so that it acommodates slightly different formats, input file wise; | |
-Set up a repository where this code version's can be consistently managed (Github?); | |
-Verify if the code structure acccomodates Sponsored Brands Tab | |
*/ | |
function optimizeBid() { | |
const ss = SpreadsheetApp.getActiveSpreadsheet(); | |
const configSheet = ss.getSheetByName('config'); //Maps the sheet where threshold and other paramters are | |
const spcSheet = ss.getSheetByName('Sponsored Products Campaigns'); //Maps the sheet where the values coming from Amazon | |
const configData = configSheet.getRange(1, 1, configSheet.getLastRow(), configSheet.getLastColumn()).getValues();//Gets the values from sheet Config | |
//Columns' names in the config tab. | |
const configTabHeaders = configData[0]; //Gets the first row of config tab to check where the columns' positions are | |
let configSearchTermColPosition = configTabHeaders.indexOf('Search Term Worked'); | |
let configMatchTypeColPosition = configTabHeaders.indexOf('Match Type'); | |
let clickThresholdColPosition = configTabHeaders.indexOf('Click Threshold'); | |
let targetAcosColPosition = configTabHeaders.indexOf('Target Acos'); | |
//Gets the values from Sponsored Product Campaign | |
let spcTabHeaders = spcSheet.getRange(1, 1, 1, spcSheet.getLastColumn()).getValues().flat(); | |
//let spcTabHeaders = spcData[0]; //Gets the first row of SPC tab to check where the columns' positions are | |
let spcSearchTermColPosition = spcTabHeaders.indexOf('Keyword or Product Targeting'); | |
//Adds new Columns, if they don't exist yet | |
let newColumns = ['CPC', 'New Bid'];//Columns' names to be checked and added, if they don't exist yet | |
for (let newColumn = 0; newColumn < newColumns.length; newColumn++) {//Iterates over the newColumns elements | |
if (spcTabHeaders.indexOf(newColumns[newColumn]) === -1) { //Checks if each of them exists in the spreadsheet header row | |
let newcolPosition = spcSearchTermColPosition + 1;//Gets 01 position on the left of column Search Terms | |
spcSheet.insertColumnsBefore(newcolPosition, 1);//Inserts the column | |
spcSheet.getRange(1, newcolPosition).setValue(newColumns[newColumn]);//Names the column added | |
newcolPosition++;//Increments the iteration to go through the next word in the array (newColumns) | |
} | |
} | |
spcTabHeaders = spcSheet.getRange(1, 1, 1, spcSheet.getLastColumn()).getValues().flat(); //Gets the values from Sponsored Product Campaign | |
//spcTabHeaders = spcSheet.getRange(1, 1, spcSheet.getLastRow(), spcSheet.getLastColumn()).getValues().flat(); //Gets the updated first row of SPC tab to check where the columns' positions are | |
//Columns' names that come in the Sponsored Product Campaigns tab. | |
spcSearchTermColPosition = spcTabHeaders.indexOf('Keyword or Product Targeting'); | |
let recordTypeColPosition = spcTabHeaders.indexOf('Record Type'); | |
let spcMatchTypeColPosition = spcTabHeaders.indexOf('Match Type'); | |
let spcOrdersColPosition = spcTabHeaders.indexOf('Orders'); | |
let spcAcosColPosition = spcTabHeaders.indexOf('ACoS'); | |
let spcClicksColPosition = spcTabHeaders.indexOf('Clicks'); | |
let spcSpendColPosition = spcTabHeaders.indexOf('Spend'); | |
let spcNewBidColPosition = spcTabHeaders.indexOf('New Bid'); | |
spcNewBidColPosition = spcNewBidColPosition + 1;//Adds to get the column to receive the calculated value below | |
let spcCPCColPosition = spcTabHeaders.indexOf('CPC'); | |
let maxBidColPostition = spcTabHeaders.indexOf('Max Bid'); | |
//Record Types to be checked and run calculations for | |
let recordTypesAccepted = ['Keyword', 'Product Targeting'];//If need be, more types can be added like so (e.g.: , 'Another Type') | |
//Locates Spend and Clicks columns' ranges (letter) so as to build the formulas for CPC column; | |
let spcSpendColRange = spcSheet.getRange(2, spcSpendColPosition + 1, 1, 1).getA1Notation(); //spcSpendColPosition gets + 1, because indexOf starts at zero | |
let spcClicksColRange = spcSheet.getRange(2, spcClicksColPosition + 1, 1, 1).getA1Notation(); //spcClicksColPosition gets + 1, because indexOf starts at zero | |
let cpcFormula = '=IFERROR(' + spcSpendColRange + '/' + spcClicksColRange + ';0)'; //Builds the formula for row of CPC column | |
let spcCPCcolRange = spcSheet.getRange(2, spcCPCColPosition + 1, spcSheet.getLastRow(), 1);//Gets the range in the format for receiving the formula | |
spcCPCcolRange.setFormula(cpcFormula).setNumberFormat("€##0.00").setVerticalAlignment("middle"); | |
let spcData = spcSheet.getRange(1, 1, spcSheet.getLastRow(), spcSheet.getLastColumn()).getValues(); | |
SpreadsheetApp.flush() | |
for (let spcRow = 0; spcRow < spcData.length; spcRow++) { //Goes through the rows of Sponsored Product Campaign tab | |
for (let configRow = 0; configRow < configData.length; configRow++) { //Goes through over the rows of config tab | |
//Gets search terms values of both SPC and config tabs by accessing the row | |
//iterated over and Search Term Column Position, therefore accessing the cell value. | |
let configSearchTerm = configData[configRow][configSearchTermColPosition]; | |
let spcSearchTerm = spcData[spcRow][spcSearchTermColPosition]; | |
//Gets Match Type terms values of both SPC and config tabs | |
let configMatchType = configData[configRow][configMatchTypeColPosition]; | |
let spcMatchType = spcData[spcRow][spcMatchTypeColPosition]; | |
//Gets Record Type from SPC tab and checks if it contains any of the types listed in the variable recordTypesAccepted; | |
let spcRecordType = spcData[spcRow][recordTypeColPosition]; | |
//Checks if SPC tab row value matches any of the record types accepted... | |
if (recordTypesAccepted.indexOf(spcRecordType) > -1 && configSearchTerm === spcSearchTerm && configMatchType === spcMatchType) { | |
//Defines variables for each of the values that will be compared | |
let spcClicks = spcData[spcRow][spcClicksColPosition]; | |
let spcOrders = spcData[spcRow][spcOrdersColPosition]; | |
let spcAcos = spcData[spcRow][spcAcosColPosition]; | |
let cpc = spcData[spcRow][spcCPCColPosition]; | |
let maxBid = spcData[spcRow][maxBidColPostition]; | |
let clickThreshold = configData[configRow][clickThresholdColPosition]; | |
let targetAcos = configData[configRow][targetAcosColPosition]; | |
//Calls a function spcAcosMinusTargetAcos that calculates rows' data passed in each loop | |
var acosMinusTargetAcos = spcAcosMinusTargetAcos(spcAcos, targetAcos); | |
let row = spcRow + 1; | |
//Calculates New Bids based on sets of criteria and places that new bid into the newly created column (New Bid); | |
if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos > 0 && acosMinusTargetAcos <= 0.2) { | |
let newBid = cpc * (1 - acosMinusTargetAcos); | |
spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271"); | |
} else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos > 0.2) { | |
let newBid = cpc * .8; | |
spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271"); | |
} else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos < 0 && acosMinusTargetAcos > -0.05) { | |
let newBid = cpc * (1 - acosMinusTargetAcos); | |
spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271"); | |
} else if (spcClicks >= clickThreshold && spcOrders >= 1 && acosMinusTargetAcos < -0.05) { | |
let newBid = cpc * 1.075; | |
spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271"); | |
} else if (spcOrders = 0 && spcClicks >= clickThreshold && spcClicks < (clickThreshold + 0.50)) { | |
let newBid = cpc * 0.90; | |
spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271"); | |
} else if (spcOrders = 0 && spcClicks >= clickThreshold + 0.50) { | |
let newBid = cpc * 0.80; | |
spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid).setBackground("#D8E271"); | |
} else if (spcOrders = 0 && spcClicks < clickThreshold) { | |
let newBid = maxBid; | |
spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid); | |
} else { | |
let newBid = maxBid; | |
spcSheet.getRange(row, spcNewBidColPosition).setValue(newBid); | |
} | |
} | |
} | |
} | |
//Formats New Bid column | |
let spcNewBidRange = spcSheet.getRange(2, spcNewBidColPosition, spcSheet.getLastRow(), 1); | |
spcNewBidRange.setNumberFormat("€##0.00").setVerticalAlignment("middle"); | |
//Looks for a record sheet and deletes it, so that a new, updated one is created | |
var delSheet = ss.getSheetByName("Sponsored Products Campaigns (Record)"); | |
if (delSheet) { | |
ss.deleteSheet(delSheet); | |
var recordSheet = spcSheet.copyTo(SpreadsheetApp.getActiveSpreadsheet()); | |
recordSheet.setName('Sponsored Products Campaigns (Record)') | |
} | |
//Rename New Bid Column into Max Bid | |
spcSheet.getRange(1, spcNewBidColPosition).setValue('Max Bid'); | |
spcSheet.deleteColumn(maxBidColPostition + 1); | |
spcSheet.deleteColumn(spcCPCColPosition); | |
//Brings user to the Record tab | |
recordSheet.getRange("A1").activateAsCurrentCell(); | |
ss.toast('The bids have been recalculated!') | |
} | |
//Gets Sponsored Product Campaign Acos minus Target Acos and outputs the result for comparisons run in the function optimizeBid | |
function spcAcosMinusTargetAcos(spcAcos, targetAcos) { | |
return spcAcos - targetAcos; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment