Getting The Most Out Of Your Keyword Research

One of the biggest challenges for SEOs is clearly communicating how decisions are made. For those of us with a more technical inclination, looking at a large data dump and extracting value is a little easier. But what about those people you communicate with that don't have this technical ability? We need a way to show (not just tell) them how we derive our conclusions. That's where scoring and ranking your keyword research comes in handy.

Setting up a ranking system will allow you to show your team (with visuals) why you're deciding to target some keywords over others. We're trying to answer the common question, "How do you pick keywords?"

What Does It Mean To Rank Keywords?

In essence, the idea of ranking keywords means to pick a series of data points you acquire during research and provide them a specific value. In order to compare these different data points, they will need to be normalized. Normalizing these data points means creating a common scale with which you can compare the value of unlike metrics as if they were like metrics. Based on this common scale, you can provide weights and or values to each of the different data points in order to provide more or less to their importance.

In our particular case, "rank" will refer to the ease with which you will be able to affect a keyword.

How Do I Start?

The process can be a little long, so I'm not going to dive into all the details. However, the instructions here should be sufficient to get you started!

Step 1: Getting Webmaster Tools Data

The first step I recommend is to download all of your keyword data from Webmaster Tools. Go to the search queries report in WMT and click the download table button as seen below:

Excellent, this is going to be the base of operations for your data. For this exercise, we'll only be looking at keywords associated with your website already. Once you have this data, you'll want to break things up into different keyword themes.

For example, I've broken my data up by the different services we provide here at Marcel Digital. When you have your terms in a separate tab, you can use a VLOOKUP to pull in the necessary WMT data. Now you need to do some normalizing.

How To Normalize Your Data

Remember the idea behind normalizing your data is to create a common scale you can use to compare all of the metrics in your report. Normalizing the data in this example will require three steps.

Step 1: Finding The Mean Of Your Data Set

For each data set, you'll need to find the mean. This is a pretty quick step, just go ahead and set the AVERAGE for your data column.

Step 2: Finding The Standard Deviation Of Your Data Set

Next, you'll want to use the STDEVPA function on the same data set to grab the standard deviation. Why grab the standard deviation? It'll tell us how far away each data point is from the mean. This will help establish a level of confidence, as well as create a scale that's a bit easier to work with.

Step 3: Standardize The Data

Our scales are still a little off, so now we need to standardize the data so that we can treat each data point similarly. The final step in creating normalized data is to use the STANDARDIZE function on your row.

Once you've got your data standardized, go ahead and do this same process for each of the other metrics you'd like to consider in your scoring.

Creating The Rank

This part is really up to you as the analyst and will likely change a lot after regression testing your model. The most basic way to do this is to simply take an average of your standardized data for each of your terms. This is essentially an un-weighted method. If you have no better information, this is probably a decent enough starting place.

Remember when you're actually valuing your data points try to imagine how you consider things easier or more difficult and apply that to your scale. For example, something with low competition and a position worse than 30 may actually be an easier opportunity. At the end of your project, you can use conditional formatting to help create simple visuals for your coworkers (since they might not understand all this nerd talk.)

That's It!

Hopefully at the end, you'll have something that provides clients and coworkers an easy way to understand where the opportunities are and where your challenges lie. Once you're done testing and have a model that works...celebrate. You deserve it!