Exclude Exact in BMM

// Exclude Exact in BMM
//
// ABOUT THE SCRIPT
// Export all selected Exact match keywords to a NKL (Negative Keyword List)
// and apply this list(s) to your BMM campaigns
//
// Created By: Martijn Kraan
// Brightstep.nl
// 
// Created: 09-09-2019
// Last update: 09-09-2019
//
////////////////////////////////////////////////////////////////////

var config = {

    // This is the part where you configure the script

    // --- About the EXACT keywords: ---
    // Do you want to use keywords from active campaigns only?
    ACTIVE_CAMPAIGNS_ONLY: 'Yes',
    // Do you want to use keywords from active ad groups only?
    ACTIVE_ADGROUPS_ONLY: 'Yes',
    // Do you want to use active keywords only?
    ACTIVE_KEYWORDS_ONLY: 'Yes',
    // How can we identify the Exact campaigns?
    // Leave empty to get the exact keywords from all campaigns
    EXACT_IDENTIFIER: 'exact',

    // --- About the BMM campaigns: ---
    // How can we identify the BMM campaigns?  
    BMM_IDENTIFIER: 'bmm',

    // --- About DSA campaigns: ---
    // Do you want to apply the exact match negatives in your DSA campaigns?
    INCLUDE_DSA: 'Yes',
    // How can we identify your DSA campaigns?
    DSA_IDENTIFIER: 'dsa',

    // --- About the NKL's (Negative Keyword Lists): ---
    // What name should the NKL carry?
    // If multiple lists are needed, the script will automatically create multiple lists by appending "1", " 2", etc
    NKL_NAME: 'Exact',
    // Do you want to empty & refill the NKL every time the script runs?
    // If 'Yes', make sure no other lists start with NKL_NAME variable.
    // If 'No', the script will only add NEW negative keywords
    EMPTY_EVERY_RUN: 'Yes',
    // What is the maximum number of negative keywords per list?
    // Check the limits at https://bit.ly/2MJhd1z
    KEYWORDS_PER_LIST: 5000

}

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

function main() {

    // Get the Exact match Keywords
    var exactKeywords = getExactKeywords();
    var numberOfLists = exactKeywords.length;

    // Check if we have to create any NKL's
    generateNKL(numberOfLists);

    // Check if we need to empty the NKL's before (re)populating them
    if (config.EMPTY_EVERY_RUN === 'Yes') {
        removeSNK();
    }

    // Let's add some Exact keywords to the NKL(s)
    addKeywordsToNKL(exactKeywords);

    // Add the NKL's to the BMM (and DSA) campaigns
    applyToCampaigns();
}

function getExactKeywords() {

    var listOfKeywordsTemp = [];
    var listOfKeywords = [];
    var map = {};

    // Build the AWQL query based on the config input
    var query =
        ' SELECT Criteria ' +
        ' FROM KEYWORDS_PERFORMANCE_REPORT ' +
        ' WHERE KeywordMatchType = "EXACT" ';
    if (config.ACTIVE_CAMPAIGNS_ONLY === 'Yes') {query += ' AND CampaignStatus = "ENABLED" '};
    if (config.ACTIVE_ADGROUPS_ONLY === 'Yes') {query += ' AND AdGroupStatus = "ENABLED" '};
    if (config.ACTIVE_KEYWORDS_ONLY === 'Yes') {query += ' AND Status = "ENABLED" '};
    if (config.EXACT_IDENTIFIER !== '') {query += ' AND CampaignName CONTAINS_IGNORE_CASE "' + config.EXACT_IDENTIFIER + '" '};

    var rows = AdsApp.report(query).rows()

    // Check all keywords (rows) and add them to a list
    while (rows.hasNext()) {
        var row = rows.next();
        var keywordText = row['Criteria'];
        // This part is for deduplication
        if (map[keywordText]) {
            // If keyword is already in list
            continue;
        } else {
            // If not, add a new keyword to the map.
            map[keywordText] = keywordText;
            listOfKeywordsTemp.push('[' + keywordText + ']');
        }
    }

    // Calculate if the list contains more than the maximum number of keywords per list.
    // If yes, split the list into multiple lists
    var chunks = Math.ceil(listOfKeywordsTemp.length / config.KEYWORDS_PER_LIST);
    for (var y = 0; y < chunks; y++) {
        listOfKeywords[y] = listOfKeywordsTemp.splice(0, config.KEYWORDS_PER_LIST);
    }

    // Return the list(s) of kewords
    return listOfKeywords;
}

function generateNKL(numListsNeeded) {

    // Count the existing NKL's. This wil be zero on the first run
    var numLists = AdsApp.negativeKeywordLists()
        .withCondition('Name STARTS_WITH "' + config.NKL_NAME + '"')
        .get()
        .totalNumEntities();

    // If we don't have enough NKL's...
    if (numLists < numListsNeeded) {
        // ... create an extra list
        createNKL(numLists, numListsNeeded);
        // If we have to much NKL's...
    } else if (numLists > numListsNeeded) {
        // ... log a message suggesting to remove the unused list manually
        Logger.log('A NKL has become obsolete. You can remove the empty list in the Shared Library (this is not possible via Google Ads Scripts)');
    }
}

function createNKL(numLists, numListsNeeded) {
    for (var y = numLists + 1; y < numListsNeeded + 1; y++) {
        var nklO = AdsApp.newNegativeKeywordListBuilder()
            .withName(config.NKL_NAME + ' ' + y)
            .build();
    }
}

function removeSNK() {

    // Get the NKL's
    var nklI = AdsApp.negativeKeywordLists()
        .withCondition('Name STARTS_WITH "' + config.NKL_NAME + '"')
        .get();

    // For each NKL...
    while (nklI.hasNext()) {
        var nkl = nklI.next();

        // ...get the negative Keywords...
        var snkI = nkl.negativeKeywords().get();

        // ... and remove the negative keywords
        while (snkI.hasNext()) {
            snkI.next().remove();
        }
    }
}

function addKeywordsToNKL(input) {

    // Get the NKL's
    var nklI = AdsApp.negativeKeywordLists()
        .withCondition('Name STARTS_WITH "' + config.NKL_NAME + '"')
        .get();

    // For each NKL...
    if (nklI.hasNext()) {
        for (var x = 0; x < input.length; x++) {
            var nkl = nklI.next();

            // ... add the keywords
            try {
                nkl.addNegativeKeywords(input[x]);
            } catch (err) {
                Logger.log('Error');
            }
        }
    }
}

function applyToCampaigns() {

    // Select the Search campaigns
    var query =
        ' SELECT CampaignName, CampaignId ' +
        ' FROM CAMPAIGN_PERFORMANCE_REPORT ' +
        ' WHERE AdvertisingChannelType = "SEARCH" ' +
        ' AND CampaignStatus = "ENABLED" ';

    var rows = AdsApp.report(query).rows()
    var campaignIDs = [];

    // Check if the campaign names match the BMM (or DSA) identifier
    while (rows.hasNext()) {
        var row = rows.next();
        var campaignName = row['CampaignName'];

        // If there's a match, than add the the campaign ID to a list
        if (campaignName.indexOf(config.BMM_IDENTIFIER) != -1) {
            campaignIDs.push(row['CampaignId']);
        }
        // Same for DSA
        if (config.INCLUDE_DSA === 'Yes') {
            if (campaignName.indexOf(config.DSA_IDENTIFIER) != -1) {
                campaignIDs.push(row['CampaignId']);
            }
        }
    }

    // apply all NKL's to the campaigns with the ID's in the list
    var nklI = AdsApp.negativeKeywordLists()
        .withCondition('Name STARTS_WITH "' + config.NKL_NAME + '"')
        .get();

    while (nklI.hasNext()) {
        var nkl = nklI.next();
        var campaignIterator = AdsApp.campaigns()
            .withIds(campaignIDs)
            .get();
        while (campaignIterator.hasNext()) {
            var campaign = campaignIterator.next();
            campaign.addNegativeKeywordList(nkl);
        }
    }
}