Adding negative keywords from google sheet

[Zachary CANNON]

I'm building a script that pulls shopping query performance data and auto adds negatives based on performance criteria and shows this in a google sheet. 

However I'm stuck when it comes to adding the negative keywords. I need a way for the script to add each query as negative at the ad group level and then iterate to the next row and add that query to the respective ad group. 

I have both the ad group id list and the negative keyword list as arrays

Anyone have an idea on how to do this?

I've attached the script and what the data in the sheet looks like. I know that the final for loops is incorrect!

Thanks!

function main() {

    var spreadsheet_url = "https://docs.google.com/spreadsheets/d/12GDoI-GJ4Xq3vFVl7zd1EaZiYWTxnC5hONh-OiDwxUA/edit#gid=0";

    var date_range = 'TODAY';

 

    var columns = ['CampaignName',

                  'AdGroupId',

                  'Query',

                   'AverageCpc',

                    'Clicks',

                   'ConversionRate',

                   'Conversions',

                   'ConversionValue',

                   'Cost',

                   'Ctr',

                   'Impressions'];

    var columns_str = columns.join(',') + " ";

    

    var sheet = SpreadsheetApp.openByUrl(spreadsheet_url).getActiveSheet();

    sheet.clear();

    if(sheet.getRange('A1:A1').getValues()[0][0] == "") {

      sheet.clear();

      sheet.appendRow(columns);

    }

    

    var report_iter = AdWordsApp.report(

      'SELECT ' + columns_str +

      'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +

      'WHERE CampaignName CONTAINS "Omnia" ' +

      'AND Clicks >5 ' +

      'DURING ' +date_range).rows(); 

    

    while(report_iter.hasNext()) {

      var row = report_iter.next();

      var row_array = [];

      for(var i in columns) {

         row_array.push(row[columns[i]]);

      }

      sheet.appendRow(row_array);

    }

//ROAS Calculation / Negative Keyword List Push to array - working

    var data_range = sheet.getDataRange();

    var last_row = data_range.getLastRow();

    sheet.getRange(1,12).setValue("ROAS");

    sheet.getRange(1,13).setValue("Poor Performer");

    var Negative = "Negative";

    var negativelist = [];

    var adGroupIdList = [];

    for(var r=2;r<=last_row;r++) {

        var convValue = data_range.getCell(r,8).getValue();

        var cost = data_range.getCell(r,9).getValue();

        var roas = (convValue / cost);

        sheet.getRange(r, 12).setValue(roas);

        

        if (roas < 2 ) {

            sheet.getRange(r,13).setValue(Negative);

            if (Negative === "Negative") {

                // Add all negative keywords to an array

                negativelist.push(data_range.getCell(r,3).getValue());

                // Add all the ad group names where keyword is labelled negative into an array

                adGroupIdList.push(data_range.getCell(r,2).getValue());

            }

        }

    }

    Logger.log(negativelist);

    Logger.log(adGroupIdList); //working to here

//**// Add negative keywords at the ad group level  

  

var groupIterator = AdWordsApp.shoppingAdGroups()

.withIds(adGroupIdList)

.get()

while (groupIterator.hasNext()) {

  var adGroup = groupIterator.next();

  var adGroupName = adGroup.getName();

  var campaignName = adGroup.getCampaign().getName();

  

  //** */

  for (var i=0; i<negativelist.length; i++) {

    adGroup.createNegativeKeyword("[" + negativelist[i] + "]");

  }

  

}

Logger.log("Finished.");

[Sigurd Fabrin]

I would opt for negative keyword lists on campaign level. AdGroup level negative keywords doesn't scale well.

If you only have 14 adGroups and 15 negative keywords, no problem - but then you don't need a script ;)

If your account is even a slightly bigger and you have say 232 adGroups and your list contain 874 phrases. You will need to add 202768 negative keywords instead of just adding the 874 to a list.

 Sigurd

[Google Ads Scripts Forum Advisor]

Hi Sigurd,

Thank you for sharing your thoughts here.

@Zachary,

You may consider on what Sigurd provided. However, if you still want to continue your use case, we can assist you with your current script. Could you please share the following Reply privately to author option?

• CID
• Script name
• Shareable link of the spreadsheet being used in the script

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

 

ref:_00D1U1174p._5004Q2I0j6H:ref

[Zachary CANNON]

Hi Teejay,

I don't have the option to reply to author? It is greyed out. Can you advise please?

[Google Ads Scripts Forum Advisor]

Hi there,

In case, you encounter an error when using the mentioned option, you can send the requested information on this email (googleadsscr...@google.com) instead, then let us know here once it has been sent.

[Zachary CANNON]

Thank you, I have sent a message to that address

[Google Ads Scripts Forum Advisor]

Hi,

Harry here, teammate of Teejay. Allow me to assist you this time.

Thanks for providing the requested details. Would you be able to point me where in your script you are having issues with? Is it with adding the negative keywords into the negative keywords list or is it adding the negative keywords into the Shopping Campaign?

I also see that you have this addNegativeToList function, but you have not called it and its included in the main function. Can you kindly confirm if you don't encounter issues in this regard?

On the other hand, can you kindly provide context to what you mean by "I would prefer for the ROAS calculation to be done on the server"? I see that you commented in line 55 that your ROAS calculation is also working. Can you kindly confirm this also?

I would appreciate it really if you can provide more context to the what you would like to achieve so that I can provide you a concise guidance on how you can improve your script.

Looking forward to your reply.

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2I0j6H:ref

[Zachary CANNON]

Thanks Harry.

There are two things I would like help with please

1. The ROAS calculation works, but it is not optimal. It is currently calculated in the google sheet and as such I am pulling in all of the data first, and then calculating ROAS. Is there a way that I could calculate conversion value / cost, push that into a ROAS array, and then only push rows to the sheet if the ROAS figure is below a certain threshold?

2. The final function to add negative keywords to the list does not work. Is there a way that instead of the current setup to add to a list, that I could add the negative keyword only at the ad group level of the corresponding row? 

[Google Ads Scripts Forum Advisor]

Hi Zachary,

Thank you for the specifics. Please see my answers below:

1. I can't seem to find a ROAS calculation in the provided spreadsheet, but I see you are already doing the calculation in your script (line 68). Is this not working as intended? Are you expecting a different number/figure of the calculated ROAS?
   • "Only push rows to the sheet if the ROAS figure is below a certain threshold" - For this, you would need to calculate the ROAS first before you push the report data in the spreadsheet (this is appendRow in line 52), then instead of using the setValue for ROAS and Poor Performer column you may push their value instead in the row_array variable. You may refer to the code below:

   •     while(report_iter.hasNext()) {
           var row = report_iter.next();
           var row_array = [];
           var thersholdROAS = 0;
           var ROAS = 0;
           var poorPerformer = '';
     
           for(var i in columns) {
             row_array.push(row[columns[i]]);
             
             // Calculate ROAS
             var convValue = row[columns[7]];
             var cost = row[columns[8]];
             ROAS = (convValue / cost);
           }
           
           row_array.push(ROAS);
           row_array.push(poorPerformer);
     
           // Check if ROAS hits threshold
           if(ROAS > thershold) sheet.appendRow(row_array);
         } 

2. Kindly check out this example that shows an approach of creating the negative keywords list, adding negative keywords to it, then add the list to the campaign. This might be a more suitable approach for you. On the other hand, you can add negative keywords to a specific ad group like in this example. You can filter the AdGroupSelector by ID using the withIds method instead of the withCondition for AdGroup Names.

Let me know your thoughts.