Using Pareto Analysis for SEO and Keyword Targeting

We’ve all been in that place where finding the best keywords to optimize a site is like sitting in a bingo hall waiting for the number to be pulled. We examine exhaustive data from analytics and keyword tools; we sort this way, we sort that way, we examine the intent and implication of each keyword.

Or we’re at the other end of the spectrum: you’re a year or two into the campaign, have created steady growth. Then things begin to stagnate; the site’s growth slows down and you’re at loss how to jump start the motor.

Fortunately, there’s a solution you can apply to either of these situations. Pareto Analysis. And, for those not into formality, the 80/20 Rule. Managers and the industrial complex in general have been using Pareto Analysis for decades.

What is Pareto Analysis?

Pareto Analysis is a decision-making technique (problem solving technique) that employs statistics to derive the most effective actions one can take (the 20%) that will deliver the maximum impact (the 80%). In Pareto’s world, it’s identifying 20% of the causes that will solve 80% of the problems.

If you’re a history buff and want the whole story, here’s the Vilfredo Pareto biography.

The idea with Pareto is to take an insurmountable amount data that locks up the decision-making process and begin to manage it in order to find “the vital few and the trivial many”. In this fashion, you can begin making decisions on root causes that deliver a big impact.

How Can Pareto Help in SEO?

Let’s go back to the two situations up above. For the first situation, a behemoth of a keyword list with tons of attributes (i.e. monthly volume, yearly visit totals, CPCs, bounce rates, keyword revenue, etc), Pareto can help by helping ease the fear of choosing incorrectly.

By applying this principle to your keyword list, you can sculpt away the riff-raff to find the 20% of keywords that will deliver the most impact for your client.

In the second example where stagnation has become the order of the day, Pareto can help you segment all those long-tail keywords that begin to start looking like a horse apiece, into an actionable group.

From that actionable group you can generate a second wave of growth and keep the re-up growing strong.

But, it’s not enough to give you the idea; next we’re going to build our own Pareto Analysis in excel.

Building Pareto Analysis for SEO in Excel

*Brushes the chalk dust off the corduroy jacket, and taps the chalkboard*

Step 1: Setting up the Pareto Analysis

For the first step, we’ll need to have all our data ready in excel. (P.S. this is all made-up data, so there are victims to this teaching session). See the example below:

Once you have your data fields of your choice, sort the data field of your choice from largest to smallest. Next it’s time to start calculating percentages.

I find that it’s easier to do this over to the side of your original data so you can do multiple comparisons. However, for simplicity’s sake, we’ll limit it to just Local Monthly so you can see how Pareto functions.

To the right of Local Monthly, create a new column called “Local Monthly %”. And to the right of that column add a new column called “80% Cut Off”.

Here’s what it should look like when you’re done:

Step 2: Graphing The Pareto Results

Fantastic. Now we have a bunch of percentages that only seemed to complicate the matter. But have no fear, we’re about 5 minutes away (depending on your excel skills) from getting some clarity.

Next, what we’ll want to do is create a 2-D column chart highlighting “Keyword”, “Local Monthly”, and “Local Monthly %”.  Don’t use a 3-D column chart (even though you might want to), because Excel won’t let you combine a fancy 3-D chart with a 2-D line graph.

For this exercise, we have to stay in boring 2-D.

When you’re done, you should have something that looks like this:

We have all the data points mapped that we need; we just can’t see them all right now. The next thing we need to do is create a multi-axis chart to graph the Pareto results. Tip: if you highlight the columns and right-arrow, you’ll eventually see the Local Monthly % in line at the bottom, like this:

Right-click this data series and change the chart type to a 2-D line chart. You should see a solid red line at the bottom of the chart now. Highlight this line, right-click again, and format the data series to make it a secondary axis.  Now you should see this:

Step 3: Drawing an 80% Line in the Sand

Now that we’ve graphed the Pareto results, we’ve got to set that 80% line in the sand to capture the 20% that will create the most impact. Draw your line and find those keywords.

Step 4: Clean, Rinse, Repeat

That’s using one single metric, you can do this type of analysis for all the data points and create targeted list of keywords that will bring you the most bang for your buck.
As you can see, Pareto Analysis for SEO can be pretty handy to slim down your keyword choices or to help craft new strategies for client’s that have become stagnant.

Download the Pareto Analysis Workbook for SEO to help get you started on your own keywords.