The 4 Steps to Keyword Analysis: How to Prioritize Your Resources


Don’t you love that feeling that comes with keyword research?

You’re left with hundreds, often thousands, of opportunities you can target to grow your business.

If you do your keyword research well, you can even identify several relatively easy keywords to go after.

I know you’re excited.

But there’s a problem…

Which one do you go after first?

Which one do you go after second?

For the vast majority of blogs and business websites, you’ll be able to create only a few really great pieces of content a month.

That means you’ll never get to every single keyword you dug up in your research.

In fact, you may never get past 10% (but you can still be incredibly successful). So, what do you do?

You prioritize.

Some keywords are better than others to go after for your business.

I’m going to show you a 4-step process you can follow to analyze the keywords you came up with and decide which keywords to pursue. 

Step 1: Organization is key

Keyword research and analysis is not something that can just be thrown together.

You can’t randomly input keywords into tools and sporadically analyze them—it’s impossible when you have potentially thousands to go through.

That’s why organization is critical. Take the time upfront to get all your keyword research into one area.

In this case, I recommend using a spreadsheet. Once you have a list of keywords to consider, put them in a single column.

Next, get the search volumes for each keyword if you haven’t already. Just copy and paste them into the Keyword Planner if you have to.

This step isn’t hard, but it could take some time.

By the end, you should have a spreadsheet like this, with all your keywords:


Step 2: It’s time to take stock

Before you even look at your keywords, you need to decide what you’re willing to invest to go after them.

For example, if you have a $500 monthly budget, you cannot target highly competitive terms such as “home insurance” because you’ll get zero traffic. Instead, it’s better to target more realistic terms and get a steady trickle of traffic.

Since keyword research is usually tied to SEO at least a bit, you need to give yourself a fighting chance at ranking #1-3 for each keyword you target.

But put aside the competition aspect for now, and make sure you know exactly how much of the following three factors you have available.

Factor #1 – budget. To target a keyword, you’ll need two things: content and promotion (mainly backlinks).

Many businesses hire someone (or a small team) to produce content and do the promotion.

Right here, you need to be able to answer these questions:

  • Do you even want to spend money on targeting keywords?
  • Alternatively, do you have to spend money to do it (because no one on your team has the skills or time to)?
  • If so, how much can you reliably afford to commit on a long term basis (at least 6 months)?

To get the most out of your content, you need to think long term. It takes months of consistent, high quality work before traffic starts to pick up.

That’s why it’s not enough to invest a lot upfront and then pull funding when the results aren’t amazing immediately.

If you are going to employ that approach, divide that upfront money into at least six portions, and plan your content and promotion accordingly in the future.

Factor #2 – manpower. If you don’t want to spend money to hire people to produce content and promote it, you need to do it yourself (or assign it to an employee).

Or you might want a mixture of the two options.

Either way, determine right now the maximum amount of time you, or someone on your team, can commit to working on a specific keyword.

Again, this needs to be an amount of time specifically carved out for this work. You need consistency.

Factor #3 – expectations. When I refer to expectations, I mean answering this question: How well do you need to rank in order to be happy?

Or a better question might be: How much traffic do you need if you spend a certain level of resources on your marketing and SEO?

If you’re starting from scratch, getting just 100 organic visits a day might justify the work you’re going to put in, at least for now.

But if you’re heading up this work at a large website, getting an extra 100 visits a day might be only 1% more traffic, which isn’t good enough.

The point here is to see if there’s any misalignment between the first two factors and your goals.

If you’re expecting big things with a small budget, you’re doomed before you even started. At this point, you need to revisit your budget and manpower available—or tone down your expectations.

Alternatively, if you’re expecting to get an extra few thousand visitors a month after 6 months of work with a budget of a few thousand dollars a month, that’s achievable, and you can move on to the next step.

Step 3: Competition will dictate desirability

All right, now we can get back to your list of keywords.

This step is about one thing: determining the level of competition for each keyword.

This competition level refers to how hard it will be to rank in the top 3 listings for that keyword in Google.

That being said, if you have another distribution channel (social media, forum, etc.) that you know you can get a ton of traffic from for content on a specific keyword, classify that as easy.

Essentially, we’re looking for an overall measure of how easy it will be to get a reasonable amount of traffic from each keyword.

Option #1 – assign each keyword a competition value manually: Create a column on your spreadsheet to assign a competition value in either of two ways:

  • General categories – competition isn’t an exact science. You may opt to simply label each keyword with something like: easy, relatively easy, average, hard, very hard, etc.
  • Specific numbers – you can also use a scale of 1-5 or 1-10, where low numbers indicate low competition and high numbers are the toughest.

