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.
First in Google Analytics on the left hand side we will browse to Behavior and click into “All Pages.”
You will now see a list of your pages. You will want to expand the dropdown to include all of them.
At the top we will now choose “Export” and click on “Excel” as the format.
In Excel, you will need to click on the first Dataset tab to see your pages export.
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.
In column B we are going to use a simple concatenate formula. Input your domain in the example.
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.
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.
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.