Google Shopping Negative Keyword Script

[Jamie Degiorgio]

Hi,

I have a script which is meant to negate search terms from a Google shopping campaign via a google sheet (column A-C) using keywords we want to keep. The idea is that we want to have a purely branded shopping campaign.

Now the script I have works perfectly within a non-shopping campaign (text), however, when I try it within a shopping account, I simply get "Could not find any ad groups with impressions that matched the given names"

I believe the issue is that the script can't find the ad group IDs of the shopping ad groups.(line 58 to start):

function main() {

  // Put your spreadsheet's URL here:

  var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/13bz0xbN5KYR2uCGv9HQXEVfjgvl2RmcszgFYs-5ezGU/edit#gid=0";

  // Make sure the keywords are in columns A to C in the first sheet.

  

 

  // ////////////////////////////////////////////////////////////////////////////

  var dateRange = 'YESTERDAY';

  // By default the script just looks at yesterday's search queries.

 

  var impressionThreshold = 0;

  // The script only looks at searches with impressions higher than this threshold.

  // Use this if you get a wide range of searches and only want to exclude the highest volume ones.

 

  // Read the spreadsheet

  try {

    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

  } catch (e) {

    Logger.log("Problem with the spreadsheet URL: '" + e + "'");

    Logger.log('Make sure you have correctly copied in your own spreadsheet URL.');

    return;

  }

  var sheet = spreadsheet.getSheets()[0];

  var spreadsheetData = sheet.getDataRange().getValues();

 

  // Record each campaigns' keywords, and the words (of more than 4 characters) that are in the keywords

  var keywords = {};

  var words = {};

  var numberOfKeywords = 0;

  var numberOfadGroups = 0;

  for (var i = 1; i < spreadsheetData.length; i++) {

    var campaignName = spreadsheetData[i][0];

    var adGroupName = spreadsheetData[i][1];

    if (keywords[campaignName] == undefined) {

      keywords[campaignName] = [];

      words[campaignName] = {};

    }

    if (keywords[campaignName][adGroupName] == undefined) {

      keywords[campaignName][adGroupName] = [];

      words[campaignName][adGroupName] = {};

      numberOfadGroups++;

    }

    var keyword = spreadsheetData[i][2];

    keyword = keyword.toLowerCase().replace(/[^\w\s\d&]/g, ' ').replace(/ +/g, ' ').trim();

    keywords[campaignName][adGroupName].push(keyword);

    numberOfKeywords++;

    var keywordWords = keyword.split(' ');

    for (var k = 0; k < keywordWords.length; k++) {

      if (keywordWords[k].length > 4) {

        words[campaignName][adGroupName][keywordWords[k]] = true;

      }

    }

  }

  var campaignNames = Object.keys(keywords);

  Logger.log('Found ' + numberOfKeywords + ' keywords for ' + numberOfadGroups + ' ad groups in ' + campaignNames.length + ' campaign(s).');

 

  // Get the IDs of the ad groups named in the spreadsheet

  var adGroupIds = [];

  var campaignIds = [];

  var campaignReport = AdWordsApp.report(

    'SELECT CampaignName, AdGroupName, CampaignId, AdGroupId '

    + 'FROM   ADGROUP_PERFORMANCE_REPORT '

    + 'WHERE Impressions > 0 '

    + 'AND CampaignName IN ["' + campaignNames.join('","') + '"] '

    + 'DURING ' + dateRange

  );

  var campaignRows = campaignReport.rows();

  while (campaignRows.hasNext()) {

    var row = campaignRows.next();

    if (campaignIds.indexOf(row.CampaignId) < 0) {

      campaignIds.push(row.CampaignId);

    }

    if (keywords[row.CampaignName][row.AdGroupName] != undefined) {

      adGroupIds.push(row.AdGroupId);

    }

  } // end while

 

  if (adGroupIds.length == 0) {

    Logger.log('Could not find any ad groups with impressions that matched the given names.');

    return;

  }

  Logger.log('Found ' + adGroupIds.length + ' ad groups in ' + campaignIds.length + ' campaign(s) with impressions that matched the given names.');

 

  // Initialise the arrays for each campaign, and sorts the keywords from longest to shortest

  var negativeQueries = {}; // Contains the queries

  var exactNegatives = {}; // Contains any negatives to add with exact match

  var phraseNegatives = {}; // Contains any negatives to add with phrase match

  for (var campaignName in keywords) {

    negativeQueries[campaignName] = {};

    exactNegatives[campaignName] = {};

    phraseNegatives[campaignName] = {};

    for (var adGroupName in keywords[campaignName]) {

      negativeQueries[campaignName][adGroupName] = [];

      exactNegatives[campaignName][adGroupName] = [];

      phraseNegatives[campaignName][adGroupName] = [];

      keywords[campaignName][adGroupName].sort(function (a, b) {

        return b.length - a.length;

      });

    }

  }

 

  // Get the queries that don't exactly match keywords

  var report = AdWordsApp.report(

    'SELECT Query, AdGroupId, CampaignId, CampaignName, AdGroupName, Impressions '

    + 'FROM SEARCH_QUERY_PERFORMANCE_REPORT '

    + 'WHERE AdGroupId IN [' + adGroupIds.join(',') + '] '

    + 'AND Impressions > ' + impressionThreshold + ' '

    + 'DURING ' + dateRange

  );

  var rows = report.rows();

  var numberQueries = 0;

  while (rows.hasNext()) {

    var row = rows.next();

    var query = row.Query.toLowerCase().replace(/[^\w\s\d&]/g, ' ').replace(/ +/g, ' ').trim();

    var campaignName = row.CampaignName;

    var adGroupName = row.AdGroupName;

    if (keywords[campaignName][adGroupName].indexOf(query) < 0) {

      negativeQueries[campaignName][adGroupName].push(query);

      numberQueries++;

    }

  }

 

  // Process queries

  Logger.log('Processing ' + numberQueries + ' queries that do not match any keywords.');

  var numberExactNegatives = 0;

  var numberPotentialPhraseNegatives = 0;

  for (var campaignName in negativeQueries) {

    for (var adGroupName in negativeQueries[campaignName]) {

      for (var i = 0; i < negativeQueries[campaignName][adGroupName].length; i++) {

        var query = negativeQueries[campaignName][adGroupName][i];

        var queryDone = false;

 

        // If the query is contained within a keyword, it has to be an exact match negative

        if (isStringInsideKeywords(query, keywords[campaignName][adGroupName])) {

          exactNegatives[campaignName][adGroupName].push(query);

          numberExactNegatives++;

          continue;

        }

 

        // Check each word (that's over 4 characters) in the query - if it's not in the words array

        // then it isn't in the keywords, so it's fine to use as a phrase negative

        var queryWords = query.split(' ');

        for (var w = 0; w < queryWords.length; w++) {

          if (queryWords[w].length > 4) {

            if (words[campaignName][adGroupName][queryWords[w]] == undefined) {

              phraseNegatives[campaignName][adGroupName].push(queryWords[w]);

              queryDone = true;

              break;

            }

          }

        }

 

        // Check if there is a keyword inside the query. If there is, see if the part of the query before

        // or after the keyword could be used as a phrase negative.

        for (var k = 0; k < keywords[campaignName][adGroupName].length && !queryDone; k++) {

          var keyword = keywords[campaignName][adGroupName][k];

          if ((' ' + query + ' ').indexOf(' ' + keyword + ' ') > -1) {

            var queryBits = (' ' + query + ' ').split(' ' + keyword + ' ');

            queryBits[0] = queryBits[0].trim();

            queryBits[1] = queryBits[1].trim();

            if (queryBits[0].length > 0 && !isStringInsideKeywords(queryBits[0], keywords[campaignName][adGroupName])) {

              phraseNegatives[campaignName][adGroupName].push(queryBits[0]);

              queryDone = true;

              break;

            }

            if (queryBits[1].length > 0 && !isStringInsideKeywords(queryBits[1], keywords[campaignName][adGroupName])) {

              phraseNegatives[campaignName][adGroupName].push(queryBits[1]);

              queryDone = true;

              break;

            }

          }

        }

 

        // If nothing smaller than the full query would work, then add the full query as a negative

        if (!queryDone) {

          phraseNegatives[campaignName][adGroupName].push(query);

        }

        numberPotentialPhraseNegatives++;

      }

    }

  }

  Logger.log('Found ' + numberPotentialPhraseNegatives + ' potential phrase match negatives and ' + numberExactNegatives + ' exact match negatives.');

 

  // Remove any redundant phrase negatives

  Logger.log('Checking for redundant negatives.');

  var numberPhraseNegatives = 0;

  for (var campaignName in negativeQueries) {

    for (var adGroupName in negativeQueries[campaignName]) {

      // Order the phrases from shortest to longest

      phraseNegatives[campaignName][adGroupName].sort(function (a, b) {

        return a.length - b.length;

      });

 

      for (var i = 0; i < phraseNegatives[campaignName][adGroupName].length; i++) {

        var shorterPhrase = ' ' + phraseNegatives[campaignName][adGroupName][i] + ' ';

 

        // As the array is now ordered, any phrase negatives with higher indices must be longer than shorterPhrase

        for (var j = i + 1; j < phraseNegatives[campaignName][adGroupName].length; j++) {

          var longerPhrase = ' ' + phraseNegatives[campaignName][adGroupName][j] + ' ';

 

          // If the shorterPhrase is within the longerPhrase, then the longerPhrase is redundant

          // so it is removed from the array. This also means duplicates are removed.

          if (longerPhrase.indexOf(shorterPhrase) > -1) {

            phraseNegatives[campaignName][adGroupName].splice(j, 1);

            j--;

          }

        }

      }

      numberPhraseNegatives += phraseNegatives[campaignName][adGroupName].length;

    }

  }

  Logger.log('Going to create ' + numberPhraseNegatives + ' phrase match negatives and ' + numberExactNegatives + ' exact match negatives');

 

  // Iterate through the Shopping ad groups and add the negative keywords

  var groupIterator = AdWordsApp.shoppingAdGroups()

    .withIds(adGroupIds)

    .get();

 

  while (groupIterator.hasNext()) {

    var adGroup = groupIterator.next();

    var adGroupName = adGroup.getName();

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

 

    for (var i = 0; i < exactNegatives[campaignName][adGroupName].length; i++) {

      adGroup.createNegativeKeyword('[' + exactNegatives[campaignName][adGroupName][i] + ']');

    }

 

    for (var i = 0; i < phraseNegatives[campaignName][adGroupName].length; i++) {

      adGroup.createNegativeKeyword('"' + phraseNegatives[campaignName][adGroupName][i] + '"');

    }

  }

 

  Logger.log('Finished.');

} // end main function

 

 

