Exporting large sets of data with Google Analytics

Jan. 09, 2012 | by Sam Vining

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:

Number of rows

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!

Be Sociable, Share!

    Comments (11)

    • MJ

      Just a heads up that this method of editing the URL to display more rows no longer works and it stays at the max of 5,000, no matter what max value you change the URL to.Sep 2, 2014 11:50 am

    • Paul

      Is there a way to segment this data by month/day/year? Say for example I am exporting eCommerce data for a year period, but I want it broken down by month is there any way to do this?Aug 7, 2013 01:18 pm

    • William

      Thanks. That's awesome!Jun 14, 2013 06:59 am

    • Gilles

      For easy downloading of Google Analytics data, have a look at http://www.download-analytics.comApr 5, 2013 03:10 pm

    • Melinda

      Hi Sam, thank you so much for the reply. I understand how to convert the the text to data using that formula, but that would be after the download. What I am I missing? Is it possible to do in GA before the export? Or are you saying that once you convert the dates to data you can use another formula to add rows with null values?Mar 5, 2013 05:46 pm

    • Sam Vining

      Thanks Jonathan!

      Melinda: I've not come across this issue I'm afraid. One tip I can offer is to make sure the date values are correctly formatted (they are exported from GA as text by default, use a formula like DATEVALUE(TEXT(cell,"dd-mm-yyy")) to convert them), your spreadsheet should know that there are missing dates in between.Mar 5, 2013 10:50 am

    • Jonathan

      Thanks for this. It's a real time saver! Much obliged.Mar 5, 2013 04:05 am

    • Melinda

      Hi guys, this is a superb tip, thanks for publishing it. One limitation I'd love your input on. When exporting data by day, Google Analytics does not send null values, i.e. days when the visitor count is 0. This makes it very difficult to look at a metric like unique visitors by day. Any thoughts on how to rectify this without turning to the API?Mar 1, 2013 12:28 am

    • Sam Vining

      Hi Jim, thanks for replying. Having tried with both browsers, I find Chrome deals with this request a bit better - that's not to say it was entirely happy though! Thanks also for the correction to the rowCount field, you're right when you say it should stay at 50,000.Jan 20, 2012 10:38 am

    • Jim

      Thanks for posting. This was helpful, although I could only pull 25K rows at a time. I tried 50K but Firefox couldn't handle it.

      I think that explorer-table.rowCount%3D100000 in the last part should actually be explorer-table.rowCount%3D50000

      That's because it's not row start and row end. It is row start and row count.Jan 20, 2012 06:37 am

    • Destry Wion

      ... "a content inventory perspective" ... "its own sensible URL reporting abilities"...

      If I don't cover my tracks, I'll hear about it in the wild, no doubt.Jan 10, 2012 02:11 pm

    • Destry Wion

      This is a HUGE tip. Thank you!

      I wish I was aware of it 4 months ago when working with a site having upwards of 130K URLs.

      From an content inventory perspective, this is the next best thing to a CMS having it's own sensible URL reporting abilities, and probably the best thing if you consider all the analytics data too.

      Awesome!Jan 10, 2012 02:05 pm

     
    Please note: the opinions expressed in this post represent the views of the individual, not necessarily those of iCrossing.

    Post a comment

    SUBSCRIBE