Update 5th January 2013: Regretfully, Google discontinued the API that Mikael’s excellent script is based upon – and therefore this approach is broken. You can still do it manually – it’s just as fast!
Read my original post on how to create a heatmaps of analytics data here.
Thanks to a smart Finn called Mikael Thuneberg, I’ve managed to whip up a quick and dirty little Google Docs template which builds these Google Analytics heatmaps in seconds (much easier and faster than the old method):
How it works
How to do it
IMPORTANT: Google disabled the API, so please read my old post on the manual heatmap method (it’s still reasonably quick :).
Watch the instructional video below (I’m a YouTube newbie, so you may have to bear with it) or follow the steps outlined afterwards.
Step 1: Load the template from Google Docs and save a copy to your Google Account (ensure only you have access to this as your password will be stored in the spreadsheet). Step 2: Pop in your username and password, into the fields on the “Data” spreadsheet tab. Feel free to hide your password in the spreadsheet. Step 3: Grab your Profile ID (Note that this is not your UA number!) from the URL in your Google Analytics interface and pop it into the corresponding cell in the template. You’re looking for the number in the URL which looks like this:
Therefore, you would use “21652238” in the spreadsheet. Step 4: Set your date range – you’ll want a decent date range so that you have a couple of hundred thousand visits at least. Step 5: Pop the ID of an advanced segment into the spreadsheet. Some are already provided under the inputs area. Otherwise “-9″ will work for most sites with goals setup. Step 6: Jump into the reporting tab at the bottom of the screen. Step 7: Copy the tables you’d like to use and pop them into Excel, and dress them up in a table with conditional formatting.
Either download my example conversion rate heatmap template for Excel or use conditional formatting and table formatting icons up in the ribbon (note you’ll need Excel 2007 for this):
Whereas the old process would have taken 30-60 mins to aggregate in the GA interface, this less than 5 minutes – and much faster with several cups of coffee.