// Script for Detecting Duplicate Search Queries in Different Ad Groups (AdWords)
//
// Copyright 2017 - igloonet, s.r.o.
// For more advanced penguin solutions visit igloonet.cz
//
// The script searches for duplicate search queries in different ad groups. The result is an inspiration for new ad groups and control over the structure and campaign settings.
//
// Do not hesitate and adjust as needed. Free to edit :)
//
// We are not responsible for the correct operation and settings of the script, nor for further editing.
var MIN_IMPRESSIONS = 5;
var SPREADSHEET_PREFIX = 'Duplicated Query Strings';
var IGNORE_LABEL_NAME = 'ignore search query';
var ACCOUNT_LABEL = 'Duplicited query';
var NOTIFY = ['ppc@igloonet.cz'];
//var IGNORE_CAMPAIGN_NAMES = ['PLA', 'TEST', 'RLSA'];
function main() {
MccApp.accounts().withCondition("LabelNames CONTAINS '" + ACCOUNT_LABEL + "'").withLimit(50).executeInParallel('checkQueryStrings', 'reportResults');
}
function checkQueryStrings() {
var ignoreLabel = AdWordsApp.labels().withCondition("Name = '" + IGNORE_LABEL_NAME + "'").get();
ignoreLabel = ignoreLabel.hasNext() ? ignoreLabel.next() : null;
var campaignIds = [];
var campaignIterator = ignoreLabel !== null ? AdWordsApp.campaigns().withCondition("Labels CONTAINS_NONE [" + ignoreLabel.getId() + "]").get() : AdWordsApp.campaigns().get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
campaignIds.push(campaign.getId());
}
var options = {apiVersion: 'v201702'};
var fields = ['CampaignId', 'CampaignName',
'AdGroupId', 'AdGroupName',
'Query', 'QueryMatchTypeWithVariant', 'Impressions', 'Clicks', 'Ctr', 'Cost', 'Conversions', 'AverageCpc', 'AveragePosition'];
var report = 'SEARCH_QUERY_PERFORMANCE_REPORT';
var query = ['SELECT', fields.join(','), 'FROM', report,
'WHERE CampaignId IN [' + campaignIds.join(', ') + ']',
'AND CampaignStatus = ENABLED',
'AND AdGroupStatus = ENABLED',
//"WHERE CampaignName DOES_NOT_CONTAIN '" + IGNORE_CAMPAIGN_NAMES.join("' AND CampaignName DOES_NOT_CONTAIN '") + "'", //filtrovanie podla mena
'AND Impressions >= ' + MIN_IMPRESSIONS,
'DURING', 'LAST_30_DAYS'].join(' '); //nastaveni zpetneho ziskavani dat, konstanty: TODAY | YESTERDAY | LAST_7_DAYS | THIS_WEEK_SUN_TODAY | THIS_WEEK_MON_TODAY | LAST_WEEK |
//LAST_14_DAYS | LAST_30_DAYS | LAST_BUSINESS_WEEK | LAST_WEEK_SUN_SAT | THIS_MONTH
var reportIterator = AdWordsApp.report(query, options).rows();
var stack = {};
/* srpacovanie dat */
while (reportIterator.hasNext()) {
var row = reportIterator.next();
var searchQuery = row.Query;
var queryData = {
searchQuery: searchQuery,
campaignName: row.CampaignName,
adGroupName: row.AdGroupName,
matchType: row.QueryMatchTypeWithVariant,
impressions: row.Impressions,
clicks: row.Clicks,
ctr: row.Ctr,
cost: row.Cost,
cpc: row.AverageCpc,
position: row.AveragePosition,
conversions: row.Conversions
};
if (typeof stack[searchQuery] === "undefined") {
stack[searchQuery] = {};
}
if (typeof stack[searchQuery][row.AdGroupId] === "undefined") {
stack[searchQuery][row.AdGroupId] = [];
}
stack[searchQuery][row.AdGroupId].push(queryData);
}
var stackToSort = [];
for (var searchQuery in stack) {
/* duplicitny vyraz */
if (Object.keys(stack[searchQuery]).length > 1) {
/* suma impressions */
var impressionsSum = 0;
/* zistenie sumy impressions */
for (var adGroupId in stack[searchQuery]) {
stack[searchQuery][adGroupId].forEach(function (match) {
impressionsSum += parseInt(match.impressions);
});
}
stackToSort.push({
impressionsSum: impressionsSum,
data: stack[searchQuery]
});
}
}
/* zoradi podla impressions sum (najviac zobrazeni hore) */
stackToSort.sort(function (b, a) {
return a.impressionsSum - b.impressionsSum;
});
return JSON.stringify({
accountName: AdWordsApp.currentAccount().getName(),
results: stackToSort
});
}
function reportResults(results) {
var timeStamp = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd HH:m:s');
var spreadsheetName = SPREADSHEET_PREFIX + ' - ' + timeStamp;
var sheet_urls = {};
for (var i in results) {
var result = results[i].getReturnValue()
if (!result) {
continue;
}
result = JSON.parse(result);
if (Object.keys(result.results).length === 0) {
continue;
}
var sheetUrl = writeResultsToSpreadsheet(result, spreadsheetName);
Logger.log(sheetUrl);
sheet_urls[result.accountName] = sheetUrl;
}
sendSummaryEmail(sheet_urls);
}
function writeResultsToSpreadsheet(result, name) {
var file = getFile(name, true);
var spreadsheet;
var maxRetries = 0;
while (maxRetries < 3) {
try {
spreadsheet = SpreadsheetApp.openById(file.getId());
break;
} catch (e) {
maxRetries++;
Utilities.sleep(1000);
}
}
if (!spreadsheet) {
throw 'Could not open file: ' + name;
}
if (spreadsheet.getSheetByName('Sheet1')) {
spreadsheet.getSheetByName('Sheet1').setName(result.accountName);
}
var sheet = spreadsheet.getSheetByName(result.accountName);
if (!sheet) {
sheet = spreadsheet.insertSheet(result.accountName, spreadsheet.getSheets().length);
}
var toWrite = [['Search Query', 'Campaign Name', 'AdGroup Name', 'Match Type', 'Impressions', 'Clicks', 'CTR', 'CPC', 'Cost', 'Avg Position', 'Conversions', 'Impressions Sum']];
result.results.forEach(function (item) {
var adGroups = item.data;
for (var adGroupId in adGroups) {
var matches = adGroups[adGroupId];
matches.forEach(function (match) {
toWrite.push([
match.searchQuery,
match.campaignName,
match.adGroupName,
match.matchType,
match.impressions,
match.clicks,
match.ctr,
match.cpc,
match.cost,
match.position,
match.conversions,
item.impressionsSum
]);
});
}
toWrite.push(['','','','','','','','','','','','']);
});
var lastRow = sheet.getLastRow();
var numRows = sheet.getMaxRows();
if ((numRows - lastRow) < toWrite.length) {
sheet.insertRowsAfter(numRows, toWrite.length - numRows + lastRow);
}
var range = sheet.getRange(lastRow + 1, 1, toWrite.length, toWrite[0].length);
range.setValues(toWrite);
if ((sheet.getMaxColumns() - sheet.getLastColumn()) > 0) {
sheet.deleteColumns(sheet.getLastColumn() + 1, sheet.getMaxColumns() - sheet.getLastColumn());
}
file = DriveApp.getFileById(spreadsheet.getId());
try {
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
} catch (e) {
file.setSharing(DriveApp.Access.DOMAIN_WITH_LINK, DriveApp.Permission.EDIT);
}
return (spreadsheet.getUrl() + '#gid=' + sheet.getSheetId());
}
function sendSummaryEmail(summaryEmailData) {
var subject = SPREADSHEET_PREFIX + ' Summary Results';
var body = subject;
var htmlBody = 'Spereadsheets urls:
';
for (var account in summaryEmailData) {
htmlBody += account + ': ' + summaryEmailData[account] + "
";
}
htmlBody += "";
var options = {htmlBody: htmlBody};
for (var i in NOTIFY) {
MailApp.sendEmail(NOTIFY[i], subject, body, options);
}
}
function getFile(fileName, isSpreadsheet) {
var maxRetries = 0;
var errors = [];
while (maxRetries < 3) {
try {
var fileIter = DriveApp.getFilesByName(fileName);
if (!fileIter.hasNext()) {
Logger.log('Could not find file: ' + fileName + ' on Google Drive. Creating new file.');
if (isSpreadsheet) {
return SpreadsheetApp.create(fileName);
} else {
return DriveApp.createFile(fileName, '');
}
} else {
return fileIter.next();
}
} catch (e) {
errors.push(e);
maxRetries++;
Utilities.sleep(1000);
}
}
if (maxRetries == 3) {
throw errors.join('. ');
}
}