Streamlining Merchant Center Management with Google Ads Script for Newly Disapproved Products

Managing a Google Merchant Center account efficiently is crucial for maintaining product visibility and sales. Disapproved products can significantly impact your performance, which is why we're introducing a powerful Google Ads script to help identify newly disapproved products in your Merchant Center.

Features

Automated Identification: Automatically detects products that have been newly disapproved.
Detailed Notifications: Sends detailed email notifications with a summary of the disapproved products.
Easy to Set Up: Simple setup process using Google Sheets for data management.

Instructions

Prepare Your Spreadsheet: Make a copy of the template Google Sheet here. This sheet will store your data.
Set Up the Script: In your Google Ads account, navigate to Tools & Settings > Scripts, then create a new script and paste the provided code.
Customize Your Script:

Replace "INSERT URL OF YOUR GOOGLE SHEET COPY" with the URL of your copied Google Sheet.
Input your Merchant Center ID in the merchantId variable.
Configure the clicksThreshold and timePeriod as desired.
Set the emailRecipient to the address where you want notifications sent.

Enable API: Shopping content in Advanced APIs
Authorize and Run: Authorize the script to run under your account and set a trigger for regular checks.

Code

/******************************************
 * Newly Disapproved Products 
 *
 * Google Ads Script for identifying newly disapproved in Google Merchant Center, 
 * if product has clicks before
 *
 * Created by: Dmytro Tonkikh, chiliad & TrueClicks
 *
 * Telegram channel : t.me/adwordsscripts
 * Websites :  https://adsapp.report
 *         https://trueclicks.com
 *
 * - Automated Identification
 * - Detailed Notifications
 * - Easy to Set Up
 ******************************************/



// Make a copy of this Google Sheet: https://docs.google.com/spreadsheets/u/1/d/1-PfwEaaReSvOup8ki_HbuZlCCj2xXpzCulA8NpuriHg/copy
var ss = SpreadsheetApp.openByUrl("INSERT URL OF YOUR GOOGLE SHEET COPY ");

//Insert your Merchant Center ID
var merchantId = '';
var clicksThreshold = 0;

var timePeriod = "";
//Available values for timePeriod variable: TODAY, YESTERDAY, LAST_7_DAYS, THIS_MONTH, LAST_MONTH, LAST_14_DAYS, LAST_30_DAYS
//Leave timePeriod empty for ALL_TIME product stats.

var emailRecipient = "test@test.com";

//DO NOT CHANGE SETTING BELOW
var productItemIds = {};
var products = {};
var languages = languageConstants();

function main() {

    var currentProducts = getProductData();
    disapprovedProducts();
    getProductStats();
    var newDisapprovals = Object.keys(products).filter(x => !Object.keys(currentProducts).includes(x));

    if (newDisapprovals.length > 0) {

        var newValues = newDisapprovals.map(function(x) { return [x, products[x].title, Utilities.formatDate(new Date(), AdsApp.currentAccount().getTimeZone(), "yyyy-MM-dd")] }).filter(function(x) { if (products[x[0]].clicks >= clicksThreshold) { return x } });
        var newValuesSheet = ss.getSheetByName('new values');
        newValuesSheet.getRange(newValuesSheet.getLastRow() + 1, 1, newValues.length, newValues[0].length).setValues(newValues);
        sendEmail(newValues);
    }

    var headers = ['id', 'title', 'clicks', 'conversions', 'aggregatedDestinationStatus', 'availability'];
    var values = Object.keys(products).map(function(x) { return headers.map(function(n) { return products[x][n] }) });
    values.unshift(headers);
    ss.getSheetByName('data').clear().getRange(1, 1, values.length, values[0].length).setValues(values);

}

function getProductData() {

    var retVal = {}
    var values = ss.getSheetByName('data').getDataRange().getValues();
    var headers = values.shift();
    values.forEach(function(x) { retVal[x[headers.indexOf('id')]] = headers.reduce(function(s, c, n) { s[c] = x[n]; return s }, {}) });
    return retVal;

}

