Brainlabs Shopping Exact Match Script Not Working For Me

[psim...@shoeinsoles.co.uk]

Hello there,

I am using a Brainlabs script to provide exact match keyword capabilities for a shopping feed (http://searchengineland.com/keywords-back-google-shopping-campaigns-230973). The script is copied below the body of this post.

The aim is to specify keywords that I want as an exact match within a shopping ad group, and to add negative keywords to the ad group automatically. The script should pick up negative keywords automatically from search terms that do not match my specified exact match keywords (listed in a Google Sheet as per the instructions).

When I run the script, it recognises the exact matches that I want to use from my Google Sheet list, however it is not adding any negative keywords to the ad groups automatically when I schedule the script to run daily. This is what the log returns:

Found 16 keywords for 1 ad groups in 1 campaign(s).

Could not find any ad groups with impressions that matched the given names.

I have put 16 keywords into my list. So far there are about 136 impressions on this ad group in the last 24-48 hours, with a majority of those not matching my exact match keywords in my Google Sheets list.

If the script was executing correctly, I would anticipate over 100 negative keywords to have been added automatically.

I can't see where I'm going wrong with this one. If anyone could help tweak the code, that would be great. 

Many thanks in advance.

Kind regards,

Paul

/**
	*
	* Exact Match For Shopping
	*
	* This script reads a list of exact match keywords for Shopping campaigns from a Google Doc,
	* and then excludes any search queries from those camapigns if they do not match those keywords.
	*
	* Version: 1.0
	* Google AdWords Script maintained by brainlabsdigital.com
	*
	**/
	function main() {
	// Put your spreadsheet's URL here:
	var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
	// Make sure the keywords are in columns A to C in the first sheet.
	//////////////////////////////////////////////////////////////////////////////
	var dateRange = "YESTERDAY";
	// By default the script just looks at yesterday's search queries.
	var impressionThreshold = 0;
	// The script only looks at searches with impressions higher than this threshold.
	// Use this if you get a wide range of searches and only want to exclude the highest volume ones.
	// Read the spreadsheet
	try {
	var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
	} catch (e) {
	Logger.log("Problem with the spreadsheet URL: '" + e + "'");
	Logger.log("Make sure you have correctly copied in your own spreadsheet URL.");
	return;
	}
	var sheet = spreadsheet.getSheets()[0];
	var spreadsheetData = sheet.getDataRange().getValues();
	// Record each campaigns' keywords, and the words (of more than 4 characters) that are in the keywords
	var keywords = {};
	var words = {};
	var numberOfKeywords = 0;
	var numberOfadGroups = 0;
	for(var i=1; i<spreadsheetData.length; i++) {
	var campaignName = spreadsheetData[i][0];
	var adGroupName = spreadsheetData[i][1];
	if (keywords[campaignName] == undefined) {
	keywords[campaignName] = [];
	words[campaignName] = {};
	}
	if (keywords[campaignName][adGroupName] == undefined) {
	keywords[campaignName][adGroupName] = [];
	words[campaignName][adGroupName] = {};
	numberOfadGroups++;
	}
	var keyword = spreadsheetData[i][2];
	keyword = keyword.toLowerCase().replace(/[^\w\s\d&]/g," ").replace(/ +/g," ").trim();
	keywords[campaignName][adGroupName].push(keyword);
	numberOfKeywords++;
	var keywordWords = keyword.split(" ");
	for (var k=0; k<keywordWords.length; k++) {
	if (keywordWords[k].length > 4) {
	words[campaignName][adGroupName][keywordWords[k]] = true;
	}
	}
	}
	var campaignNames = Object.keys(keywords);
	Logger.log("Found " + numberOfKeywords + " keywords for " + numberOfadGroups + " ad groups in " + campaignNames.length + " campaign(s).");
	// Get the IDs of the ad groups named in the spreadsheet
	var adGroupIds = [];
	var campaignIds = [];
	var campaignReport = AdWordsApp.report(
	"SELECT CampaignName, AdGroupName, CampaignId, AdGroupId " +
	"FROM ADGROUP_PERFORMANCE_REPORT " +
	"WHERE Impressions > 0 " +
	'AND CampaignName IN ["' + campaignNames.join('","') + '"] ' +
	"DURING " + dateRange
	);
	var campaignRows = campaignReport.rows();
	while (campaignRows.hasNext()) {
	var row = campaignRows.next();
	if (campaignIds.indexOf(row["CampaignId"]) < 0) {
	campaignIds.push(row["CampaignId"]);
	}
	if (keywords[row["CampaignName"]][row["AdGroupName"]] != undefined) {
	adGroupIds.push(row["AdGroupId"]);
	}
	}//end while
	if (adGroupIds.length == 0) {
	Logger.log("Could not find any ad groups with impressions that matched the given names.");
	return;
	}
	Logger.log("Found " + adGroupIds.length + " ad groups in " + campaignIds.length + " campaign(s) with impressions that matched the given names.");
	// Initialise the arrays for each campaign, and sorts the keywords from longest to shortest
	var negativeQueries = {}; // Contains the queries
	var exactNegatives = {}; // Contains any negatives to add with exact match
	var phraseNegatives = {}; // Contains any negatives to add with phrase match
	for (var campaignName in keywords) {
	negativeQueries[campaignName] = {};
	exactNegatives[campaignName] = {};
	phraseNegatives[campaignName] = {};
	for (var adGroupName in keywords[campaignName]) {
	negativeQueries[campaignName][adGroupName] = [];
	exactNegatives[campaignName][adGroupName] = [];
	phraseNegatives[campaignName][adGroupName] = [];
	keywords[campaignName][adGroupName].sort(function(a,b) {return b.length - a.length;});
	}
	}
	// Get the queries that don't exactly match keywords
	var report = AdWordsApp.report(
	"SELECT Query, AdGroupId, CampaignId, CampaignName, AdGroupName, Impressions " +
	"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
	"WHERE AdGroupId IN [" + adGroupIds.join(",") + "] " +
	"AND Impressions > " + impressionThreshold + " " +
	"DURING " + dateRange);
	var rows = report.rows();
	var numberQueries = 0;
	while (rows.hasNext()) {
	var row = rows.next();
	var query = row["Query"].toLowerCase().replace(/[^\w\s\d&]/g," ").replace(/ +/g," ").trim();
	var campaignName = row["CampaignName"];
	var adGroupName = row["AdGroupName"];
	if (keywords[campaignName][adGroupName].indexOf(query) < 0) {
	negativeQueries[campaignName][adGroupName].push(query);
	numberQueries++;
	}
	}
	// Process queries
	Logger.log("Processing " + numberQueries + " queries that do not match any keywords.");
	var numberExactNegatives = 0;
	var numberPotentialPhraseNegatives = 0;
	for (var campaignName in negativeQueries) {
	for (var adGroupName in negativeQueries[campaignName]) {
	for (var i=0; i<negativeQueries[campaignName][adGroupName].length; i++) {
	var query = negativeQueries[campaignName][adGroupName][i];
	var queryDone = false;
	// If the query is contained within a keyword, it has to be an exact match negative
	if (isStringInsideKeywords(query, keywords[campaignName][adGroupName])) {
	exactNegatives[campaignName][adGroupName].push(query);
	numberExactNegatives++;
	continue;
	}
	// Check each word (that's over 4 characters) in the query - if it's not in the words array
	// then it isn't in the keywords, so it's fine to use as a phrase negative
	var queryWords = query.split(" ");
	for (var w=0; w<queryWords.length; w++) {
	if (queryWords[w].length > 4) {
	if (words[campaignName][adGroupName][queryWords[w]] == undefined) {
	phraseNegatives[campaignName][adGroupName].push(queryWords[w]);
	queryDone = true;
	break;
	}
	}
	}
	// Check if there is a keyword inside the query. If there is, see if the part of the query before
	// or after the keyword could be used as a phrase negative.
	for (var k=0; k<keywords[campaignName][adGroupName].length && !queryDone; k++) {
	var keyword = keywords[campaignName][adGroupName][k];
	if ((" " + query + " ").indexOf(" " + keyword + " ") > -1) {
	var queryBits = (" " + query + " ").split(" " + keyword + " ");
	queryBits[0] = queryBits[0].trim();
	queryBits[1] = queryBits[1].trim();
	if (queryBits[0].length > 0 && !isStringInsideKeywords(queryBits[0], keywords[campaignName][adGroupName])) {
	phraseNegatives[campaignName][adGroupName].push(queryBits[0]);
	queryDone = true;
	break;
	}
	if (queryBits[1].length > 0 && !isStringInsideKeywords(queryBits[1], keywords[campaignName][adGroupName])) {
	phraseNegatives[campaignName][adGroupName].push(queryBits[1]);
	queryDone = true;
	break;
	}
	}
	}
	// If nothing smaller than the full query would work, then add the full query as a negative
	if (!queryDone) {
	phraseNegatives[campaignName][adGroupName].push(query);
	}
	numberPotentialPhraseNegatives++;
	}
	}
	}
	Logger.log("Found " + numberPotentialPhraseNegatives + " potential phrase match negatives and " + numberExactNegatives + " exact match negatives.");
	// Remove any redundant phrase negatives
	Logger.log("Checking for redundant negatives.");
	var numberPhraseNegatives = 0;
	for (var campaignName in negativeQueries) {
	for (var adGroupName in negativeQueries[campaignName]) {
	// Order the phrases from shortest to longest
	phraseNegatives[campaignName][adGroupName].sort(function(a,b) {return a.length - b.length;});
	for (var i=0; i<phraseNegatives[campaignName][adGroupName].length; i++) {
	var shorterPhrase = " " + phraseNegatives[campaignName][adGroupName][i] + " ";
	// As the array is now ordered, any phrase negatives with higher indices must be longer than shorterPhrase
	for (var j=i+1; j<phraseNegatives[campaignName][adGroupName].length; j++) {
	var longerPhrase = " " + phraseNegatives[campaignName][adGroupName][j] + " ";
	// If the shorterPhrase is within the longerPhrase, then the longerPhrase is redundant
	// so it is removed from the array. This also means duplicates are removed.
	if (longerPhrase.indexOf(shorterPhrase) > -1) {
	phraseNegatives[campaignName][adGroupName].splice(j,1);
	j--;
	}
	}
	}
	numberPhraseNegatives += phraseNegatives[campaignName][adGroupName].length;
	}
	}
	Logger.log("Going to create " + numberPhraseNegatives + " phrase match negatives and " + numberExactNegatives + " exact match negatives");
	// Iterate through the Shopping ad groups and add the negative keywords
	var groupIterator = AdWordsApp.shoppingAdGroups()
	.withIds(adGroupIds)
	.get()
	while (groupIterator.hasNext()) {
	var adGroup = groupIterator.next();
	var adGroupName = adGroup.getName();
	var campaignName = adGroup.getCampaign().getName();
	for (var i=0; i<exactNegatives[campaignName][adGroupName].length; i++) {
	adGroup.createNegativeKeyword("[" + exactNegatives[campaignName][adGroupName][i] + "]");
	}
	for (var i=0; i<phraseNegatives[campaignName][adGroupName].length; i++) {
	adGroup.createNegativeKeyword('"' + phraseNegatives[campaignName][adGroupName][i] + '"');
	}
	}
	Logger.log("Finished.");
	} // end main function
	// Check if a word is a substring of any strings in the keywords array
	function isStringInsideKeywords(word, keywords) {
	for (var k=0; k<keywords.length; k++) {
	var keyword = " " + keywords[k] + " ";
	if (keyword.indexOf(" " + word + " ") > -1) {
	return true;
	}
	}
	return false;
	}

[Thea Vega (AdWords Scripts Team)]

Hello Paul,

Do make sure that you are following the steps as instructed by BrainLabs for this third party script. Since your concern is more on the issues of the third party script (since your results were not accurate), I recommend that you reach out to the author. They can provide you better support and assistance regarding the whole logic of the script.

Thanks,

Thea

AdWords Scripts Team

[psim...@shoeinsoles.co.uk]

Hi Thea,

Thanks for your reply. I've got in touch with them and waiting for a reply. However, it was posted in 2015 by the CEO of Brainlabs. Hoping he gets in touch but I guess he's a busy guy.

In the meantime, if I could explain which bit isn't working to pinpoint the issue:

I have followed all the steps. The script should pick up any searches that do not match my list of exact keywords in the Google Sheets spreadsheet. It should even do this: "The script chops the query into bits and makes phrase match negatives wherever possible, so each negative can exclude as many unwanted searches as possible while keeping anything that matches the keywords".

The script isn't adding the negatives to the ad group concerned at all so I think it is the adding of the negatives to the ad group that is currently failing.

If anyone can see where the problem is, that would be awesome.

Many thanks.

Kind regards,

Paul

[psim...@shoeinsoles.co.uk]

Apologies,

It is working now. It was a trailing space error.

Sorry for the trouble.

Kind regards,

Paul

[Jamie Degiorgio]

Hi,

I am having the same issue with the script, can you elaborate on the trailing space error please?

Best,

Jamie

[Google Ads Scripts Forum Advisor]

Hi Jamie,

I would also suggest reaching out to the author of the third party script to get the full description of this workaround. The author of the third party is more familiar with the logic of the code and how it is working.

Regards,

Ernie John Blanca Tacata Google Ads Scripts Team

ref:_00D1U1174p._5004Q23um8b:ref