This 5 minute post is a quick hack to help those looking for a way to export large data sets from Google Analytics.
As you will no doubt be aware, the “export” button enables your to export up to 500 rows at any one time, for the most part this is fine but what happens when you want to do more that that? 500 rows at a time quickly becomes a laborious task! The scenario I came up against the other day was during some keyword research work. I was looking to download all the search terms people had used to reach my site in the last 6 months. Once I ran the report I had 16068 rows of data, at 500 rows a go that would have been 33 separate downloads! A quick google search later threw up some hacks put forward by others, none of these worked so I played around with it and found the following solution.
The following 4 steps show how to export 6 months worth of search terms for your site to aid you with tasks such as keyword research, SEO or PPC planning.
Open up your GA account and set the right date range:
Then on the left hand side select Traffic Sources –> Search Engine Optimization –> Queries
On bottom right-hand side of the page a number is displayed, this is the total number of rows you have in this data set. In this example I have 17297.
At this point you should also set the “Show rows” total to 500 as well.
Armed with the the total row number head to the url which should look like this:
At the very end of the url, amend the D500/ to be the number shown for your “total number of rows”, so in this example I would use 17297. The url will now read:
Hit refresh and the page will reload showing all your rows.
Export to CSV as you would normally, but now you will get the full data dump and not just the 500 rows.
Small caveat: I have not yet used this for numbers over 20,000. I have read on some blogs and in some forums that people have had trouble with past (now defunct) hacks when they tried to export data sets over 20,000 rows. If do experience that issue with this hack please do let me know via the comments below.
Hope that helps you save a bit of time in your work