Heatmapping Conversion Rates, Automagically

In this post I will briefly discuss the introduction of automated Google Analytics reports, and then share with you a report I created you that quickly generates a Conversion Heatmap via the GA API (Useful for informing your day parting strategy for PPC campaigns).

As a digital marketer, I've always felt that one key element of successful analysis is being able to focus more time actually reviewing and actioning the data, as opposed to digging it out.

Needless to say, I was happy when a couple of months ago Google upgraded the level of integration between its Apps Script services and Google Analytics.

Essentially this means it is now possible to programmatically access all you juicy GA stats. As the kids would say, automation of reports FTW.

Google provide a good introductory video, which shows you how it is possible to build a custom dashboard within Google spreadsheets, and also some pretty easy-to-understand documentation so I won't go to far into the specifics. But, getting data into a sheet is a quite simple two stage process:

Why is this useful? All the data is in GA?

I love how much data is available in GA. However, so much data can lead to a lack of actionable analysis - the noise created by not-so-meaningful numbers often clouds the real gems of insight.

Imagine a world where all your dashboards a pre-populated with only the information that is useful to your particular role. This is where using this sort of integration can make that simpler life a reality.

Giving it up: Conversion Heatmap

It's useful to know how well visitors convert to a sale or enquiry at times of the day or day of the week. This allows a strategic use of day-parting within PPC campaigns, meaning your given budget is spent at the most effective times.

Using the GA API and Spreadsheet integration I've created a report which quickly generates a report to highlight conversion hotspots, in a 'heatmap' kind of fashion.  

This Google Spreadsheet does the following:

• allows the selection of a GA profile and relevant goal
• grabs the conversion data by hour of day and day of the week
• pivots the data to allow cross referencing of days and hours
• conditionally formats the table to highlight areas of high conversion

By doing all this, it allows the analysis of any account to happen quickly, without the need to go and pull the data each time this report is needed. This is my analysis nirvana – all the insights, none of the legwork (but not in a lazy sense, of course).

As I've cribbed many things from tools shared by the digital marketing community, I wanted to open this up and share the tool with anyone who is interested

Using The 'Conversion Heatmapping' Report

To use the report just follow these really simple steps:

Step 1: Sign into the you the account you use to log in to AdWords and then visit the Google API Console.  Navigate to the API Access area and grab the API Key and fire into a text document for later.

Step 2: Open the 'Conversion Heatmapping' spreadsheet.

Step 3: In 'File' menu select 'Make a copy...' so it is in your Drive (Note: make sure you are signed in as Google seems a bit funny about directly logging you in when you open the page, so you may just need to hit 'sign in' in the top right corner if having trouble copying)

Step 4: Go to 'Tools' in the main navigation menu and select 'Script editor...'

Step 5: Once the script editor opens, choose 'Resources' from the menu and navigate through 'Use Google APIs...'

Step 6: In the dialogue that opens:

1. turn on the Google Analytics API
2. put in the API Key that you grabbed earlier.  Once done, you can close the tab that the Script Editor opened in.

Now that's everything hooked up, it's time to use the spreadsheet itself. To do so, simply:

1. Enter the Google Analytics profile number in cell J4 (Note: this is not your UA-xxxx number – see here for how to get the ID)
2. Select the goal slot that is home the the relevant action you want to pull the conversion rate for
3. Press the big shiny button

I hope you find this tool useful.  Have you found any practical uses since this integration has been rolled out?