Advanced Keyword Modelling – SES 2012

Earlier today I gave a presentation at SES London 2012 with Ron Jones from Symetri on the topic of advanced keyword modelling for keyword research. While Ron gave the theoretical overview of the concepts a good researcher might use to understand and categorise how users search, I gave a practical demonstration using SEO Tools for Excel and the Adwords API Extension.

I also gave a preview of a new tool we’re testing to help keyword researchers predict keywords for the fresh index. More on that later, because it’s buggy as hell at the moment and I’d like to save it for launch when it’s ready.

Check back later for the video, because with some luck I’ll have managed to record my part of the session.

Advanced Keyword Modelling – SES 2012


Here’s the video:

If you’d like to hear about my updates on this post, follow me on Twitter.

A few useful resources:

– SEO Tools for Excel

– Adwords API Extension

Using Keyword Categories in Excel


Video Transcription

Hi everybody. Earlier on today I gave a presentation with Ron Jones at Symmetry all about advance key modelling, specifically how to use data to make decisions about your SEO strategy, site architecture, that kind of thing. Ron talked about some of the principles behind keyword categorisation and using categories to design and architect a better website, while I took more of a practical viewpoint and actually showed how to create keyword categories and how to do some basic to advanced Excel, actually, to help solve problems in understanding how people search. I’m going to switch to Microsoft Excel and just give you a run through of what I demonstrated earlier on today.


Okay. So thank you to Zack at Prezzybox for always letting us use a little bit of retail based data in our keyword research examples. We’ve got a few things. So we’ve got some search engine ranking data here, which I’ll explain why we’ve got that in a moment. We’ve also got analytics data from an anonymous time period, but very retail birthday based. Then we’ve got some raw Google search volume data.


So, one of the biggest challenges with keyword research is being able to actually categorise this raw data into something meaningful. Ron was arguing that if you create a keyword category that’s relevant to a specific area, like if you’re in automotive, then you might want to understand what the most popular car makes and models are, or what colours do people prefer when they’re buying a car. When it’s retail, it might be that somebody is interested in outdoor toys, but which outdoor toys? Using categorisation in that regard in SEO helps us structure a website more meaningfully. But getting to that structure is a bit of a journey because, as you can see, this is relatively meaningless. We’ve got local search volumes, great. So we know that 74,000 people searched for the word chocolate last month in theUK. But so what? And that’s what this presentation is about.


So I’m going to show you how to create a keyword category and I’m going to go one step further and show you how to visualise that category. Then I’m just going to show you a few other things that AdWords API extension for Excel does. If you’re not familiar with our AdWords API extension, in as short a demo as possible, it does this. You’ve got a keyword, and I might want to get the AdWords average search volume for that keyword. This will default toUKexact, I believe. So pressing Return and running that formula will get me the total number of searches in theUKfor that term. That’s how the extension works. When we’re looking at this data here, this is an export of the Google AdWords API using the array formula. So you can find out how to install that on our website.


Let me just make this data a little bit simpler, and we’ll just delete that out. Let’s just imagine for a moment that we want to create a category, and for the purposes of this demo, I’m going to show you how to create a category to help us understand a little bit more about how people search for birthday gifts in retail.


Now to do that, we need to be able to create the category. If you head to SEOmoz or Google SEOmoz keyword categories, you’ll see a post on how this is done. Here’s the practical example. I’m just going to grab my formula and go back to Google search data. So, imagine for a moment that in any category there might be markers or strings that describe the fact that the keyword itself belongs to that category. So if you’re optimising for occasions, you might be interested in Christmas or you might be interested in Valentine’s, right? So that might create a category called occasions. Price, it might be cheap, it might be discount, might be sale and so on. Each of these types of keywords might belong in a phrase that would indicate that a user’s search is based around price. Similarly, if somebody’s searching for a gift for an 18th birthday present, you’d see the characters “1, 8, t, and h” appear in the string. So, that’s basically how categorisation works, and it’s really down to us just to find a formula that will allow us to filter by all of these keywords, all in one go.


That formula is called an array formula and here’s how it works. If we go back to our search data, I’m going to paste in the formula that I copied a moment ago and I’m going to just grab, from my keywords type list, that row of birthday strings. Let’s call them markers. So we’re going to highlight all of those, and then pressing F4 will anchor that range. Now if I do a CTRL-Shift and Enter to make it an array formula, it’s started to filter. Now I’m going to just neaten that up a little bit by saying “Birthday,” and in my formula I believe that it’s looking at “O” not “C.” So it’s actually fetching the column header here and saying whether or not it’s found that marker in the keyword. Let’s say CTRL-Shift and Enter again, and there we go. So now I can filter the keywords that might be related to birthdays. So, that’s pretty cool, and that’s the first part of the demo.


The next thing that I wanted to show the audience was, okay, we know how many people are searching for those keywords, but how many entries did we get? That’s relatively simple because we’ve got some basic analytics data here, and we’re going to use what’s called the VLookup. Now VLookup simply says, “Find me this value in another place in my spreadsheet, like another keyword, and fetch the data that’s adjacent to it.” And you specify how many columns to the right in VLookup you want data to be fetched.


So, back to my Google search data. We’re going to create a VLookup. Now for those of you familiar with tables, you might have noticed that I’ve named my table, and I’ve named it “Analytics,” which makes it nice and simple when I’m forming the VLookup. Column index was two, was the column next to the keywords, and I want to match exactly. I found an extra bracket there, there we go. Let’s call that “Analytics.” Now we’ve got analytics entries, local search volume, and the keyword.


