×
October 30, 2020

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:


(Click for a full-sized image.)

 

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!

Gary Devine

Gary Devine

Head of Search

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...

See All Posts

Contact Us

Google Rating
4.8
Michele Miller
Michele Miller
05:52 25 Oct 17
We asked Direct Clicks to build a website for Robokids, we wanted to ensure they understood our brand and market. Highly recommend Direct Clicks, the service and communication are fabulous. Graham was just terrific.
Christopher Cassidy
Christopher Cassidy
08:53 11 Aug 17
Graham did an excellent job bringing the vision of my website to life. He explained things well, was easily contactable and very responsive. Highly recommended! Thanks again
Amir Reyad
Amir Reyad
02:44 25 Sep 17
Gilard is my campaign manager, he has done a remarkable job for my antenna business. At first I was hesitant but I now say I have full trust is these guys. My business has never been healthier. They have really proved them selves. I referred a friend which manages a solar business and within weeks he’s advised me that their doing a great job! Thanks Gilad and Ben for your hard work. I’m glad for you guys to be part of our business
Brad Lawson
Brad Lawson
03:10 13 Sep 17
Solid company with great staff, always very responsive. Anything we do with Direct Clicks is thoroughly explained and we're always kept in the loop. It's been nearly 2 years and we haven't looked back since going with Direct Clicks for our online marketing.
Juliana Lalli
Juliana Lalli
13:07 10 Aug 17
Professional and service driven. We changed our minds so many times and the team were very accommodating and patient. From pre sales to completion, they were fantastic. Highly recommend Direct Clicks and will be certainly using them in the future.
Katryna Tancred
Katryna Tancred
04:08 25 May 18
We were hesitant to change from managing our own ads to paying a fee but we couldn't be happier with switching to Direct Clicks. Our viewers are more engaged and we're converting more leads. They spoke our language and made sure we understood the options we had. The after sale service cannot be faulted. Highly recommended
Mike Thomas
Mike Thomas
02:29 02 Jul 18
I can thoroughly recommend Direct Clicks. They are very knowledgeable and easy to work with. Gary really knows his stuff and it is a pleasure to work with him. We also worked with Ben and Amir and together they were a great team. Mike Thomas
Unscripted Signs Sales
Unscripted Signs Sales
09:16 09 Aug 18
Great company and great staff who delivered excellent results! We will continue to work with them....
Keith Betty
Keith Betty
23:25 13 May 18
We engaged Direct Clicks to re-design and upgrade our existing website - which involved layout, structure, underlying tech, transfer of existing domains and ongoing hosting. We found both the sales as well as the delivery processes to be '5 star' and would recommend them without hesitation.
Saul Gerber
Saul Gerber
03:36 31 Jan 18
I had a very successful experience with this company. Gilad Shabab was my contact and he was extremely helpful. My psychology practice built up well and is now self sustaining. I would not hesitate to recommend to friends. Thank you Direct Clicks
Levi de Bilde
Levi de Bilde
01:12 04 Dec 18
Long time customer of +4 years and still going. Direct Clicks offer great support, advice and a seamless service. Thank you !!
Elaine Huang
Elaine Huang
00:31 12 Dec 18
Outstanding customer service experience working with Amir at Direct Clicks
KEYless Entry Systems
KEYless Entry Systems
23:50 28 Jan 19
After trying numerous agencies ( Sponsored links and Reach Local), Direct Clicks were recommended to me. The difference was amazing. I can actually talk to someone and the support I have received from both the Adwords and SEO teams has been first-class. Highly recommend.
Live2ride Team
Live2ride Team
02:31 05 Feb 19
We are extremely satisfied with the results Graham and the team at DirectClicks delivered for our website. Migrating a website from a previous provider is not always an easy task, but Graham was able to guide us step by step, explaining our options in detail, as well as giving us an easy to follow tutorial on how to use WordPress. I would highly recommend DirectClicks to anyone seeking assistance in creating a new website. Great company, excellent service and support!
Sandra Checch
Sandra Checch
22:37 14 Apr 19
Great team to work with esp Helen. Always friendly and helpful
Ben Hindle
Ben Hindle
03:43 04 Apr 19
Just recently started doing business with DirectClicks and have been impressed with the service, knowledge and attention to detail of Gary.
Brett Ketelaar
Brett Ketelaar
23:48 13 Mar 19
Highly recommend Direct Clicks. Great web design and fresh ideas
Alen Barsamian
Alen Barsamian
08:24 27 Apr 19
Easy to deal with and very knowledgeable, have seen positive results and more clients since signing up with them, Thank you team!
Sophie Farrar
Sophie Farrar
01:00 30 Apr 19
We have worked with Direct clicks for a while now and I am always impressed with the wonderful service from Gary Devine. Highly recommend.
Olga M
Olga M
00:41 14 May 19
Professional and quick response. Gary, my campaign manager, has added value to my business with his suggestions and monthly reports.
Graham Peters
Graham Peters
03:44 12 Jun 19
There are many companies around that offer similar services. What I like about Direct Clicks is that they really understand the important roll their work plays in the success of the businesses they serve. Additionally, and importantly, they take responsibility for their work and the associated outcomes.
Khal
Khal
06:22 27 Jul 19
Amir has been such a great help! What an amazing team! Been with them for years and have only had gains and lots of insight into business marketing! Thanks Amir and Ben you guys are life savers!
Albert Dorostkar
Albert Dorostkar
03:13 05 Aug 19
i"m very happy with the level of service i received from Eva Li and the team at Direct clicks.Highly recommend the team from Direct Clicks. They have always been fast and efficient with their service. Thanks Eva Li
George Winners
George Winners
10:57 28 Aug 19
Direct Clicks is one of the best digital agencies i have dealt with for marketing my business, very professional and knowledgeable.
Georgette Chaoul
Georgette Chaoul
10:14 29 Aug 19
Great service and very communicative account managers. Creative
Tony Collins
Tony Collins
04:57 06 Sep 19
Thank you again Graham. Looks great. Job well done again. Please pass on to all a thank you. It is never a problem when I ask for help with our web page. I have already recommended you to others.
Jared Roomer
Jared Roomer
22:38 17 Sep 19
Direct Clicks are real professionals. They take the time to really understand their customer and the industry. Very happy with their friendly and efficient service too.
Mikarla Bauer
Mikarla Bauer
03:10 10 Nov 19
Direct Clicks have been an amazing company to deal with. i approached them last year to do my web design and SEO and the results and service have been first class. We have doubled our bookings over the last 12 months and significantly grown the business. I have been recommending their services to everyone!!
Hannah Tansey
Hannah Tansey
22:09 24 Feb 20
The Direct Clicks team are SO helpful and friendly. No task is ever too much! Amir is extremely efficient and always patient no matter how many requests i have!
Berge Okosdinossian
Berge Okosdinossian
10:27 24 May 20
Excellent team and particularly our account manager, Bianca Cipri. She is highly attentive and responsive, turning things around in a timely manner, but more importantly, executing on our particular requirements. Highly recommend.
Tim Johnson
Tim Johnson
23:31 22 Apr 20
Very flexible, very attentive. Great media buying strategy. Happy to go above and beyond to get a campaign running well.
Tony Collins
Tony Collins
02:44 02 Apr 20
Once again had another request for the Team at Direct clicks to make a few changes with our web page and Once again was very impressed with their work. Thank you to all at Direct Click especially Graham (Always helpful)
leila diab
leila diab
00:16 17 Jun 20
Direct Clicks and in particular Bianca Cipri are an amazing advertising agency to work with. Bianca; our marketing coordinator, is always professional, dedicated and responsive to all campaigns from start to finish. I highly recommend you check Direct Clicks if you feel your business marketing is looking tired or needs to reinvent itself and requires that extra push!
Noreena Mijic
Noreena Mijic
00:31 07 Jul 20
Its been just a little over 12 years now that I have been using Direct Clicks as my wed designer and host. I truly believe that their ideas and advise for my website has been a major key to the growth and success of Cushions R Us Sydney. I have been working closely with Senior Content Manager, Graham Cattley who knows exactly how to project my ideas into words and images that addresses the audience accordingly. I have found Direct Clicks very responsive and reliable in withholding the design intent of the website, and continually being in touch with fresh changes and upgrades. I highly recommend direct clicks to anyone looking for a provider who's customer service is second to none. Thank you for all your hard work Graham and the Team and Direct Clicks. Noreena Mijic Director CUSHIONS R US SYDNEY
richard lowry
richard lowry
03:08 21 Jul 20
Amir has been most helpful through the years and business has improved tremendously since I brought Amir and Direct Clicks on board.
Big Bus co
Big Bus co
11:54 25 Aug 20
We have been looking for many years for a good company that can handle our Google Adwords and website handling and finally found Direct Clicks. We can't be happier with their service and quick response as well as professionalism. We highly recommend Direct Clicks if you would like to get your website up and running quickly.
Armani Gallery
Armani Gallery
00:51 10 Aug 20
Direct Clicks are as professional and quick as one can get. They are easy to deal with and are always friendly and helpful. Special thanks to Bianca & Amir for always being responsive, efficient and most of all patient with our constant requests and changes. Such an amazing team! Would highly recommend Direct clicks to anyone who is looking to enhance their business and bring it to life.
Christeen Seidhom
Christeen Seidhom
07:39 01 Sep 20
Very fast, response to our emails with minutes, get the job done 100%.
Paul Nicholson
Paul Nicholson
00:00 06 Aug 20
The team at Direct Clicks have been managing my website and online marketing for nearly 10 years. They have always been attentive and fast with any of my needs I would highly recommend their services.
See All Reviews Write a review
Facebook Rating
nan
DC Review
Scroll Up