{"id":4137,"date":"2020-05-13T06:04:48","date_gmt":"2020-05-13T03:04:48","guid":{"rendered":"https:\/\/trackingchef.com\/?p=4137"},"modified":"2022-10-27T16:19:27","modified_gmt":"2022-10-27T13:19:27","slug":"easily-creating-day-hour-heatmaps-from-google-analytics-in-excel","status":"publish","type":"post","link":"https:\/\/trackingchef.com\/google-analytics\/easily-creating-day-hour-heatmaps-from-google-analytics-in-excel\/","title":{"rendered":"Easily creating Day\/Hour heatmaps from Google Analytics in Excel"},"content":{"rendered":"\n
One of the more interesting exports I use with clients is a breakdown of metrics by Day of the week (Sun-Sat) and Hour of the day (0\u201323). This gives a clear visual representation of peak hours on their web properties.<\/p>\n\n\n\n
While many metrics can be used, I usually find these to be the more interesting ones: Sessions, Conversions, Conversion rate.<\/p>\n\n\n\n
In this guide, I\u2019ll walk you through building the above three, and how to extract valuable insights from them.<\/p>\n\n\n\n
The data shown in the guide is extracted from Google\u2019s demo account<\/a>.<\/p>\n\n\n\n UPDATE:<\/strong> I’ve added a new post<\/a> on how to extract this report in Google Data Studio (simpler, faster, always updated!).<\/p>\n\n\n\n In order for us to get this detailed breakdown we need to create a Custom Report in GA.<\/p>\n\n\n\n Start by clicking Customization in top navigation bar and select \u201cNew Custom Report\u201d.<\/p>\n\n\n\n The report type I like to work with is \u201cFlat table\u201d, which can accommodate up to three dimensions.<\/p>\n\n\n\n In the dimension section, add \u201cDay of week\u201d and \u201cHour\u201d(Note that there are several variations, these are the easiest to work with).<\/p>\n\n\n\n In the metrics section, for this example add \u201cSessions\u201dand \u201cGoal completions\u201d.<\/p>\n\n\n\n Save the report and set the time frame for the data.<\/p>\n\n\n\n I like to take data from a wide time range to get a better sample. Usually 3 month of data is good enough. If your site is heavy on traffic make sure you don\u2019t run into sampling issues as this might skew your report.<\/p>\n\n\n\n In this example I\u2019ve extracted data for the months of September-November 2016.<\/p>\n\n\n\n Be sure to extract all rows (should add up to 168) by adding rows to the report view.<\/p>\n\n\n\n Now you can extract it to Excel (xlsx format).<\/p>\n\n\n\n You can grab my ready made report here<\/a> and insert it to you Analytics account.<\/p><\/blockquote>\n\n\n\n In the Excel workbook we will only use the second sheet, \u201cDataset 1\u201d.<\/p>\n\n\n\n First, if the numbers in the Day and hour columns aren\u2019t identified by Excel as numbers, select the two columns. Open the small drop-down and select \u201cConvert to Number\u201d.<\/p>\n\n\n\n Now, select the entire table and one the \u201cInsert\u201d tab on the menu click \u201cAdd Pivot table\u201d.<\/p>\n\n\n\n In the popup window click OK. This will open the Pivot table in a new sheet.<\/p>\n\n\n\n Now you will see the new sheet with an empty Pivot table. Drag the \u201cDay of the week\u201d item to the columns section and \u201cHour of the day\u201d to the rows section.<\/p>\n\n\n\n Finally, drag the \u201cSessions\u201d item to the value section.<\/p>\n\n\n\n Voil\u00e0. You have a detailed table depicting the traffic to your site by days and hours.<\/p>\n\n\n\n To make this table easier to read, we will now add a heatmap to bring out the high\/low areas.<\/p>\n\n\n\n I recommend copying the table to a new sheet for this.<\/p>\n\n\n\n Select only the table\u2019s values (without Day\/Hour) and select Conditional Formatting. I like to use a color scale of red\/green. You can play with colors however makes sense to you.<\/p>\n\n\n\nStep 1 \u2014 Extracting the data<\/h2>\n\n\n\n
<\/figure>\n\n\n\n
<\/figure>\n\n\n\n
<\/figure>\n\n\n\n
Step 2 \u2014 Manipulating the data<\/h2>\n\n\n\n
<\/figure>\n\n\n\n
<\/figure>\n\n\n\n
<\/figure>\n\n\n\n
Step 3 \u2014 Presenting the data<\/h2>\n\n\n\n