Home > Web Analytics | Web Site Planning | Web Strategy | Web Usability > How to combine Screaming Frog Data with Google Analytics data

How to combine Screaming Frog Data with Google Analytics data

Posted by Iamoldskool

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

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

Over to Google Analytics and head over 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 5000 so you get as much data as possible.

Hang on a minute Jim you’re saying….I have a lot more than 5000 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 9347 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, now 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, label them so you know which is which

Now make a third empty worksheet for your compiled data

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 names “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 then 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)

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 reorganise itself.

Then click file > Return data to Microsoft Excel

On the next one, just click ok…and that’s it, you 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

Jim

Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

TOP
Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Linkedin