Keyword tracking with Google Sheets

Having a major SEO monitoring platform like Brightedge or Conductor is great. They come with integrated rank tracking. Plus, they have interesting interpretations of that data. A strict rank tracker won’t provide that.

These insights come at a cost. Lots of costs, actually:

• Money – the monitoring platforms are expensive.

• Time – a good configuration job takes time. It implies long-term commitment.

• Inertia – it’s harder to get data out than in. We analyze within the tool. This means long-term commitment.

What if we could get the best of these analyses and avoid these burdens? Say we’re working with a small site. Or we’re working with a small section of a site. Or we’re trying choices on for size. Or that we can’t afford to shell out!

We want the power, without the pitfalls. With a bit of elbow-grease, we can get there. Let’s walk through a spreadsheet I use every day. It does some things you’d expect from the big platforms, without the cost.

Five common monitoring questions we can answer without expensive tools

Here are five questions monitoring platforms answer. They’re simple enough. And often they go unasked if we don’t have a platform handy:

1. Do we rank for what we’re targeting?
   We’ve chosen some terms. Do we show up?

2. Does the right content rank?
   The ranking page for a term is sometimes wrong. If it is, we could have technical or information architecture problems.

3. How have our rankings changed?
   In the last week? The last month?

4. How would traffic increase with a higher ranking?
   Let’s set a ranking goal. Sometimes we hope for a #1 ranking. Other times we know #4 is the highest we can hope for. If we get there, what reward will we have?

5. Which ranking changes matter?
   For big terms, small changes make a difference. A top ranking for a minor term often doesn’t. Which changes in ranking should we focus on?

Let’s see how to answer these questions.

What do we need?

Before we start answering, we do need data to dig into. Here’s what we need:

From rank tracker	From Us
Rank today	Keyword
Rank 7 days ago	Landing page mapped to each keyword
Rank 30 days ago	Target ranking for each keyword
Estitmated search volume	Anticipated CTR at each rank

I’ve set up the spreadsheet to use exports from rank tracker Stat. With some work, we could use other sources — Moz or AuthorityLabs, for instance. The things in the “from us” column might not make sense yet. read on, we’ll clarify.

Getting the data into the sheet

Ranking data

We’ve exported this data from Stat. It’s pasted in the “Paste data here” tab. With some tweaking, you can use data from any source.

The sheet basis a lot of its analysis on the data pasted here. For instance, it treats the most recent date in this table as “today”. Any data from “7 days ago” or “30 days ago” is, therefore, relative to that date. That also means there’s no value in putting more than 30 days of data in here!

Our data

Keywords

The spreadsheet will only calculate information for terms manually added to this list. This is helpful if your export includes many terms. Google Sheets lacks the horsepower of Excel! Of course, the terms must also be in the export from the ranking tool.

Landing pages mapped to each keyword

Put your intended landing page here. This is basically the data field your rank tracker doesn’t have. It’s the difference between the monitoring platforms and the rank trackers. It’s a fine line to draw.

Target ranking

This allows a little extra analysis, as we’ll see. With your SEO hat on, where do you think your site ought to rank?

Anticipated CTR at each rank

Any CTR table is fine. This one is generated by a mathematical formula that has approximately correct characteristics. Some of the monitoring platforms allow you to select different models. In practice, that doesn’t matter. What’s important is the shape of the curve. Adding any more detail assumes more detail than we have a right to.

Answering our questions

Let’s look at the calculations necessary to answer our questions. The sheet puts all the calculations in one place. This is convenient because:

• We need similar data to answer each question.

• We often ask these questions at the same time.

• Updating one sheet is simple. Even if updating it requires a copy and paste.

Each calculation is straightforward. None take more than a VLOOKUP. Follow along in the sheet while reading — it’ll keep things concrete. And if you still feel confused, take a look at our Excel for SEOs guide. It’s plenty applicable for Sheets, too.

Do we rank for what we’re targeting?

This is the basic question. If this was all we needed, we could’ve stuck with the rank tracker! However, this is the basis for answering the rest of our questions. Fortunately, it’s easy to do.

Does the right content rank?

This is one of the simplest features that integrated platforms provide. In addition to specifying terms, they allow you to associate a desired landing page with a term.

We need to look at:

• The keyword

• The page we say we want to rank for this keyword

• The page that is actually ranking for this keyword

The “page we want to rank”, like the keyword, is hard-coded into the sheet. This is the definitive place that information is stored. That’s usually fine because the point of this is to be able to do a lightweight analysis. That said, there’s no reason the data couldn’t be stored somewhere else and pulled in for this.

In this case, I’m using conditional formatting to shade the Ranking URL column red if an undesirable page is ranking.

 

How have our rankings changed?

This builds on the previous step. If we know how we rank today, and 7 days ago, we can compare our rankings on each of these days. Again, this is the sort of thing you might be able to get within your ranking platform. Not always — a surprising number of them don’t make this data easily accessible in the default dashboard.

 

How much would better rankings increase traffic?

We can often get a pretty good idea where a client could reasonably rank. For instance, say we were recommending long-tail pages for them to create. We can eyeball various SERPs, looking at things like the DA and PA of sites that are already there, and guess at where the new pages will land.

In the last step, we estimated potential traffic based on current ranking. We could just as easily estimate potential traffic for this hypothetical ranking.

By taking the difference of potential traffic and estimated current traffic, we get an idea of what the client stands to gain.

This is one of the most straightforward techniques for justifying investment in SEO to clients.

 

Which changes in ranking have impacted us the most?

This is an analysis I love. It uses basically the same calculation as the previous section. The difference is it deals with two real numbers instead of hypothetical ones.

We try to keep our clients from looking at rankings too often. Sometimes they insist, and sometimes it really is appropriate to be working with them on a weekly basis.

Rankings move around a lot, even day to day. That means there is a lot of noise. If we’re going to productively have a conversation about ranking changes on a weekly basis, we need to focus on two or three changes that were the most impactful.

They way I do this is by calculating the amount of traffic that we could have received in our position 30 days ago. I also calculate estimated traffic for today. Then I take the difference between these sums.

It’s a simple enough calculation. That said, it really brings to the fore changes that have had a big impact. I find the two or three terms that stand out and focus the conversation on those.

 

Next steps

Have a play with the sheet. Honestly, that’ll be much more informative than reading my wordy descriptions. If nothing else, understand that this is what you’re paying for when you buy into a monitoring platform. If it sounds hard, there are folks out there that provide this service!

We’re always interested to see solution to other micro-problems. I’d love to see any that you all have to share!

GET THE SHEET