Google Analytics days of the week

June 2013 EDIT: we now have a new Google Analytics dimension for day names:

google analytics days of the week new dimension

I get this question a lot:

Which days of the week are the most profitable for my website?
How can I see Google Analytics days of the week?

You should know that this kind of information is not available as is in Google Analytics 🙁
(Now you know)

However, here is a method you can use to get to that information.

Creating our custom report for Google Analytics days of the week

Back in the day, there was a report in the Visitor section of GA v4 that would list the date of a visit in a readable format, such as “Wednesday, April 1st, 2010”. By extracting this date in Excel, you could get insights on which day of the week drove the most traffic, conversion, etc. But that report is gone now so suck it up and try the following replacement method, involving custom reports.

Create a custom report and call it “Days of the week” and select the “Flat table” mode.

Add the following dimensions:

  • Day of the week
  • Date

Add the metrics you need. In this example we will stick to visits and conversion rate but go crazy if you want.

custom report google analytics day of the week

Here is the result:

days of the week

As you can see, you get a list of day numbers (0 for Sunday, 1 for Monday, 2 for Tuesday, etc.) and dates clumped in YYYYMMDD format.

At this point the custom report is already very useful as is. You can use an advanced filter to exclude days where the day number is equal to 6 or 0 to obtain week days. Include only day numbers of 6 or 0 to get week ends. Make advanced segments too!

You guessed it: if you want to exploit this data outside of Google Analytics, you now need to use a spreadsheet such as Excel, Google Spreadsheet or a program such as Tableau after you export that data.

Once you are satisfied with the custom report, click on the Export button at the top of the screen:

Google Analytics days of the week export options

Choose the “CSV for Excel” or the new Excel (XLSX) format, download the file onto your computer and open it in Excel/Google Spreadsheet or another tool of your choice. If you chose XLSX, you will get an Excel workbook with two tabs; navigate to the “Dataset1” tab.

You should be looking at this kind of report:

excel dump - days of the week

Now start replacing numbers with days of the week. Use a good old Find/Replace or use the following macro:

[code]
Sub replaceDaysOfWeek()

‘ this macro will replace days of the week (0-6) with actual weekdays ( Sunday, Monday, etc.)
‘ by Julien Coquet http://juliencoquet.com

Columns("A:A").Select
Selection.Replace What:="0", Replacement:="Sunday", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="1", Replacement:="Monday", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="2", Replacement:="Tuesday", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="3", Replacement:="Wednesday", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="4", Replacement:="Thursday", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="5", Replacement:="Friday", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="6", Replacement:="Saturday", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub
[/code]

EDIT: the macro part is optional since June 2013 since you can use the day name dimension instead of the day ID.

Ok, “what now?” you ask. Well, it’s PIVOT TABLE TIME!

Pivots tables for the win

In Excel, go to Data > PivotTable Report

A new dialog box opens, in which you should drag the Day field on the leftmost column of the table and the Visits Data on the pivot table builder. You will get the following preliminary table:

Google Analytics day of the week pivot

Now add some sorting on metrics and bam! You finally get the table of your dreams 😉

Of course you could stick to Google Analytics custom reports but you will not be able to “read” week days normally.

Conclusion:

In all fairness, you can use the Google Analytics interface but we all know our beloved HiPPOs and boses do not use the interface and we have to feed them Excel spreadsheets.

That said, now that you have this method, you can select a specific data range and update your Pivot Table in an instant!

You can also go directly through the Google Analytics API to retrieve this kind of information!

Of course, if solutions other than Google Analytics provide the same kind of CSV export, you can apply the same process 😉

Thanks to my colleague Yehoshuah Coren for reminding me of the topic 😉

As always, your constructive comments are welcome 😉

Author: Julien Coquet

Julien Coquet is a veteran of digital analytics, with 20+ years of experience in al things data, web and app. He is currently Senior Director of Data, EMEA @ Monks.

One thought on “Google Analytics days of the week”

  1. This is the second blog post, of your blog I actually browsed.
    Although I really like this particular one, “Google Analytics days of the week – Julien Coquet – Negligible Quantities – blog
    web analytics” the best. Thanks -Dylan

Leave a Reply

Your email address will not be published. Required fields are marked *

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