Store Account Level Quality Score in a Google Spreadsheet

EDIT 2013-04-22: Check out an updated version of this script that stores Account, Campaign, and AdGroup level quality score.

EDIT 2013-03-18: I added DESC to the .orderBy() clause so that it orders the impressions from highest to least.

I was reading a post on SearchEngineLand.com the other day about storing and analyzing your account level quality score.

The Author of the article provided a script, but I figured I could come up with my own version pretty easily.  In the following script, I am using the same logic to calculate the account level quality score but I am looking at all campaigns and all keywords with an impression in the last 30 days.

Depending on your account size, this might be too much data to analyze in the 30 minute limit imposed by AdWords, but ordering by impressions should get you the top 50000 (again, another AdWords limit) keywords with impressions.

You can find the spreadsheet I am storing data in here: https://docs.google.com/spreadsheet/ccc?key=0Aotb6eheEOpodGNKMW1UWkZKekc5NWxkR3Zra3lzWVE

Your best bet is to copy that sheet, erase my data (except the headers) and copy the new spreadsheet url into the script. If you have multiple accounts that this will run in, you can store the data in the same spreadsheet but add additional tabs for the account name. Then update the ACCOUNT_NAME variable and you should be all set.

Thanks,
Russ

/***************************************
* Store Account Level Quality Score in Google Spreadsheet.
* Version 1.1
* ChangeLog v1.1
*  - Changed ACCOUNT_NAME to SHEET_NAME and updated the default value.
*  - Removed getSpreadsheet function
*
* Created By: Russ Savage
* Based on script originally found at: http://goo.gl/rTHbF
* FreeAdWordsScripts.com
*********************************/
function main() {
  var SPREADSHEET_URL = "Your Spreadsheet Url Goes Here";
  var SHEET_NAME = 'Sheet1';
  var today = new Date();
  var date_str = [today.getFullYear(),(today.getMonth() + 1),today.getDate()].join("-");
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var qs_sheet = spreadsheet.getSheetByName(SHEET_NAME);
  
  var kw_iter = AdWordsApp.keywords()
    .withCondition("Status = ENABLED")
    .forDateRange("LAST_30_DAYS")
    .withCondition("Impressions > 0")
    .orderBy("Impressions DESC")
    .withLimit(50000)
    .get();

  var tot_imps_weighted_qs = 0;
  var tot_imps = 0;
  
  while(kw_iter.hasNext()) {
    var kw = kw_iter.next();
    var kw_stats = kw.getStatsFor("LAST_30_DAYS");
    var imps = kw_stats.getImpressions();
    var qs = kw.getQualityScore();
    tot_imps_weighted_qs += (qs * imps);
    tot_imps += imps;
  }
    
  var acct_qs = tot_imps_weighted_qs / tot_imps;
  
  qs_sheet.appendRow([date_str,acct_qs]);
}

Store Account, Campaign, AdGroup, and Keyword Level Quality Score

UPDATE: 2014-02-15 - I updated the script to v2.0 which speeds the script up dramatically, includes the ability to store data into a CSV and use whatever date range you like for the stats.

I've had a lot of good feedback on my previous script: Store Account Level Quality Score. To that end, I've been asked a few times about storing Campaign and/or AdGroup level quality scores as well so I figured it would be a good time for an update.

Below is a script that will attempt to store Account, Campaign, and AdGroup level quality scores for the top 50000 keywords in your account. It will store the results in a google spreadsheet. In order for this to work, you will need to set up a new google spreadsheet. with three sheets named Account, Campaign, and AdGroup. You can simply make a copy of my spreadsheet found here (File > Make a copy...) : Account, Campaign, AdGroup Quality Score Spreadsheet (No longer needed as of v1.1)

Thanks,
Russ

Pause All Keywords With No Impressions

Let's start with a very simple script. This one will find all of the keywords in your account that has never had an impression, and pause (or delete if you see the comment below) that keyword so that it will not negatively impact your quality score. According to Google, the longer something sits in your account and stagnates, the greater the impact to your quality score. As a reader pointed out, the fourth bullet here seems to contradict this statement. This would be a great script to schedule every few months to make sure you are trimming all the dead weight from your accounts.

Thanks,
Russ

