When building the Google Shopping campaigns, a split between branded traffic and non-branded traffic is often chosen. The statistics of branded campaigns are often more positive than non-branded campaigns and to optimize bids, it is advisable to make this split into accounts.

By using the prioritization in Shopping campaigns, non-branded search terms enter the campaign with the highest priority. In this campaign, branded search terms are excluded, so that you now have a branded and a non-branded campaign.

Practice

Although in theory a nice structure of the campaign structure, in practice it always appears that this is not watertight. Despite the proper setting of the priorities and exclusions, we will receive non-branded search terms in the branded campaigns. This is annoying for account optimization, because there is now too high a bid for non-branded search terms. The only solution is to exclude all non-branded terms in the brand campaigns. Because this can take a lot of time if you do this manually, we have automated this process with the script below.

How it works

This scripts works for different campaign structures. To use this script, the tree structure in an ad group must be broken down to product level. The script examines for each ad group which product groups are contained therein, collects the brand names of these product groups and then compares them with the search terms in the ad groups. Does none of the brand names appear in the search terms? Then the search term is excluded.

To make sure all brandnames are found, the brandname will be split if existing out of multiple words. Each separate word will be checked in the query. You can also enable the check for acronyms.

Settings

To run this script properly, you don't have to set much:

  • LOG: Specify whether the script should report the intermediate steps, by adjusting the value to 'true'.
  • PRODUCT_FEED_URL: Enter the URL of the XML product feed as it appears in the Google Merchant Center
  • LABEL_ADGROUPS: To keep track of script progress, all ad groups are tagged when they are checked.
  • FILTER_CAMPAIGN_NAME: Use this filter to select branded campaigns only based on the campaign name.
  • USE_MPN: Set to true if you want to use the MPN as a branded keyword.
  • USE_EAN: Set to true if you want to use the EAN as a branded keyword.
  • USE_ACRONYMS: To use acronyms as brandname, set to true. Otherwise set to false
  • BRAND_MAPPING_SHEET_URL: Optionally, use a spreadsheet for brand typo's or different languages
  • BRAND_MAPPING_SHEET_NAME: When using the spreadsheet, set the sheetname

Frequency: Schedule this script to run hourly.

Script
// Copyright 2019. Increase BV. All Rights Reserved.
//
// Created By: Tibbe van Asten
// for Increase B.V.
// 
// Created: 21-03-2019
// Last update: 16-09-2019
//
// ABOUT THE SCRIPT
// With this script you can exclude non-branded queries 
// from branded shopping campaigns. The brand is taken from the Merchant Center.
// By doing this every day, the accountstructure will remain clean.
//
////////////////////////////////////////////////////////////////////

var config = {
  
  LOG : true,  
  
  // Connect Merchant Center. Add user to MC that runs this script.
  // Also enable Advanced API 'Shopping Content'.
  MERCHANT_ID : "123456789",
  
  // Label all checked adgroups.
  LABEL_ADGROUPS : "Non-Brand Excluded",
  
  // Select only your branded campaigns by campaignname.
  FILTER_CAMPAIGN_NAME : "[B]",
  
  // If you want to use MPN or EAN as branded keywords, set to true.
  USE_MPN : true,
  USE_EAN : true,
  
  // If you don't want to use acronyms as brandname, set to false.
  USE_ACRONYMS : true,
  
  // Optionally, use a spreadsheet for brand typo's or different languages.
  // Copy the following sheet: https://docs.google.com/spreadsheets/d/1fAxCYsksEljl71LljDzk4jkfBtSTg2ur1zyGnvXOU1E/copy
  // Leave empty when you don't want to use this.
  BRAND_MAPPING_SHEET_URL : "",
  BRAND_MAPPING_SHEET_NAME : "Mapping"
  
}

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

