I help a lot of companies with website redesigns or CMS migrations (from an SEO standpoint). As part of that process, it’s extremely important to download all of a website’s top landing pages so you can check them in staging and when the redesign or migration goes live. By doing so, you can test the pages to ensure they resolve correctly before you pull the trigger, and then right after you launch. Basically, you can nip serious SEO problems in the bud by crawling those urls.
I also help a lot of large-scale websites with redesigns or migrations. For example, sites with 500K, 1M, or more pages that need to be downloaded, crawled, and analyzed. For situations like that, it’s not simple to export the top landing pages from Google Analytics. Unfortunately, you cannot export urls in bulk from the UI. You are limited to 5K urls per report. Then you would need to click to the next page in the pagination to retrieve the next 5K rows. That’s not efficient, to say the least.
You could also tap into the GA API, but you need programming skills. That’s unless you leverage a tool or plugin that has the functionality built for you. More on that soon. :)
The GA rowCount Hack is Gone
There used to be a hack in Google Analytics that let you manually set the row count in your reports via the url in your browser. I loved that feature, since it enabled me to get around the issue I mentioned above. For example, I could add rowCount%3D15000 in the url, and boom, I had a report with 15K rows of data.
Well, Google Analytics removed the ability to do that and now the most you can get in one batch is five 5K rows. Sure, you can click to the second page of results and keep exporting, but that gets old quickly. Exporting up to 5K pages is fine for some smaller sites, but not if you have hundreds of thousands of pages, or more.
So, wouldn’t it be great if there was some plugin or add-in that could help with this? Well, there is. Read on!
Analytics Edge for Excel
While researching options last year, I came across an Excel add-in called Analytics Edge. The add-in is packed with functionality, including the ability to export pages from Google Analytics with ease. And not just export pages, but export them in bulk! That’s exactly what I was looking for.
Again, the plugin can do many things for you, but I’ll just focus on exporting top landing pages from Google Analytics. If you are going through a redesign or migration, and want to export all of the top landing pages from organic search, then this could be a great solution for you. It has worked extremely well for me.
Note, there is a free version of the add-in that contains the Google Analytics connector. You can also pay more for the “Core” add-in, which gives you even more functionality. The screenshots below are from the core add-in, so you might see slightly different options with the free version. Now let’s get started.
How To Export Top Landing Pages From Google Analytics (Beyond 5K)
1. Download and install the Analytics Edge add-in for Excel. Then register the add-in to get started.
2. Click the Analytics Edge menu at the top of the worksheet to access the various options available to you.
3. Click “Free Google Analytics” in the menu and then “Accounts”. You will need to connect the add-in to your Google account so you can access various properties and views in GA. You should enter the account that’s tied to your Google Analytics properties and views. Note, you can add several Google accounts for future access, if needed.
4. Next, click “Free Google Analytics” and then “Analytics Reporting”. A small macro box will show up, which will capture the commands you are using via the add-in. Simply name your macro to move on.
5. Here’s where the magic takes place. This is where you will choose the settings for your new report. First, select a login and then a view. The options will match the Google account(s) you entered previously.
6. Next, choose the dimensions and metrics you want to see in your report. For this example, I’ll keep it simple. We’ll choose landing pages, sessions, and bounce rate.
7. Instead of clicking finish, let’s select the Dates tab. There are some presets here like “Last 30 Days”, but you can enter a custom date as well. I’ll choose “Last 30 Days” to keep this simple.
8. If you click the “Sort” tab, you can sort by sessions (which is typically what we would want for top landing pages).
9. Last, but not least, we want organic search data for this example. So head over to the “Segments” tab and select “Organic Traffic” in the list. This will limit the data to landing pages from organic search.
10. Then click Finish. Analytics Edge will begin pulling the data from Google Analytics. Note, for larger sites, this could take a while. Be patient. You’ll notice the plugin says, “Running…” and you might see the Query Status window showing the number of rows downloaded.
11. Once completed, you’ll see a “Step Results” worksheet filled with data. You’ll also notice there are only 50 records in the sheet. This is where most people freak out and think the plugin screwed up. It didn’t. It’s simply showing you a sample of the data that was pulled. If all looks ok, then you need to write the data to a new worksheet.
12. To write the data to a new worksheet, you need to click “File” in the Analytics Edge menu, and then “Write Worksheet”. You can name your new worksheet whatever you like and then click OK. Once you do, you’ll notice a new worksheet with all of your data. For the view I used, I retrieved 98K top landing pages from organic search over the past 30 days. Awesome.
Bonus: The only thing left to do here is to use the concatenate function to build the full url. Once you do, you can export that list and crawl it via Screaming Frog, Deep Crawl, etc. You can read more about checking top landing pages in a recent post of mine. There are some helpful tips in that post once you have a final set of urls.
Summary – Breaking Through The 5K Limit
For those of you that are working on large-scale sites, the process I documented above can help you export many more urls than you could via the UI in Google Analytics. By using an Excel add-in like Analytics Edge, you can export hundreds of thousands of urls, if needed (and all in one shot.) That will save you time, and your sanity.
So gather your intel now. Then crawl away. It’s a great way to check urls in bulk when going through a redesign or CMS migration. Good luck.
GG
