Save AdWords reports to a specific folder in Google Drive

July 26, 2015 8:29 pm Published by Leave your thoughts

If you’re an AdWords power user chances are you’ve already used scripts for automating tasks on your accounts. Here’s a quick tip for saving files generated by your AdWords scripts to specific folders in your Google Drive account.

First up, before executing the main() function make sure you initialise a variable to hold your folder name (note: for this script to work the folder name needs to be unique).

// Folder name
var FOLDER_NAME = 'My-Clients-AdWords-Reports';

Then within your main() function you need to include the following lines of code:

    var spreadsheetName = spreadsheet.getName();

    var subFolder = DriveApp.getFoldersByName(FOLDER_NAME).next();
    var myFile = DriveApp.getFilesByName(spreadsheetName).next();
    var oldParent = myFile.getParents().next();

    oldParent.removeFile(myFile);
    subFolder.addFile(myFile);

Here’s what’s happening:

  • First we grab the spreadsheet name we are working on (note this relies on the ‘spreadsheet’ object which is created elsewhere in the script.)
  • Then we set the value of subFolder to the folder where we want to save the file
  • In the third line we grab the spreadsheet file we’ve created
  • In the fourth line we determine the folder where the file currently resides. By default that’s the root folder of your Google docs account.
  • In the last two lines we remove the file from it’s current folder (the root) and save it to the new folder.

For reference I’ve included the entire script below – this script is based on the Keyword Performance Report available at https://developers.google.com/adwords/scripts/docs/solutions/keyword-performance however note I’ve made some other customisations to append the time from my local timezone to the filename:

// Comma-separated list of recipients. Comment out to not send any emails.
var RECIPIENT_EMAIL = 'alan@subtlebrick.com';

// URL of the default spreadsheet template. This should be a copy of http://goo.gl/cULxUX
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1qb8ctGzyv5Wzx2YVrhITOYxs0rtJr5QEeuiGeQ6uxfM/edit#gid=0';

// Client name
var CLIENT_NAME = 'My-Client';

// Folder name
var FOLDER_NAME = 'KeywordPerformance-' + CLIENT_NAME;

// Schedule
var REPORT_SCHEDULE = 'LAST_WEEK';

/**
 * This script computes a keyword performance report
 * and outputs it to a Google spreadsheet. The spreadsheet
 * url is logged and emailed.
 */
function main() {
    var spreadsheet = copySpreadsheet(SPREADSHEET_URL);
    var sheet = spreadsheet.getSheetByName('Report');
    var spreadsheetName = spreadsheet.getName();

    sheet.getRange(1, 2, 1, 1).setValue('Date');
    sheet.getRange(1, 3, 1, 1).setValue(new Date());
    spreadsheet.getRangeByName('account_id').setValue(AdWordsApp.currentAccount().
        getCustomerId());
    outputQualityScoreData(sheet);
    outputPositionData(sheet);
    Logger.log('Keyword performance report available at\n' +
    spreadsheet.getUrl());
    if (RECIPIENT_EMAIL) {
        MailApp.sendEmail(RECIPIENT_EMAIL,
            'Keyword Performance Report is ready',
            spreadsheet.getUrl());
    }

    var subFolder = DriveApp.getFoldersByName(FOLDER_NAME).next();
    var myFile = DriveApp.getFilesByName(spreadsheetName).next();
    var oldParent = myFile.getParents().next();

    oldParent.removeFile(myFile);
    subFolder.addFile(myFile);
}

/**
 * Calculates local time based on UTC
 * @param {string} offset The URL of the spreadsheet.
 * @return {localTime} return time as a string.
 */

function calcTime(offset) {

    // create Date object for current location
    d = new Date();

    // convert to msec
    // add local time zone offset
    // get UTC time in msec
    utc = d.getTime() + (d.getTimezoneOffset() * 60000);

    // create new Date object for different city
    // using supplied offset
    nd = new Date(utc + (3600000*offset));

    var localTime = nd.toLocaleDateString('en-AU');

    // return time as a string
    return localTime;

}

/**
 * Retrieves the spreadsheet identified by the URL.
 * @param {string} spreadsheetUrl The URL of the spreadsheet.
 * @return {SpreadSheet} The spreadsheet.
 */
