Keyword Performance Report - Manager Account

This script extends Keyword Performance Report to work for multiple accounts. It generates a collection of Google Spreadsheets with a number of distribution charts related to the quality score and average position of ads triggered on Google Search.

A new set of Keyword Performance reports gets created whenever the script executes. You can access all of these reports in Google Drive. Optionally, the script can also email the report to one or more recipients.

Scheduling

The script uses last week's statistics to generate the report. Schedule it for Weekly, on Mondays.

How it works

The script starts off by creating a folder for today's date under a user-defined root folder in Google Drive. It then creates a copy of a template spreadsheet, with all graphs pre-configured, for each account it processes. The script then populates the data values in the Report sheet, and constructs the graphs in the other sheets automatically.

You can set the list of accounts to be processed, the root folder under which the script creates the reports, as well as the list of email addresses to be notified once the script completes.

Setup

• Create a new script with the source code below.
• Update the ACCOUNTS in case you need to run report only on a subset of accounts under your manager account.
• Update ROOT_FOLDER_NAME with the name of the Google Drive folder under which the reports should be saved. Also ensure that this folder exists in Google Drive, and that the script has write permissions to this folder.
• Update RECIPIENT_EMAIL to specify your email preference.
• Schedule it to run Weekly, on Mondays.

Source code

// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name MCC Keyword Performance Report
 *
 * @overview The MCC Keyword Performance Report script generates a Google
 *     Spreadsheet that contains keyword performance stats like quality score
 *     and impression share of ads, as well as several distribution charts.
 *     An indvidual report is generated for each Advertiser account under the
 *     MCC account. See
 * https://developers.google.com/google-ads/scripts/docs/solutions/adsmanagerapp-keyword-performance
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.0
 *
 * @changelog
 *  - version 2.0
 *   - Updated to use new Google Ads scripts features.
 *  - version 1.1.4
 *   - Fixed issue of calling getRangeByName on spreadsheet vs sheet.
 * - version 1.1.3
 *   - Refactored to improve readability. Added documentation.
 * - version 1.1.2
 *   - Added validation of user settings.
 * - version 1.1.1
 *   - Improvements to time zone handling.
 * - version 1.1
 *   - Make the file cloning process more robust.
 * - version 1.0
 *   - Released initial version.
 */

/**
 * Name of the Google Drive folder under which the generated reports are stored.
 */
const ROOT_FOLDER_NAME = 'ACCOUNT_PERFORMANCE';

/**
 * List of accounts for which keyword performance report is run. To run for all
 * accounts under the MCC, leave the array empty. E.g.
 *
 * var ACCOUNTS = ['925-591-3280', '918-501-8835'];
 */
const ACCOUNTS = [];

/**
 * URL of the default spreadsheet template. This should be a copy of
 * https://goo.gl/q4DFrt
 */
const SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

/**
 * Comma-separated list of recipients. Comment out to not send any emails.
 */
const RECIPIENT_EMAILS = ['email@example.com'];

/**
 * The size of the quality score map to output.
 * DO NOT change this value.
 */
const QUALITY_SCORE_MAP_SIZE = 10;

/**
 * The size of the position map to output.
 * DO NOT change this value.
 */
const POSITION_MAP_SIZE = 10;

function main() {
  validateEmail(RECIPIENT_EMAILS);
  console.log(`Opening Google Drive folder ${ROOT_FOLDER_NAME}.`);
  const rootFolder = DriveApp.getFoldersByName(ROOT_FOLDER_NAME).next();
  const formattedDate = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd');

  const todayFolder = rootFolder.createFolder(formattedDate);
  console.log(
      `Created a new folder named '${todayFolder.getName()}' ` +
      `under root folder '${rootFolder.getName()}'. ` +
      `All reports will be generated under this folder.`);

  let accountSelector = AdsManagerApp.accounts();
  if (ACCOUNTS.length > 0) {
    accountSelector = accountSelector.withIds(ACCOUNTS);
  }

  const accountIterator = accountSelector.get();

  for (const account of accountIterator) {
    processAccount(account, todayFolder);
    console.log(`Done processing ${account.getCustomerId()}`);
  }
  if (RECIPIENT_EMAILS.length) {
    MailApp.sendEmail(
        RECIPIENT_EMAILS.join(','), 'Keyword Performance Reports are ready',
        todayFolder.getUrl());
  }
}