// Check if a word is a substring of any strings in the keywords array

function isStringInsideKeywords(word, keywords) {

  for (var k = 0; k < keywords.length; k++) {

    var keyword = ' ' + keywords[k] + ' ';

    if (keyword.indexOf(' ' + word + ' ') > -1) {

      return true;

    }

  }

  return false;

}

Can anyone help please?

Best,

JD

[Google Ads Scripts Forum Advisor]

Hi Jamie,

Thank you for your email.

So our team can better investigate, could you provide your customer ID and the name of the Script? Also, could you provide a more public version of the spreadsheet with only dummy data so our team could also use it with our investigation?

You may send the requested details using the Reply privately to author option.

Best regards,

Peter Laurence Napa Oliquino Google Ads Scripts Team

ref:_00D1U1174p._5004Q23um7Y:ref

[Google Ads Scripts Forum Advisor]

Hi Jamie,

Thank you for the information. However, I had to delete your most recent response to ensure that your account details are secured.

Moving forward, I took a look at your account and the Script and I found that there is a condition in your Script that whenever an impression is 0, all the adgroups would result in the "Could not find any ad groups with impressions that matched the given names" and the Script would then automatically finish.

If you have questions, you may reach out to the author of the third party Script for further assistance.

[Jamie Degiorgio]

