Shopping_Performance report has duplicate rows in it. How do i combine those duplicates, and their data together?
Jay Wilner
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 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);
}
Google Ads API Forum Advisor
Thank you for reaching out to the Google Ads API support team.
Please note that our team can only assist with the technical queries or concerns related to the Google Ads API. As this issue is related to the Google Ads Scripts, I am routing this request to the Google Ads Scripts team as they are well equipped to assist you with this.
Thanks,
 |
Google Ads API Team |