I recommend the second way because we’ll be using it later on.

Here comes the hard part: figuring out the competition level for each keyword. This can take a lot of time, especially if you’re doing it all yourself.

Basically, you need to get the top 3 results (or more) for each keyword, and then look at the following factors:

  • How relevant is the content? (i.e., is it clearly optimized for the keyword?)
  • How impressive is the content? (can you make something significantly better?)
  • How many backlinks point to the page? (only count high quality ones)
  • How authoritative is the site? (e.g., Forbes is highly authoritative, is not)

You could also look at factors such as mobile-friendliness and page load speed, but you’ll never be able to analyze all your keywords if you include too many factors.

This doesn’t need to be a perfect analysis, but it should be at least a good estimate of what you’re up against.

Put all those together to come up with an overall competition score.

Option #2 – use a tool to gauge competition: I know I don’t have time to do the above for thousands of keywords.

The good news is that many signals can be checked automatically with tools. You can find a bunch of options in the keyword competition section of this guide.

These tools look at the above factors and then use a formula to calculate an overall competition value (usually out of 10 or 100).


This can take this step down from several hours to just minutes, which is obviously a great thing.

The one thing you sacrifice is control.

You have to trust that the minds behind the tool are weighing the factors correctly and generating a relatively good competition estimate.

I suggest trying out a tool and then manually going through a dozen keywords to see whether the tool’s competition assessment matches yours.

Step 4: It’s time to turn to math

“Oh crap, I don’t remember calculus…”

Don’t worry, you’ll need only very basic math here.

This is the final step of our analysis, where we create a score that will tell us which keywords to prioritize.

Let’s recap what we’ve done so far and, more importantly, what we’re looking for in a great keyword.


  • We want keywords with low competition.
  • We want to get a lot of traffic if we rank highly for it (more is better).
  • It must be realistic—if a keyword has competition that clearly exceeds your budget, it should automatically be the lowest priority.
  • We need a minimum amount of traffic to make it worth your time.

Part #1 – filter and eliminate: Those last two points are the easiest to start with. If a keyword doesn’t meet those conditions, it should be assigned low priority and removed from consideration.

Start with the minimum traffic level.

You’ve already decided the minimum return you need, and we’ll use that here.

If your minimum was 100 visitors per day, or 3,000 per month, a keyword with a monthly search volume of 50 will not be worth it.

Your cutoff will probably be 500-1,000 for that example. With 7-15 pieces of content, you could hit your goal, which is reasonable for most. Keep in mind that you will get only about 30% of the monthly search volume as #1 these days.


Filter out all the keywords below that monthly search volume.

Next, based on your predetermined budget and manpower, along with SEO experience, determine what competition is too high.

If you have a small budget with very little manpower or SEO experience, eliminate all keywords that are above average in difficulty.

You’ll have to judge this for yourself.

Part #2 – calculate a priority score: Now you’re left with a list of keywords that would be both good and realistic to rank for.

They should all be keywords you would target if you had enough time.

This is where the math comes in.

We’ll use the following formula:

(A*Traffic) / (B*Competition) = Priority Score

A and B are both constants that we’ll figure out in a second. Traffic and competition both come from your earlier numbers.

A high priority score is a good thing. The higher the score, the sooner you should target it.

The constants can be anything, but they mainly depend on two things:

  • Risk tolerance – if you’re willing to take a risk and go for high volume keywords (that require more resources to target) make “A” larger. If you want more reliable results (small wins), make “B” larger.
  • Skill level – if you’re an expert SEO, you can decrease “B” because competition isn’t as scary. If you’re not as experienced, make “B” larger.

Before you do this, I’d advise to normalize your traffic numbers. You should do this since competition is already normalized from 1 to 10 (or to 5).

To do so, take the logarithm of each number. For example:

  • log(1,000)=3
  • log(50)=1.69

Then, multiply each of these numbers by a scaling factor that is equal to 10 (or 5) divided by the largest number you have. If you only had the two examples above, the scaling factor would be equal to 3.33 (10 divided by 3).

Now all your traffic numbers are out of 10, and you’ll get a more reasonable set of priority scores.

Sort your list and get to work: You’ve done all the hard work. The last step is to sort your final list by the priority score, highest to lowest.

Now, plan your content and promotion schedule according to this list. Start at the keyword with the highest priority score, and work your way down.


As you can see, keyword analysis isn’t incredibly difficult, but it takes a lot of work.

While you may want to take shortcuts, don’t.

