{"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

Step 1 \u2014 Extracting the data<\/h2>\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

\"image-4564768\"<\/figure>\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

\"image-6623607\"<\/figure>\n\n\n\n

Now you can extract it to Excel (xlsx format).<\/p>\n\n\n\n

\"image-7184840\"<\/figure>\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

Step 2 \u2014 Manipulating the data<\/h2>\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

\"image-1859250\"<\/figure>\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

\"image-2298283\"<\/figure>\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

\"image-4758039\"<\/figure>\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

Step 3 \u2014 Presenting the data<\/h2>\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\n

Note that \u201c0\u201d on the Days axis is Sunday and so on.<\/p>\n\n\n\n

\"image-9282672\"<\/figure>\n\n\n\n

Now that the data is on a new sheet we can replace the \u201cSessions\u201d in the Pivot table with \u201cGoal Completions\u201d and get the same report for this metric.<\/p>\n\n\n\n

You can grab my xlsx file to compare<\/a>.<\/p><\/blockquote>\n\n\n\n

Optional Step #1:<\/h3>\n\n\n\n

You can also add the total row and column and apply a color scale specifically for each (e.g. selecting only the total row). This will give you insight to comparing entire week days (Which days sells best in total? Which hour is best for daily newsletter?).<\/p>\n\n\n\n

Optional Step #2:<\/h3>\n\n\n\n

Create a third table that takes calculates the conversion rate for Goal Completions. You can either extract the number as an additional metric when creating the report, or calculate it combining the two tables you created.<\/p>\n\n\n\n

Step 4 \u2014 Analyzing the data<\/h2>\n\n\n\n

Analyzing the data at this level should take into account if your site works across multiple timezones. Behavior can also differ based on traffic sources, for example a weekly newsletter sent every Thursday morning will effect that day\u2019s traffic significantly.<\/p>\n\n\n\n

Looking at the two reports we produced we can see several key behaviors:<\/p>\n\n\n\n

  1. Morning hours are of the highest traffic, peaking at 10am.<\/li>
  2. Early morning visitors, 6\u20138am, have a lower conversion rate than later morning traffic.<\/li>
  3. Goal conversion rates are highest at 1\u20133pm.<\/li>
  4. Monday through Wednesday are the busiest days, peaking on Tuesday.<\/li>
  5. Similarly, and quite unsurprisingly, Sunday and Saturday are slow days, with Saturday afternoon\/evening being lowest.<\/li>
  6. Friday has a surprisingly high conversion rate at 10am-3pm.<\/li><\/ol>\n\n\n\n

    This of course isn\u2019t a closed list and there plenty of other insights that can be drawn from these reports.<\/p>\n\n\n\n

    Actionable takeaways from the data<\/h3>\n\n\n\n
    1. Test \u201cEarly Bird\u201d campaigns on site to encourage the 6\u20138am visitors to convert.<\/li>
    2. Similarly, test driving traffic by email campaigns on Friday morning\/noon.<\/li>
    3. Routine Email marketing campaigns should be set to send at 10am-2pm.<\/li><\/ol>\n\n\n\n

      Final thoughts<\/h2>\n\n\n\n

      This is a rather simple exercise that can generate some quick insights to user behavior. That being said, like all analysis it should be taken with a grain or two of salt.<\/p>\n\n\n\n

      Traffic coming from different sources will behave differently. Traffic to your blog will differ from your product pages.<\/p>\n\n\n\n

      Any insight drawn from these reports should be double checked against typical behaviors that you\u2019ve identified in key segments.<\/p>\n\n\n\n

      And of course, test any change you apply based on your analysis. It just might be that current behavior is working fine and your tweaks will change it for the worse. Set a clear goal you want to improve and only then examine how your changes affect the users.<\/p>\n\n\n\n

      Happy analyzing! \ud83d\ude42<\/p>\n\n\n\n

      Originally published on my blog on Medium<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

      A 5-min guide to create and analyze an insightful report of user behavior on your site.<\/p>\n","protected":false},"author":1,"featured_media":4138,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37,2],"tags":[38],"_links":{"self":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/4137"}],"collection":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/comments?post=4137"}],"version-history":[{"count":5,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/4137\/revisions"}],"predecessor-version":[{"id":5384,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/4137\/revisions\/5384"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/media\/4138"}],"wp:attachment":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/media?parent=4137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/categories?post=4137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/tags?post=4137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}