Google Ads Script – Disapproved Products Alerts

Stop revenue loss due to disapproved products in the Google Merchant Center.

My inbox is bursting at the seams with evidence of people suffering >10% drop in Google Shopping performance due to product disapprovals, _without them noticing for weeks or even months_!

If you are running Shopping ads, I bet you, at least every now and then one or more products will get disapproved.
No exceptions.

The same goes for products in your Performance Max campaigns.

This is the nature of the beast; products in your shopping feed get disapproved for all sorts of reasons, at times you least expect it.

• • Your product could be missing a critical attribute value.
  • There can be a mismatch between your product data in the feed and the listing on your site (price, availability, color, …).
  • Or someone, somewhere, added a sales discount to your images.(Google will not allow it)
  • Or Google decided to change their policy for the gazillionth time.
  • Or…

The list goes on and on.

Here’s the thing:
When you fail to identify a top-performing product being disapproved, sales and revenues crash like the TSLA stock after a Musk tweet.

Keeping up with disapproved products is a MUST!

Monitoring the status of your products is, to say the least, a tedious repetitive burden.
It will result in Obsessive-Compulsive Disorder if you don’t automate the process.
You are constantly checking your Merchant Center account to see if your top-performing products are eligible to run.
Every day, every hour.

Now imagine the comfort of being 100% sure that you will be notified immediately when one of your top-performing products gets disapproved.
After the alert, you can simply jump in, and fix the issue right away.
Your Google Shopping performance is steady.

That’s exactly what my latest script does.

This script notifies you via e-mail when products in your merchant center account, that got a significant amount of clicks, are disapproved.
You can set the threshold for the minimum number of clicks and the date range. This way your sheet and inbox won’t be flooded with alerts for disapproved products that did not get any clicks anyway.

The output will look something like this:

(click image to enlarge)

 

 

➥  ACTION: Schedule this script to run  daily.

(Don’t worry if you have never run a script before. You do not need any coding skills. It is as simple as copy-paste.)
INSTRUCTIONS:

1. 1. See the script code below. Install the script in your account.
      Don’t worry if you have never done this before. You do not need any coding skills. It is as simple as copy-paste. Simply follow these instructions on how to set up and schedule Google Ads scripts.
   2. Create a new Google Sheet
      (tip for Chrome users: simply type ‘sheets.new’ in the address bar)
   3. Add the complete URL of the spreadsheet to the script (line 17)
   4. Add your email address to the script (line 18)
   5. Set min number of clicks and date range (Line 20 and 21)
   6. Add your Google Merchant Center ID (Line 23)
   7. Add your account name (Line 24)
   8. Enable the Shopping API: Go to the top right corner of the script, there is a button ‘Advanced API’s’. Click on that button and enable the Shopping Content API.
   9. Preview
   10. Schedule to run daily

TIP: Get Access to my Ultimate List of 400+ Google Ads Scripts >>

 

/**
*
* Report Disapproved Products
*
* Creates a report indicating which products are disapproved, including the reason why and a way to fix it
* Sends an alert via email whenever products are disapproved
*
* Version 1.0
*
* @author: Nils Rooijmans
*
* contact nils@nilsrooijmans.com for questions and the MCC version of the script 
*/

// CHANGE SETTINGS HERE

var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url between the double quotes
var EMAIL = ""; //insert your email adresses between the double quotes. You can add multiple email addresses between the double quotes, just separate them via a comma, ie: "john@doe.com, jane@doe.com"

var MIN_CLICKS = 1;  // ignore products with less clicks during the date range specified via PERIOD, NOTE: minimum value is 1
var PERIOD = 'LAST_30_DAYS'; 

var GMC_ID = ""; // insert Google Merchant Center id between double quotes. NOTE: the account you use to authorize this Google Ads script should also have access to the merchant center account associated with the id  

var ACCOUNT_NAME = ""; // insert account name between double quotes. 


// NO CHANGES NEEDED BELOW THIS LINE

var SHEET_REPORT_HEADER = [
    "Product ID",
    "Product Title",
    "Product Link",
    "Clicks",
    "Disapproval Reason",
    "Solution",
    "Documentation"
  ];


function main() {
  
  // first we clear the report sheet and add header
  prepareSpreadsheet();  
  
  // let's get the products that are of interest
  var productsOfInterest = getProductsOfInterest();
  
  // let's check for disapprovals among the products of interest
  var disapprovedProducts = getDisapprovedProducts(productsOfInterest, GMC_ID);
  
  // finally, let's report on any issues
  if (disapprovedProducts.length > 0) { // there is at least one issue
    Logger.log("Total NR of Disapproved products: "+disapprovedProducts.length);
    reportResults(disapprovedProducts);
  }
}


