Upcoming LunaMetrics Seminars
Seattle, Nov 3-7 New York City, Nov 17-21 Washington DC, Dec 1-5 Los Angeles - Anaheim, Dec 8-12

Free Excel Workbook for Analyzing Screaming Frog Data

We here at LunaMetrics are born from data and to data we return time and time again to uncover insights and craft strategy. But staring at large sets of data is a mind numbing process, one I personally hate. So when I began performing health checks for large websites I immediately starting thinking about how I could eliminate as much work as possible. Using some Excel magic, many Mr. Excel videos, and data pulled from Screaming Frog I created a simple copy & paste workbook that counts, totals, and visualizes all the data Screaming Frog gives you.

Shout out to Dan Sharp of Screaming Frog for his great feedback on this workbook. Keep an eye out for Screaming Frogs new version being released in the next couple weeks. The big addition? Data visualization. Can’t wait for that!

Free Excel Screaming Frog Analysis Spreadsheet

How It Works

After crawling a website with Screaming Frog, export the data into one of the three available file formats (.csv, .xls, or .xlsx).

Copy all of the data from the Screaming Frog worksheet (starting in cell A4) into cell A2 of the ‘data’ sheet of this analysis workbook.

  • The easiest way to copy all the data is to click cell A4, the use Ctrl + Shift + RIGHT to select all columns of data.
  • Then Ctrl + Shift + DOWN to select all rows of data.
  • Then use Ctrl + C to copy the selected cells.
  • Go to the data sheet of this workbook and Ctrl + V to paste that data into cell A2.
  • Pasting the data into cell A2 will align all the data with the column headers I already have in row 1.

If this data does not align properly, then there is an added or missing column of data which you will need to adjust for in the ‘data’ spreadsheet.

The Report Tab

The reporting tab is set up to make customizing this report for each website as easy as possible.

Screaming Frog Analysis Summary Sheet

File Type Analysis

This section is made to be modified. Column C contains the file type you would like to count from the data pulled from Screaming Frog. Column D takes what you enter in Column C and counts the number of times it is present in Column B of the ‘data’ spreadsheet.

Screaming Frog Filetype

 

On-Page Breakdowns

The section next to the file type breakdown looks at titles, descriptions, H1-2s and H2-2s. For each you will see the number of pages with the element, without the element, and how many unique and duplicates there are of each element.

Titles: You are able to change the max pixel width for this section. In the cell next to “Pixel Width Exceeds” enter a number you consider to be the max pixel width. I have 512px as the default, but I have also heard pixel width is actually more like 482px wide. I know there will never be a consensus, so I gave you control.
Screaming Frog Analysis Page TitleScreaming Frog Analysis On Page H1Screaming Frog On Page H2

 

Image Breakdowns

Like the File Type section, this section also allows for some customization. Just change the Type to whatever image file you’re looking for. The spreadsheet will do the rest. It will count how many of each image file were crawled and tell you how many of those files exceed 40kb. If you don’t like 40kb then feel free to change the 40 in the equation to whatever you like.

Screaming Frog Analysis Images

Response Codes

This table counts all the response codes and throws them into a nice graph so you can see both. Each code has a comment which includes and explanation of that code. As with previous tables, you can change the response codes if what I have included does not capture some of the codes on your site.

Screaming Frog Analysis Response Code

Page Depth

This section buckets and graphs the depth of pages and graphs it in a bar chart to the right. Great for visualizing where the bulk of your site resides.

Screaming Frog Analysis Page Depth

HTTP vs HTTPS

Used in conjunction with other metrics in this sheet like canonical element counts and duplicate hash counts, you can judge whether there is an issue with HTTPS duplicate content (and if so how severe).

Duplicate and Unique Hash Counts

Every page that has content has a hash. This is a good way to look for truly duplicate content.


 

Update:

06-2014

  • Canonical Element #2 column removed from the ‘data’ spreadsheet

 

Having problems? Check out these great excel resources to help you solve them.

Excel is Fun with Mike Girvin

Mr. Excel with Bill Jelen

Disclaimer: You break it, you bought it.

Sean McQuaide

About Sean McQuaide

Sean McQuaide is a Search Analyst in the SEO department of LunaMetrics. His passion for business and all things digital fuels his need to ask the simplest of questions, why. Along with his education in marketing and economics he brings to LunaMetrics a background in web design, SEO and sales. He is a movie addict and avid drinker of craft beer, but would take on a mountain bike trail over those two any day.

http://www.lunametrics.com/blog/2014/05/23/screaming-frog-data-analysis-excel-spreadsheet/

14 Responses to “Free Excel Workbook for Analyzing Screaming Frog Data”

Dan says:

