Useful hourly reporting for Google Analytics

Several years ago, the Google Analytics team introduced a new feature allowing inquiring researchers to see a few metrics by hour of the day. It gave a much clearer picture of when visitors made time in their busy lives to read a websites’ content. It always helped me to estimate good times to publish new content or when to send an email. Sadly, the new version of Google Analytics breaks up the date range into hours showing the selected metric for each and every one. Here’s how to see what hour of day is most important to you and your website.

Step 1

Select the data range you want to view. Here, I’m viewing a year’s worth of data. Click the “Hourly” button along the top of the graph (highlighted in red).

Here’s the problem: this graph is useless. There are a few peaks, but it is impossible to figure out when those peaks occurred. Also, there’s no way to tell when visitors are coming to your website. Let’s make this into a graph that collates the hour across all days in the date range. To do that, we have to pull our data out of Google Analytics.

Step 2

Export the graph to your spreadsheet of choice. Here, I’m using Google Spreadsheets to handle the collation. Once you open up the file, you should have two columns of numbers. One labelled “Hour” and, if you’re using the default, “Visits.” The hours column numbers the hours in your date range from 0 – n. We need to change those numbers to repeat from 0 (midnight) to 23 (11 pm). There are several ways to do this. I’m going to use the mod method. Here’s the syntax:

=mod(dividend, divisor)

Step 3

In our case, the dividend will be the Hour column cells (A1, A2, etc.) and the divisor will be 24 (for each hour in the day). Once the formula is entered and copied all the way down your spreadsheet, it will be ready to be put in a pivot table.

Select the data and convert it to a pivot table. For each section in the pivot table wizard, put these items into the following sections:

The modified hours column goes in “Rows” (“Row Labels” in Excel) and the Visits go in the “Value” (also “Value” in Excel) section. You should then have a nice chart collating all the 1 a.m. values together, the 2 a.m. values together, etc.

Now, use the pivot table to make your graph. Using the basic tools in Google Spreadsheets, I made a handsome graph which I could take further and build into a presentation for a client. But that’s a post for another day.

Now you have a graph showing visits (or another metric) by hour of the day. Let me know in the comments if you’re having any issues and I’ll try to assist.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.