Back in August, a tip was released on the Official Google Analytics Blog that allows you to export more than 500 rows from a report. In the post, this technique was used to export more than 500 rows worth of keyword data. Here we often use this technique to export more than 500 rows worth of pages from the Top Content report.
What you may not realize is that you can also use this trick to export more than 10 previous and next pages from the Navigation Summary report. As you may recall, the navigation summary report looks something like this:
In some case, 10 previous and next pages may be just what you need. But what if you want more?
First, look up at the address bar. For Google Analytics reports, you will often see some anchor text at the end of the URL. For example, this URL:
https://www.google.com/analytics/reporting/content_detail_navigation?id=3125976&pdr=20091031-20091130&cmp=average&d1=%2Findex.htm#lts=1259675463422
contains the following anchor text:
#lts=1259675463422
The first step is to remove this, including the pound symbol (#) from the URL. Next, add the following to the end of the URL:
&limit=50000
You cannot export more than 50,000 rows, so it’s usually a good idea just to set the limit to this to get as many rows as possible. If you only want the first 1000, you can always set limit=1000 instead.
After adding this to the URL, hit enter and wait for the page to reload. Note that the page will not look any different than before. Adding limit=50000 only affects what you get in the export, not what you see in the interface. Also, only the CSV and TSV exports are affected. You can’t get a PDF or XML with more than 10 previous and next pages. You can’t even use the CSV for Excel export. But plain, old CSV and TSV work just fine.
There’s not much more to say about this, so we’ll move on to the next technique.
Pivot tables in Google Analytics are pretty neat. They allow you to see a two dimensional view of your data for a given metric or two. One way we might want to use pivot tables would be to see the number of visits made for each of our keywords, grouped by the hour of day:
What’s this? You can’t pivot by the hour of day? Download GARE and then come back quick!
So as you’ll notice, you can only see the first 5 hours. And by first 5 hours I mean the 5 hours that had the highest number of visits. You can also cycle through the rest of the hours in chunks of 5.
Or you can add this parameter to the URL in the address bar:
&tcols=50000
You should remember to remove the anchor text and hit enter when you’re done like before. Unfortunately, the report will now revert to a previously unpivoted state. You’ll need to get back to the report you were looking at previously, but once you’re there, you should see as many columns as are available. In this case, there should be 24 columns (though you may have less if you have hours without visits):
Note, if try to filter the report, you’ll lose the extra pivot columns and revert back to only 5. There is a way to get around this. For example, let’s say you filtered by “cheese” to only show keywords containing cheese. instead of just adding &tcols=5, add the following:
&tcols=50000&q=cheese&tpivk=hour
This will result in a pre-filtered report. You can then select pivot as before and see as many pivot columns as you like for keywords containing cheese. Note that we also added &tpivk=hour to pivot by hour of the day. That’s because as soon as you change what you’re pivoting by, you’ll lose the filter. So there’s quite a bit more involved if you need a filtered report, but it’s still doable.
Now the only problem remaining is that there’s no good way to sort by hour. At least not from the interface. You can export to CSV and do a vertical sort on the columns to get everything in order by hour. And if you’re needing more than 500 rows worth of keywords containing cheese, pivoted by hour, you can also add &limit=50000:
&tcols=50000&q=cheese&tpivk=hour&limit=50000
If you were able to make good use of one of these tips, let me know! I’d also like to hear any suggestions for improvement or other things you might like to be able to do with the Google Analytics Interface, but aren’t sure if it can be done. Maybe you’d like to see this made into a Greasemonkey script or add-on? Let me know!