The last thing that I might want to combine with this is search ranking. Now in my ranking table, let’s just call this “Rankings Data.” Notice that we’ve got the keyword and then one, two, three, four, five columns to the right. So, the VLookup needs to look a little bit like this. Double click the table name when it appears in the tool tip. It was column index number five, and we want to match exactly with the zero, and off it goes to fetch those rankings. Now, you might have just noticed that some of those are errors. That’s because there is no data in this case. So we can wrap our query, our formula inside an IFError. So, IFError, do this thing, basically. So the value of IFError might be no data. There we go. Cool.


So, the last part of this is, well, how do we visualise this data? How do we make it into something that is relatively actionable, and quite often I like to just create a pivot table, which I’ll show you really quickly. So, if you go to insert pivot table, add that to a new spreadsheet, drop my keywords at the bottom, we’ll want our local search volume, we’ll also want our rankings. We always need to make sure that our ranking is set to sum in this case. I want to be able to filter by search volume and filter by rankings and, of course, the keyword category that I’ve made up.


So, now I’ve got my local search volume. Show me all keywords where search volume is higher than 46. I might want to sort by largest to smallest, like so. Only show me keywords that rank on page two. So let’s select some low hanging fruit terms that we might want to target with a bit of SEO, some link building, or maybe improve the on-page optimisation of our website. Finally, only show me keywords that are related to our birthday category.


So there we go. We’ve got an interesting data set from which we can visualise with a chart. So I’m just going to insert a bar chart. Now, we’ve got two sets of data here – the rankings and the search volume. Now, the rankings really need to go on a separate axis. So we can do that by formatting our rankings. We’re going to format that selection. We’re going to put it on a secondary axis. Then we’re going to change the chart type with a right mouse button click, change series chart type to dots. Now the next thing I want to do is reverse my axis, because I want to see the top ranking keywords at the top and the bottom ranking keywords at the bottom. And values in reverse order, click close. Then the only final thing that we need to do is if we format that data series, let’s take that line out because the line is irrelevant. The data isn’t technically joined. There we go. It’s a very, very simple chart that shows, on the right-hand side, rankings from position 1 to position 30, our keywords along the bottom with local search volume.


So that’s how you visualise your keyword priorities. You could use this data to make some decisions about your site architecture. In what order should you be linking to things, what content should be on your home page versus content that perhaps can just link to a little deeper because it’s not quite as high a priority.


In the session, I also covered a couple of other bits and pieces, because the brief for the session was actually to show how I might expand keywords. I just wanted to share a tip, which is based on the AdWords API plug-in and how to generate a keyword list based on a particular geo-location. So, in this case, we’re looking at keywords related to the word “hotels.” I’m very interested in how people search for keywords related to hotels in foreign climes. So let’s look atTaiwan. This is a query called “=arraysGetAdWordsIdeas.” It’s part of the AdWords API toolkit. While that’s running, I’ll explain what its doing. It’s looking at a table called Root4, fetching that keyword, and then playing back an exact match search volume for all keywords that are related to the word “hotel” according to the Google AdWords API Suggestion Service. You’ll see here that’s the country code forTaiwan. I’m asking it to show me all search volumes on the Web, and I’m asking for 100 keyword ideas. That will go quite high, around about 800 to 1000 per API call, and because it’s the API call, it is actually one API call. So that’s only $0.25, sorry, I beg your pardon, it’s only $0.25 for every 1000 API calls. So in theory you could have 800,000 keywords for $0.25, which is pretty cool.


This is something else that I shared, which is Ubersuggest. If you’re just trying to expand a keyword list, head to Ubersuggest and get the HTML link for your own search results. So, you’ve got here the query for cats. Now if I just grab this XPathOnURL query, which is courtesy of Niels Bosma’s SEO tools, the XPathOnURL query, which you saw in the previous video, if I go and select a range and press F2, then paste my query in which is referencing the URL in A3, which is the Ubersuggest URL, if I make this an array formula with CTRL-Shift and Enter, it’ll go off to Ubersuggest and fetch all of the results on that page, eventually. It takes a moment. So it should just be coming any moment now. There you go. So there are the top keywords related to the word “cats” according to Ubersuggest with setting toUKsearch, which is immensely powerful if you think about how you can combine that with your search volume data.


Anyway, thanks a lot for listening. That’s the end of the presentation. You can watch this video, or you can go and download the presentation by following that bitly link. Thanks a lot.

Image: PSD

Have We Been Helpful?

Builtvisible are a team of specialists who love search, SEO and creating content marketing that communicates ideas and builds brands.

To learn more about how we can help you, take a look at the services we offer.

Stay Updated: Sign Up for New Blog Alerts

Follow: | | |

Tags: , , | Categories: Research

5 thoughts on “Advanced Keyword Modelling – SES 2012

  1. Richard, this video just blew my mind. Over the last month I have really been trying to teach myself how to use Excel at a higher level to make my workflow more efficient. This video is a bit overwhelming to me, but quite helpful (and a bit inspirational). I feel so “behind the times” in a way. :-)

    I guess my first step is to get my AdWords API account. Although, it makes it quite inconvenient that I must create a completely different user in order to get access to this… Oh well. The prices we pay, right?

  2. Hi Richard,

    Do you have any tips for getting the AdWords API token – what to say or emphasize in the email, etc? We’ve been rejecting a few times, and I’m at a lost.

    Also, I second Michael. Awesome video. And the use of XPath to fetch UberSuggest data is a great idea/ addition to your previous post on keyword categorization.

  3. Hi Cleo – I’m afraid you’ll have to follow the threads coming from here: you need to have reasonable spend in your account, and a technical scope for a web application that meets the adwords terms and conditions of use.

  4. It’s probably a better idea for you to use the same account you already have PPC spend with. Enjoy though!

  5. Thanks Richard! Much appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *