/// --------------------------------------------------------------- // Name : Get Specified Placement Metrics (MCC Versions) // // Authors : info@optimumclick.co.uk & nick.broad@searchQualified.co.uk // // Description : // // i) Make a copy of the sample placement sheet found here ("https://docs.google.com/spreadsheets/d/1pso7ChqoLYeVRuNWkNt5lwwurK87g8r6WfTnPiqPKyw/edit#gid=0") // ii) Add your list of domains to the "Domains" tab into column A. // iii) Set the CONFIG parameters below according to instructions. // iv) Run the script // v) Previous results will be cleared and new values added. The domain data across all accounts is aggregated // shown against the master list of domains on the "Domains" tab (as specified in ii)) // // Notes // // *** Contact Us for a hidden feature *** // // *** Single Account Version available *** // // Change Log : // // 14th Dec 2016 : Version 1.0 // // --------------------------------------------------------------- // User configuration // ------------------ var CONFIG={ // Enter the url of the sheet containing the target placements CONTROL_SHEET:"", // Enter the tab name containing the target placements, this tab must exist with the list of placements. PLACEMENT_LIST_TAB:"Domains", // Reporting start date. Format is YYYYMMDD, blank defaults to 1 year before END_DATE START_DATE:"", // Reporting end date. Format is YYYYMMDD, blank defaults to yesterday. Hence, if both dates are blank the default is last year END_DATE:"", // Include paused campaigns ? Y/N. If left blank the default is Y INCLUDE_PAUSED_CAMPAIGNS:"Y", // Include conversion metrics ? Y/N. If left blank the default is N INCLUDE_CONVERSIONS:"Y", // Enter the clients ids of the accounts you wish to run against (maximum 50). ACCOUNT_IDS:[ '123-111-1111', '123-222-2222', '123-333-3333', '123-444-4444' ], // End User configuration // ------------------------------------------------------------------- // Do not change past here, unless you are familiar with AdWords scripts METRIC_LIST:['Impressions','Clicks','Cost'], DEBUG:false } function main() { var controlSheet=SpreadsheetApp.openByUrl(CONFIG.CONTROL_SHEET) ; if (!controlSheet) { Logger.log("Cannot open control sheet " + CONFIG.CONTROL_SHEET) ; return ; } // Set-up parameters for the placement report var parameters=setUpParameters(controlSheet) ; var parametersJSON=JSON.stringify(parameters) ; debugMsg(parametersJSON) ; // ------------------------------- // Validate the config customer ids and process each accounts var accountsToRunOn=[] ; var accountIterator=MccApp.accounts().withIds(CONFIG.ACCOUNT_IDS).get() ; if (accountIterator.totalNumEntities()>0) { while (accountIterator.hasNext()) { accountsToRunOn.push(accountIterator.next().getCustomerId()) ; } if(accountsToRunOn.length > 0) { Logger.log("Process " + accountsToRunOn.length + " Accounts") ; MccApp.accounts().withIds(accountsToRunOn).executeInParallel("processAccount", "aggregateData", parametersJSON); } } } function setUpParameters(controlSheet) { var parameters={} ; // Get list of placements parameters.placementList=getPlacementList(controlSheet) ; if (!parameters.placementList) return ; // Define list of metrics parameters.metricList=CONFIG.METRIC_LIST ; if (CONFIG.INCLUDE_CONVERSIONS.toLowerCase()=="y") parameters.metricList.push('Conversions') ; // Define report where clause if (CONFIG.INCLUDE_PAUSED_CAMPAIGNS.toLowerCase()!="n") { parameters.whereClause="WHERE CampaignStatus != REMOVED " ; } else { parameters.whereClause="WHERE CampaignStatus = ENABLED " ; } parameters.whereClause+=" AND Impressions > 0 " ; // Define date range if (CONFIG.END_DATE=="") { parameters.endDate=getYesterday() ; } else { parameters.endDate=CONFIG.END_DATE ; } debugMsg(parameters.endDate) ; if (CONFIG.END_DATE=="") { parameters.startDate=getLastYear(parameters.endDate); } else { parameters.startDate=CONFIG.START_DATE ; } debugMsg(parameters.startDate) ; return (parameters) ; } function aggregateData(results) { var controlSheet=SpreadsheetApp.openByUrl(CONFIG.CONTROL_SHEET) ; if (!controlSheet) { Logger.log("Cannot open control sheet " + CONFIG.CONTROL_SHEET) ; return ; } var mccParameters=setUpParameters(controlSheet) ; for (var i = 0; i < results.length; i++) { // Get the ExecutionResult for an account. var result = results[i]; Logger.log('Customer ID: %s; status = %s.', result.getCustomerId(), result.getStatus()); // Check the execution status. This can be one of ERROR, OK, or TIMEOUT. if (result.getStatus() == 'ERROR') { Logger.log("-- Failed with error: '%s'.", result.getError()); continue ; } else if (result.getStatus() != 'OK') { Logger.log("-- Failed with error: '%s'.", result.getError()); continue ; } var parameters = JSON.parse(result.getReturnValue()) ; for (var placement in parameters.placementList) { if (mccParameters.placementList[placement]) { for (var param in parameters.placementList[placement]) { debugMsg(param + "->" + parseFloat(parameters.placementList[placement][param])) ; if (!mccParameters.placementList[placement][param]) mccParameters.placementList[placement][param]=0 ; mccParameters.placementList[placement][param]+=parseFloat(parameters.placementList[placement][param]) ; } } } } mccParameters.single=false ; mccParameters.resultsTabName=CONFIG.PLACEMENT_LIST_TAB ; writeResults(mccParameters) ; } function getYesterday() { var today = new Date(); var yesterday = new Date(today); yesterday.setDate(today.getDate() - 1) ; return (Utilities.formatDate(yesterday, 'TZ', 'yyyyMMdd')) ; } function getLastYear(_date) { var oneYearAgo=parseInt(_date.substring(0,4))-1 ; // Basically YYYY - 1 var month=parseInt(_date.substring(4,6))-1 ; // Months are 0-11 var day=parseInt(_date.substring(6)) ; var _date=new Date(oneYearAgo.toString(), month, day) ; _date.setDate(_date.getDate() + 1) ; return (Utilities.formatDate(_date, 'TZ', 'yyyyMMdd')) ; } function getPlacementList(controlSheet) { var placementTab=controlSheet.getSheetByName(CONFIG.PLACEMENT_LIST_TAB) ; if (!placementTab) { Logger.log("Error : Cannot access control sheet Accounts tab " + CONFIG.PLACEMENT_LIST_TAB) ; return ; } var rows = placementTab.getDataRange(); var values = rows.getValues(); var numRows=values.length ; if (numRows<2) { Logger.log("No placements specified") ; return ; } // Placements start at row 2. var placements={} ; for(var i = 1; i>> " + str) ; } function processAccount(parametersJSON) { var parameters=JSON.parse(parametersJSON) ; var queryStr='SELECT Criteria, ' + parameters.metricList.join(",") + ' ' + 'FROM PLACEMENT_PERFORMANCE_REPORT ' + parameters.whereClause + ' ' + 'DURING ' + parameters.startDate + ',' + parameters.endDate ; debugMsg(queryStr) ; var report = AdWordsApp.report(queryStr) ; var rows = report.rows(); while(rows.hasNext()) { var row = rows.next(); var placement=row['Criteria'].split(":")[0] ; if (parameters.placementList[placement]) { // debugMsg("Get Stats for " + placement) ; for (var i=0;(i" + parseFloat(row[param])) ; if (!parameters.placementList[placement][param]) parameters.placementList[placement][param]=0 ; parameters.placementList[placement][param]+=parseFloat(row[param]) ; } } } parameters.resultsTabName=AdWordsApp.currentAccount().getName() ; parameters.single=true ; writeResults(parameters) ; return(JSON.stringify(parameters)) ; } function writeResults(parameters) { var controlSheet=SpreadsheetApp.openByUrl(CONFIG.CONTROL_SHEET) ; if (!controlSheet) { Logger.log("Cannot open control sheet " + CONFIG.CONTROL_SHEET) ; return ; } // Find target tab var resultsTab=controlSheet.getSheetByName(parameters.resultsTabName) ; // If account and tab exists, then delete and recreate. if (parameters.single) { if (resultsTab) controlSheet.deleteSheet(resultsTab) ; // Create tab if it doesn't exist and write the domain list resultsTab=controlSheet.insertSheet(parameters.resultsTabName) ; resultsTab.appendRow(["Domains"]) ; // Enter placements for (var placement in parameters.placementList) { resultsTab.appendRow([placement]) ; } } else { if (!resultsTab) { Logger.log("Unable to create the results tab " + parameters.resultsTabName) ; return ; } } // Write the headers in - redo this as conversions are optional resultsTab.getRange(1, 2, 1, resultsTab.getMaxColumns()-1).clear() ; var headers=[] ; headers.push(parameters.metricList) ; resultsTab.getRange(1, 2, 1, parameters.metricList.length).setValues(headers) ; if (parameters.addPlacements) { // Clear any existing placements if (resultsTab.getMaxRows()>1)resultsTab.getRange(2, 1, resultsTab.getMaxRows()-1, 1).clear() ; } // ------------------------------------------ // Update the placements with aggregated data var rows = resultsTab.getDataRange(); var values = rows.getValues(); var numRows=values.length ; if (numRows<2) { Logger.log("No placements specified") ; return ; } // Placements start at row 2. for(var i = 1; i1) { resultsTab.getRange(spreadSheetRow, 2, 1, resultsTab.getMaxColumns()-1).clear() ; } resultsTab.getRange(1, 2, 1, parameters.metricList.length).setValues(headers) ; resultsTab.getRange(spreadSheetRow, 2, 1, parameters.metricList.length).setValues(metricValues) ; } } }