SEO · Tools

Visualizing Link Data With Screaming Frog and Excel Part 1

When Google rolled out penguin there was a tizzy online. Site owners freaked out, SEOs began running tests and searches to figure out what exactly happened. One of the characteristics that seemed to be popping up was over use of exact match anchor text. A few people had even gotten emails asking for the anchor text in their comments to be changed:

tweets on comment removal Visualizing Link Data With Screaming Frog and Excel Part 1View the story “Please remove my comments” on Storify

Since I don’t practice commenting for links (there is a right and wrong way I just don’t use this method) this wasn’t a big concern for me, and the backlink checks I ran were a relief. Nothing real heavy on any exact match anchors. But then I thought “What about the internal links?” We had been focusing on improving our internal linking on numerous sites the past year, had we gone overboard?

We use a variety of tools to measure backlinks, but the main one we tend to use more often than not is Open Site Explorer (OSE). There is an option in OSE to only check the internal links, but it doesn’t provide all of the information Screaming Frog will on the internal links.

I was beginning to think I would have to just use the data I had until I remembered I had just bought a full license to Screaming Frog. I was able to use the information provided by Screaming Frog to establish what anchors were used, how many times they were used and where they were directed at by also utilizing excel. When I finished this sorting, I then went back and sorted our external links using a similar method using OSE and then compared how we were using our external links against our internal links, I’ll cover that process in part two. Needless to say this provided some great information and insight, not only from a client perspective but from a competitive perspective as well.

But, before we start. What’s Screaming Frog?

What is Screaming Frog?

In a word? Amazing. This desktop application crawls a site from top to bottom (if you’ve purchased a full license otherwise it caps out at 500 URI) and provides just about everything about your site you could imagine to analyze. It’s really easy to get lost when you first fire this up, but let’s stay focused. By the end of this article we’ll have gone through the steps for visualizing the internal and external link data in addition to comparing the two. For now we’re only after the internal linking data.

What you need from Screaming Frog

The internal linking information is located in the “In Link” section located in the lower window:

sf screenshot 1 1 Visualizing Link Data With Screaming Frog and Excel Part 1

In this tab you’ll see the anchor text, what page the link is coming from, what the alt is (if an image) and if the link is no followed or not. The trouble is, this data is only exportable one page at a time. Meaning, you can’t export an entire site “In Link” data. You have to right click on the page you wish to look at then select export then select “In Link”:

sf screenshot 1 2 Visualizing Link Data With Screaming Frog and Excel Part 1

If you want to export only one page and then proceed to the excel portion of this tutorial then go ahead, if you wish to look at multiple pages (or the entire site) you need to create a folder to save all of the CSV files you’ll be exporting the in link files to. Each time you export a file you’ll have to give it a unique name. The default in link file name is “in_links.csv”, and saving one again without changing the file name will just replace the previous one.

This is a tedious process which is why I suggest breaking it down by section. If you just want to check the anchor text of the main navigation, let Screaming Frog run completely then go export only the main navigation sections. If you stop Screaming Frog prematurely it will only show the internal link data from just those pages crawled before it was stopped. Bigger sites will take some time, smaller sites not so much.

sf screenshot 1 3 Visualizing Link Data With Screaming Frog and Excel Part 1

Once you’ve gotten all of the CSV files you need in one folder you’re ready for the next part.

Combining the CSV Files Into One

Open the folder the items are in and click in the address bar to highlight the location of the folder, then copy and paste the highlighted address into notepad.

sf screenshot 1 4 637x213 Visualizing Link Data With Screaming Frog and Excel Part 1

Go to the start menu and search for “cmd” right click on the command prompt to run as administrator. In the command prompt and enter in the address you just pasted into notepad as shown below:

“cd C:\Users\name\file\file\name-of-the-folder-the-csvs-are-in”
(replace the bold with the path you have to the folder containing the .CSVs)

sf screenshot 1 5 637x101 Visualizing Link Data With Screaming Frog and Excel Part 1