Hi Peter,

 

Thanks for your response, however the ad group in question has over 0 impressions for yesterday, thus the script should not stop and should continue to run. I have even added in more ad groups into the google sheet to confirm that this is the case. What I believe to be happening is that the script can not locate the shopping ad groups IDs from the adsApp.report, then it will not match with the campaigns and adgroups within my sheet.

 

I also ran this in a non shopping account and worked fine, do you know why the issue is with shopping campaigns only?

 

Best,

Jamie

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/4UDvjN0WdGI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/3p0TA000000000000000000000000000000000000000000000QFPQLO00mAvDZQ8-TD6oxGwDs5Kk-g%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hi Jamie,

There appears to be an issue with the third party Script that does not allow your code to run despite meeting the conditions. That said, since third party Scripts are already outside of our scope, I would recommend that you reach out to the authors of the Script for further assistance.

Thanks,

[Jamie Degiorgio]

Hi Peter,

 

To wrap this up, can you please just clarify one thing for me. Would the following AdsApp.Report capture Shopping AdGroup IDs:

 

  var adGroupIds = [];

  var campaignIds = [];

  var campaignReport = AdsApp.report(

    'SELECT CampaignName, AdGroupName, CampaignId, AdGroupId '

    + 'FROM   ADGROUP_PERFORMANCE_REPORT '

    + 'DURING ' + dateRange

 

Thanks,

Jamie

--

-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/4UDvjN0WdGI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/jTwUp000000000000000000000000000000000000000000000QFRHUB00ZHmyIN-jRJGAQksh2vNhfQ%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hi Jamie,

Yes, the ad group performance report includes shopping ad groups.

Regards,
Matt