Getting your analysis right will save you from chasing the wrong keywords and wasting hundreds of hours, and it will help you target keywords that will give you the quickest results.

If you have any questions about keyword analysis, just leave me a comment below.

Source: quicksprout

The 4 Steps to Keyword Analysis: How to Prioritize Your Resources

The Complete Guide to the Google Analytics Add-on for Google Sheets

google analytics

SEOs and marketers always have a lot on their plates.

That’s why we care so much about tools that save us time.

Any chance you get to automate some of your work is one that you should take.

That’s why when I first came across the Google Analytics add-on for Google Sheets, I knew I had to share it with you in a post.

What exactly is this add-on, and why is it useful? I’m going to assume you know what Google Analytics is.

But you may not know what Google Sheets is. It’s essentially the free spreadsheet competitor to Excel that Google has developed over the years.

The best part is your spreadsheets can live in the cloud and be worked on by multiple people at the same time.

The add-on I’ll show you how to use allows you to pull data from your Google Analytics account using the API and create reports with it.

Not only that, you can re-run these reports at any time.

That’s really powerful because once you create a report, you don’t have to spend time remaking it.

Whether you work for clients or do marketing for an internal team, you can generate these reports on a regular basis for your meetings and progress reviews.

Why would you want this? If it’s not clear yet, it will be soon. Playing with data in Google Analytics is fine, but it’s not the most usable interface.

Compare that to a spreadsheet, where you can use a ton of different functions (like filtering, custom graphing, etc.) on the data you retrieve.

Additionally, it’s really easy to generate those reports on a regular basis and make improvements whenever you’d like.

At this point, you should know if this add-on is going to make your life easier or not. If you know it will, keep reading on, and I’ll show you the ins and outs of it. 

Step 1: Install the add-on

Installing the add-on is easy.

Start by opening a new Google Sheet.

Then, click on the “add-ons” menu option at the top, and choose “get add-ons.”

A new window will pop up. Type in “Google analytics” into the search box at the top right side, then press “Enter.”


There should be one obvious add-on with the Google Analytics name and symbol. Click it, then press the “+free” button on the next window to install it.


The add-on should now be installed for use with all your future sheets.

Click on the “add-ons” menu again, and you should see a new listing for “Google Analytics.”


If you don’t see it there, you may have to refresh the page.

Finally, you should get a pop-up at some point, telling you the link to the support forum, but if you didn’t get it, here’s the link. If the add-on is not working correctly, that’s where you should post your questions.

Step 2: Create your first report

This add-on, while it should simplify your life, can actually be a little overwhelming if you dive right in.

In this section, we’ll create an example report and go over the basic settings and options you have.

Start by going back to the Google Analytics option in the “add-ons” menu, and this time, click on “Create new report.”


Once you do that, a menu like the one below should show up on the right hand side of your screen:


In order for this to work, you need to be signed in (in Sheets) to the same Google account that you use for Google Analytics.

The first few settings are obvious: give your report a title, and choose the website (property) that you want to analyze.

The metrics and dimensions are where things get interesting.

Metrics, or key performance indicators (KPIs), are the heart of most marketing reports. I wrote a detailed post on the 14 most common metrics for SEOs that you might want to refer to now.

Many of those metrics can be found in Google Analytics:

  • Traffic
  • Average time on page
  • Pages per visitor
  • Bounce rate

When you click on the “metrics” field, a list will appear with a huge variety of metrics. You can choose any metric you’d like for now, but I’m going to start with “users.”

While you’ll probably want to choose more than one metric for your actual reports later on, one is fine for now.

The last field is the dimension field. In Google Analytics, you can filter data based on things like source, referral path, keyword, and so on. That’s what dimensions are here—they allow you to segment your reported data.

For our example, pick any dimension you want, or leave it blank.

Then, finish off by clicking “create report.”

After a few seconds, you should see something like this:


Here’s the confusing part: This didn’t actually create the report that most people would expect. Instead, it just created the instructions that the add-on needs to run the report and pull data from your Analytics account.

Let’s actually run the report: Now go back to the “add-ons” menu, but this time, click on “run reports.”

This will run all the reports you set up in the active spreadsheet, but since we only have one for now, it’ll do just that one.

A few seconds later, you’ll get a confirmation box, saying the report was run. And at the bottom, a new tab will appear:


Click the tab, and you should see the data in the report, as expected:


This will match your Google Analytics data, but feel free to double check.

You can create as many reports as you’d like. The settings will all be stored in the main tab. When you run your reports, you’ll get a tab for each report (you won’t get a new tab if the report has already been run before).