function prepareSpreadsheet() {

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getActiveSheet();
  sheet.clear(); //remove earlies alerts
  sheet.clearConditionalFormatRules();
  sheet.appendRow(SHEET_REPORT_HEADER);
  
  var range=sheet.getRange(1,1,1,SHEET_REPORT_HEADER.length);
  range.setFontWeight("bold"); 
}


function getDisapprovedProducts(productsOfInterest, gmc_id) {
  
  var pageToken;
  var maxResults = 250;
  
  var disapprovedProducts = [];

  do {
    
    var productStatuses = ShoppingContent.Productstatuses.list(gmc_id, {pageToken: pageToken, maxResults: maxResults});
    
    if (productStatuses.resources) {
      for (var i = 0; i < productStatuses.resources.length; i++) {
        var product = productStatuses.resources[i];
        
        // productId's have the format like "productId" = "online:nl:NL:136258511"
        var productId = product.productId.split(':').pop();
        
        if (productsOfInterest[productId] != null) {
          //Logger.log("We've got a product of interest to check, \n  productId : "+productId+"\n  productTitle: "+productsOfInterest[productId].title+"\n  Nr of Clicks: "+productsOfInterest[productId].clicks);
          for(j=0; j<product['destinationStatuses'].length;j++){
            if (product['destinationStatuses'][j]['destination'] == "Shopping") { // only check Shopping ads
              if (product['destinationStatuses'][j]['status'] == "disapproved") {
                if(product['itemLevelIssues'] == undefined) {
                  var reason = "Disapproval reason unknown";
                }else{
                  var reason = product['itemLevelIssues'][0].description;
                  var solution = product['itemLevelIssues'][0].detail;
                  var documentation = product['itemLevelIssues'][0].documentation;
                }; 
                Logger.log("- the product is disapproved, productId : "+productId+" reason: "+reason);

                disapprovedProducts.push([
                  product.productId.split(':').pop(), // productId's have the format like "productId" = "online:nl:NL:136258511"
                  product.title,
                  product.link,
                  productsOfInterest[productId].clicks,
                  reason,
                  solution,
                  documentation
                ]);                
              }
            }
          }
        }
        
      }
    } else {
      Logger.log("No more products in account " + gmc_id);
    }
    pageToken = productStatuses.nextPageToken;
  } while (pageToken);
  
  Logger.log("NR of disapproved products: "+disapprovedProducts.length);
  
  return disapprovedProducts;
}


function getProductsOfInterest() {

  var productsOfInterest = {};
  
  var gaqlQuery = "SELECT segments.product_item_id, segments.product_title, metrics.clicks FROM shopping_performance_view WHERE metrics.clicks >= "+MIN_CLICKS+" AND segments.date DURING "+PERIOD;
  
  var results = AdsApp.search(gaqlQuery);
  var count = 0;
  
  while (results.hasNext()) {
    
    var result = results.next();
    
    var productId = result.segments.productItemId;
    var productTitle = result.segments.productTitle;
    var nrOfClicks = result.metrics.clicks;
    
    productsOfInterest[productId] = {
      id:productId,
      title:productTitle,
      clicks:nrOfClicks
    };  
    
    count++;
  }

  Logger.log("NR of products of interest: "+count);
  
  return productsOfInterest;
}


function reportResults(results) {

    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    var sheet = ss.getActiveSheet();
    var lastRow = sheet.getLastRow();

    // write issues to sheet
    var range = sheet.getRange(lastRow+1, 1, results.length, SHEET_REPORT_HEADER.length);
    range.setValues(results);
    range.sort([{column: 4, ascending: false}]);  
  
    // send the email
    var emailBody = 
      "Number of Disapproved products: " + results.length + "\n" + 
      "See details: "+ SPREADSHEET_URL + "\n\n" +
      "For more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com \n" + 
      "---\n" + 
      "This email is generated by a copy of the free Google Ads Script - Disapproved Products Alert, (C) Nils Rooijmans \n" +
      "---\n";

    MailApp.sendEmail(EMAIL, "[GOOGLE ADS ALERT] - Disapproved Products - "+ACCOUNT_NAME, emailBody);
}

 

Here’s what other PPC Pro’s had to say:

Join thousands of PPC geeks who already have access:

TIP: Get Access to my Ultimate List of 400+ Google Ads Scripts >>

If the button above isn’t working for you, you can sign up here to get access.