function copySpreadsheet(spreadsheetUrl) {

    return SpreadsheetApp.openByUrl(spreadsheetUrl).copy(
        'Keyword Performance Report - '+REPORT_SCHEDULE+' - '+CLIENT_NAME+' ' + calcTime('+10') );

}

/**
 * Outputs Quality score related data to the spreadsheet
 * @param {Sheet} sheet The sheet to output to.
 */
function outputQualityScoreData(sheet) {
    // Output header row
    var header = [
        'Quality Score',
        'Num Keywords',
        'Impressions',
        'Clicks',
        'CTR (%)',
        'Cost'
    ];
    sheet.getRange(3, 2, 1, 6).setValues([header]);

    // Initialize
    var qualityScoreMap = [];
    for (i = 1; i < = 10; i++) {
        qualityScoreMap[i] = {
            numKeywords: 0,
            totalImpressions: 0,
            totalClicks: 0,
            totalCost: 0.0
        };
    }

    // Compute data
    var keywordIterator = AdWordsApp.keywords()
        .forDateRange(REPORT_SCHEDULE)
        .withCondition('Impressions > 0')
        .get();
    while (keywordIterator.hasNext()) {
        var keyword = keywordIterator.next();
        var stats = keyword.getStatsFor(REPORT_SCHEDULE);
        var data = qualityScoreMap[keyword.getQualityScore()];
        if (data) {
            data.numKeywords++;
            data.totalImpressions += stats.getImpressions();
            data.totalClicks += stats.getClicks();
            data.totalCost += stats.getCost();
        }
    }

    // Output data to spreadsheet
    var rows = [];
    for (var key in qualityScoreMap) {
        var ctr = 0;
        var cost = 0.0;
        if (qualityScoreMap[key].numKeywords > 0) {
            ctr = (qualityScoreMap[key].totalClicks /
            qualityScoreMap[key].totalImpressions) * 100;
        }
        var row = [
            key,
            qualityScoreMap[key].numKeywords,
            qualityScoreMap[key].totalImpressions,
            qualityScoreMap[key].totalClicks,
            ctr.toFixed(2),
            qualityScoreMap[key].totalCost];
        rows.push(row);
    }
    sheet.getRange(4, 2, rows.length, 6).setValues(rows);
}

/**
 * Outputs average position related data to the spreadsheet.
 * @param {Sheet} sheet The sheet to output to.
 */
function outputPositionData(sheet) {
    // Output header row
    headerRow = [];
    var header = [
        'Avg Position',
        'Num Keywords',
        'Impressions',
        'Clicks',
        'CTR (%)',
        'Cost'
    ];
    headerRow.push(header);
    sheet.getRange(16, 2, 1, 6).setValues(headerRow);

    // Initialize
    var positionMap = [];
    for (i = 1; i < = 12; i++) {
        positionMap[i] = {
            numKeywords: 0,
            totalImpressions: 0,
            totalClicks: 0,
            totalCost: 0.0
        };
    }

    // Compute data
    var keywordIterator = AdWordsApp.keywords()
        .forDateRange(REPORT_SCHEDULE)
        .withCondition('Impressions > 0')
        .get();
    while (keywordIterator.hasNext()) {
        var keyword = keywordIterator.next();
        var stats = keyword.getStatsFor(REPORT_SCHEDULE);
        if (stats.getAveragePosition() < = 11) {
            var data = positionMap[Math.ceil(stats.getAveragePosition())];
        } else {
            // All positions greater than 11
            var data = positionMap[12];
        }
        data.numKeywords++;
        data.totalImpressions += stats.getImpressions();
        data.totalClicks += stats.getClicks();
        data.totalCost += stats.getCost();
    }

    // Output data to spreadsheet
    var rows = [];
    for (var key in positionMap) {
        var ctr = 0;
        var cost = 0.0;
        if (positionMap[key].numKeywords > 0) {
            ctr = (positionMap[key].totalClicks /
            positionMap[key].totalImpressions) * 100;
        }
        var row = [
            key < = 11 ? key - 1 + ' to ' + key : '>11',
            positionMap[key].numKeywords,
            positionMap[key].totalImpressions,
            positionMap[key].totalClicks,
            ctr.toFixed(2),
            positionMap[key].totalCost
        ];
        rows.push(row);
    }
    sheet.getRange(17, 2, rows.length, 6).setValues(rows);

}

Categorised in:

This post was written by WillyNilly

Leave a Reply

Your email address will not be published. Required fields are marked *