SEOmoz Daily SEO Blog |
Keyword Research - Using Categories to Make Your Process More Actionable Posted: 08 Feb 2011 07:48 AM PST Posted by richardbaxterseo One of the earliest and arguably most important parts of the SEO process is keyword research. Keyword research helps you answer that all important question, "In what quantities do people use search engines to find the products and services on my website?". Your research process will ultimately govern the method you use to structure your website, inspire your content strategy and kick start your link building campaigns. So, it's a bit of a shame that some SEO's don’t like doing keyword research. It’s data intensive, requires some heavy lifting with Excel, and, let’s be honest, at times feels a bit like guess work. We're reliant on data to make the right decision, and that decision could have consequences months if not years down the line for your SEO project. This is a "give it up" postFor several years now, I have used a methodology for our client keyword research that I believe adds deeper, actionable insight in to the decision making process. You see, a list of keywords with search volumes is all well and good, but it’s not particularly actionable, is it? I’m going to show you part of a process that helps to change all of that. Before we get started, I’ll make the disclaimer now – I believe in this process so much that I built a keyword tool that does all of this work for you. Obviously I’m going to give a nod to that point, but none of what you'll learn in this post depends on using our tools. What you need to know firstTo follow along, you need to understand a key principle in my methodology. That principle goes a little like this: To make keyword research more actionable, you need to be able to categorise, group and filter keywords. Deep insight into category based search behaviour can make your research considerably more effective. That’s it! Now, let’s think about what I just said. For my example, I’m going to use an automotive / cars based analogy, (I love cars, though this concept works for nearly any industry!) Imagine you’ve got a used cars website, and you want to know if there’s any potential search traffic in location based terms for popular car brands, like “used Audi in Birmingham” or “second hand Mercedes London”. If you wanted to determine the top, most searched for locations for keywords that contain known car manufacturer brands – you might have a problem. If you generate a lot of keyword data, how are you going to be able to group the terms into their corresponding keyword categories? Here’s a basic example of what I mean: At first glance, this chart looks like it’s been produced from a single list of keywords. It has in fact been produced with only 2 simple filters in a much larger dataset. “Brand” and “Location”. The data in this chart might help you decide in what order to target your keywords (and their variations) with content, or even justify an entirely new content type for your large scale dynamic website. What I'm going to show you will teach you a way to generate your data first and sort it all out by categorising it later, and not just by “location”, or “brand”, either. Let’s get started. Gather your keywordsFirstly, we need to build a keyword list. Let’s start by listing the sources of keywords you can get inspiration from, like Google Analytics, Wordtracker’s keyword suggestion tool, Wordstream’s tool, and my all time personal uber-favourites – Mergewords.com and Ubersuggest. If you’ve got access to PPC data, use that too. Want some ideas on how to build a rich and varied keyword list full of potential opportunity? Using my example, I want to build a list of car manufacturers (“brand”) by UK City (“location”), and I’d like to compare demand for used vs new (“condition”). To do that, I’ll choose some of my favourite manufacturers, Porsche, BMW, Mercedes and VW. Grab your list of cities and head over to mergewords.com Yay! 2,376 new keywords. Now for the search volumes. Get search volumesSo you have a few thousand keywords to gather search volume for. You’ve got a few options, one of those being an epic copy and paste mission via Google’s keyword tool . Don’t panic, it’s actually not that bad. You’d be surprised just how quickly you can collect a lot of keyword data, even manually, if you get your process right. By using Chrome , you can build up a sweet downloads folder full of CSV files, 100 keywords at a time. A reasonable copy, paste and download mission can yield a great data set. Obviously, this stage requires effort. For you savvy SEOs with development skills, you might want to consider writing a script to access the Google Adwords API. Freelancer.com or oDesk are good places to go to get a script written, too. I found a worthy solution in a script I had made by a freelance developer on Freelancer.com, from which I automated the keyword data collection process via Mozenda . Being able to gather data for around 50,000 keywords at a time really enabled me to do some interesting things, like capture 10 related keywords from the suggest API and run all of those through the search volume API, too. Big data for the win. Create your categoriesOk, time for some Excel heavy lifting. We’re going to start by creating our keyword categories, and then use an Excel array formula to categorise each of the keywords in our data set. For our example, we’re really interested in filtering for keywords that have location, brand and “condition” based keywords. Do bear in mind this is advanced Excel, and will require some problem solving on your part. Stick with it, it’s very, very cool. Create a category table with headers for each of your category names and add “markers” in to each category: Next, in a separate Excel tab, you’re going to need to build up your keyword search volumes table. Create columns for your keywords, search volumes and category names: Next, the awesome bit. We’re going to use an Excel array formula to identify keywords that belong in a category by matching strings of text between our category markers and the terms contained in the keyword list. Here’s what a categorised list will look like: And here’s the formula we use to make it happen: {=IF(SUM(NOT(ISERROR(FIND('Keyword Types'!$A$2:$A$7,$A2)))*1)>0,P$1,"Non-"&LOWER(P$1))} Where “'Keyword Types'![CELLRANGE]” refers to the category column we’re matching in the category table, “$A2” is our keyword, in this case “audi” and “P$1” is the name of our category column, in this case, “Brand”. You’ll also notice that there are some curious curly brackets included in the formula. That’s what makes our formula work across an array. What’s an array formula?“An array formula is a formula that works with an array, or series, of data values rather than a single data value.” – Chip Pearson We’re using an array formula because we’re attempting to match values across a range of cells. Any one keyword marker string could appear in our keyword, so our formula needs to be capable of checking across a range of values. Array formulas can unlock an entirely new level in Excel, as my good friend Tom Szekeres taught me some time ago. They're just amazing! That. Is. All. Putting the data togetherFollow this step by step process to see if you can get your first categorised list of keywords to work in Excel: 1) Paste the formula into excel and highlight the part of the formula surrounded in red in this screenshot:
2) Click your category tab and highlight your category list. Press F4. 3) Press CTRL, Shift and Return and Boom! A categorised list of keywords – in our case any keyword that contains “new”, “used”, and “second hand”. Why such an advanced approach to add to your keyword research methodology?When you’re designing a brand new site architecture, or enhancing an existing one, justifying new content groups, changes to dynamic meta templates or defining keyword strategy decisions should always been made on the data. If you have the capability to work with large, expanded data sets, with a scalable categorisation approach, there’s no doubt you can make decisions a lot more confidently. I hope that with a little practice and perseverance, you will agree with me. Some example categories for your industry might include: - Gender ("Men's", "Women's", "Girl's", "Boy's") You've also got product groups and names, buyer intent (research, review, purchase) and perhaps even groups that directly reflect the position of a category page in your site architecture ("Tier 1", "Tier 2"). Cool huh? What data should I use?All of it. Everything you can find. As much as you can get your hands on. That’s the point. You might not want to rely solely on Google search volumes, and, if you can, you should build rankings data and analytics entries into your data set. All I can say is thank the gods for VLOOKUP. That’s another blog post though – if you want me to write it, shout out in the comments. What should I do next?Well, the next most sensible suggestion might be to analyse your data using a pivot table. Making pivot tables is a really easy way to quickly deep dive into your data across multiple data points. I wrote a pivot table tutorial (including how to make pretty charts for keyword research), the content of which is designed specifically for keyword researchers. |
Did SEOmoz PRO Just Become Free? Posted: 07 Feb 2011 01:44 PM PST Posted by randfish Yes. Yes it did (sort of). In fact, you could skip this post and sign up free for 30 days right here (though there's more cool details I'll explain below). Last week, in the midst of an extremely busy day, the team at SEOmoz huddled around a bowl of green jelly beans to share something even more magical than tiny green candy - our numbers. We recently passed 7,500 PRO members and January was our biggest traffic month ever, with nearly 1 million visits to SEOmoz + Open Site Explorer. The web app alone is tracking nearly 20,000 campaigns (sites), 500K keywords and 60mil+ pages each week. Linkscape just had its three fastest index updates, meaning the data in OSE, the mozBar and the web app are the freshest and most accurate ever. January has been good to us, so it's time we return the favor. That's why, starting today, we're making SEOmoz PRO membership 100% FREE to try for the first 30 days. But... That's not all. For the first 10,000 folks who take the free trial, we're entering you to win PRO membership free for life: If you're already PRO (or sign up before 12am midnight tonight), you're already entered to win. If not, you can try it right now for free, so I'd suggest doing that :-) You can get all the details of the contest here. The free trial includes every feature inside PRO, including: #1: Track Campaigns in the PRO Web App
Create a campaign for the sites you care about and every week, the web app tracks:
The web app is still in beta, so new features launch every few weeks and we're always open for feedback about what you want to see included. #2: Unlimited Access to Open Site ExplorerOur link analysis tool, Open Site Explorer, has become one of the most popular SEO tools on the web and PRO is the only way to get unlimited access to metrics, reports, link lists and CSV exports. A list of anchor text phrases pointing to searchengineland.com There have been some great posts on how SEOs use and apply the data in OSE including - An Inside Look at Competitors' Backlinks (from Fabio Ricotta), How We Used Open Site Explorer at Microsoft (from Chris Moore) and HTTP Status Reports w/ OSE, VLookup and Xenu (from Richard Baxter). #3: Use of the Entire SEOmoz ToolkitAlthough we're constantly upgrading and migrating features into the web app, we've still got an impressive collection of dozens of useful tools for SEO professionals and web marketers. PRO gets you the awesome upgrades to the mozBar - our SEO toolbar for Mozilla Firefox and Google Chrome. Just a few of the SEO tools we provide And it includes great tools like the new On-Page Optimizer, the powerful Keyword Difficulty Tool, our classic Historical PageRank Tool and many more. #4: Ask Your Burning Questions to the Q+A ExpertsSometimes you have all the answers, but you still want to double-check. SEOmoz's team of experts has years of consulting experience and we love to share out knowledge and our advice with PRO members. a very kind tweet from DavidBlg on Twitter In addition, there's 4 years worth of questions and replies to search through and find an answer instantly. #5: Exclusive Invites to PRO-only Training Webinars (and the webinar archive)Every month, we give educational, 90 minute webinars covering topics ranging from The Cutting Edge of Organic Marketing to Local SEO to Advanced, Competitive Link Building. A slide from December's webinar on Cutting Edge of Organic Marketing I personally make and deliver many of these, and the feedback is always tremendous. Plus, the slides are always available for download and use, meaning you can re-use my cheesy comics to help present your ideas and plans internally (and educate your team, too). OK, technically there's lots more, but you've got the idea - PRO membership is definitely worth trying FREE for 30 Days. And, heck, you might win a lifetime of free PRO, just for signing up. Any questions? Just ask us in the comments! |
You are subscribed to email updates from SEOmoz Daily SEO Blog To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
Google Inc., 20 West Kinzie, Chicago IL USA 60610 |