The first step to using the Ultimate On-Page SEO Template is installing the necessary add-in for Microsoft Excel. Bing Ads Intelligence, a pretty sweet keyword research tool, is recommended for this template but not necessary. However, the add-in that will make your life significantly easier is SEOtools for Excel, by Niels Bosma.
(Be sure to watch the installation video on the download page, as it can be a bit tricky if you haven’t worked with Excel add-ins before.)
Confirm that SEOtools for Excel is installed correctly: If it is, you’ll see a “SEOtools” link on the primary navigation bar of Microsoft Excel.Next, download my template! Or, watch the demo video
The first tab, “SEO Audit,” serves as a benchmark for your SEO project. Following the tool tips using your newly installed add-ins will provide key on-page and off-page information about your target domain.
You should have at least a smidgeon of an idea as to what the different header’s mean for SEO. If not, do a quick Google search and brush up on your know-how. Ultimately, this tab is to serve as a benchmark, upon which to be continually improved.
The “Keyword Research [Brainstorm]” tab is the first of two tabs that will be used to develop the seed list of keywords for your optimization campaign. Follow the tooltips and use the referenced web applications to get the off-the-top list of keywords.
With the four columns in this tab, do your best to get a sum total of 200 brainstormed keywords.
There isn’t any sense in reinventing the wheel, especially when certain parts of the wheel work already. The third tab, titled “Keyword Research [Visitors],” allows you to compare your target keywords with the keywords that are already being used to find content on your website. The more visits, the better.
Of all the tabs in this workbook, this one is the least necessary for a successful campaign. If you have any bit of trouble navigating your way through it, don’t hesitate to place it on the backburner until you have more time to focus.
Obviously, a seed list of 200 keywords is going to be tough to incorporate within your site in its entirety. Instead, we’ll use the “Keyword Analysis” tab to cut the keyword list down to a selective few. Though we’ll most likely need to find a middle ground, the ideal keywords are those that are heavily searched each month without many search results. That way, your pages will quickly index and rank properly, and you’ll be sure to gain a decent amount of traffic.
This tab calculates two scores for each keyword: Keyword Efficiency Index (KEI) and a “Total Score.” The KEI is a once standard formula for calculating the effectiveness of a keyword; however, it’s a cinch to find half a dozen naysayers with a quick Google search. The “Total Score” can be combined with the KEI to improve the accuracy of determining keyword effectiveness.
Once you’ve filled-out the tab, highlight the information and sort by KEI. Delete any row with an index lower than 100. Next, sort by the Total Score. The top 10-15 keywords—barring any newly-discovered relevance—are your targets.
Tie your target keywords with URLs of the pages you’d like to optimize. Following the columns in the “Content Optimization” tab from left-to-right, fill out the information to properly update your page elements—including meta-descriptions, headers, and the like.
The goal of this tab is to incorporate your target keywords into the different on-page elements of your website. One rule of thumb is to use the keywords as early in the element as possible. In other words, for the keyword “cookies,” the header “We make delicious cookies!” isn’t as effective as “Cookies are our specialty!”
On to the “Tracking (Rank)” tab! This tab will be used on an ongoing basis to track and improve your SEO strategies.
The first column is for your target keywords. Just paste them, and use RankChecker once a month to fill out the rest of the information.
Take a temperature check at the start of the project, set your goals, and do a quick 5-minute copy-and-paste from your analytics account as often as you’d like. (The more often, the more sincere your data will be.)
Once you’ve populated the Ultimate On-page SEO Template and pasted in your initial benchmark data for future tracking, you’re ready to implement your changes. We’re talking logging into your content management system (CMS) and pasting the new titles, headers, URLs, and H-tags in their appropriate locations. This can be an incredibly tedious process, but if you’ve done your due diligence, the payoff will be worthwhile.