How to Combine Screaming Frog Data with Google Analytics Data

I love Screaming Frog. It is without doubt the best SEO tool I use on a daily basis (no offense, Moz). The sheer amount of data you can get about your website, or someone else's website, is incredible. You can find broken links, you can check for your Google Analytics (or any other) code on all pages through the custom search, and you can even go so far as to follow all the redirects and find out the redirect paths in a website.

In this quick guide, I'm going to show how Screaming Frog data can be used to help perform a content audit.

The data in Screaming Frog is incredible, but one thing it can't do (yet…give it time) is tell you how popular your pages are. For that, you need an analytics package. We're going to be working with Google Analytics on this one, as it's probably the most well known (and well used) of the analytics services out there, and we're going to combine the two data streams into one to give you a full overview of your content and just how popular it is. As this data is from a website I work with (rather than my own), I'm going to hide the URLs in the screenshots for obvious reasons.

Why would you want to do this?

Combining Google Analytics data with your Screaming Frog data has a myriad of advantages. You can get an overall picture of your site and identify any issues that are occurring on popular pages. You can see which pages within your site have no page views at all, or the ones that have very few page views. Maybe there are issues on these pages that become immediately apparent when you combine the two datasets.

Getting your data

Step 1: Screaming Frog

Spider the website you're working with in Screaming Frog. Just type the URL in the box and click go, and off it goes getting all the data from your website.

Filter the list to just include HTML and hit export:

Step 2: Google Analytics

Head over to Google Analytics and go to the "All Pages" tab:

Set a decent data range of a couple of months so you get some decent data (especially if it's a low traffic site), and set "show rows" at the bottom to 5,000 so you get as much data as possible.

"Hang on a minute, Jim," you're saying….I have a lot more than 5,000 in my list. How do I get the rest? Well, that's a simple hack. Go to the URL at the top and look at the end of it for the 5000. It will look something like this:

Now just up that figure to cover all of your page views, and you'll have a huge long list. I have 9,347 on my list, so I'm going to up it to 10,000.

Great. Now export that data to an Excel file:

Now you have the two sets of data in Microsoft Excel format. Next, we're going to combine these two data sources into one

First step. Open them up and put them both into a single excel file on different worksheets, then label them so you know which is which:

Now, make a third empty worksheet for your compiled data. Here's a view of the worksheets you should have at this point:

To make this work, we'll need the URL (page name column) to be the same on both sheets. The Screaming Frog data contains the domain, where as the GA data doesn't, so use find and replace on the Screaming Frog data to remove the domain up to the first trailing slash. The two data sources should now have URLs that match.

With me so far? Great. Now it's time to link the data sets together and get that lovely combined data in your third worksheet.

Linking the data

OK. Go to your Screaming Frog worksheet and select all the data and on the formula tab, click define name – give it an easily identifiable name (I would name it the same as your worksheet).

Then do the same with the GA data: Select it > Formula Tab > Define name > Name it the same as the worksheet.

Got both of them defined? Groovy, time to put this data together.

Save your file.

Go to your third worksheet, named "compiled data."

Then on the data tab, select "From Other Sources" then From Microsoft Query.

It will then ask you to choose your data source, choose excel file from the options and click OK. Then, find your saved Excel file and select it; you'll be given the option to include your two named data sources.

Select both, and add them to columns in your query. Click next, you'll then be presented with what looks like an error message (but isn't really).

Click OK.

Then drag "Page" on the GA Data onto "Address" on the Screaming Frog Data like this

And, you'll notice all the data from the two data sources below will reorganise itself.

Then, click file > "Return data to Microsoft Excel."

On the next one, just click ok… and that's it. You should now have a single worksheet with the combined data from Screaming Frog and Google Analytics to play with and do what you want.

Hope my little tutorial made sense and people find it of use. I'd love to hear what other people use this tutorial to accomplish in the comments :-)

Thanks all!