Hi, love this! It’s (almost) fantastic and incredible. I say almost…I can’t get it to work. Have pasted in new crawl data ok but report sheet isn’t updating. Is there a ‘go’ button or something i’m missing? Thanks

Sean McQuaide Sean McQuaide says:

Hi Dan,

What version of Excel are you running?

-Sean

Dan says:

Hi Sean – Mac 2011 – is this a windows only job? Thanks

Sean McQuaide Sean McQuaide says:

I don’t think the OS is an issue, I tested it on a Mac before launching. I would say check to see if your calculation options are set to manual. That would definitely prevent the equations from updating. I did not save the workbook with manual calculations turned on, so it is unlikely that that setting would be turned on.

A note about count updates, every once and a while Screaming Frog will add another column of data because it detected something abnormal in the crawl – for instance this sheet has two canonical columns. If you find that your data is off check the Screaming Frog column headers that you exported and make sure they match the column headers in the data sheet.

Dan says:

Strange – working ok now! Must have been doing something wrong y’day.

Thanks again, this is a great tool!
Dan

Sean McQuaide Sean McQuaide says:

Great! Glad you like it :)

Rajesh Magar says:

HI Sean,

That is completely magnificent work. And I am sure your excel is going to rock website auditing work.

Mark Vozzo says:

I’ve been doing SEO for nearly a decade and I’m a huge fan of ScreamingFrog. I love this report and it take the data and makes pretty charts that are easy to understand of the data – well done!

I did come across a few things:
1) The Worksheet included a column for “Canonical Link Element 2″, when the ScreamingFrog data set only has one column in it’s output, hence this breaks things when doing a copy and paste.

2) Unique Titles Count
When I ran screamingfrog over a site that has a few “unique titles” and “unique Descriptions” etc and a whole bunch of empty Title/Description Tags etc the entire set of “Unique” value counts stays at zero. I think there is something funny with the =COUNTIF formulae in the worksheet.

3) PDF Files
I also have some links PDF files (urls, end with PDF) but the content value from ScreamingFrog came in a Text/HTML. So this is probably a crawl issue or due to some CMS redirection/tracking. But if would be great if your report could look at Url file extensions to get a better feel for different content types.

It’s an awesome worksheet, thanks for building it.

Sean McQuaide Sean McQuaide says:

Hey Mark,

Thanks for moving the conversation to the blog.

1) There is an additional column of data in my template which some sites may not have in their data. Screaming Frog picks up additional information and adds columns to hold that information as needed, so its impossible to anticipate those changes. However you’re right that Canonical #2 is not apart of a typical crawl. I have removed the Canonical #2 column and updated the download. My Recommendation: Make sure the column headers line up before pasting the data from Screaming Frog. Open the template document, delete any column in the ‘data’ sheet which do not match the column headers in the Screaming Frog export spreadsheet. The equations will adjust if you delete an entire column.

2) That COUNTIF statement is correct. I have a helper column in the ‘data’ sheet which counts the number of times the number one appears in the column. The 1 appears because the formula evaluates the Title column and compares the current row to all rows before it. If it is unique it lists a 1, if it is a duplicate it lists the number of duplicates. So if the formula in AE sees that the text in the Title column has happened 3 times before it, it will list 4 as the value of AE.

3) You can modify the formula I used to count soft 404’s to look for PDFs that return a text/html content type but are actually pdf documents. We do that by looking for any cell that contains text/html in column B and counting the number of URLs in column A that end in pdf. Here is what it would look like =COUNTIFS(data!$B:$B, C19, data!$A:$A, “*pdf”). This COUNTIFS formula looks at all cells in column B in the ‘data’ sheet that match whatever you put in cell C19. In the template C19 contains application/pdf, so you would change that to be text/html. Then the formula looks at column A in the ‘data’ sheet, which contains the urls, and looks for any url that ends in pdf. * is an Excel wild card similar to its REGEX partner. It is very useful for looking for strings of characters inside of a cell.

If there is still some confusion let me know, Ill try and clear it up.

Great tool. I will DL and reuse, AND send you lots of positive thoughts. I’m missing one feature: In the Data sheet, a conditional formatting on the columns that are not showing best practice values. I.e. Mising or too long title tags, response code not equal to 200 etc. This way, its easy to get an overview of what is to be fixed on the website crawled.
Rgds
Jakob

Tom says:

Is it possible that the export Excel Sheet changed?

Sean McQuaide Sean McQuaide says:

Jakob,
I would try downloading the workbook again.
-Sean

Tom says:

Hi Sean,

if you use the paid version of screaming frog, there will be more columns in the export file.

Greetings Tom

Laura says:

Hi There,

I can’t seem to access the file at all… “This webpage is not available” :(

Would it be possible for you to email it to me? It looks great!

Thanks,

Laura

Leave a Reply