Trying to export a full Google Analytics dataset can be maddening.
Working and sorting data within analytics programs can be helpful, because the inherent power of the program can very quickly filter entire data sets down to manageable sizes. But at some point, I need to bring the data “local” and be able to manipulate it in my own environment so that I can sort and present data on my own terms.
For many versions now, Google Analytics has had an often-frustrating limit to the number of rows it will export. It will show you all the data within the GA environment, but when it comes time to export it, the limit is 500. (You could, theoretically, export rows 1-500, then a second export of rows 501-1000, and so on. But that’s not a practical, time-saving solution.)
Version 4 of Google Analytics had a workaround I covered here in 2009. The workaround for version 5 is remarkably similar, but it integrates GA’s new URL structure.
Here’s how to set the export value:
First, go to your desired report. Scroll to the bottom of the report to the “Show rows” dropdown. Select any quantity of rows except for the default 10 rows, as seen here. Also, keep an eye on the total number of rows in the report. In the following image, it’s 8188.
The report should now be showing the number of rows you selected. Again, it doesn’t matter whether it’s 25, 50, or 100, as long as it’s not the default 10 rows.
Next, go to the URL field of your browser.
Scroll to the far right side of the Google Analytics URL. This area of the URL contains the value for the number of rows to show in the on-page report. The final characters of the URL will look something like the following image, although some of the specific values may be different. Note that Google Analytics’ dynamic URLs look slightly different from the ones you may be used to, because they use Hex codes for characters such as the ampersand (&) and equal sign (=).
Look closely at the final characters of the URL. This should be a number that corresponds to the number of rows you selected from the dropdown box earlier. In this example, it’s 25.
Remember the “total” rows number you remembered earlier (in my example, it was 8188)? It’s time to use it. Replace the existing value (e.g., 25) with that number, so that your number immediately follows the %3D characters (with no spaces in between).
Now, with your cursor still in the URL field, hit Return. <– This is important. You need to hit return so that the new URL you just requested will be loaded.
At this point, your on-screen report will not look different. But relax, because when you export the data, it will all be there. The number of exported rows will match the number you input at the end of the URL.
Questions? Let me know if it doesn’t work in the comments.