Removing Unwanted Data from an AdWords Scripts Report

in AdWords Scripts

This post builds on my tutorial explaining how to build a monthly report using AdWords scripts and Google spreadsheets by introducing some techniques for filtering out data. If you followed my tutorial, then you might have noticed a couple of problems with the data:

  • If you're using the display network, you might see keywords with the text “Content” appearing several times in the report. These rows contain aggregate data showing the display network performance for each ad group.
  • You're likely to find that most of the top keywords are branded, which might not be as interesting as seeing the other keywords that are performing well.

You can filter out the display network and branded keywords by placing a WHERE clause in the report's AWQL. The rest of this post will show you how.

Removing Display Network Data

By default, the keywords performance report contains data for both the search and display networks. You can see which network generated a row in the report by including the AdNetworkType1 field: this will display either SEARCH for search network performance, or CONTENT for the display network. You can also use the field in the WHERE clause of your query:

If you modify your script to use the above query, then it will only display search network keywords, without the “Content” rows.

Removing Branded Keywords

Removing a list of keywords is a bit more involved as there could be many of them. You can remove groups of keywords using one of the CONTAINS operators:

SELECT 
   KeywordText, KeywordMatchType, Impressions, Clicks, 
   Cost, AveragePosition, Conversions, ConversionValue
FROM 
   KEYWORDS_PERFORMANCE_REPORT
WHERE
   KeywordText DOES_NOT_CONTAIN_IGNORE_CASE 'mybrand' AND
   KeywordText DOES_NOT_CONTAIN_IGNORE_CASE 'another brand'
DURING 
   LAST_MONTH
Filtering Keywords using the CONTAINS Operator

This will remove any keyword that contains the text “mybrand” or “another brand” regardless of its capitalization or where it appears, so the report won't contain keywords such as “the mybrand” or “Another Brand of mine”, for example. Alternatively, if you know the exact keywords you want to exclude, then you can supply a list of them:

SELECT 
   KeywordText, KeywordMatchType, Impressions, Clicks, 
   Cost, AveragePosition, Conversions, ConversionValue
FROM 
   KEYWORDS_PERFORMANCE_REPORT
WHERE
   KeywordText NOT_IN ['mybrand', 'another brand']
DURING 
   LAST_MONTH
Filtering using a List of Keywords

This approach is more compact, but it will only remove rows that match one of the keywords in the list exactly; keywords such as “the mybrand” and “Another Brand of mine” will now be included. You can, of course, combine single conditions with value lists:

SELECT 
   KeywordText, KeywordMatchType, Impressions, Clicks, 
   Cost, AveragePosition, Conversions, ConversionValue
FROM 
   KEYWORDS_PERFORMANCE_REPORT
WHERE
   KeywordText DOES_NOT_CONTAIN_IGNORE_CASE 'mybrand' AND
   KeywordText NOT_IN ['another brand']
DURING 
   LAST_MONTH
Filtering using a List and CONTAINS Operator

Now, keywords such as “the mybrand” will be excluded but “Another Brand of mine” won't.

This approach is OK, but might not scale for you if you're managing many accounts and have to deal with different scripts for each one. Another approach you could take is to build the WHERE clause dynamically based on values in a spreadsheet; you could then have a single script and just reference a different set of keywords for each client. To try this out, start by creating a new sheet in the template and adding some keywords to it:

Brand Keywords to Exclude
Brand Keywords to Exclude

I'm going to reference this range of cells from my script, which I could do by specifying the start and end rows, but this would mean that you would need to update the script every time you added a new keyword; most spreadsheet tools have a feature, called Named Ranges, for dealing with this type of problem: named ranges allow you to give a range of cells a name for use in other areas of your spreadsheet, and when you change the cells referenced by the name, the other areas of the spreadsheet will update automatically. You can add a named range via the Named and protected ranges… option in the Data menu:

Opening the Named Range Panel
Opening the Named Range Panel

This will open a panel at the right of the screen for administering named ranges. Click on the Add a range link to create a new range:

The Named Range Panel
The Named Range Panel

Call the range “BrandKeywords”, then click on the grid in the second textbox:

Button for Selecting Named Range Data
Button for Selecting Named Range Data

Highlight the cells containing the keywords, and the data range will be updated:

Selecting the Brand Keyword Cells
Selecting the Brand Keyword Cells

Press Done to save the named range, and now you can refer to it in a script like this:

var brandKeywords = sheet.getRange("BrandKeywords").getValues();
Using a Named Range in a Script

The getValues() method of a range returns a two-dimensional array of values, so you can use a function to create a one-dimensional list of values suitable for using in an AWQL query:

function generateStringList(values) {
    var stringList = [];
    for (var i = 0; i < values.length; i++) {
        if (values[i].length > 0) {
            stringList.push('"' + values[i][0] + '"');
        }
    }
    return "[" + stringList.join(",") + "]";
}
Generating a List for the AWQL Query

This creates a one-dimensional array by looping through each row in the input two-dimensional array and extracting the value in the first column. The function builds the list required for the AWQL query by wrapping each value in single quotes, joining the array to create a single string, and wrapping the results in square brackets. You can use it in your AWQL like this:

var brandKeywords = sheet.getRange("BrandKeywords").getValues();
var brandKeywordsList = generateStringList(brandKeywords);
 
var awql = "SELECT " + fields.join(",") +
        " FROM KEYWORDS_PERFORMANCE_REPORT " +
        " WHERE KeywordText NOT_IN " + brandKeywordsList +
        " DURING LAST_MONTH";
Using the generateStringList() Function in AWQL

Hopefully this tutorial will give you a start with AWQL filters, and you can check out the AWQL Guide to find out more about it.

Comments

I'm keen to get feedback on my posts, so if you have any questions or comments, then please send me a message and I'll be happy to help.