Hello,
I am a new javascript programmer using Google Ads Scripts to fetch data from our client's Google Merchant Center (product shopping ads data), process that data, then use in optimizing their campaign(s). I am using the Shopping Performance Report api <https://developers.google.com/google-ads/api/fields/v14/shopping_performance_view> to report on impressions, clicks, revenue, etc. The data is pulled into a google sheet and then i wrote some javascript to process that data row by row, looking at the values and making decisions, labeling product performance into one of 8 different performance categories ('top performer', 'worst performer' etc.). At the end of that row loop, i store that row's data into a temporary array ('tempArray'). I do that for all the rows in the api report object. *My problem is the data sometimes returns two rows for the product ID. Not sure why. Is that normal?* However, I need to do the following just don't know how yet: Once the data is finished processing, I need to loop through it and combine any elements that have the same product Id AND combine their values (clicks total = duplicate 1 clicks + duplicate 2 clicks, impressions total = duplicate 1 impressions + duplicate 2 impressions). The tempArray data looks like this (example duplicate elements): [ [shopify_US_8059591393525_43884977225973, null, Chasing Joy Hat in Khaki, 8, 0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, low impressions], [shopify_US_8059591393525_43884977225973, null, Chasing Joy Hat in Khaki, 12094, 115, 0.009508847362328427, 36.93, 1.165053, 87.08227563, 31.698128754657517, 2.3580361665312752, 0.010130895652173913, under-index]] Do I need to change the data structure to an array of objects, then use a JS array function like map or reduce? I found this article on merging duplicates but it was too complex at first glance: https://www.tutorialspoint.com/merge-and-remove-duplicates-in-javascript-array Thanks in advance! ================================================= My code: const settings = { url: 'Google Sheet URL here', sheet: 'Sheet1', suppSheet: 'suppFeed', // write data for supplemental feed custom label 1 for labelizer lookBackDays: 90, convRate: 2.7, breakEvenRoas: 4.0, impressionThreshold: 50, clicksThreshold: 35, minCTR:0.01 } function main() { const timeZone = AdsApp.currentAccount().getTimeZone(); const format = 'yyyy-MM-dd'; const today = new Date(); const oneDay = 1000*60*60*24; const startDate = Utilities.formatDate(new Date(today-(oneDay*settings.lookBackDays)),timeZone,format); const yesterday = Utilities.formatDate(new Date(today-oneDay),timeZone,format); const query = `SELECT segments.product_item_id, segments.product_custom_attribute3, segments.product_title, metrics.impressions, metrics.clicks, metrics.ctr, metrics.cost_micros, metrics.conversions, metrics.conversions_value FROM shopping_performance_view WHERE segments.date BETWEEN "${startDate}" AND "${yesterday}"`; const response = AdsApp.search(query); const data = []; const dataSupp = []; // store the itemID and custom label 1 (performance label from labelizer program) var count = 0; //counter to count products processed while (response.hasNext()) { const row = response.next(); //before pushing row to array, we want to process the data into 'labels' or 'buckets' according to their performance var item_id = row.segments.productItemId; var new_item_id = item_id.replace("_us_","_US_"); var custom_label_3 = row.segments.productCustomAttribute3; var title = row.segments.productTitle; var impressions = row.metrics.impressions; var clicks = row.metrics.clicks; var ctr = row.metrics.ctr; var costMicros = row.metrics.costMicros; var cost = costMicros/1000000; var conversions = row.metrics.conversions; var conversionValue = row.metrics.conversionsValue; //calculate cpa, roas and % conv if ((cost==0) || (conversions==0)) { var cpa = 0; } else { var cpa = cost/conversions; } //roas if ((conversionValue==0) || (cost==0)) { var roas = 0; } else { var roas = conversionValue/cost; } //conv Rate if ((clicks==0) || (conversions==0)) { var convRate = 0; } else { var convRate = conversions/clicks; } // put into performance buckets var isProductType = ''; //////////////// if low impressions ///////////////////// if (impressions < settings.impressionThreshold){ isProductType = 'low impressions'; } //////////////// if clicks >= click minimum, run thru nested if's to check ROAS values ////// else if (clicks >= settings.clicksThreshold && roas >= settings.breakEvenRoas*1.3) { isProductType = 'over-index'; } else if (clicks >= settings.clicksThreshold && roas >= settings.breakEvenRoas) { isProductType = 'index'; } else if(clicks >= settings.clicksThreshold && roas > 0) { isProductType = 'under-index'; } else if (clicks >= settings.clicksThreshold && roas ==0){ isProductType = 'worst performer'; } /////////////// if clicks < click threshold, with conversions //////////////////// else if (clicks <= settings.clicksThreshold && conversions >0 && roas >= settings.breakEvenRoas*1.3) { isProductType = 'over-index-lowData'; } else if (clicks <= settings.clicksThreshold && conversions >0 && roas >= settings.breakEvenRoas) { isProductType = 'index-lowData'; } else if(clicks <= settings.clicksThreshold && conversions >0 && roas > 0) { isProductType = 'under-index-lowData'; } else if (clicks <= settings.clicksThreshold && conversions >0 && roas == 0) { isProductType = 'worst performer-lowData'; } /////////////// if clicks < clicks threshold, with no conversions, look at performance based on CTR but use 30X impr threshold //////////// else if (clicks <= settings.clicksThreshold && conversions == 0 && impressions >= settings.impressionThreshold*30 && ctr >= settings.minCTR*4 ) { isProductType = 'over-index-ctr'; } else if (clicks <= settings.clicksThreshold && conversions == 0 && impressions >= settings.impressionThreshold*30 && ctr >= settings.minCTR ) { isProductType = 'index-ctr'; } else if (clicks <= settings.clicksThreshold && conversions == 0 && impressions >= settings.impressionThreshold*30 && ctr > 0 && ctr < settings.minCTR) { isProductType = 'under-index-ctr'; } else if (clicks <= settings.clicksThreshold && conversions == 0 && impressions >= settings.impressionThreshold*30 && ctr == 0 ) { isProductType = 'worst-performer-ctr'; } /////////////// if clicks < clicks threshold, with no conversions, but < 30ximpressionThreshold ////// else if (clicks <= settings.clicksThreshold && conversions == 0 && impressions >= settings.impressionThreshold && ctr == 0 ) { isProductType = 'worst-performer-ctr-lowData'; } else if (clicks <= settings.clicksThreshold && conversions == 0 && impressions >= settings.impressionThreshold && ctr >= settings.minCTR*4 ) { isProductType = 'over-index-ctr-lowData'; } else if (clicks <= settings.clicksThreshold && conversions == 0 && impressions >= settings.impressionThreshold && ctr > 0 && ctr < settings.minCTR) { isProductType = 'under-index-ctr-lowData'; } else if (clicks <= settings.clicksThreshold && conversions == 0 && impressions >= settings.impressionThreshold && ctr > 0 && ctr >= settings.minCTR) { isProductType = 'index-ctr-lowData'; } else { isProductType = 'not enough data'; } data.push([ new_item_id, custom_label_3, title, impressions, clicks, ctr, cost, conversions, conversionValue, cpa, roas, convRate, isProductType ]) Logger.log(data); dataSupp.push([ new_item_id, isProductType ]) count = count+1; //increment counter } // end of while loop, all rows processed //Logger.log('we processed: ',count,' products!') //Logger.log(dataSupp); //add header row to data sheet data.unshift([ 'Item ID', 'custom label 3', 'Title', 'Imp', 'Clicks', 'Ctr', 'Cost', 'Conv', 'Conv value', 'Cpa', 'Roas', '% Conv', 'custom label 1' // column containing performance label data ]); //add header row to data sheet dataSupp.unshift([ 'Item ID', 'custom label 1' ]); //write data to data sheet const sheet = SpreadsheetApp.openByUrl(settings.url).getSheetByName(settings.sheet); sheet.clearContents(); // delete any old data first sheet.getRange(1,1,data.length,data[0].length).setValues(data); //write data to Supplemental data sheet const suppSheet = SpreadsheetApp.openByUrl(settings.url).getSheetByName(settings.suppSheet); suppSheet.clearContents(); // delete any old data first suppSheet.getRange(1,1,dataSupp.length,dataSupp[0].length).setValues(dataSupp); } -- -- =~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~ Also find us on our blog: https://googleadsdeveloper.blogspot.com/ =~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~ You received this message because you are subscribed to the Google Groups "AdWords API and Google Ads API Forum" group. To post to this group, send email to adwords-api@googlegroups.com To unsubscribe from this group, send email to adwords-api+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/adwords-api?hl=en --- You received this message because you are subscribed to the Google Groups "Google Ads API and AdWords API Forum" group. To unsubscribe from this group and stop receiving emails from it, send an email to adwords-api+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-api/82e6ed09-09cc-4f01-b8dd-20dad265d440n%40googlegroups.com.
