Google Analytics days of the week

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

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.

Here is the result:

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:

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:

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:

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 😉