function main() {
  
  checkLabel();
  var values = connectSheet();
  var products = connectMerchant();
  var adGroupIterator = selectAdgroups();  
  
  // When all adgroups are checked, all labels will be removed to start again
  if(adGroupIterator.totalNumEntities() == 0) {
      var adGroupIterator = AdsApp
        .shoppingAdGroups()
        .withCondition("LabelNames CONTAINS_ANY ['"+config.LABEL_ADGROUPS+"']")
        .withCondition("CampaignName CONTAINS '"+config.FILTER_CAMPAIGN_NAME+"'")
        .withCondition("CampaignStatus = ENABLED")
        .get();
    
      while(adGroupIterator.hasNext()) {
    	var adGroup = adGroupIterator.next(); 
        adGroup.removeLabel(config.LABEL_ADGROUPS);
      }    
  }
  
  while(adGroupIterator.hasNext()){
    var adGroup = adGroupIterator.next(); 
    
    var brands = [];
  	var brands = selectBrands(adGroup, products, brands, values);
    
    if(brands.length > 0){
      
      var keywordCount = 0;
    
      // Select queries within this adgroup
      var report = AdsApp.report(
        "SELECT Query " +
        "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
          "WHERE AdGroupId = '" + adGroup.getId() + "' " +
          "AND QueryTargetingStatus = NONE"
      );

      var rows = report.rows();
      while (rows.hasNext()) {
        var row = rows.next();

        var query = trimQuery(row);
        
        // This variable will define if a query is excluded or not. If false, the query will
        // be excluded. If set to true, we will keep the query.
        var match = false;

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

          var brandSplitLength = brands[i].replace("'", " ").replace("-", " ").replace("&", "").replace("  ", " ").split(" ").length;
          var brandSplit = brands[i].replace("'", " ").replace("-", " ").replace("&", "").replace("  ", " ").split(" ");

          // It's possible that parts of a brandname are present in a query.
          // In that case, we will treat the query as branded
          if(brandSplitLength > 1) {
            for (var x = 0; x < brandSplit.length; x++) {  

              if(brandSplit[x].match(/[a-z]/g)){
                if((query.indexOf(brandSplit[x]) > -1 || query.indexOf(brandSplit[x].replace("é", "e")) > -1) && brandSplit[x].match(/[a-z]/g).length > 1){
                  var match = true;
                  break;
                }    
              }
            }
          }

          // After looking at parts of the brandname, we want to know if the brandname
          // is present or the brandname without numbers or spaces is present in the query.
          if(match === false && (query.indexOf(brands[i]) > 1  || query.indexOf(brandSplit) > -1)) {
            var match = true;
            break;
          }  
          
          // The last thing we'll check, is acronyms. Like th for Tommy Hilfiger or hb for Hugo Boss
          // These therms will also not be excluded
          if(match === false && brandSplitLength > 1 && config.USE_ACRONYMS === true){
            var acronym = brands[i].match(/\b(\w)/g).join('').toLowerCase();
            
            if(query.indexOf(acronym) > -1){
              var match = true;              
              break;
            }       
          }

        } // brandsIterator
        
        // If no match is found in the query, we will exclude the query from the adgroup
        if(match === false){
          adGroup.createNegativeKeyword(query);
          
          if(keywordCount == 0){
            if(config.LOG === true){
              Logger.log(" ");
              Logger.log("Campaign: " + adGroup.getCampaign().getName() + ", Adgroup: " + adGroup.getName());
              Logger.log("---");
            }
          } keywordCount++;

          Logger.log("EXCLUDED: " + query + " in " + adGroup.getName());                                                                                                                                                                
        } 

      } // rowIterator
      
    } // if brands exist
    
    adGroup.applyLabel(config.LABEL_ADGROUPS);
    if(config.LOG === true){
      Logger.log("Label added to " + adGroup.getName());
    }
    
  } // adGroupIterator
  
  Logger.log(" ");
  Logger.log("Thanks for using this custom script by Tibbe van Asten. Winning!");
  
} // function main()

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

function connectSheet(){

  if(config.BRAND_MAPPING_SHEET_URL != "" && config.BRAND_MAPPING_SHEET_NAME != ""){
    
    // Connect to the spreadsheet
    var ss = SpreadsheetApp.openByUrl(config.BRAND_MAPPING_SHEET_URL);
    var sheet = ss.getSheetByName(config.BRAND_MAPPING_SHEET_NAME);
    var range = sheet.getDataRange();
  	var values = range.getValues();
  
  }
  
  return values;
    
} // connectSheet()

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

function connectMerchant(){
  
  	if(config.MERCHANT == "123456789"){
      throw Error("Change the Merchant ID in the settings");
    }  
    
  var pageToken;
  var pageNum = 1;
  var maxResults = 250;
  var products = [];
  
  do {
    var productList = ShoppingContent.Products.list(config.MERCHANT_ID, {
      pageToken: pageToken,
      maxResults: maxResults
    });
    
    if (productList.resources) {
      for (var i = 0; i < productList.resources.length; i++) {
        // We'll focus only on watches
        if(productList.resources[i]["productType"] == "Watch"){
          products.push(productList.resources[i]);
        }
      }
    }

    pageToken = productList.nextPageToken;
    pageNum++;
  } while (pageToken);
  
  return products;
  
} // function connectMerchant

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