/*********************************************
* Pause Keywords With No Impressions All Time
* Version 1.1
* Changelog v1.1
*   - Updated for speed and added comments 
* Created By: Russ Savage
* FreeAdWordsScripts.com
**********************************************/
var TO_NOTIFY = "your_email@domain.com";
function main() {
  // Let's start by getting all of the keywords with no impressions
  var kwIter = AdWordsApp.keywords()
    .withCondition("Impressions = 0") // could be "Clicks = 0" also
    .forDateRange("ALL_TIME") // could use a specific date range like "20130101","20131231"
    .withCondition("Status = ENABLED")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .get();
 
  // It is much faster to store all the keywords you want to process
  // and then make the changes all at once. This takes advantage
  // of the batch processing behind the scenes.
  var toPause = [];
  while (kwIter.hasNext()) {
    var kw = kwIter.next();
    toPause.push(kw);
    // This is to make sure you see things during the preview
    // When you run it for real, you can remove this clause to
    // increase speed.
    if(AdWordsApp.getExecutionInfo().isPreview() &&
       AdWordsApp.getExecutionInfo().getRemainingTime() < 10) {
      break;
    }
  }
  
  // Now go through each one and pause them.
  for(var i in toPause) {
    toPause[i].pause();
    //Or you could use toPause[i].remove(); to delete the keyword altogether
  }
  
  // Sent an email to notify you of the changes
  MailApp.sendEmail(TO_NOTIFY, 
                    "AdWords Script Paused "+toPause.length+" Keywords.", 
                    "Your AdWords Script paused "+toPause.length+" keywords.");
}

Automatically add AdWords Data to a Google Slide

Have you ever had to give a presentation about the performance of an AdWords account and spent a lot of time copy-and-pasting data from AdWords into your slides? If so, now you can automatically push data from AdWords into Google Slides.

This script leverages the recently announced integration of AdWords Scripts with the Google Slides API. Because this is one of the advanced APIs, the code is a bit more complicated and you will have to enable the Google Slides API from the script through an additional authorization step.

The code below appends a new slide to your Google Slide deck and adds some basic AdWords metrics. You can modify this code to add exactly the data from AdWords you want.

/* 
// AdWords Script: Add a Slide with AdWords Data
// --------------------------------------------------------------
// Copyright 2017 Optmyzr Inc., All Rights Reserved
// 
// This script takes a Google Presentation as input and appends a slide with basic AdWords metrics.
// Use this to automate creating an appendix of AdWords data to existing PPC report slides.
// The AW data we append is basic but can easily be tweaked to your own needs.
//
// For more PPC management tools and reports, visit www.optmyzr.com
//
*/

// Update this line with the presentation you want to edit. 
// E.g. this is for presentation https://docs.google.com/presentation/d/1RxIzTJC6Jwwd3H5aaRjA-zj3d5IhcG9uOTuOfwk8PUg/edit#slide=id.optmyzr_slide_a1f911e6-9538-427d-9e2f-12fdc951f752
var PRESENTATION_ID = "1RxIzTJC6Jwwd3H5aaRjA-zj3d5IhcG9uOTuOfwk8PUg"

function main() {
  
  var pageId = createSlide(PRESENTATION_ID);
  
  // Get the page element IDs for a basic TITLE_AND_BODY layout
  var baseElementId = readPageElementIds(PRESENTATION_ID, pageId);
  var titleId = baseElementId + "_0";
  var textId = baseElementId + "_1";
  
  // Edit the following with the text for the slide's title
  var titleText = "Automatically Fetched AdWords Data";
  updateElement(PRESENTATION_ID, titleId, titleText);
  
  // The next line gets text for the body section
  var dataForSlide = getLastMonthData();
  updateElement(PRESENTATION_ID, textId, dataForSlide);
  
  Logger.log("Done updating slides at https://docs.google.com/presentation/d/" + PRESENTATION_ID);
  
}

function getLastMonthData() {
  var currentAccount = AdWordsApp.currentAccount();
  //Logger.log('Customer ID: ' + currentAccount.getCustomerId() +
  //    ', Currency Code: ' + currentAccount.getCurrencyCode() +
  //    ', Timezone: ' + currentAccount.getTimeZone());
  var stats = currentAccount.getStatsFor('LAST_MONTH');
  var clicks = stats.getClicks();
  var impressions = stats.getImpressions();
  var text = clicks + " clicks from " + impressions + " impressions.";
  return(text);
}