function getProductStats() {

    var query = "SELECT segments.product_item_id, segments.product_feed_label, segments.product_language, metrics.clicks, metrics.conversions FROM shopping_performance_view WHERE segments.product_item_id IN ('" + Object.keys(productItemIds).join("','") + "')";

    if (timePeriod !== "") {
        query = query + " AND segments.date DURING " + timePeriod;
    }

    var report = AdsApp.search(query);

    while (report.hasNext()) {
        var row = report.next();
        var productId = ["online", languages[row.segments.productLanguage], row.segments.productFeedLabel, row.segments.productItemId].join(':');

        if (products[productId]) {
            products[productId].clicks += parseInt(row.metrics.clicks)
        }
    }

}

function disapprovedProducts() {

    var retVal = {}

    var pageToken;

    var query = `SELECT
                product_view.id,
                product_view.offer_id,
                product_view.title,
                product_view.brand,
                product_view.currency_code,
                product_view.price_micros,
                product_view.language_code,
                product_view.condition,
                product_view.channel,
                product_view.availability,
                product_view.shipping_label,
                product_view.gtin,
                product_view.item_group_id,
                product_view.creation_time,
                product_view.expiration_date,
                product_view.aggregated_destination_status,
                product_view.item_issues
              FROM ProductView
              WHERE product_view.aggregated_destination_status = 'NOT_ELIGIBLE_OR_DISAPPROVED' AND product_view.availability = 'IN_STOCK'`

    do {
        var report = ShoppingContent.Reports.search({ query: query, pageToken: pageToken }, merchantId);
        if (report.results) {

            report.results.forEach(function(p) {
                p.productView.clicks = 0;
                p.productView.conversions = 0
                retVal[p.productView.id] = p.productView;
                products[p.productView.id] = p.productView;
                var offerId = p.productView.offerId;
                productItemIds[offerId] = true;
            })

            pageToken = report.nextPageToken

        }
    } while (pageToken)

    return retVal
}

function languageConstants() {

    var retVal = []
    var search = AdsApp.search("SELECT language_constant.resource_name, language_constant.code FROM language_constant");

    while (search.hasNext()) {
        retVal.push(search.next())
    }
    return retVal.reduce(function(s, c) { s[c.languageConstant.resourceName] = c.languageConstant.code; return s }, {})

}

function sendEmail(values) {

    var htmlBody = `<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: Arial, sans-serif; }
        .container { width: 600px; margin: auto; }
        .header { background-color: #f2f2f2; padding: 20px; text-align: center; }
        .content { padding: 20px; }
        .footer { background-color: #f2f2f2; padding: 10px; text-align: center; font-size: 12px; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h2>Disapproved Products Update</h2>
        </div>
        <div class="content">
            <p>Google Ads script identified <b>${values.length} product(s)</b> newly disapproved in your Merchant Center account.</p>
            <p>Check products in <a href="${ss.getUrl()}">Google Spreadsheet</a></p>
            <p>To review the specific reasons for each disapproval and to take the necessary steps for resolution, please follow the instructions below:</p>
            <ol>
                <li>Log in to your <a href="https://merchants.google.com/mc/products/diagnostics?a=${merchantId}">Merchant Center account</a>.</li>
                <li>Navigate to the "Diagnostics" page to review the disapproved products and their issues.</li>
                <li>Update your product data according to the provided guidelines.</li>
                <li>Resubmit your products for review.</li>
            </ol>
            <p>For detailed guidance on resolving these issues, please visit our <a href="https://support.google.com/merchants/">Help Center</a>. If you require further assistance, do not hesitate to contact support.</p>
            <p>Your prompt action is appreciated to ensure your products are available to shoppers without interruption.</p>
            <p>Best regards,</p>
            <p>Google Ads Script</p>
        </div>
        <div class="footer">
            This is an automated message. Please do not reply directly to this email.
        </div>
    </div>
</body>
</html>`

    MailApp.sendEmail({ to: emailRecipient, subject: "Newly Disapproved Products", name: "Google Ads script", htmlBody: htmlBody })
}

Support us on Patreon. Support Ukraine 🇺🇦 !