function selectAdgroups(){
  
	if(config.FILTER_CAMPAIGN_NAME == ""){
      
      var adGroupIterator = AdsApp
        .shoppingAdGroups()
        .withCondition("Status = ENABLED")
        .withCondition("CampaignStatus = ENABLED")        
        .withCondition("LabelNames CONTAINS_NONE ['"+config.LABEL_ADGROUPS+"']")
        .get();
      
    } else {
      
       var adGroupIterator = AdsApp
        .shoppingAdGroups()
        .withCondition("Status = ENABLED")
        .withCondition("CampaignStatus = ENABLED")
        .withCondition("CampaignName CONTAINS_IGNORE_CASE '"+config.FILTER_CAMPAIGN_NAME+"'")
        .withCondition("LabelNames CONTAINS_NONE ['"+config.LABEL_ADGROUPS+"']")
        .get();      
      
    }
  
	return adGroupIterator;
  
} // function selectAdgroups()

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

function checkLabel(){
  
  if(config.LABEL_ADGROUPS !== "" && !AdsApp.labels().withCondition("Name = '"+config.LABEL_ADGROUPS+"'").get().hasNext()) {
    AdsApp.createLabel(config.LABEL_ADGROUPS);

    	Logger.log("Label " + config.LABEL_ADGROUPS + " created");
    	Logger.log(" ");
  }
  
} // function checkLabel()

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

function selectBrands(adGroup, products, brands, values) {
  
  var productGroups = [];

  var productGroupIterator = adGroup
    .productGroups()
    .withCondition("PartitionType = UNIT")
    .withCondition("ProductGroup DOES_NOT_CONTAIN ' *'")
  	.withCondition("ProductGroup DOES_NOT_CONTAIN 'OtherCase'")
    .get();

  while(productGroupIterator.hasNext()){
    var productGroup = productGroupIterator.next();

    productGroups.push(productGroup.getValue());
    var brand = checkValue(products, productGroup.getValue(), "brand") || ""; 
    var mpn = checkValue(products, productGroup.getValue(), "mpn") || "";  
    var gtin = checkValue(products, productGroup.getValue(), "gtin") || "";

    // Only add new brands
    if(brands.indexOf(brand.toLowerCase()) < 0 && brand != ""){
      brands.push(brand.toLowerCase());
    }
    
    // Add MPN as branded keywords
    if(config.USE_MPN === true && brands.indexOf(mpn.toLowerCase()) < 0 && mpn != ""){
      brands.push(mpn.toLowerCase());
    }
    
    // Add EAN as branded keywords
    if(config.USE_EAN === true && brands.indexOf(gtin.toLowerCase()) < 0 && gtin != ""){
      brands.push(gtin.toLowerCase());
    }
    
    // Check sheet for additional brandname alternatives
    if(brand != "" && config.BRAND_MAPPING_SHEET_URL != "" && config.BRAND_MAPPING_SHEET_NAME != ""){      
      for(var i = 1;i < values.length;i++){
		    if(values[i][0] == brand){
          for(var x = 1;x < values[i].length;x++){
            if(brands.indexOf(values[i][x].toLowerCase()) < 0){
              brands.push(values[i][x].toLowerCase());
            }
          } // brandIterator
      	}
      } // sheetIterator      
    } // check brand mapping

  } // productGroupIterator
  
  return brands;
  
} // function selectBrands()

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

function checkValue(products, productId, field) {
  
  for (var i = 0; i < products.length; i++) {
    
    if(products[i]["offerId"] == productId){
		var value = products[i][field];
      	break;
    } // if statement

  } // for statement
  
  return value;
  
} // function checkValue()

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

function trimQuery(row){
  
  // When the searchquery exceeds the limit of 10 words, we will split
  // the query and put max. 10 words back together as a phrasematch query    	
  var query = "";
  if (row["Query"].split(" ").length < 10) {
    query = "[" + row["Query"] + "]";
  } else {
    for (var i = 0; (i < row["Query"].split(" ").length) && (i < 10); i++){ 
      query += row["Query"].split(" ")[i] + " ";                 
    }
    query = '"' + query.replace(/\s+$/,'') + '"';
  }
  
  return query;
  
} // function trimQuery()

Sharing Knowledge

Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.

Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.

Contact us

Nils Rooijmans
Google Ads Scripter
Water Cooler Topics
Nils Rooijmans, Google Ads Scripter
Bas Baudoin
Teamleader PPC
Happy Leads
Bas Baudoin, Teamleader PPC
Martijn Kraan
Freelance PPC Specialist
Brightstep
Martijn Kraan, Freelance PPC Specialist
Tibbe van Asten
PPC Specialist
Founder Adsscripts
Tibbe van Asten, Senior PPC Automation Specialist