function createSlide(presentationId) {
  // You can specify the ID to use for the slide, as long as it's unique.
  var pageId = Utilities.getUuid();

  var requests = [{
    "createSlide": {
      "objectId": pageId,
      //"insertionIndex": 1,
      "slideLayoutReference": {
        "predefinedLayout": "TITLE_AND_BODY"
      }
    }
  }];
  var slide =
      Slides.Presentations.batchUpdate({'requests': requests}, presentationId);
  //Logger.log(slide);
  //Logger.log("Created Slide with ID: " + slide.replies[0].createSlide.objectId);
  
  return (pageId);
}

function updateElement(presentationId, elementId, textToAdd) {
  
  var requests = [{
      "insertText": {
        "objectId": elementId,
        "text": textToAdd,
      }
    }];
  var result =
      Slides.Presentations.batchUpdate({'requests': requests}, presentationId);
  //Logger.log(result);
}

function readPageElementIds(presentationId, pageId) {
  // You can use a field mask to limit the data the API retrieves
  // in a get request, or what fields are updated in an batchUpdate.
  var response = Slides.Presentations.Pages.get(
      presentationId, pageId, {"fields": "pageElements.objectId"});
  //Logger.log(response);
 var objectIds = response.pageElements[0].objectId;
  var parts = objectIds.split("_");
  var objectIdBase = parts[0] + "_" + parts[1];
  //Logger.log("objectIdBase: " + objectIdBase);
  return(objectIdBase);
}

We maintain the most current version of this code on GitHub.

For a fully automated way to create PPC reports with interesting visualizations like Quality Score, a word cloud, a cause chart, or a heatmap, take a look at Optmyzr, my company.

Thanks,
Fred

Store Account Performance Report in a Google Doc

Day 2 and I'm still playing around with the new reporting API. I figured I'd post something that people started asking a little about from my previous post about Storing Account Level Quality Score which is storing all account level performance in a spreadsheet.

All you need to do is create a brand new Google Doc to store your data and paste that url into the script. The first time the script runs, it will create column headings. You can add, remove, or rearrange columns as you like by moving the column names around. You should reset your spreadsheet after doing this though.

Thanks,
Russ

/**************************************
* Store Account Performance Report in a Google Doc
* Version 1.1
* Changelog v1.1 - Removed apiVersion, Removed get spreadsheet
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************/
function main() {
  var spreadsheet_url = "Your Spreadsheet Url Goes Here";
  var date_range = 'YESTERDAY';
  var columns = ['Date',
                 'AccountCurrencyCode',
                 'AccountDescriptiveName',
                 'AccountId',
                 'AccountTimeZoneId',
                 'CustomerDescriptiveName',
                 'ExternalCustomerId',
                 'PrimaryCompanyName',
                 'PrimaryUserLogin',
                 'Device',
                 'AverageCpc',
                 'AverageCpm',
                 'AveragePosition',
                 'Clicks',
                 'ConversionRate',
                 'ConversionRateManyPerClick',
                 'Conversions',
                 'ConversionsManyPerClick',
                 'ConversionValue',
                 'Cost',
                 'CostPerConversion',
                 'CostPerConversionManyPerClick',
                 'Ctr',
                 'Impressions',
                 'SearchBudgetLostImpressionShare',
                 'SearchExactMatchImpressionShare',
                 'SearchImpressionShare',
                 'SearchRankLostImpressionShare',
                 'ValuePerConversion',
                 'ValuePerConversionManyPerClick',
                 'ViewThroughConversions'];
  var columns_str = columns.join(',') + " ";
  
  var sheet = SpreadsheetApp.openByUrl(spreadsheet_url).getActiveSheet();
  if(sheet.getRange('A1:A1').getValues()[0][0] == "") {
    sheet.clear();
    sheet.appendRow(columns);
  }
  
  var report_iter = AdWordsApp.report(
    'SELECT ' + columns_str +
    'FROM ACCOUNT_PERFORMANCE_REPORT ' +
    'DURING ' +date_range).rows();
  
  while(report_iter.hasNext()) {
    var row = report_iter.next();
    var row_array = [];
    for(var i in columns) {
       row_array.push(row[columns[i]]);
    }
    sheet.appendRow(row_array); 
  }
}