Heat maps can be a really efficient way of analysing data. The colour variation can quickly help you spot the highs and lows of your data as well as trends. Often collating the data for your heat map can take a fair amount of time though, putting you off doing this rewarding exercise.

Let me show you how to turn your Google Analytics data into a heat map, fast!


In Google Analytics, head to Customisation and select Custom Reports. Add in metrics you’d like to analyse, e.g.:

  • Sessions
  • Transactions
  • Revenue
  • Conversion Rate
  • AOV
  • Goals
  • Goal Conversion Rate
  • Average Session Duration
  • Pages per Session


Under dimensions select Day of Week Name and Hour. You can pop a filter on if you want to analyse a particular set of users.

For example, if you’re analysing data to improve your PPC efforts you might want to select medium = cpc. If you’re trying to work out the best time to post on social media to drive conversions, select Default Channel Grouping = social.

Next, hit Save.


If you’re analysing data for an e-commerce site, try this template. It includes Sessions, Transactions, Conversion Rate, Revenue, AOV and Average Session Duration.


Once you’re in your report, make sure you can see all available rows of data and you have the correct date range selected. If you’re planning on making changes from the information you glean, choose a decent period of time. My suggestion, at least 90 days.

Now we’re going to export to Excel. It’s important that you choose Excel for exporting this report as we need to use a pivot table, something that Google Sheets or Apple’s Numbers just don’t offer. (*Update: Google Sheets have now released a pivot table feature woohoo! If you want to export to Google Sheets and use that for the following steps you now can.)


Open your report in Excel and navigate to the Dataset1 tab. Use the Insert menu to create a pivot a new pivot table. Arrange the fields as follows:

Day of Week Name > Columns
Hour > Rows
Transactions (or whichever metric you’re analysing) > Values


Now you should have something along the lines of….


Pre heat map data


Time to add the heat map!

Select the data ignoring any blank or total column/rows. Back in the Home menu, from Conditional Formatting select Colour Scales and choose whichever scale floats your boat.


Google Analytics Data With Heat Map Applied - Beeson Media


There you have it. Your Google Analytics data in a heat map. Woohoo! 🎉

Bet that was faster than you thought!

But….now that you’ve got the heat map, what are you supposed to do with it? 🤔


Capitalise on the Highs, Fix Up the Lows


See a few pockets of great results? Awesome. Capitalise on these where you can…

  • Increase PPC bids
  • Use sales driven messaging on social media
  • Schedule blog posts to go live
  • Send email campaigns
  • Launch new products
  • Have flash sales

Where you have sections of low performance, review your marketing strategy for these times. Are your ads visible? Are you posting on social media at these times? Or if you’re location specific, are these simply times where your target market is asleep? If so, reduce any wasted marketing efforts.


For All the PPCers Out There


Conversion data heat maps and ad scheduling go hand in hand. If you don’t already adjust bids according to their hourly performance, get on it!

Use the method above to find the best converting hours for you then checkout the BrainLabs Digital 24 Hour Bidding script to automate the process. This can reduce wasted spend on your account by loads and help you maximise conversions.


So there you have it. Google Analytics, custom reports, pivot tables, data heat maps and even AdWords scripts.

Enjoyed this post? Learned something new? Ah, that’s great! 🙌 How about showing your appreciation by sharing it? Every little bit helps ☺️

Thanks for reading and happy analysing!