/**
 * Process an account.
 *
 * @param {Object} account The acount to be processed.
 * @param {string} rootFolder The root folder under which reports are saved.
 */
function processAccount(account, rootFolder) {
  AdsManagerApp.select(account);

  const spreadsheet =
      copySpreadsheet(SPREADSHEET_URL, rootFolder, account.getCustomerId());
  spreadsheet.getRangeByName('date_label').setValue('Date');
  spreadsheet.getRangeByName('date_value').setValue(new Date());
  spreadsheet.getRangeByName('account_id')
      .setValue(AdsApp.currentAccount().getCustomerId());
  outputQualityScoreData(spreadsheet);
  outputImpressionShareData(spreadsheet);
  console.log(
      `Keyword performance report available at\n${spreadsheet.getUrl()}`);
}

/**
 * Makes a copy of the template spreadsheet for a customer.
 *
 * @param {string} spreadsheetUrl The URL of the spreadsheet.
 * @param {string} rootFolder The root folder under which reports are saved.
 * @param {string} customerId The customer id.
 * @return {SpreadSheet} The spreadsheet.
 */
function copySpreadsheet(spreadsheetUrl, rootFolder, customerId) {
  const fileName = `Keyword Performance Report - ${customerId}`;

  const copy = validateAndGetSpreadsheet(spreadsheetUrl).copy(fileName);

  // Make sure the spreadsheet is using the account's timezone.
  copy.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  const newFile = DriveApp.getFileById(copy.getId());

  const oldParent = newFile.getParents().next();
  oldParent.removeFile(newFile);
  rootFolder.addFile(newFile);

  console.log(
      `Generated spreadsheet - ${newFile.getUrl()} ` +
      `for customer ID: ${customerId}.`);
  return SpreadsheetApp.openByUrl(newFile.getUrl());
}

/**
 * Outputs Quality score related data to the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet to output to.
 */
function outputQualityScoreData(spreadsheet) {
  // Output header row
  const header = [
    'Quality Score', 'Num Keywords', 'Impressions', 'Clicks', 'CTR (%)', 'Cost'
  ];
  spreadsheet.getRangeByName('quality_score_headings').setValues([header]);

  // Initialize
  const qualityScoreMap = getEmptyStatMap(QUALITY_SCORE_MAP_SIZE);

  // Compute data
  computeQualityData(
      getLastWeekKeywordsWithPositiveImpressions(), qualityScoreMap);

  // Output data to spreadsheet
  const rows = [];
  for (const key in qualityScoreMap) {
    const ctr = calculateCtr(qualityScoreMap[key]);
    const row = [
      key, qualityScoreMap[key].numKeywords,
      qualityScoreMap[key].totalImpressions, qualityScoreMap[key].totalClicks,
      ctr.toFixed(2), qualityScoreMap[key].totalCost
    ];
    rows.push(row);
  }
  spreadsheet.getRangeByName('quality_score_body').setValues(rows);
}

/**
 * Outputs impression share related data to the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet to output to.
 */
function outputImpressionShareData(spreadsheet) {
  // Output header row
  headerRow = [];
  const header = [
    'Impression Share', 'Num Keywords', 'Impressions', 'Clicks', 'CTR (%)',
    'Cost'];
  headerRow.push(header);
  spreadsheet.getRangeByName('position_headings').setValues(headerRow);

  // Initialize
  const positionMap = getEmptyStatMap(POSITION_MAP_SIZE);

  // Compute data
  computeImpressionShareData(
      getLastWeekKeywordsWithPositiveImpressions(), positionMap);

  // Output data to spreadsheet
  const rows = [];
  for (let key in positionMap) {
    const ctr = calculateCtr(positionMap[key]);
    const row = [
      // Divide impression share into slabs of 10%
      '(' + (key-1) * 10 + '-' + key * 10 + ')%',
      positionMap[key].numKeywords, positionMap[key].totalImpressions,
      positionMap[key].totalClicks, ctr.toFixed(2), positionMap[key].totalCost
    ];
    rows.push(row);
  }
  spreadsheet.getRangeByName('position_body').setValues(rows);
}