After it shows the next line (the cursor should be beside the folder path now) type in:

copy *.csv allinlinks.csv
(replace the bold with a file name of your choice)

sf screenshot 1 6 637x319 Visualizing Link Data With Screaming Frog and Excel Part 1

Close out the command prompt and go to the folder you created to find a newly created .CSV file with every other file you exported from Screaming Frog into one.

sf screenshot 1 7 637x260 Visualizing Link Data With Screaming Frog and Excel Part 1

If you need a better guide on this step of the process check out this guide over at Solve Your Tech.

Sorting the Internal Link Data with Pivot Tables

Open up the new file and sort it by type to find all of the excess sorting information (delete these rows) at the very top and at the very bottom:


sf screenshot 1 8 Visualizing Link Data With Screaming Frog and Excel Part 1


sf screenshot 1 9 Visualizing Link Data With Screaming Frog and Excel Part 1

As a personal preference I remove the first column and delete the first row to show only the information I’m interested in. In addition I prefer to switch the names of the tabs and rearrange them:

sf screenshot 1 10 637x116 Visualizing Link Data With Screaming Frog and Excel Part 1

In my opinion this makes it easier when referencing back to this main sheet when looking at the overall anchor use. But we’ll get to that in a bit.

Next we’re going to create the three new worksheets. First click on insert then click on pivot table (Richard Baxter wrote a great tutorial on pivot tables that’s really helpful). Make sure the setting is set to open in a new worksheet and click okay.

sf screenshot 1 11 637x463 Visualizing Link Data With Screaming Frog and Excel Part 1

In the new screen select URL and Anchor Used and place them in the “Row Labels” section. Make sure Anchor Used is on the bottom. Next grab URL again and place it in the “Values” section. We’re now able to view how many times each URL was linked to and what anchor text was used and how many times that anchor text was used for that particular URL.

sf screenshot 1 12 Visualizing Link Data With Screaming Frog and Excel Part 1

Feel free to resize the columns and color code to fit your liking.

sf screenshot 1 13 Visualizing Link Data With Screaming Frog and Excel Part 1

The next tab we’re going to create is one that will give us a visual representation of the URLs linked. So go back to the original tab we sorted and click insert but this time we’re going to click on pivot chart. Again make sure it will open up in a new worksheet and select URL and drag it to the “Row Labels” and “Values” Section. You’ll need to move and resize the chart until you’re able to view it clearly. In this example I’m using a bar chart that is displaying the total number of times these pages were linked to at the end of the bar.

sf screenshot 1 14 637x529 Visualizing Link Data With Screaming Frog and Excel Part 1

You can also tweak the design settings until you’re at a view that you’re comfortable with.

Return again to the original spreadsheet and perform the same steps you just did to create the bar chart that is displaying the number of times each page was linked to. But when you get to selecting the data to include select the anchor text instead.

sf screenshot 1 15 Visualizing Link Data With Screaming Frog and Excel Part 1

Now you’ll have a visual display of the anchor text used to view.

By the time you’ve done all of this you should have four tabs.

sf screenshot 1 16 Visualizing Link Data With Screaming Frog and Excel Part 1I suggest keeping the original tab to reference back to when viewing the anchor text and an anomaly pops up you’re able to view what page has that has anchor text and where it’s coming from.

Okay so, now what?

  So you finally got to the end and you’ve got a lot of data to look at. What this information will do is show what you or your clients are focusing your links at internally and with what anchor text. Some questions you should be asking now are:

  • Is there enough variety in anchor text?
  • Are the pages being targeted with the correct anchor text?
  • How does the variety of the internal use of anchor text compare to the external use?
  • Are there any pages not being targeted internally that should be?

This is great to use if you’re trying to get an understanding on where previous individuals had been focusing their attention. It’s also perfect if the site is just starting out and you want to keep track of where links are getting directed at.  Just re-run the report every few months and keep track of where everything is going and how it’s getting described in the anchor text.

This is also perfect for looking at what the competition is doing internally. If they’re targeting specific pages internally you can also rest assured they’re targeting those same pages in a link building campaign.

Now I realize this will be easier to accomplish for smaller sites than bigger ones. For really big sites I suggest exporting the in link data one section at a time, otherwise you’ll be at your screen all day. Hopefully the folks at Screaming Frog will have a function in their next release that will allow for exporting site wide internal link data. But until that happens, have fun reviewing your internal linking! And stay tuned, part two is not far away.

12/27/12 Update:

First, I want to apologize for those I said I was going to get the second part of this post up. I’ve got no real good excuse other than a mix of life and procrastination got in the way. I really hate writing this but after putting it off and rewriting the follow up post several times I read a few months back a post by Chelsea Blacker of BlueGlass. In the post she detailed perfectly what I was looking to do in the part two of this.

I encourage you to go read the amazing tutorial she put together on comparing your internal and external link spread. It’d detailed, it generates impressive data and more importantly it’s something you can do right now.

1d41278e998726cc8fca88c5ee31c199 64 Visualizing Link Data With Screaming Frog and Excel Part 1
Joshua Titsworth is a SEO Analyst that is passionate about all things Internet related. When he isn’t online tweeting or blogging, he can be found tracking down shanked golf balls across public courses.

You Might Also Like

Comments are closed.

16 thoughts on “Visualizing Link Data With Screaming Frog and Excel Part 1

    1. Thanks Andy, Screaming Frog reached out to me on Twitter and said there’s an easier way to export all of the inlink data. In the main menu click on export then click all links, this exports every thing you need. I’m working on getting the article updated. Thanks again!

  1. Hi Joshua,

    I appreciate the effort to show us this procedure.

    If your goal is to gather internal anchor text data, what conclusions would you arrive at on a large ecommerce website that has primary, secondary and some faceted navigations using category names, product names which match search queries folks use?

    It seems that to have an ideal navigation would be to match keyword usage and those are the anchors.

    If you suggest having a mixture of anchor text links, to me that would suggest that the navigation and other forms of links would need to be arranged so they do not all have the same anchors but do go to the same page. I can see having diluted in body links on various form of copy having variances.

    IE: Auto anchor and Car anchor on different parts of the site.

    Clear me up…

    Thanks+

    1. Hey Scott,
      Thanks for the comment and the question. One thing I should clear up is there’s a difference between internal linking from within content and from the navigation. Any navigation anchors should be consistent so not to confuse users.

      Using the example you gave let’s say “Car Parts” is part of the navigation for your site, but within the content area you’d not only refer to this same section as “car parts” where it was natural (though not as much as other possible variations because it is in the navigation), you’d also refer to this section as “automobile parts”, “vehicle parts” or another variation of the phrase because car parts aren’t always called car parts.

      The bottom line is that when writing content, we need to allow link anchors to be more naturally generated with both variations and other words that aren’t even keyword specific anchors.

  2. What a coincidence! Last week I was just thinking about trying to find a way to do something similar. Thank you Joshua for sharing this! Cannot wait to see part 2.

    1. Haha, thanks Michael. It’s something I was thinking about for a few weeks as well. I searched for a tool or something that would do this for me, and then thought well what could provide me the data I need so I could then sort it. Part 2 should be just as informative :)

  3. Joshua great post with some really usable stuff which I have some similar things I’m working on that could use these tips…. ya get the gold star today Josh!

  4. Very nice post Joshua. Big fan of Screaming Frog and always awesome to see more ways people find to use it. Excellent.

  5. Thanks for the great tutorial. I had no idea it was possible to combine separate files in to one using the command prompt. I checked my internal anchor text by hand last week and it was a pain, this is certainly a better way of doing it.

  6. I entirely agree with Mike, I didn’t know files could be combined in this was too! I will definitely have to try this. Great to learn, and thanks for mentioning my post :)