// 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('. '); } }