/**
 * Uses the given keyword iterator and populates the given position map.
 * @param {Iterator} keywordIterator - the keywords to use for getting scores.
 * @param {array} positionMap - the map to fill with keyword data.
 */
function computeImpressionShareData(keywordIterator, positionMap) {
  for (const keyword of keywordIterator) {
    let index =
        Math.ceil(keyword.metrics.searchAbsoluteTopImpressionShare * 10);
    const data = positionMap[index];
    if (data) {
      data.numKeywords++;
      data.totalImpressions += parseFloat(keyword.metrics.impressions);
      data.totalClicks += parseFloat(keyword.metrics.clicks);
      data.totalCost += parseFloat(keyword.metrics.costMicros)/1000000;
    }
  }
}

/**
 * Gets an empty stat map.
 * @param {number} size - the number of entries in the stat map.
 * @return {array} the empty stat map.
 */
function getEmptyStatMap(size) {
  const qualityScoreMap = [];
  for (i = 1; i <= size; i++) {
    qualityScoreMap[i] =
        {numKeywords: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
  }
  return qualityScoreMap;
}

/**
 * Calculates the click through rate given an entry from a map.
 * @param {object} mapEntry - an entry from the  map
 * @return {number} the click through rate
 */
function calculateCtr(mapEntry) {
  let ctr = 0;
  if (mapEntry.numKeywords > 0) {
    ctr = (mapEntry.totalClicks / mapEntry.totalImpressions) * 100;
  }
  return ctr;
}

/**
 * Gets an iterator for keywords that had impressions last week.
 * @return {Iterator} an iterator of the keywords
 */
function getLastWeekKeywordsWithPositiveImpressions() {
  return AdsApp.search(`SELECT ad_group_criterion.keyword.text, ` +
                       `ad_group_criterion.quality_info.quality_score, ` +
                       `metrics.video_views, ` +
                       `metrics.ctr, ` +
                       `metrics.conversions, ` +
                       `metrics.average_cpc, ` +
                       `metrics.clicks, ` +
                       `metrics.cost_micros, ` +
                       `metrics.search_absolute_top_impression_share, ` +
                       `metrics.search_top_impression_share, ` +
                       `metrics.average_cpm, ` +
                       `metrics.impressions ` +
                       `FROM keyword_view ` +
                       `WHERE metrics.impressions > 0 AND ` +
                       `segments.date DURING LAST_7_DAYS`);
}

/**
 * Uses the given keyword iterator and populates the given quality score map.
 * @param {Iterator} keywordIterator - the keywords to use for getting scores.
 * @param {array} qualityScoreMap - the score map to fill with keyword data.
 */
function computeQualityData(keywordIterator, qualityScoreMap) {
  for (const keyword of keywordIterator) {
    if (keyword.adGroupCriterion.qualityInfo) {
      const data =
            qualityScoreMap[keyword.adGroupCriterion.qualityInfo.qualityScore];
      if (data) {
        data.numKeywords++;
        data.totalImpressions += parseFloat(keyword.metrics.impressions);
        data.totalClicks += parseFloat(keyword.metrics.clicks);
        data.totalCost += parseFloat(keyword.metrics.costMicros)/1000000;
      }
    }
  }
}

/**
 * Validates the provided email and throws a descriptive error if the user
 * has not changed the email from the default fake address.
 *
 * @param {string} emails The email address.
 * @throws {Error} If the email is the default fake address.
 */
function validateEmail(emails) {
  for (const email of emails) {
    if (email == 'email@example.com') {
      throw new Error('Please use a valid email address.');
    }
  }
}

/**
 * Validates the provided spreadsheet URL
 * to make sure that it's set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
    throw new Error(
        'Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);
}