It is surprisingly hard to get day-of-week information out of Google Analytics, and the ‘best’ scenario I have heard is to compare one week to another. That’s not much of a trend, but I guess it’s something. With Next Analytics for Excel, there is a simple way to get reports by day-of-week that lets you create some amazing reports. I’ll give you a hint – the pivot tab lets you pick the display format for the date.

Let’s start with a simple query with Dimensions of date and country, and the Metric visits. Pick a date range of interest – I like the Past 28 days because it is more than just a couple of weeks and yet is still fairly recent.

Now if we click over to the Pivot tab and pivot the date across the columns, we will see that we can change the format for how the date is displayed. There are several date formats listed, but not all. The possible combinations are actually quite numerous and can be found at the end of the FAQ article for this blog. Of particular interest here is the use of ‘ddd’ or ‘dddd’ for the day – they would show the day of week instead of the day number. Entering one of them into the format dialog, we get our day-of-week report. [Note that we can also choose to display the Sum (default) or the Average in each cell – important if you choose a time period that is not a multiple of 7].

If you were looking closely, you noticed the days might be in order but they don’t necessarily start at the beginning of the week. That can be easily fixed, though, using one of the Advanced analytic functions on the Analyze tab – Sort Column Labels, Custom Order. When we click on the command in the list provided, we see that is requires a list of strings separated by the tilde (~) character. Entering the days in the order we want and clicking the Add button pushes the customized command into the right side to be executed, and we have our sorted data. While we are here, we can sort it or filter it as desired using other script commands available in Next Analytics. Since the script commands will be repeated when we refresh the report, we can automate the entire report generation and not be left with any manual steps.

Now it is just a matter of formatting or charting – simple Excel functions. Of course, we could have used any of the other available dimensions to show a segmented analysis – see how your AdWords campaigns perform through the week, see if organic searches go up or down on the weekends, or if referrals are highest on Tuesday. Are some of your landing pages more effective on weekends? You could even change the metric and see how time on site is affected by the day of week.

This is just another example of how Next Analytics for Excel lets you analyze your Google Analytics data in ways that matter to you. Day of the week analysis is an important segmentation that you may have been overlooking. At Next Analytics, we can help you with your custom reporting. What day is good for you?