Exporting large sets of data with Google Analytics

For top-level information, Google Analytics offers some nice summary graphs that help you quickly get to grips with your site’s information. For those of us that like to get our hands dirty with the data underneath though, Analytics offers a handy ‘export to CSV’ function for endless tinkering in your spreadsheet application of choice.

The default limit for exporting rows of data is set at 500 – fine for a smaller site with just a few dozen pages, or reports over a short time period. But what to do if you operate a larger site, and would like to export information for your several thousand pages? Or would like to download the entire list of keywords that brought users to your site? Based on the popularity of your site or variety of your content, these lists could easily generate tens of thousands of rows – potentially hundreds of thousands over a long enough period. This could lead to painstaking hours exporting 500 rows at a time to get at the data you need, and find the crucial insight that will change your world.

Fortunately for all of us spreadsheet-jockeys there is an easier way. Analytics veterans will be aware of a workaround to help them export larger amounts of rows at once – simply adding the text &limit= and the desired number of rows (up to a limit of 50,000) to the end of any Analytics URL, for example: https://www.google.com/analytics/reporting/keywords?……………..&limit=5000. With this extra parameter you’d be able to export up to 100 times more rows at once!

Anybody who has adopted the latest update of Google Analytics may have been dismayed to discover that this tactic will not work with version 5. So how can we achieve the same result now?

The process is similar, and hopefully a bit more straight-forward. When viewing your report of choice, scroll down to the bottom of the page and you should see this in the right corner:

From the drop-down menu, choose ‘500’. Once your page has loaded, you’ll notice an extra bit of text has been added to the end of your URL: it should now finish with explorer-table.rowCount%3D500. Replace the ‘500’ in this string with your desired figure – as before, anything up to 50,000 will work. Hit enter and export, and you have your data!

All well and good, but what if your data requirement is slightly larger? What if you need to export, say, 200,000 rows? Find the part of your URL that contains this text: geo-table.rowStart%3D0. The ‘0’ here can be amended to begin at any row you desire – so for the next 50,000, let’s start at row 50,001 with the following text: geo-table.rowStart%3D50001. The end of your URL should be changed to explorer-table.rowCount%3D100000. You’ll now have the next 50,000 rows ready to export.

Using this technique you could export one million rows by loading just twenty pages. To do this 500 rows at a time would require you to generate 2000 pages – quite a time-saving!