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
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:
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:
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:
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:
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:
Call the range “BrandKeywords”, then click on the grid in the second textbox:
Highlight the cells containing the keywords, and the data range will be updated:
Press Done to save the named range, and now you can refer to it in a script like this:
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:
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:
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.