Метод альфа-бета кампаний в Google Ads

/*******************************
 * Find the Winners and Losers from the
 * Search Query Performance report for
 * Alpha Beta Campaign Strategy.
 *******************************/

// The list of email addresses to send the report to.
// We will also give editor access to the Google Spreadsheet.
var NOTIFY = ['your_email@example.com'];

// The name of the report in your Google drive
var SPREADSHEET_PREFIX = 'AlphaBetaResults - ';

// This string is used to identify your Beta campaigns.
// Make sure this string is found in all your Beta campaign names
var BETA_SUFFIX = '| Beta';

// This is the timeframe used to calculate the statistics.
var REPORT_TIMEFRAME = 'YESTERDAY';

// This is the list of columns that will be displayed in the 
// Winners and Losers report. Make sure that any columns used in
// the Criteria below are listed here as well.
var REPORT_COLUMNS = [
    'CampaignName', 'AdGroupName',
    'KeywordTextMatchingQuery', 'MatchType', 'Query',
    'Impressions', 'Clicks', 'Cost', 'ConvertedClicks'];

// This is the set of criteria that will be used to
// determine the winners. Any of the numerical columns
// should work here. These are just samples, find what
// works for you.
var WINNING_CRITERIA = [
    'Clicks > 100',

// If using money columns, be sure to represent them in micros
// https://developers.google.com/adwords/api/docs/guides/reporting-concepts#money'Cost < 1000000',   'ConvertedClicks >= 5'
];
var LOSING_CRITERIA = [
    'Impressions > 100',
    'Clicks < 5',
    'ConvertedClicks = 0'];

function main() {
    generateWinnersLosersReport();
}

function generateWinnersLosersReport() {

// This string will be appended to the report to create a unique   
// name each day. If you run this intra-day, you can add hours (HH) to   
// to the format string. 
    var dateString = Utilities.formatDate(newDate(),
        AdWordsApp.currentAccount().getTimeZone(),
        'yyyy-MM-dd HH');
    var crits = [WINNING_CRITERIA, LOSING_CRITERIA];
    var sheets = [
        getSheet(SPREADSHEET_PREFIX + dateString, 'Winners'),
        getSheet(SPREADSHEET_PREFIX + dateString, 'Losers')
    ];

// Grab all the results first. That way we can remove the winners and losers   
// to keep the ones with "not enough data".
    var allResults = pullSearchQueryPerfReport(REPORT_TIMEFRAME, REPORT_COLUMNS, []);
    for (var i in crits) {
        var results = pullSearchQueryPerfReport(
            REPORT_TIMEFRAME,
            REPORT_COLUMNS,
            crits[i]
        );
        writeResultsToSheet(results, sheets[i]);
        removeFromAllResults(allResults, results);
    }

//Handle the 'Not Enough Data' case
    var notEnoughDataSheet = getSheet(SPREADSHEET_PREFIX + dateString, 'Not Enough Data');
    writeResultsToSheet(allResults, notEnoughDataSheet);
    sendEmail(sheets, dateString);
}


// This function pulls the search query report and 
// formats it to be easy to insert into a Google Sheet.
function pullSearchQueryPerfReport(timeframe, columns, crit) {
    var reportName = 'SEARCH_QUERY_PERFORMANCE_REPORT';
    var reportQueryTemplate = 'SELECT %s FROM %s WHERE %s DURING %s';

// Add a criteria so that we only look at data from Beta campaigns.
    crit.push("CampaignName CONTAINS '" + BETA_SUFFIX + "'");
    var reportQuery = Utilities.formatString(reportQueryTemplate,
        columns.join(','),
        reportName,
        crit.join(' AND '),
        timeframe);
    var reportIter = AdWordsApp.report(reportQuery, {
        includeZeroImpressions: true
    }).rows();
    var results = [];
    while (reportIter.hasNext()) {
        var row = reportIter.next();
        var rowArray = [];
        for (var i in columns) {
            rowArray.push(row[columns[i]]);
        }
        results.push(rowArray);
    }
    return results;
}


