Update 5th January 2013: I’ve made this approach a little easier, faster and more accessible for your average Joe. Thanks to Dries Bultynck, Adrian, Wenting and Jordan for encouraging me to fix this up.
Last week, Tim Leighton-Boyce shared a post about day-of-the-week reporting in Google Analytics V5. It reminded me of a cool little visualization Core Metrics uses to illustrate the best converting hour and day of the week, as well as how to construct it in Microsoft Excel.
Yes. It’s really that simple to do, and gives you a visual indication of predicting sales and email sweet spots, and when to increase the PPC budgets.
Here’s the unbelievably simple way to make it happen…
Getting the data
First of all, you’ll need Google Analytics, Excel 2007 (or later) and a LOT of data. ‘How much data, Rob?’ you say? Well, in the above example, I used a sample of 300,000 visits. ‘Ooh…that much.’ should be your response.
Anyway, once you’ve got that little bit sorted, here’s your next step.
- Load up this Google Analytics custom report I built for the job (Hour & Day of the Week)
- Apply any segments you need and select a date range that includes full weeks with LOTS of data
- Export the days of the week report into Excel or CSV format - keep in mind you need to update the number of rows on the report (see the little drop down beneath the data table)
- Open it up in Excel 2007+
- Remove the “Totals” row at the bottom of the data
- Select the data and create a pivot table in a new spreadsheet
- Put “Hour” in the rows and “Day of Week” as the columns (show example)
- Place whichever metric you like in the values area
- Copy the table and paste the “Values only” into another spreadsheet
Creating the visualization
Then format it as a table to make it more readable.
Voila, you’re done.
It can just as easily be applied to other metrics, like ROI, revenue per visit, average order value, bounce rates and every other buzz metric you have to report for your own purposes, or for clients who require buzz metrics.
Hopefully this visualization gets you thinking about the needs of your visitors at particular times of the day and week. If not, then I probably need to work on my conversion skills, too.
Important: If you want to see accurate conversion rate totals, you will first want to create a pivot table for visits, and a second table for sales/bounces/goals/revenue. From this point, you will be able to calculate a conversion rate by dividing the goals table by the visits table. This will give you more accurate totals, rather than an average generated off the rates Excel sees.
Alternative way to visualise the data - Show it along a single axis like this:
Need a hand?
Let me know in the comments and I will help you out.