It’s important to stay ahead of the game and decent spreadsheet software such as Microsoft Excel is still regarded as one of the most empowering and flexible ways to track your SEO campaigns. In conjunction with important tools such as Google Analytics & Keyword Tools, Excel is able to help provide unrivalled analysis to aid the decision making processes for your business.

This article will provide you with some simple tips and tricks to help you reap rewards in terms of visitors, CTR’s, conversions and conversations.

Keywords

When discussing SEO, it’d be impossible not to mention keywords. And with this, there are two sides to the story. Firstly, which keywords to settle upon and secondly, tracking their effectiveness.

Deciding which keywords to run with is a big decision. It needs to fit in with both your business goals and marketing strategy, leaving no room for error. Using an Excel spreadsheet as a tool to track your keyword ideas in conjunction with a combination of free and paid online tools, you’ll be able to save both time and money in the long term.

Two of the most popular tools include, Google Keyword Tool & Google Trends. Use the aids to provide insight as to the search volumes of your terms (both local and worldwide) and also to help with variations upon your keywords (e.g. if your main term is “Light Bulbs” you could find alternatives such as “Halogen Bulbs”, “Energy Saving Light Bulbs”).

Add your findings to your spreadsheet using columns

• Keyword
• Local Search Volume
• Worldwide Search Volume (if relevant)
• Competition (Low, Medium, High)
• Trend (You can calculate this however you wish)
• Additional Notes

This will give you a quick indicator as to which keywords your company should target. By using sorting and filtering you should start to see some patterns. Put simply, high competition and low search volume is more often than not, worth being left alone.

Once you’ve decided upon your specific search terms, you’ll need to track these too. Again, free tools are great. Rank Checker will provide you with a quick breakdown of where you appear in the three major search engines. Create a new spreadsheet, with the columns;

• Keyword
• Month
• Google Rank
• Yahoo Rank
• Bing Rank

I’d recommend doing a separate spreadsheet for each month, then you can always cross reference these in the future to analyse trends specific to your website. Alternatively, you could have one page for each, Google, Yahoo & Bing. Knowing which keywords are effective will provide you with insight as to where to channel your paid and organic campaigns.

Directories

If you’re a business with an emphasis on SEO (which to be honest, who isn’t these days?), you’re likely interested in spreading a wide net for your content. Submitting your posts to directories is a great way to increase the reach of your content, but merely submitting them shouldn’t be the end of it.

Measuring the effectiveness of an action or process is key, and there is no simpler or more effective method than creating an Excel spreadsheet. First, you’ll need to find those directories, if you haven’t already. A quick Google search will provide you with a comprehensive list for your specific needs. Once you’ve got the list, create an Excel document with columns for:

• Directory Name
• Date Link Submitted
• Date Link Confirmed
• Pricing
• Additional Info

Knowing where and when you submitted a post to a directory will save you time (and money) in the future, reducing the occurrence of duplication and ineffective listings. (If you are worried you have entered something twice, have a look at this post describing how to deduplicate data). It always pays to include an additional info column to remember login details and the like.

Expenses

Tracking your spend makes simple accounting sense. Excel is a fantastic way to note down where your SEO budget is going. From paid directories to PPC campaigns and everything in between. Your spreadsheet may include the following columns:

• Expense
• Average Monthly Cost
• Annual Cost
• Additional Notes

Now, this document in itself provides little more insight than where your money is going. However, when analysed in conjunction with conversation rates for example, Excel will allow you to decipher the exact effectiveness of a campaign per cost. It’s also a great “go to” document when making budgeting decisions.

Competitor Analysis

Of course, in order to stay one step ahead of the competition, you need to know what they’re up to. That’s just common sense, right?

Again, there are plenty of tools out there to help you track the numbers of backlinks to your site, paid and unpaid. Open Site Explorer is a handy tool to quickly scan a webpage and offers insight to domain authority, page rank and the links from other sites to your page.
Further to this, you can do exactly the same for your competitors. Where are their links coming from? Why aren’t these sources linking to you? You’ll soon be able to see what their site offers which yours doesn’t.

To make things easier to analyse trends, use your spreadsheet. See if there’s one domain that favours your content across any number of posts. For this you can add as many or as little columns as you desire, but you may wish to include;

• URL
• Total Links
• Linking Root Domains
• Page Authority
• Domain Authority
• Facebook Shares
• Tweets
• Google +1’s

It goes without saying that a link from a site with a higher domain authority will increase the likelihood of your content reaching a greater audience. The more links and shares that you receive can only be beneficial too for spreading your word.

To conclude, there’s a world of data out there which you can analyse. Excel can help you put these into graphs, charts, columns, rows and more to ensure your findings are digestible. It can also help you decide whether a particular SEO campaign has been successful in terms of return on investment and much more. We’ll discuss more of these in further posts, so stay tuned.

Interested in sharpening your own Excel skills so you can build your own SEO spreadsheet management dashboard? Attend an Excel training course from Best STL where you’ll learn about formulas across worksheets and how to manage large lists of data.