Advanced Heat Mapping for Google Ads
Shows Performance with Time Segments
Heat mapping is a great way to find insights in complex data sets.
Gary shows how to pull data from Google and present it in a heat map that helps you find the low-hanging fruit.
Did you know that you can pull a heat mapped report from Google Ads that shows you performance not just by day of the week or hour of day, but both at the same time? It looks like this:
If you did know that, congratulations – you’re awesome!
If you didn’t, I have put together this guide so that you can run the same report and get a level of insight not readily available from the standard Google Ads interface. This guide will require you to have an active Google Ads account and Microsoft Excel.
Part one: pulling the data from Google
1. Login to Google Ads
2. Set the date range to the period you want to assess
3. At the top, click on reports, then reports again
4. Select ‘custom’, and then select table
5. Search for level of detail ‘day’, and add in day of the week and hour of the day as ‘rows’
6. Search again for level of detail ‘conversions’, and add in the conversions as a column
7. Search again for level of detail ‘cost’ and add in the cost as a column
8. You are now ready to download the report!
Part two, using excel to make it pretty!
1. Apply filters to the header row
2. Arrange hour of the day by smallest to largest
3. Select the whole table and apply pivot table
4. On the now blank pivot table tab add the day of the week in as the ‘rows’
5. Add the hour of the day as the columns
6. Set the value as ‘sum of conversions’
This will then give you the table you can see above without the heat mapping. You will need to open a new tab and copy and paste the table there. You will not need to run the whole report again, you will only need to change the values field (Part 2 step 6) ‘sum of cost’.
Once you have this you will need to copy and paste the new table on to the tab you created a moment ago. You will now have both tables on the same sheet but you will not have the CPA table. In order to generate the CPA table, you will need to manually layout the column headers and row titles as you can see on the image above. Then run the CPA formula (Cost/Conversions) across all cells.
The last part is to apply conditional formatting (colour scales) to each section. Notice how the total rows differ from the main body? You will need to make sure each is heat mapped separately so that the formatting doesn’t get skewed.
And there you have it! I hope this report helps you optimize your campaigns a little better, and I hope you are able to increase your conversions or to set smarter schedules based on what you learn.
Please feel free to contact the author if you have any questions about this process or are still learning how to make the most of Google Ads!
I’ve worked in Advertising & Digital since I graduated from university in 2010. I spent 2 and half years working for Google in the UK where I fell in love with digital and creative work. I now run a great team here in Sydney doing great work.
YOU MIGHT ALSO LIKE
From our blog posts...
Mon - Fri: 9am-5pm
Direct Clicks is one of Australia’s top digital agencies, providing a range of services including Google advertising, search engine optimisation (SEO), website design & development, social media marketing & advertising and much more to help businesses grow online.