Create Charts Using AdWords Scripts For Quick Insights

Using AdWords scripts to automatically generate reports will allow you to spend that time performing account optimizations.

Chat with SearchBot

google-g-analytics-ss-1920

Spreadsheets! Spreadsheets everywhere!

Anyone trying to optimize their AdWords account undoubtedly spends a large amount of time looking at spreadsheets and generating Excel charts for slide decks. Using AdWords scripts to automatically generate these reports will allow you to spend that time performing the optimizations on your account.

The Charts class in AdWords Scripts might be the most underutilized in the bunch. In fact, just digging around the Google developer resources, there are really no examples of how to incorporate charts into your reporting.

Charts can be easily saved to your Google Drive account or inserted into your summary emails to get a sense of what you need to focus on quickly.

 An Introduction To The Charts Object

The full documentation for the charts object can be a little daunting at first, so the first place you probably want to start is the Google Charts Gallery. Here, you can find examples of just some of the types of charts you can build and insert into your reporting.

The first thing you will probably notice is that all the examples in the charts gallery are generated using client side Javascript, not the server side scripting that we need to use for AdWords scripts. The scripts API is very similar to the client side libraries, but some of the motion and interactive elements are not available.

Of course, you can always write a script that generates an .html file and uploads it to a hosting service, but we’ll have to leave that for another tutorial. Let’s start with a simple column chart to show you a breakdown of keyword quality scores across your account. As always, we will build out some reusable functions to speed up development in the future.

Generating The Quality Score Breakdown

The focus of this tutorial is really on using the Charts class, but in order to build a chart, you need to have some data. Here is a quick code snippet that will query all the keyword quality scores in your account and return a keyword count for each of the 10 possible scores.

function getQualityScoreDistribution() {
  var OPTIONS = { includeZeroImpressions : false };
  var cols = ['Id','QualityScore'];
  var report = 'KEYWORDS_PERFORMANCE_REPORT';
  var query = ['select',cols.join(','),'from',report,
               'where AdNetworkType1 = SEARCH',
               'and CampaignStatus = ENABLED',
               'and AdGroupStatus = ENABLED',
               'and Status = ENABLED',
               'and IsNegative = false',
               'during','TODAY'].join(' ');
  var results = { 1 : 0, 2 : 0, 3 : 0, 4 : 0,  5 : 0, 
                  6 : 0, 7 : 0, 8 : 0, 9 : 0, 10 : 0};
  var reportIter = AdWordsApp.report(query, OPTIONS).rows();
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    results[row.QualityScore]++;
  }
  var arrayFormat = [];
  for(var k in results) {
    arrayFormat.push([k,results[k]]);
  }
  arrayFormat.sort(function(a,b) { return a[0] < b[0]; });
  return arrayFormat;
}

You can add this to any script you currently have and call it to generate a distribution of keyword quality scores. Now we can start to build the logic to generate the chart.

Building The Data Table Object

There are two main classes that we will need to combine in order to create a chart: the DataTable and the actual Chart. The DataTable is where you define the actual information you want to display and the Chart object defines how you want to display that data, such as a line or column chart.

You will notice that the Charts API relies heavily on builder classes. It works the same way as the AdGroup Builder class in AdWords scripts and allows you to configure complex objects without the need to pass a ton of parameters to the constructor.

The DataTable builder object expects you to define the column names and data types first using the addColumn() function. Next, you can add the rows of data using the addRow() function. Here is a quick reusable function to help build out the DataTable object.

function getDataTableBuider(columns,data) {
  var dataBuilder = Charts.newDataTable();
  for(var i in columns) {
    var column = columns[i];
    dataBuilder.addColumn(column.type, column.title);
  }
  for(var i in data) {
    dataBuilder.addRow(data[i]);
  }
  return dataBuilder;
}

There are three different types of columns: Date, String, and Numeric. In our example, the Quality Score column will be a string and the number of keywords in each bucket will be a number. Next, let’s design what our chart is going to look like.

Displaying Your Data

Choosing how you want your data to look can be one of the most time consuming parts of this whole process. What I recommend is to start with one of the sample charts in the gallery and use jsFiddle to change the settings until you have what you need.

For this chart, I started with the simple column chart example and changed the colors and formatting until I had something I liked. I recommend using colors that match the slide templates you would use for a presentation to streamline the entire process further. Flicking back and forth between the documentation and jsFiddle, I was able to put this together.

jsFiddle Can Dynamically Execute Javascript

jsFiddle dynamically executes Javascript making it easier to test formatting

Just like the building the data table above, let’s put this into its own function so that we can reuse this anytime we need a similar chart.

function getColumnChart(dataBuilder,options) {
  var colChart = Charts.newColumnChart();
  for(var option in options) {
    colChart.setOption(option, options[option]);
  }
  colChart.setDataTable(dataBuilder);
  return colChart.build();
}

The options for each chart can be found in the documentation. Of course, any options that are centered around animations or hovering won’t work for us since we are creating a static image; but, everything else can be set in the format { ‘config.option’ : ‘value’ … }. The quotes will need to be used when the keys have a period in them.

Bringing It All Together

Now it is time to put together our main() function, which is nice and short since we split out the major parts of the code into smaller, reusable functions.

function main() {
  var qsData = getQualityScoreDistribution();
  var columns = [ // Define the columns for our data
    { type : Charts.ColumnType.STRING,
     title : 'Quality Score'
    },
    { type : Charts.ColumnType.NUMBER,
     title : 'Number of Keywords'
    }
  ];
  var dataBuilder = getDataTableBuider(columns,qsData);
  // We pass in all the options that we tested in jsFiddle
  var colChart = getColumnChart(dataBuilder, {
    'title' : 'Keyword Quality Score Distribution',
    'hAxis.title' : 'Quality Score', 
    'hAxis.titleTextStyle.color' : 'black',
    'vAxis.title' : 'Number of Keywords', 
    'vAxis.titleTextStyle.color' : 'black',
    'chartArea.backgroundColor' : '#f2f2f2',
    'legend.position' : 'none',
    'colors' : ['#61aac7','#61aac7','#61aac7','#61aac7','#d55301',
                '#61aac7','#61aac7','#61aac7','#61aac7','#61aac7']
  });
  var dateStr = Utilities.formatDate(new Date(), 
                                     AdWordsApp.currentAccount().getTimeZone(), 
                                    'yyyy-MM-dd');
  MailApp.sendEmail({
     to: "[email protected]",
     subject: "Daily Quality Score Distribution - "+dateStr,
     htmlBody: "<img src='cid:graph' alt='' />",
     inlineImages: {
       graph: colChart.getBlob().setName("graph"),
     }
   });
}

The final section of code that actually sends the graph in an email is an example of what you can do with the graph once you generate it. I was able to slightly modify the example code from the MailApp documentation to accomplish this.

You can (and should) create a standard HTML email template using functions that you can reuse across all of your scripts.

Conclusion

This is of course just a simple example to get you started with using Charts in your AdWords reporting. There are a ton of options for charts available, although not all are available in the server side API (the Gauge report for example). It takes a little up front time to build the frameworks for generating these reports, but in the long run, you will be able to save time and money by automatically generating your weekly or monthly reports.

Editor’s note: the code snippets in this article have been updated to correct errors that appeared when it was first published. Apologies for any inconvenience. 


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Russell Savage
Contributor
Russell Savage is an Application Engineer for Cask Data and loves tinkering with marketing data and automation in his spare time. He is the creator of FreeAdWordsScripts.com, where he posts AdWords Scripts for anyone to use.

Get the must-read newsletter for search marketers.