Editing reports: On the original “report configuration” tab, your report settings will always be available to be edited.

You can change dates, add and remove metrics or dimensions, and even add things like filters, which I’ll go into next.

To add more than one metric to a report, you’ll need to select the metric box, put the cursor at the end, and then press “alt + enter” to create a new line. Then type in the new metric as usual.

Step 3: Understand all the different options

Congratulations, you’ve run your first report!

But that’s just the tip of the iceberg because there are a ton of different combinations and options in this add-on that you should be aware of.

Let’s go through all the fields in the main report configuration tab, one by one. You need to know what each of them does and how you can use them:

  • Report name – Just a quick note: if you delete the name in this cell, the report will not be run when you run your reports. This name will show up at the top of each report, but it will also be the label of the report sheet at the bottom of your spreadsheet.
  • View (profile) ID – That’s the ID of your Analytics property that data is being pulled from. It will be pulled automatically when you create the report. However, you could duplicate reports for multiple sites by copy/pasting the rest of the cells and changing this value.
  • Start date/End date – You can specify the date range that the data is pulled from.
  • Last N days – You can also specify to just pull data from the last “N” number of days, where N is any number you input into the cell. Note that you can use either this option or the start/end date option—not both.
  • Metrics – You can add multiple metrics for each report. You can get a full list of the different metric labels here so that you can just edit the configuration instead of creating a new report every single time.
  • Dimensions – You use these to segment your traffic to get metrics separated for each type of user. However, dimensions need to be compatible with the metrics in your report; otherwise, they won’t work. If you’re just typing in dimensions, go to that list of metrics, and select either a dimension or metric to see which ones are compatible.


  • Sort – You can set up the report to automatically sort the results if you find yourself wasting time doing that manually. You’ll have to manually input the metric or dimension here that you want to sort by (e.g., “ga:sessions”). You can sort in reverse by putting a minus sign in front (e.g., “ga:sessions”).
  • Filters – You can use filters to remove certain parts of your traffic that you don’t want to see. For example, if you didn’t want to include referral traffic in your report, you’d enter “ga:medium%3D%3Dreferral” in this box. Refer to the “filter syntax” and “filter operators” on this page to see what’s available.
  • Segment – This is true segmenting, allowing you to look at a specific section of data. To use this field, you’ll need to enter a value like “sessions::condition::ga:medium%3D%3Dreferral.” You can find more examples here.
  • Sampling level – There are three acceptable values here: “DEFAULT,” “FASTER,” or “HIGHER_PRECISION.” For most metrics, the default value (of “DEFAULT”) is fine. If the report is taking too long, choose “FASTER” to sacrifice accuracy for speed.
  • Start index – If for some reason you want to ignore the first “X” results, you can do so by specifying a start index. For example, if you type in 5 here, the first 4 results will not be shown.
  • Max results – You can choose the number of results to be returned in your reports, up to 10,000. By default, you’ll get 1,000.
  • Spreadsheet URL – If you want your report data to be sent to a different spreadsheet for any reason (e.g., if you have a sheet for a specific client already), you can just enter the URL of the file where the report should go.

I know that was a lot, but struggle through it, and you’ll have everything you need to get going.

When you consider all these different fields, you can create just about any custom report you want. Be prepared for reports to fail if you’re adding many values to them. Just add them one at a time, and tweak them until they work (test each time you add one).

Step 4: Create reports that are actually useful

At this point, you have a pretty solid understanding of what the add-on is all about and how to use it.

It’s time to create reports that you’ll actually use on a regular basis—that’s the whole purpose of this exercise.

Although you might be good from here, let’s outline the general steps:

  1. Decide which metrics you want to measure
  2. Decide which segments you want to analyze
  3. Create the report
  4. *Run the report periodically
  5. Manipulate the results (sort, filter, graph) as needed

I put a star by #4 because there’s an alternative. If you haven’t noticed yet, you have a third option when you go to the add-on in the menu called “Schedule reports.”


With the scheduling feature, you can have reports run automatically every hour, day, month, etc.—basically, whenever you want.


Saving time and being able to create consistent reports from your analytics data are both important things for marketers.

If you create reports from Google Analytics on a regular basis, you’ll likely benefit from giving this add-on for Google Sheets a try.

Once you’ve created a report, you can then make charts from the data or share the data directly with your client (if you don’t want them messing around in Google Analytics).

If you have any questions about this add-on or tips on using it more effectively, please share them in the comment section below.

Source: quicksprout

The Complete Guide to the Google Analytics Add-on for Google Sheets