// This function writes the results to a given spreadsheet
function writeResultsToSheet(results, sheet) {
    if (results.length > 0) {
        var keywordIndex = REPORT_COLUMNS.indexOf('KeywordTextMatchingQuery');
        sheet.appendRow(REPORT_COLUMNS)
        for (var i in results) {

// if the keyword starts with a plus sign,
// we need to add an apostrophe so google sheets
// doesn't get annoyed.if(results[i][keywordIndex].indexOf('+') ===0) {
            results[i][keywordIndex] = "'" + results[i][keywordIndex];
        }
        sheet.appendRow(results[i]);
    }
}


// This function removes the results in toRemove from the results
// in the allResults array. Used to remove winners and losers so 
// that all we have left are the "not enough data" queries.
function removeFromAllResults(allResults, toRemove) {
    var allResultsRowHash = {};
    for (var i in allResults) {
        var rowHash = Utilities.base64Encode(JSON.stringify(allResults[i]));
        allResultsRowHash[rowHash] = 1;
    }
    for (var i in toRemove) {
        var rowHash = Utilities.base64Encode(JSON.stringify(toRemove[i]));
        if (allResultsRowHash[rowHash]) {
            allResults.splice(i, 1);
        }
    }
}


// Sends the spreadsheet in an email to the people in the
// NOTIFY list. 
function sendEmail(sheets, dateString) {
    var subjectLineTemplate = 'Alpha Beta Results - %s - %s';
    var subjectLine = Utilities.formatString(subjectLineTemplate,
        AdWordsApp.currentAccount().getName(),
        dateString);
    var bodyTemplate = 'Here is a spreadsheet with the ' + 'winners and losers for account: %s: \n\n %s';
    var body = Utilities.formatString(bodyTemplate,
        AdWordsApp.currentAccount().getName(),
        sheets[0].getParent().getUrl());
    for (var i in NOTIFY) {
        MailApp.sendEmail(NOTIFY[i], subjectLine, body);
    }
}


// Helper function to get or create a given sheet in 
// a spreadsheet. When creating a new spreadsheet, it also
// adds the emails in the NOTIFY list as editors.
function getSheet(spreadsheetName, sheetName) {
    var fileIter = DriveApp.getFilesByName(spreadsheetName);
    if (fileIter.hasNext()) {
        var ss = SpreadsheetApp.openByUrl(fileIter.next().getUrl());
        var sheets = ss.getSheets();
        for (var i in sheets) {
            if (sheets[i].getName() == sheetName) {
                sheets[i].clear();
                return sheets[i];
            }
        }
        return ss.insertSheet(sheetName);
    } else {
        var ss = SpreadsheetApp.create(spreadsheetName);
        ss.addEditors(NOTIFY);
        var sheet = ss.insertSheet(sheetName);
        ss.deleteSheet(ss.getSheetByName('Sheet1'));
        return sheet;
    }
}


// Replace the main from above with this one.
// We create the report from before but then
// also add the losers to the beta group.
function main() {
    generateWinnersLosersReport();
    addLosersToBetaGroup();
}


// This code pulls the losers from from the 
// Beta campaign using the same criteria as before.
function addLosersToBetaGroup() {
    var loserResults = pullSearchQueryPerfReport(
        REPORT_TIMEFRAME,
        REPORT_COLUMNS,
        LOSING_CRITERIA
    );
    if (loserResults.length > 0) {
        var campKwHash = transformSearchQueryResults(loserResults);
        var campaignNames = Object.keys(campKwHash);

// Find all the Beta campaigns
        var campIter = AdWordsApp.campaigns().withCondition("Name CONTAINS '" + BETA_SUFFIX + "'").get();
        while (campIter.hasNext()) {
            var camp = campIter.next();
            var campName = camp.getName();

// If the campaign is in the list of Beta campaigns we need
// to add negative toif(campaignNames.indexOf(camp.getName()) >=0) {
            var negativesList = campKwHash[campName];
            for (var i in negativesList) {

// Add the negatives.
                camp.createNegativeKeyword(negativesList[i]);
            }
        }
    }
}


