Google Analytics Hack – More Columns for Pivot Tables
Tags: Google Analytics, hacking, hacks
This is an advanced hack for Google Analytics. Unfortunately it won’t help you hack into your competitors’ accounts ;) but will help you export more data from Google Analytics pivoted tables.
This is not the same hack as the exporting more than 500 columns of data from any report. That one was one of the most annoying things I fixed (to be read hacked) long time back.
You’ll need:
- Firefox
- Live HTTP header (http://livehttpheaders.mozdev.org/)
What are we hacking?
We’ll make possible for the analyst in you to download more than 5 columns from Google Analytics’ Pivot Table reports. Warning: it may cause head bleeding from too much banging against the wall when you’ll analyze more data :)
Usually, when you download pivot tables, you will be able to download only 5 columns (pivot by landing pages – only 5 in the example below:
Only 5 Columns in Pivot Tables
Ok, let’s start:
- Go to any report that has the pivot table option and segment the data the way you want to analyze it (for testing purposes you can use the example above: go on Traffic Sources report, then Referring Sites and then pivot by Landing Page and choose Showing Visits)
- Start Live HTTP header (in Firefox, under Tools) – do not load any other pages between step 2 and 3
- Make sure you checked the Capture box on live HTTP header
- Go back to your Google Analytics window and select a new number of page rows on, under Show rows (the footer of the pivot table). At this moment, the table should reload – tricky, the URL didn’t change ;)
- Open the Live HTTP header window
- The first line there should be the request sent to Google Analytics in order to reload the pivot table. You should see something similar to
Live HTTP Header
- Copy the link (should be the first if you didn’t load any other page or made other HTTP request – music or video streams) and load it in another tab. You should have something similar to:
Pure html table
- Now look for the &tcols=5 parameter. Change it to &tcols=50 and load the new URL
- Here you go, 50 columns:
50 Columns In Pivot Tables
This data is what you need, but you’ll just make your life even more miserable. You want to have this exported in Excel.
Here’s a bonus hack for Google Analytics (I feel like today you should buy me a coffee)
The URL that exports Google Analytics reports in CSV for Excel (&fmt=5) is:
https://www.google.com/analytics/reporting/export?fmt=5 and some parameters after.
You will have to copy everything starting and including &id=…. from the URL you previously modified (the one with &tcols=50) and append it to Google’s export URL. The export URL will look like:
https://www.google.com/analytics/reporting/export?fmt=5&id=1234567&esig=4&seg0=-8
&pdr=20091016-20091115&cmp=visit_segments&trows=25&rpt=ReferringSourcesReport&seg=1&view=4
&tchcol=0&tst=0&segkey=source&afs=false&eid=TableChanged&tab=0&tcst=0
&tpivk=request_uri_1&tcols=50
Exported Pivot Columns
Now, go back and start trying to do the same.
Pitstop Media offers ROI based internet marketing services results oriented web analytics services. With the use of Google Analytics we’ve helped companies reduce paid advertising cost by 148% while we increased their conversion rates by as much as 410%. Let us increase your conversion rates, too!
TraiaN said:
Nov 25, 09 at 1:20 pmThanks Claudiu. You got a new subscriber on your blog :). Just let me know if greasemonkey will do it.
Claudiu M said:
Nov 25, 09 at 1:15 pmTotally love it… especially as I was baning my head to the wall when seeing that Google Analytics only gives 5 by default. Funny how i got to your article just 2 hours after I got my head bleeding :)
Wondering if a greasemonkey scripts could be made out of it.
Carter Cole said:
Nov 25, 09 at 5:52 pmi was sent here by the analytics twitter account… why dont they just let you export more?
TraiaN said:
Nov 25, 09 at 7:24 pmwouldn’t then life be to easy? :)
DaN said:
Nov 26, 09 at 5:13 amThx!
cliffwang said:
Sep 04, 10 at 7:28 pmcool, thanks for sharing. I’ll try it.
Mike S said:
Sep 11, 10 at 4:53 pmStill sounds like a lot of manual effort if you want to do it week after week. Inexpensive products like Next Analytics for Excel would automate it and not suffer any limitations of 50 columns or 500 rows.
AK said:
Feb 21, 11 at 7:54 amHi,
Thanks. This method appears to work only for the first 100 columns. Is there a way to download say a 1000 columns?
TraiaN said:
Feb 21, 11 at 10:08 amTry to combine it with this google analytics hack. Let me know if it works :)
Maarten Berge said:
Sep 27, 11 at 10:36 amNice hack. Have you figured out how to do this in the new GA interface? Found this variable:
%26explorer-table-pivotTable.pivotStart%3D0 which means start from position 0 in the pivot. I did try and set %26explorer-table-pivotTable.pivotEnd%3D7. Dreaming that it there were a variable that one could overrule the max columns to get more say 7 columns in my example. The variable might not excist. Just started looking at this so I may find figure it out.
TraiaN said:
Sep 27, 11 at 11:28 amHi Maarten,
I didn’t try with the new interface yet. One thing I do is keep an IE window with the old interface (for purposes such as managing the advanced segments, exports, etc) and use the new interface for reporting only.