Free Excel Workbook for Analyzing Screaming Frog Data

By /

May 23, 2014

Workbook updated on 10/29/14 with the following features:
– A cleaner style that makes reading the dashboard easier
– A new area in the workbook for outlining the top 5 takeaways from the data
– Better, consolidated visualizations makes spotting issues faster
– Space added to insert client logo

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 Spreadsheet
 

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 Content Breakdown

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-titles-and-meta-descriptions screaming-frog-H1-countscreaming-frog-H2-count

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-image-analysis
 

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-response-code-analysis (1)
 

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.
Page Depth Analysis for Screaming Frog
 

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).
screaming-frog-http-https-analysis (1)

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:
Jun-14

  • 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 is a Search Engineer at LunaMetrics. Passionate about business and technology, Sean focuses his energy on making complex things simple and actionable. Along with his education in marketing and economics, he brings to LunaMetrics a background in web design, SEO and sales.

  • Dan

    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

      Hi Dan,

      What version of Excel are you running?

      -Sean

  • Dan

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

    • Sean McQuaide

      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

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

    Thanks again, this is a great tool!
    Dan

    • Sean McQuaide

      Great! Glad you like it :)

  • Rajesh Magar

    HI Sean,

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

  • http://linkd.in/mvozzo Mark Vozzo

    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

      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.

  • http://boyer-draeby.dk Jakob Boyer-Dræby

    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

    • Sean McQuaide

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

  • Tom

    Is it possible that the export Excel Sheet changed?

  • http://www.wrel.de Tom

    Hi Sean,

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

    Greetings Tom

  • Laura

    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

  • Joe Savitch

    I have the unpaid version of Screaming Frog and this worked perfectly. I love some of the comments and will be using the pdf formula mentioned below. Great spreadsheet. The only thing I would suggest is putting it behind some kind of “fangate” I would have happily traded my email address for this piece of content! Great Job!

  • Joe Savitch

    Does the latest update of screaming frog v4.2 change the way this template would work? I am noticing the columns not lining up the way they used to. Love the spread sheet… just wondering if it was me doing something wrong… or a compatibility issue? Thank you!

  • B Chris Scott

    Amazing Sheet. Just what I was looking for. I agree with @JoeSavitch. I would definitely of opted in for this…

Contact Us.

LunaMetrics

24 S. 18th Street, Suite 100,
Pittsburgh, PA 15203

Follow Us

1.877.220.LUNA

1.412.381.5500

getinfo@lunametrics.com

Questions?
We'll get back to you
in ONE business day.