Skip to content

Instantly share code, notes, and snippets.

@g-ads-org
Created July 20, 2020 12:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save g-ads-org/c5277e04c0c7b3d3db2365882b24ec43 to your computer and use it in GitHub Desktop.
Save g-ads-org/c5277e04c0c7b3d3db2365882b24ec43 to your computer and use it in GitHub Desktop.
/**
*
* Make Exact Match Exact
*
* Adds negatives for any search query that doesn't actually exactly match an exact
* match keyword.
*
* Version: 2.0
* Google AdWords Script maintained on brainlabsdigital.com
*
*/
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Options
var campaignNameDoesNotContain = [];
// Use this if you want to exclude some campaigns. Case insensitive.
// For example ["Brand"] would ignore any campaigns with 'brand' in the name,
// while ["Brand","Competitor"] would ignore any campaigns with 'brand' or
// 'competitor' in the name.
// Leave as [] to not exclude any campaigns.
var campaignNameContains = [];
// Use this if you only want to look at some campaigns. Case insensitive.
// For example ["Brand"] would only look at campaigns with 'brand' in the name,
// while ["Brand","Generic"] would only look at campaigns with 'brand' or 'generic'
// in the name.
// Leave as [] to include all campaigns.
// Choose whether the negatives are created, or if you just get an email to review
var makeChanges = true;
// These addresses will be emailed when the tool is run, eg "daniel@example.com"
// If there are multiple addresses then separate them with commas, eg "a@a.com, b@b.com"
// Leave as "" to not send any emails
var emailAddresses = '';
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function main() {
var campaigns = {};
var adGroups = {};
var exactKeywords = [];
var exactGroupIds = {};
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Pull a list of all exact match keywords in the account
var campaignIds = getCampaignIds();
var report = AdWordsApp.report(
'SELECT AdGroupId, Id, Criteria '
+ 'FROM KEYWORDS_PERFORMANCE_REPORT '
+ 'WHERE Impressions > 0 AND KeywordMatchType = EXACT '
+ 'AND CampaignId IN [' + campaignIds.join(',') + '] '
+ 'AND AdGroupStatus IN [ENABLED, PAUSED] '
+ 'AND Status IN [ENABLED, PAUSED] '
+ 'DURING LAST_30_DAYS'
);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var keywordId = row.Id;
var adGroupId = row.AdGroupId;
exactKeywords.push(adGroupId + '#' + keywordId);
exactGroupIds[adGroupId] = true;
if (!adGroups.hasOwnProperty(adGroupId)) {
adGroups[adGroupId] = [
[],
[],
[]
];
}
adGroups[adGroupId][2].push(row.Criteria.toLowerCase().trim());
}
exactGroupIds = Object.keys(exactGroupIds);
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Remove ad groups with non-exact keywords
var nonExactGroupIds = {};
for (var i = 0; i < exactGroupIds.length; i += 10000) {
var exactGroupIdsChunk = exactGroupIds.slice(i, i + 10000);
var report = AdWordsApp.report(
'SELECT AdGroupId, Id '
+ 'FROM KEYWORDS_PERFORMANCE_REPORT '
+ 'WHERE KeywordMatchType != EXACT AND IsNegative = FALSE '
+ 'AND AdGroupId IN [' + exactGroupIdsChunk.join(',') + '] '
+ 'AND Status IN [ENABLED, PAUSED] '
+ 'DURING LAST_30_DAYS'
);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var adGroupId = row.AdGroupId;
nonExactGroupIds[adGroupId] = true;
}
}
var onlyExactGroupIds = [];
for (var i = 0; i < exactGroupIds.length; i++) {
if (nonExactGroupIds[exactGroupIds[i]] == undefined) {
onlyExactGroupIds.push(exactGroupIds[i]);
}
}
Logger.log(onlyExactGroupIds.length + ' ad groups (with only exact keywords) were found.');
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Pull a list of all exact (close variant) search queries
for (var i = 0; i < onlyExactGroupIds.length; i += 10000) {
var onlyExactGroupIdsChunk = onlyExactGroupIds.slice(i, i + 10000);
var report = AdWordsApp.report(
'SELECT Query, AdGroupId, CampaignId, KeywordId, KeywordTextMatchingQuery, Impressions, QueryMatchTypeWithVariant, AdGroupName '
+ 'FROM SEARCH_QUERY_PERFORMANCE_REPORT '
+ 'WHERE AdGroupId IN [' + onlyExactGroupIdsChunk.join(',') + '] '
+ 'DURING LAST_30_DAYS'
);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var adGroupId = parseInt(row.AdGroupId);
var campaignId = parseInt(row.CampaignId);
var keywordId = parseInt(row.KeywordId);
var searchQuery = row.Query.toLowerCase().trim();
var keyword = row.KeywordTextMatchingQuery.toLowerCase().trim();
var matchType = row.QueryMatchTypeWithVariant.toLowerCase().trim();
if (keyword !== searchQuery && matchType.indexOf('exact (close variant)') !== -1) {
if (adGroups[adGroupId][2].indexOf(searchQuery) > -1) {
// This query is a positive keyword in the ad group
// so we don't want to add is as a negative
continue;
}
if (!campaigns.hasOwnProperty(campaignId)) {
campaigns[campaignId] = [
[],
[]
];
}
campaigns[campaignId][0].push(searchQuery);
campaigns[campaignId][1].push(adGroupId + '#' + keywordId);
if (!adGroups.hasOwnProperty(adGroupId)) {
adGroups[adGroupId] = [
[],
[]
];
}
adGroups[adGroupId][0].push(searchQuery);
adGroups[adGroupId][1].push(adGroupId + '#' + keywordId);
}
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Parse data correctly
var adGroupIds = [];
var adGroupNegatives = [];
for (var x in adGroups) {
adGroupIds.push(parseInt(x));
adGroupNegatives.push([]);
for (var y = 0; y < adGroups[x][0].length; y++) {
var keywordId = adGroups[x][1][y];
var keywordText = adGroups[x][0][y];
if (exactKeywords.indexOf(keywordId) !== -1) {
adGroupNegatives[adGroupIds.indexOf(parseInt(x))].push(keywordText);
}
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Create the new negative exact keywords
var results = [];
for (var i = 0; i < adGroupIds.length; i += 10000) {
var adGroupIdsChunk = adGroupIds.slice(i, i + 10000);
var adGroupIterator = AdWordsApp.adGroups()
.withIds(adGroupIdsChunk)
.get();
while (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
var adGroupId = adGroup.getId();
var adGroupName = adGroup.getName();
var adGroupIndex = adGroupIds.indexOf(adGroupId);
var campaignName = adGroup.getCampaign().getName();
for (var j = 0; j < adGroupNegatives[adGroupIndex].length; j++) {
if (makeChanges) {
adGroup.createNegativeKeyword('[' + adGroupNegatives[adGroupIndex][j] + ']');
}
results.push([campaignName, adGroupName, adGroupNegatives[adGroupIndex][j]]);
}
}
}
if (!makeChanges || AdWordsApp.getExecutionInfo().isPreview()) {
Logger.log(results.length + ' new negatives were found.');
} else {
Logger.log(results.length + ' new negatives were created.');
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Email the results
if (emailAddresses == '') {
Logger.log('No email addresses given - not sending email.');
} else if (results.length == 0) {
Logger.log('No changes to email.');
} else {
var attachments = [];
var headers = ['Campaign', 'Ad Group', 'Negative'];
attachments.push(createEscapedCsv([headers].concat(results), 'Ad-Group-Negatives.csv'));
if (!makeChanges || AdWordsApp.getExecutionInfo().isPreview()) {
var verb = 'would be';
} else {
var verb = 'were';
}
var subject = AdWordsApp.currentAccount().getName() + ' - Making Exact Match Exact - ' + Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd');
var body = 'Please find attached a list of the ' + results.length + ' negative keywords that ' + verb + ' added to your account.';
var options = {
attachments: attachments
};
MailApp.sendEmail(emailAddresses, subject, body, options);
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Prepare an array to be made into a CSV
function createEscapedCsv(array, csvName) {
var cells = [];
for (var i = 0; i < array.length; i++) {
var row = [];
for (var j = 0; j < array[i].length; j++) {
row.push(array[i][j].replace(/"/g, '""'));
}
cells.push('"' + row.join('","') + '"');
}
return Utilities.newBlob('\ufeff' + cells.join('\n'), 'text/csv', csvName);
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Get the IDs of campaigns which match the given options
function getCampaignIds() {
var whereStatement = '';
var whereStatementsArray = [];
var campaignIds = [];
for (var i = 0; i < campaignNameDoesNotContain.length; i++) {
whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain[i].replace(/"/g, '\\\"') + "' ";
}
if (campaignNameContains.length == 0) {
whereStatementsArray = [whereStatement];
} else {
for (var i = 0; i < campaignNameContains.length; i++) {
whereStatementsArray.push(whereStatement + 'AND CampaignName CONTAINS_IGNORE_CASE "' + campaignNameContains[i].replace(/"/g, '\\\"') + '" ');
}
}
for (var i = 0; i < whereStatementsArray.length; i++) {
var campaignReport = AdWordsApp.report(
'SELECT CampaignId '
+ 'FROM CAMPAIGN_PERFORMANCE_REPORT '
+ 'WHERE CampaignStatus = ENABLED '
+ whereStatementsArray[i]
+ 'DURING LAST_30_DAYS'
);
var rows = campaignReport.rows();
while (rows.hasNext()) {
var row = rows.next();
campaignIds.push(row.CampaignId);
}
}
if (campaignIds.length == 0) {
throw ('No campaigns found with the given settings.');
}
Logger.log(campaignIds.length + ' campaigns were found.');
return campaignIds;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment