There is a vital tool SEOs don’t talk about:
Microsoft Excel. Or Google Spreadsheet.
I’m a big fan of both.
You will never hear this when SEOs tell you the tools they use.
But smart SEOs use it.
No matter what color hat of SEO you are.
Or, White hat.
You need a spreadsheet to build a killer SEO campaign.
Why do you need spreadsheets?
Various essential SEO tools like Ahrefs, Moz, Majestic, Screaming Frog, Google Search Console and Google Analytics gives the data you need to make good decisions.
But these data are scattered across these various tools.
It’s difficult to make wise decisions when the reports aren’t in one place where you can easily view and analyze them.
It’s difficult to prioritize.
Spreadsheets make the life of an SEO easier.
You make smart decisions with them.
How To Use Spreadsheets To Increase The ROI Of Your SEO Campaign
Rankings are good.
They are the goals of an SEO.
But I don’t like to talk about rankings.
Pure rankings are worthless.
You need traffic. Not just rankings.
Not just traffic. Traffic that produces revenue.
Traffic that produces better ROI.
Where do you start?
I’ll also assume that you have (or you’re managing) a website that currently ranks on Google.
Maybe it’s not the kind of rankings you want.
You probably want more and better visitors from search engines.
Okay, I’m using Google Spreadsheets here.
In case you still don’t know the basics, these are columns A, B, C, and so on.
Now, you’re going to build up this spreadsheet by adding information that will help you run better SEO campaigns.
So, what’s next?
It’s time to start building columns.
This is the first column.
Keywords are the most important element for every search engine.
Choosing the right keywords to optimize for is crucial to a successful SEO campaign.
But I’ll be the first to admit that keyword-focused search engine optimization is dying pretty quickly.
For example, I visited a local plumbing website and saw this:
“Our NYC Plumbing Company is a trusted New York plumbing company name, known for quality, professional NYC plumbers and superior work. We offer prompt expert plumbing service from our Plumbers in NYC for your home or business, with available same day and emergency plumbing repairs.”
Below is the screenshot if you want proof:
How many times do they have to mention “plumbers,” “plumbing” before I know that is what they do?
How many times do they have to repeat “NYC” before I know that is where they are located?
This is outright spam. And it looks awkward.
If you’ll be plastering your keywords everywhere on your web page, that is not the kind of smart SEO I’m talking about here.
Keywords remain important.
Keyword-focused SEO might be dying but search engines still look for key phrases on your web pages and the meaning behind them – that is why you need keywords.
You need keywords to help search bots understand your contents better.
Keywords are also the language searchers use.
I may want to target a keyword like “SEO marketing.”
I just add that to my Google Spreadsheet.
The Monthly Search Volume
You need a keyword with a decent search volume.
If a keyword only receives a few tens of searches per month, it may not be worth targeting. You may want to target a keyword with hundreds, or even thousands of searches per month.
According to Google Keyword Planner, “SEO marketing” receives thousands of searches per month.
I created the column “# of searches” and put the search volume there.
Note that if your business is based in a specific place, and it only serves people in that location, you should target this city when using Google Keyword Planner.
For example, if your business is based in New York, and you only serve the people there, you should put that in the filter.
Below is what I mean:
This says “All locations” which also mean searches made worldwide.
Now, change that to New York:
There will be changes in the number of searches after doing this.
For example, Google Keyword Planner reports that the keyword “SEO marketing” receives about 9,900 searches per month.
But after changing the location to “New York,” it now shows that this keyword receives 320 searches per month.
This targeting is very important for local businesses. Keep this in mind.
How Important Is The Keyword To Your Business?
It’s not every keyword that is relevant to your business would generate highly-targeted visitors that would convert.
Let’s assume that you’re a plumber based in Brooklyn, and you only serve Brooklyn.
Now you want to choose between keywords “Brooklyn plumber” and “New York plumber.”
Your time is better spent ranking for “Brooklyn plumber” because it’s likely to produce better visitors.
A keyword could be receiving thousands of searches per month and produce terrible conversions on your website.
You need more visitors converting into customers rather than thousands of visitors hitting your website, and less converting into customers.
I normally grade a keyword on the scale of 1 to 3.
This is just how I do it. Yours could be different.
A grade of 1 means the keyword is not really relevant to my business but could still create more awareness for my product/services.
For example, let’s say I run a swimming pool business that’s based in California, I could target keywords like “swimming workouts” which receives about 14,800 average monthly searches, in the United States.
This keyword will generate a lot of visitors if I can rank in one of the top positions. But most of the traffic generated from this keyword wouldn’t convert into business.
It’s because most people searching for swimming workouts aren’t looking to build a swimming pool.
And also, most of these people aren’t even based in California.
But this keyword will create more awareness for my business.
Someone who landed on my swimming workout page might have a friend who lives somewhere in California and needs a swimming pool. People like this could refer their friends to my business.
I’ll give this keyword a grade of 1 because it only creates more awareness. Not really business.
Now, let’s talk about a keyword like “California swimming pool.”
I’ll give this keyword a grade of 2 because this person shows a strong intent that they a somehow interested in swimming pools in California.
While I can’t say if the searcher who types this keyword into Google is looking to build a swimming pool in California, the fact that they use the state name in the phrase means my website should be showing up for this keyword.
My grade 2 keywords aren’t highly targeted visitors, but they are much better than keywords that merely generate awareness.
A keyword like “pools in California” is another I’ll give a grade 2.
The keywords I rate grade 3 are the perfect match for my business. There are much likely to convert into sales.
I’ll continue using the California business example.
A keyword like “California pool builders” is a great keyword I should be ranking for.
People who type this keyword are looking for a pool builder in California, which is exactly what I do.
Keywords like “pool builders San Diego” and “pool builder Los Angeles” also fall within the grade 3 category because I serve the whole California.
You can use this model of grading keywords, or you can create a better one that suits your business. There’s no automated tool for this, and you don’t need it if there is ever one because it’s much of a manual thing.
The rule here is never to target grade 2 keywords when they are lots of grade 3 keywords I could rank for.
Also, I never target keywords in the grade 1 when they are grade 2 keywords I could rank for.
The example above shows how I prioritize keywords to target on search engines.
The idea might look simple, but it’s a powerful way of generating lots of traffic that lead to sales.
Each web page on your site should have at least one keyword (or a set of keywords) it’s targeting.
Note that I don’t advise that you always target keywords in every content you publish.
But there should always be pages which are purposely made to get traffic from search engines. That’s how smart SEOs work.
Adding the URL column helps you set which URL is targeting which keyword.
This helps you better optimize your campaign when you go out there doing off-page SEO for your site.
There will be times when you see that a particular page is ranking highly for a keyword you aren’t even targeting on the page.
You may want to make a page targets the keyword. Or, you could decide to improve your current page to targets it better.
What I call engagement can be completely different from what another website owner calls engagement.
I personally track engagement by the average time users spend reading a page, and the bounce rate percentage it has.
I’ve seen website owners who measure engagement with how many social shares their contents get.
I don’t see anything wrong with this conflicting views on engagement.
I think it’s all down to the goal of the site owner.
A high 70% bounce rate and a low dwell time are a bad engagement for me.
If people are frequently bouncing off my page and aren’t reading my content, it means that I have a lot more work to do to improve the page.
Bounce rate and dwell time affect SEO.
Search engines use them to evaluate how much their users like your website.
You can get both bounce rate and dwell time from Google Analytics if you’ve installed it on your website.
Add this to the column, and input the engagement metrics for every published content on your website.
For example, a page on one of my niche sites has a dwell time of 7 minutes and 35 seconds, and a bounce rate of 95%.
This particular page doesn’t have much content on it, and its goal is to convert visitors. Hence, the reason for the high bounce rate.
Internal links are often overlooked.
All SEOs care about is backlinks.
But internal links are easy to make, manage and most of all they improve SEO and conversions.
In case you don’t know what they mean, internal links are simply links from one page on your site to another page on your site
Internal links help you pass authority from one page to another on your site. Link juice from a high authority page could boost the rankings of underperforming pages.
They guide users to high-value, high-converting pages on your website.
Some pages on your site have more authority than other pages. You have to recognize this high-authority pages and ensure that they are linking to others.
I use Moz Open Site Explorer to determine high-authority pages on my website.
For example, I took a look at the website of a local business like Case Design, a home improvement company in Maryland.
I want to recognize their authority pages, so I put their URL into the box at Open Site Explorer.
I clicked “Top Pages.”
Then I can see the authority pages.
As you can see on the above screenshot, these pages have high page authority and had received lots of links.
There are some pages on the site that have zero links. By linking to them, they would be increasing their authority and performance on search engines.
I am sure you’ve heard and read a lot about backlinks. Almost every SEO out there talks about them.
It’s because they are very important when trying to rank on search engines.
Your website may not be performing as much as you hope because you have very few high-quality backlinks when compared to competitors.
You should have a column for this.
How many backlinks do you have to a web page?
Put it there.
Make sure you exclude every low-quality link from the number.
Low-quality links from comments, social bookmarks, among others shouldn’t be included in the number.
High-quality links only.
Maybe a web page currently has 10 links, how about if you can get it 10 more links this month?
That will definitely increase its rankings.
Google Desktop and Mobile Rankings
Rankings positions on desktop and mobile can be different due to the mobile usability of a website.
Some sites are more mobile friendly, so they tend to perform better on mobile due to how much mobile users find them useful.
So, I’ll advise that you put your desktop and mobile ranking positions on separate columns in your Google spreadsheet.
Ahrefs’ Rank Tracker allows me to track the ranking positions of my web pages on both desktop and mobile search results.
Google Search Console click-through rates
I’m a big fan of Google search console (formerly Google Webmasters Tools).
One of the places I immediately go to when I sign in is the “Search Analytics” report under “Search Traffic.”
I look at the click-through rates of my listing for each keyword.
To Google, better click-throughs mean a page is getting attention. Google will want to improve this page on its search results.
Create a column for this in your Google spreadsheet.
You’ll be tracking this metric to see what changes at the end of the week, month or quarter.
Now, What Do You Do With All These Data?
After putting data in each column for every page on your website, what do you do with it?
Should you leave the spreadsheet there and move on?
If you’ve done this boring work, it should lead to something. That is… to improve your rankings across the board.
Here are the reasons why you need this spreadsheet:
- To identify content without intentional keyword targeting. Your website might be strongly showing up for a keyword you didn’t target at all. You may want to build a better and high-quality content to target that keyword. It shows your site will find it easier to rank for the keyword.
- To identify on-page opportunities to improve. With a spreadsheet, you’ll find some keywords where your website is under-performing due to low-quality contents. You may want to improve your pages to increase your rankings.
- To identify keywords that have no content mapped to them. You’ll discover good keywords you aren’t targeting too. You may want to build contents for them in the future, but except you put them into the spreadsheet, you could easily forget and miss out on big opportunities to gain more search traffic.
- To identify link building need. There will always be some keywords where the competitors don’t have many links than you. By getting a few more high-quality links, you could easily overtake competitors in search engines. A spreadsheet lets you recognize these opportunities by labeling them. So when next you’re building links to your website, you know which pages you should be sending links.
- To prioritize and focus your work. Doing this and that won’t take your SEO campaign forward. You need to be organized, know what to do, when and how. Keeping a spreadsheet for your SEO campaign will make you more effective and efficient.
A spreadsheet is an invaluable tool to every SEO.
Thanks for reading and please share this article with your friends on social media.