closeVariants.js

/*
*
* Sortera Close Variants med Levenshtein Distance
* 
* Levenshtein Distance i korthet = antal tecken du 
* måste ändra för att kunna skapa två lika strängar.
*
* För mer info: http://en.wikipedia.org/wiki/Levenshtein_distance
* 
*/


// Settings, ändra så det passar.
var impressionThreshold = 10;
var date = "LAST_7_DAYS";


function main() {
    var searchTerms = getSearchTermReport();
    var searchTermsWithLevDist = searchTermLevDist(searchTerms);
    createSpreadSheet(searchTermsWithLevDist);    
    Logger.log('Now go grab those bad close variants!');
}

function searchTermLevDist(searchTerms) {
    for(var i = 0; i < searchTerms.length; i++) {
        var q = searchTerms[i].query;
        
        var k = searchTerms[i].keyword;
            k = k.replace(/\+/g,"") // Tar bort + vid bred modifierad matchning
                 .toLowerCase(); // Query är alltid lowercase

        searchTerms[i].levDist = getLevenshteinDistance(q,k);
    }

    return searchTerms;
}

function getLevenshteinDistance(a, b) {
    if(a.length === 0) return b.length; 
    if(b.length === 0) return a.length; 
 
    var matrix = [];
 
    var i;
    for(i = 0; i <= b.length; i++){
        matrix[i] = [i];
    }
 
    var j;
    for(j = 0; j <= a.length; j++){
        matrix[0][j] = j;
    }
 
    for(i = 1; i <= b.length; i++){
        for(j = 1; j <= a.length; j++){
            if(b.charAt(i-1) == a.charAt(j-1)){
                matrix[i][j] = matrix[i-1][j-1];
            } else {
                matrix[i][j] = Math.min(matrix[i-1][j-1] + 1,
                                    Math.min(matrix[i][j-1] + 1,
                                        matrix[i-1][j] + 1));
            }
        }
    }
    return matrix[b.length][a.length];
}


function createSpreadSheet(searchTerms) {
    var now = new Date();
    var spreadSheetName = "Close Variant Report - " + Utilities.formatDate(now, "PST", "yyyyMMdd");

    var spreadsheet = SpreadsheetApp.create(spreadSheetName);
    var sheet = spreadsheet.getActiveSheet();
    
    sheet.getRange("A1").setValue("Keyword");
    sheet.getRange("B1").setValue("Query");
    sheet.getRange("C1").setValue("CTR");
    sheet.getRange("D1").setValue("Campaign");
    sheet.getRange("E1").setValue("AdGroup");
    sheet.getRange("F1").setValue("Levenshtein Distance");

    spreadsheetRow = 2;

    for(var i = 0; i < searchTerms.length; i++) {
        sheet.getRange("A" + spreadsheetRow).setValue(searchTerms[i].keyword);
        sheet.getRange("B" + spreadsheetRow).setValue(searchTerms[i].query);
        sheet.getRange("C" + spreadsheetRow).setValue(searchTerms[i].ctr);
        sheet.getRange("D" + spreadsheetRow).setValue(searchTerms[i].campaignName);
        sheet.getRange("E" + spreadsheetRow).setValue(searchTerms[i].adGroupName);
        sheet.getRange("F" + spreadsheetRow).setValue(searchTerms[i].levDist);

        spreadsheetRow += 1;
    }

    Logger.log("Report done:");
    Logger.log("https://docs.google.com/spreadsheet/ccc?key=" + spreadsheet.getId());
}

function getSearchTermReport() {
    var cols = ['CampaignId','CampaignName',
                'AdGroupId','AdGroupName',
                'KeywordId', 'KeywordTextMatchingQuery',
                'Query', 'Ctr', 'Clicks'];
    var report = 'SEARCH_QUERY_PERFORMANCE_REPORT';
    var query = ['select',cols.join(','),'from',report,
                 'where Impressions > ', impressionThreshold,
                 'and MatchTypeWithVariant = NEAR_EXACT',
                 'DURING', date].join(' ');
    var result = [];
    var report = AdWordsApp.report(query).rows();



    while(report.hasNext()) {
        var row = report.next();
           result.push({ 
            'campignId': row.CampaignId,
            'campaignName' : row.CampaignName,
            'adGroupId': row.AdGroupId,
            'adGroupName': row.AdGroupName,
            'keywordId': row.KeywordId,
            'keyword': row.KeywordTextMatchingQuery,
            'query': row.Query,
            'ctr': row.Ctr,
            'clicks': row.Clicks,
            'levDist': 0
        });
    }
    return result;
}