SEO Audit
Have you ever tried to export a full list of your URLs from Google Analytics? If you have, you probably have realized that you can’t actually do this. A list like this can be very useful to have during an SEO audit of your website. I highly recommend using a tool like Screaming Frog to do this. However, if you have more than 500 pages and don’t want to buy Screaming Frog you can still do this quite quickly with Google Analytics. Follow the steps below to see how you can achieve this with a few simple Excel formulas. Also, for more on an SEO audit, I highly recommend reading How to Conduct a Content Audit on Your Site by Neil Patel.
Step 1
First in Google Analytics on the left hand side we will browse to Behavior and click into “All Pages.”
Step 2
You will now see a list of your pages. You will want to expand the dropdown to include all of them.
Step 3
At the top we will now choose “Export” and click on “Excel” as the format.
Step 4
In Excel, you will need to click on the first Dataset tab to see your pages export.
Step 5
Go ahead and sort by the most unique pageviews, as that is where you will want to focus your efforts first, and then delete the first row and the rest of the columns. What you should have now is simply a list of the pages.
Step 6
In column B we are going to use a simple concatenate formula. Input your domain in the example.
=CONCATENATE("http://domain.com",A1)
Step 7
Now you will want to drag the formula down to apply it to the rest of the rows.
And now you are left with your pages and the URLs in column B.
Step 8
Now we are going to convert the URLs in column B into hyperlinks so you can easily click into them when doing your SEO audit. So in column C we will use the following formula.
=HYPERLINK(B1)
And then again, like we did in Step 7, you will want to copy the formula down. And what you are left with is a full list of your URLs from Google Analytics.
Once you have done this once or twice you can easily accomplish this in a matter of seconds! It is a great way to get organized for that next SEO audit on your site. As always feel free to leave your comments below.
Useful information, I just had a request to make my report have all clickable URLs. Thanks!
Sure np, Stephanie. Glad it was helpful!
Thank you very much. I knew of both functions already, but didn’t think to use them together in this way! Great workaround.
Np David. I use these all the time myself to add URLs to my exports so that they are clickable. Makes working on the data much faster.
Thanks Brian. How far back should you set your time frame? I was using the default past 30 days, and came up with 134 URLs, but I knew that wasn’t the full list. I expanded it to start in 2004 and found 1999 URLs. What is a meaningful time frame to use if I’m trying to set up a good redirection plan when I replace the old site with a new one? Or if I’m trying to find 404’s, etc.?
If you are needing to do redirects, I would go all the way back. You never know which of those URLs will have backlinks or link juice, which you don’t want to lose when you go to a new site.
Thanks a lot Brian! That is exactly what I’m looking for :)
Thank you Brian – this was such a time saver! I really appreciate you going to the effort to publish these instructions.