Visualize Your Best Converting Hour & Day of the Week

August 23rd, 2011 → 4:45 pm by Robert Kingston // 10 Comments

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.

Hour and day of week visualisation of conversion rates using Google Analytics and Excel 2007+

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
Drill down into each day of the week and export the hourly breakdown of each day (Zero is Sunday, One is Monday and so on…)
Now, go to the “Hour of Day” tab up the top of the report and export the hourly break down of all days combined.
With that data, you can now build a table like I have done above – you may need to sort by hour of day before popping it into the format I’ve used.

Now, you can either download and use my template here, or create the visualization yourself by following the instructions below (both options are easy, which I’ve already pointed out, and let’s face it; you can, too, because you’re reading this blog).

Creating the visualization

Let Excel do the work (she’ll do all the work for you if you treat her right.) Anyway, just apply the “colour scales” conditional formatting to the top data row, the far right data column and the core section of the chart.

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.

Share this post

Show it to your friends: Tweet

You Might Like These Posts Too

Subscribe to my blog for more great analytics and conversion optimisation tips.

10 Comments → “Visualize Your Best Converting Hour & Day of the Week”

Download Custom Report to Compare Days of the Week in Google Analytics

6 months ago

[...] handy conditional formatting feature to make the interesting data really sing out. For example, to visualise your best converting hours of each week [Opens in new tab]. The post includes an Excel template.Here’s the import link again: [...]
Downloadable heatmap visualisation of best time of day and week « Ecommerce Analytics Tips and Links

6 months ago

[...] http://www.optimisationbeacon.com/analytics/visualize-your-conversion-rate-by-hour-day-of-the-week/ Share this:TwitterLike this:LikeBe the first to like this post. [...]
Tim Leighton-Boyce

6 months ago

I really like that! And thank you for the mention.

It occurs to me that it might be good to experiment with other metrics as well as conversion rate. The snag with conversion is that a few sales in the early hours of the morning (for example) could give the wrong impression.

It might be worthwhile considering simple revenue, for example.

I’d also be tempted to filter the information. You refer to applying ‘any segments you need’. I’d certainly experiment with data which excludes sources like your existing email visits in order to get an idea of what people would ordinarily do.

There’s so many things you could do with this technique! There seem to be endless possibilities.
Anon

6 months ago

This is more than awesome.

I just ran this for my all-time AdWords data (as it is one of few variables I can really control) and the info is very clear as to what we should do – now if only the client can understand!

(around here we have a saying “I can explain it to you, but I cannot comprehend it for you” and unfortunately this applies occasionally…)

As for any shortcuts, how do you end up collecting this data without clicking a million times on Analytics to get the data sorted right, etc etc?? (click once to get the day of the week by hour, click to sort, click to get more than 10 on the page, then export, then back-key a bunch of times to get back to day of the week, and then rinse and repeat 7 times…)

there’s got to be a way to do some kind of macros with the new GA.

Meanwhile, awesome job done by the Optimisation Beacon folks!!
Robert Kingston

5 months ago

@Tim – Thanks for your kind words – Simple graphs like these can be quite useful… Good point about the wee hours of the day – luckily there were no spikes in my data. That said, if there ever were, I’d love to see revenue graphed. Per visit value could also be good – if you have the data that is.

@Anon – Thanks, I’m glad you liked it. Truth be told, I wish there was a shortcut! Unfortunately I did it all manually in GA V5. Hopefully this graph shows your client exactly what they need from you. Perhaps you could do two – one showing the current state and a second showing the ideal state and what that means in dollar figures for them.
Rob Kingston

5 months ago

Actually, regarding a shortcut – there surely must be a way to do it in Excel using Excellent Analytics. Would love to hear if anyone can put a solution together…
lydia

4 months ago

thanks for this Rob!
what actions did you take out of the insights you got from the highest and lowest conversion rates by hour and by day?

did you for example, change your marketing plans to focus on certain times of day etc.

and i think you should also take into account the no. of visits by hour as well, because just looking at conversion rates alone would skew the picture, (if say i had very few visits my conv rates would look awesome)
Robert Kingston

4 months ago

Thank you, Lydia.

One of the insights I took away from this is that you could run more appealing offers when conversion rates are low and ease back on offers when conversion rates are higher (that way, you can ride a wave of extra revenue coming into the site).

There are some caveats to this – just like you and Tim mentioned. Conversion rates can change dramatically when data is lean. Also, some of the peaks may correspond with email marketing or day parting in SEM campaigns. Always a good idea to be aware of how that may affect the metrics. One thing you may like to consider is using bounce rates or pages/visit instead.

Either way, tonnes of data will usually smooth peaks out.
lydia

4 months ago

thanks Rob. that’s true about the sem campaigns and edms. i’m going to check when we blast out those edms. hmm.
by the way i’ve just finished the analysis for our website using this method. keep you posted if we see any results!

oh, and i’ve started following you on twitter.

cheers
searchengineman

4 weeks ago

What a great Chart…Really Useful

Searchengineman