Visualizing Website Data with Screaming Frog and Google Docs or Excel
Does your boring old data look like this?
Would you rather it looked exciting and actionable like this?
Recently, I was asked by an in-house CMO at Mozcon 2014 what the best SEO tool on the market is.
My response was two words: Screaming Frog.
Apparently, my answer was a frequent one. He seemed surprised that so many people loved this tool, and began to prod a bit more deeply.
CMO: Why is Screaming Frog so great?
Me: Because it allows you to see all of your website at once and identify issues at scale.
CMO: What do you mean “see” your website?
Me: Well, you can see all of your URLs, meta data, response codes, internal links, and other important data in one place.
CMO: (glossy-eyed by this point) I've tried it, but can’t really get into it because that raw information doesn't help me visualize the issues.
Me: (handing Mr. CMO my card) I get that. Be sure to get in touch if you need help understanding it - we’re always happy to help.
As SEO’s who live and die by data, it’s clear to us that many people (especially at the C-level) struggle to understand raw data. It’s up to us to use our expertise to turn that raw data into contextual information that our clients can use to make smart business decisions.
Why I Love Screaming Frog
No, not this one...
That's the frog you're looking for!
Now let me start out by saying that I don’t work for Screaming Frog, I am not getting paid for my endorsement and I am not being held at gunpoint while writing this post. It is simply the best crawler tool available on the market and it keeps getting better with every update.
Every single audit I do starts with a Screaming Frog crawl. With the simple click of a button, Screaming Frog will deliver a massive amount of critical data and important information about the health and structure of your (or a client’s, or a competitor’s) website. It’s incredibly easy to slice and dice this data into easy to consume chunks of technical SEO goodness.
Now, there are a ton of great pieces of content out there about how to get the most out of the tool. For my money, this guide by the folks over at SEER Interactive is pretty much the granddaddy of them all. So, feel free to peruse at your leisure.
In this post, we’re not going to tell you how to use the tool. We’re going to help you better convey what the outputs of the tool actually mean.
Why Data Visualization Works
Remember our CMO friend? Well, there are a lot of people out there just like him. Heck, I’m one of them. Don’t get me wrong, I enjoy geeking out on spreadsheets as much as the next SEO/data analyst/nerd, but there’s something about good ol’ pie chart that makes me stand up and shout “I get it!”
Maybe it’s the holistic nature of the data, maybe is because I’m a visual creature, maybe it’s my affinity to circular fruit-stuffed pastries - who knows, but data visualization just works for me. And, when used correctly, this data can tell you a story.
Using Data Visualization to Tell Your Story
Great data visualization can capture your audience and provide clarity on the issue at hand to anybody - regardless of their level of experience with the data.
For a long time, we used what’s called a radar chart to assist with visualizing website issues. The resulting output looked something like this:
Effective? Meh, kinda.
Sexy? Heck no!
This semi-useful graph is what spurred an initiative this year at Three Deep to provide an effective and efficient replacement to our standard visualization charts.
Enter the Screaming Frog Crawl Overview Report.
For those of you familiar with Screaming Frog, you may know about the different report exporting options that you have for crawl data:
The Crawl Overview Report provides a high level view of the site from the standpoint of various metrics. This is basically what you see on the right-hand side of the screen when you are performing a crawl.
My goal was to take this data and make it easier to understand and visually pleasing.
Note: To give credit where credit is due, Luna Metrics took a pretty good stab at doing this using all of the crawl data. But, any Mac owner knows that copying and pasting thousands of cells at once can cause your machine to “pinwheel” for eternity.
Building upon their example, I took to Google Docs to to create an overview output that looks like this:
Looks better than the old radar chart, huh?
Here’s how it works - The Crawl Overview Report has a consistent format that, once pasted into the ‘Data’ sheet, will always produce these graphs. The metrics selected for graphical representation (Response Codes, URI Issues, Page Titles, Meta Descriptions, H1s, H2s, Images and Click Depth) are what we consider as key on-page SEO elements.
It’s easy to read and easy to share with prospective or current clients - and I’m giving access to you!
**UPDATE: Due to popular demand, I have created an Excel version of the Data Visualization Template! Please download this template here:
Excel Version: Crawl-Data-Visualization-TemplateV7.1
Here’s how to use it on your site:
- Crawl your site with Screaming Frog.
- Under the ‘Reports’ tab, select Crawl Overview (side note - I highly recommend buying this tool so that you can save all of your crawls. It's only about $14/month - do it!)
- Save it as a .csv file
- Select and copy all cells in the report
- Access the Crawl Overview Data Visualization Template here, or download the Excel Version: Crawl-Data-Visualization-TemplateV7.1.
- Make a copy in your Google Drive.
- On the ‘Data’ sheet, select all cells and paste your Overview Report
- Voila! You’re done! Bask in the warm waters of charty nirvana!
So go try it out for yourself. Love it? Hate it? Suggestions? Want to share how you use it? Let me know in the comments below.
**UPDATE 1/03/2017 : Excel doc has been updated to include to accommodate the new layout of the crawl overview report in Screaming Frog version 7.1. The template will not work properly with previous versions. So, update your Screaming Frog version if you have not yet!