// This function transforms the data from the Search Query report
// into a map of { campaignName : [ "[query1]","[query2]", ... ] }
function transformSearchQueryResults(results) {
    var campKwHash = {};
    var campColumn = REPORT_COLUMNS.indexOf('CampaignName');
    var queryColumn = REPORT_COLUMNS.indexOf('Query');
    for (var i in loserResults) {
        var row = loserResults[i];
        var campName = row[campColumn];
        var query = row[queryColumn];
        if (!campKwHash[campName]) {
            campKwHash[campName] = [];
        }
        campKwHash[campName].push('[' + query + ']');
    }
    return campKwHash;
}

/*******************************
 * Automatically add any new keywords in your
 * Alpha campaigns as exact match negatives in
 * the corresponding Beta campaign.
 *******************************/
// Just as before, these strings will be
// used to identify your Alpha and Beta campaigns.
// This script assumes that your Alpha campaigns are
// named "Campaign Name | Alpha" with the corresponding
// Beta campaign named "Campaign Name | Beta"
var ALPHA_SUFFIX = '| Alpha';
var BETA_SUFFIX = '| Beta';

function main() {
    var results = getKeywordReport();
    var toUpdate = {};
    for (var key in results) {
        var campData = results[key];
        for (var i in campData.alpha.keywords) {
            var kw = campData.alpha.keywords[i];
            if (campData.beta.negatives.indexOf(kw) == -1) {
                if (!toUpdate[campData.beta.campName]) {
                    toUpdate[campData.beta.campName] = [];
                }
                toUpdate[campData.beta.campName].push(kw);
            }
        }
    }
    var campIter = AdWordsApp.campaigns().withCondition("Name CONTAINS '" + BETA_SUFFIX + "'").get();
    while (campIter.hasNext()) {
        var betaCamp = campIter.next();
        var betaCampName = betaCamp.getName();
        if (toUpdate[betaCampName]) {
            var negativesToAdd = toUpdate[betaCampName];
            for (var i in negativesToAdd) {
                betaCamp.createNegativeKeyword('[' + negativesToAdd[i] + ']');
            }
        }
    }
}

// This function uses the Keywords report and
// the campaign negatives report to build a list
// of the keywords and negatives in each campaign.
function getKeywordReport() {
    var columns = ['CampaignName', 'Criteria', 'IsNegative'];
    var reportQueryTemplate = "SELECT %s FROM %s " + "WHERE IsNegative IN [true,false] " + "AND CampaignName CONTAINS '%s' ";
    var alphaReportQuery = Utilities.formatString(reportQueryTemplate,
        columns.join(','),
        'KEYWORDS_PERFORMANCE_REPORT',
        ALPHA_SUFFIX);
    var betaReportQuery = Utilities.formatString(reportQueryTemplate,
        columns.join(','),
        'CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT',
        BETA_SUFFIX);
    var queries = [alphaReportQuery, betaReportQuery];
    var results = {};
    for (var i in queries) {
        var reportIter = AdWordsApp.report(queries[i], {
            includeZeroImpressions: true
        }).rows();
        while (reportIter.hasNext()) {
            var row = reportIter.next();
            if (row.CampaignName.indexOf(ALPHA_SUFFIX) == -1 &&
                row.CampaignName.indexOf(BETA_SUFFIX) == -1) {
                continue;
            }
            var campType = (row.CampaignName.indexOf(ALPHA_SUFFIX) >= 0) ? 'alpha' : 'beta';

            var cleanCampName = row.CampaignName.split(ALPHA_SUFFIX)[0];
            cleanCampName = cleanCampName.split(BETA_SUFFIX)[0];

            if (!results[cleanCampName]) {
                results[cleanCampName] = {
                    alpha: {keywords: [], negatives: [], campName: ''},
                    beta: {keywords: [], negatives: [], campName: ''}
                };
            }
            results[cleanCampName][campType].campName = row.CampaignName;
            if (row.IsNegative == 'true') {
                results[cleanCampName][campType].negatives.push(row.Criteria);
            } else {
                results[cleanCampName][campType].keywords.push(row.Criteria);
            }
        }
    }
    return results;
}