An SEO Beginner Link Analysis Strategy

Starting work on a new website, or reviewing work done on an existing client site, is an exciting and daunting task. The importance of a link analysis on the top pages of a website cannot be overstated, because by doing so you gain important insights into why a website is ranking, the current strength of the page’s links, and how the page got to where it currently stands. Of course, doing a link analysis can also uncover potential problems, such as previous spammy tactics, that have been taken that you should be aware of, in order to clean up the profile or refocus pages.

Tools Needed

First, let’s talk about the tools you will need to conduct a thorough link analysis for your website’s top pages.

SEOmoz’s OpenSiteExplorer – you will want Pro-level access so that you can export the top 10,000 links for a page (if your pages have that many links).
SEMrush (a basic account will give you the top terms that your page URL is ranking for. A premium account is $49/month and could be worth the investment if you will be doing more frequent link analyses (if you are working in an agency environment, for example).
Microsoft Excel with some Ninja skills.

My Process

Use OpenSiteExplorer to find the Top Pages

First I use OSE to find top linked-to pages. For this example, let’s do the top 10 pages on the website. Once you enter your site URL and run the report, you will see a Top Pages tab on the report. Click that tab. This report shows you the top linked-to pages on your site. Your homepage should be in the Top 10. If it is not, include it as one of your top 10. Export this to an Excel spreadsheet so that you have a record of it.

Use SEMrush to find the top rankings for each of your top 10 pages

Now I go to SEMrush to find the rankings and terms for each of the site’s top pages. Enter the site URL. The returned report will tell you the rankings for each page. Unfortunately, the report that is returned to you will have all of your site’s URLs mixed in, so you are going to have to do some clean-up on the data in Excel to find the results you need.

Export the data to Excel. Once the data is in Excel, copy and paste the exported data into a new tab on the Top Pages spreadsheet. Or, you can create a new tab on your SEMrush Excel page and copy/paste your top 10 linked-to pages into that tab. Now, we need to do some data scrubbing to find the information you need. Here are some steps to take to scrub your data:

Sort your URLs alphabetically.
Copy this data to a new worksheet (so that you don’t lose all of your other URLs once we start filtering)
After setting your column heads to “Filter”, unselect the URLs you want to keep.
Highlight all of the exposed data (your non-Top 10 URLs), right click, and Delete.
Now go back to your header and Select All. Now you are left with your URLs, anchor texts, linking pages, target pages, and all of the good metrics you have been provided.
Now create a new spreadsheet. You will need two worksheets:
- Top Pages and Keywords
- SEMrush Dirty Data
Now you have one of two choices. You can copy the keywords, rankings, and URLs into “Top Pages and Keywords” using some Excel functions. Since our URLs are alphabetized already, we can pull out the keywords fairly easily. But for the sake simplicity, copy and paste your keywords and their rankings to “Top Pages and Keywords.” Here’s how I organize it:

Once you have the rankings for your Top 10 pages, arrange these in a new tab on your spreadsheet and save them for later.

Use OpenSiteExplorer to export links for each top page

Next, return to OpenSiteExplorer to find and export the top 10,000 links for your page. You are going to want to run a couple of different reports. You do not want to filter out the followed or no-followed links. You want a full view of the site’s links.

Report 1: Internal Pages Linking

First, narrow your report to “Internal Linking Pages Only” and “This Page Only”. This will return all pages on your website that are linking to the page, as well as the anchor text used to link to the page. Export this report to Excel.

Report 2: External Pages Linking

Next, change your report criteria to “External Linking Pages Only”. Run the report, which will return you the top 10,000 external pages linking to your site. This report will also give you anchor text, Page Authority, and other important metrics for the linking page. Export this report to Excel.

Now you have 20 different Excel spreadsheets, two for each page. Consolidate the data to one Excel sheet per page, using tabs. I name mine “External” and “Internal.” Choose a naming convention that works for you.

I now like to sort the Anchor Text column alphabetically, so that when I generate the graph to see how balanced my links are, they will appear alphabetically.

Now create a third tab on your spreadsheet containing both your Internal and External pages. Pull this tab to be first in the order. Name it “Calculations” or some such nonsense. Using your SEMrush data for the keywords your page is ranking for, pull out the number of exact and inexact anchor text links that you page has from external and internal pages.

To do this, put the following formula into cell A2, under the heading “External Exact Links”:

=COUNTIF(“Sheet”!”AnchorTextRow”, “[AnchorText1]”>

*You will want to replace (“Sheet”) with the name of the worksheet from which you are calling the information. This will look like (Internal!). Replace (“AnchorTextRow”) with the row where your Anchor Text is listed (will look like A:A) and (“[AnchorText1]”) with your keyword (will look like “[Little Blue Widgets]”. Your whole formula should look like =COUNTIF(Internal!A:A, “Little Blue Widgets”)*

And this under the heading “External Inexact Links”:

=COUNTIF(“Sheet”!”AnchorTextRow”, “*[AnchorText1]*”>

Here is my calculations tab using the above functions:

Report 3: Top 10,000 links for the entire root domain

You may also want to have an export of the top 10,000 links for the entire root domain, using OpenSiteExplorer. This will give you the data to have a view of your overall site’s link profile.

Visualizing the data

For this part, you are going to need a basic understanding of how to create graphs in Excel. If you do not know how to do this, I recommend reading this post from the SEOmoz blog about Visualizing Link Data.

Let’s create some graphs now to see what is going on with our data and where we need to improve.

Highlight all of your data and go to Insert -> Chart. I choose a bar graph, because then I can plot my X and Y axes. Since all of your data (including your column headers) is selected, the data range will be correct. Plot your X (horizontal) as “Internal/External Exact/Inexact” and your Y axis as “URL/Keyword”. Now you can tell which keywords have the most Exact and Inexact keywords. You can also deduce from this graph how strong your brand is, by the number of direct keywords using your brand, and how strong your exact match keyword links are, both externally and internally.

Here’s what I come out with:

From the play data I’m using above, you can see that “SEO” is optimized fairly well externally from inexact match, and for internal inexact. However, it is not present for any exact-match links. If I was targeting “SEO” as a keyword, I would want to try to remedy this.

You can do a lot more with the data given as well. With your data and a few Excel ninja skills, you can find percentages of exact vs inexact anchor text links, both internally and externally, and plot them on a bar graph. This is just one of the ways you can visualize your data to help you know what you have, and therefore what you need.

Go dig into some data! And read Mike’s Excel Guide for SEOs for a great intro into writing Excel functions. His lessons are helping me to clean up my Excel act and take my Excel and SEO data skills to the next level.