Free Google Ads Shopping Script – New Experience – White List Positive Keywords With a ‘Contains’ Approach

The amount of times I have tried to use others scripts to do my whitelist work on Google Shopping campaigns, is unbearable to count. They all seem to have similar limitations and drawbacks and I’ve grown tired and scared of running them and screwing things up on a campaign / ad group level to use them anymore.

So, I built my own script for this task. Which automates a lot of negative work on Standard Shopping campaigns in Google Ads.

There are no external Google Sheets, as I find keeping things contained in a script is far less error prone. You set your whitelists in an array, and then enter a few thresholds and other qualifiers and then you are done.

This script is super lightweight & fast too, it user the same qualifiers to lookup the Google Search Query report, so to make the lookup only based on what you need, for the script to do it’s job.

I will try to make a version of this in the future which maps down to Ad Group level. But seeing as most client accounts I audit these days are ‘catch all’ on the ad groups > product groups, I don’t think in most cases you will find it problematic for your everyday needs.

What can you do with this script?

• Specify a list of ‘positive’ matched keywords you want to only appear on, in Google Shopping
• Exclude all non-applicable terms away from the whitelist
• Utilise for brand matching or generic matching. Making it easy to have Brand only Shopping campaigns and specific keyword related shopping campaigns
• Have it run daily, based on any criteria you wish, to make quick work of manual interventions
• Not worry about too many mis-spellings and variations, as this is looking between the whitelist and search terms with a ‘CONTAINS’ logic. So you don’t have to write full terms out, just portions of what will 100% match to most of your required queries – it really works!

How to setup the script

Enter the list of keywords you want in your whitelistKeywords within the array format provided. I just use a Google Sheets with a text list – then CONCATENATE with the inner / outer portions below. Make sure the last array doesn’t have a comma at the end!! Don’t worry about how many terms you add, I’ve run it with hundreds previously.

Replace the daysago field, the Clicksthreshold and ImpressionsThreshold fields below

Then enter the campaign name you wish to have this script applied to. Do not touch anything else. Then run in preview and it’ll also give you a log file showing the actions.

// Copyright 2023, Capewind Ventures Limited. All Rights Reserved.
//
// Created by MAM Digital for free public use license - enjoy
//
//     http://www.mamdigital.co.uk
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Google Shopping Positive Whitelist Script
 *
 * @overview This script will make easy work of campaign negatives where
 * you know exactly the positive keywords you want to appear against
 *
 * @author darryl@capewind.ventures
 *
 * @version 1.0
 *
 */

var config = {

    // Whitelist keywords that you want to search for in search terms
    whitelistKeywords: [
        'keyword1',
        'keyword2',
        'keyword3',
        'etc'
 ],

    // Number of days of data to query (adjust as needed)
    daysAgo: 7,  // Change this value to specify the number of days of data to query

    // Minimum number of clicks to trigger adding a negative keyword
    clicksThreshold: 1,  // Change this value to your desired threshold

    // Minimum number of impressions to trigger adding a negative keyword
    impressionsThreshold: 10,  // Change this value to your desired threshold

}

function main() {
    var allQueries = {};

    Logger.log("Retrieved whitelist keywords.");

    // Replace 'Your Campaign Name' with the name of your specific Google Shopping campaign
    var campaignName = 'Your Campaign Name';

    var campaignIterator = AdsApp.shoppingCampaigns()
        .withCondition("CampaignName = '" + campaignName + "' AND CampaignStatus = ENABLED")
        .get();

    while (campaignIterator.hasNext()) {
        var campaign = campaignIterator.next();

        // Specify the date range for which you want to retrieve search term data
        var today = new Date();
        var daysAgo = new Date(today.getFullYear(), today.getMonth(), today.getDate() - config.daysAgo);
        var dateTo = config.includeToday ? Utilities.formatDate(today, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd') : Utilities.formatDate(new Date(today.getFullYear(), today.getMonth(), today.getDate() - 1), AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
        var dateFrom = Utilities.formatDate(daysAgo, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

        // Build a query to retrieve search queries that meet the criteria
        var query = "SELECT Query, Clicks, Impressions " +
            "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
            "WHERE CampaignName = '" + campaignName + "' " +
            "AND Clicks >= " + config.clicksThreshold + " " +
            "AND Impressions >= " + config.impressionsThreshold + " " +
            "DURING " + dateFrom + "," + dateTo;

        // Retrieve search query data using the optimized query
        var report = AdsApp.report(query);

        var rows = report.rows();
        while (rows.hasNext()) {
            var row = rows.next();
            var query = row['Query'];
            var clicks = row['Clicks'];
            var impressions = row['Impressions'];

            if (!allQueries.hasOwnProperty(query)) {
                allQueries[query] = { clicks: clicks, impressions: impressions };
            } else {
                allQueries[query].clicks += clicks;
                allQueries[query].impressions += impressions;
            }
        }
    }

    // Log all queries and their click counts and impressions for debugging
    for (var query in allQueries) {
        var clicks = allQueries[query].clicks;
        var impressions = allQueries[query].impressions;
        Logger.log("Query: '" + query + "', Clicks: " + clicks + ", Impressions: " + impressions);

        // Check if the query contains any whitelist keyword
        var isWhitelisted = config.whitelistKeywords.some(function (keyword) {
            return query.toLowerCase().indexOf(keyword.toLowerCase()) !== -1;
        });

        // Check if the clicks and impressions meet the thresholds
        if (!isWhitelisted && clicks >= config.clicksThreshold && impressions >= config.impressionsThreshold) {
            Logger.log("Adding negative keyword '" + query + "' to the campaign.");
            // Add code to create negative keywords at the campaign level
            campaign.createNegativeKeyword('[' + query + ']');
        }
    }

    Logger.log("Script execution completed.");
}

If after running you encounter problems. Don’t hesitate to get in touch with me!