Newbie question: My data array (from google ads scripts) 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 Scripts Forum Advisor]

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

After reviewing your concern, I understand you are having some issues while running the script. Could you please provide the following details to investigate further. 

• Google Ads account ID / CID
• Name of the script in the account.
• The uncropped screen shot of the error you encountered.

This message is in relation to case "ref:!00D1U01174p.!5004Q02qXNTj:ref"

Thanks,
 

Google Ads Scripts Team

[Dmytro]

It is happening because you are using 'segments'. If product title was modified in the shopping performance report you'll receive 2 product rows.

[Jay Wilner]

ID: 160-068-3827

Name of the script in the account: labelizer 11042023
The uncropped screen shot of the error you encountered: https://imgur.com/ogUSHmP <== screenshot of gsheet written to by the script. 

[Jay Wilner]

Dmytro:

Thank you so much for replying to my question. Had a couple queries about your reply if you don't mind:

"It is happening because you are using 'segments'."  <== which of my statements using segments are you referring to? https://imgur.com/l96KpaU

"If product title was modified in the shopping performance report you'll receive 2 product rows." <== What do you mean here? What statements in my code are you referring to please? Here is a screenshot of three instances of 'title': https://imgur.com/PU26Yi4

[Dmytro]

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);

segments.product_item_id - is not unique in shopping_performance_view report.

segments.product_title - when you update product title in merchant center it won't make any impact on past Google Ads data and you'll have segmeted data for the same product several times.

[Google Ads Scripts Forum Advisor]

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

After reviewing your concern, I understand that you are getting duplicate rows. I would like to inform you that I have checked your script and sometimes getting two rows for product Id is a normal behaviour return by javascript code. For combine the elements that have the same product id you need to make use of javascript array functions.