Google Analytics uses a line chart across most of their pages, showing the trend of traffic over time. I guess the idea is that you can visually see trends from week to week, month to month, but that has never worked for me. The weekly bump and weekend dips make the chart busy and confusing. Is it trending up? Are Mondays always higher than Tuesdays? It needs a better visualization. How about a calendar view? Next Analytics and Excel to the rescue!

To build a calendar-type view, we need one ‘row’ for each week, and the columns should be the days of the week. Well, week number is one of the available dimensions, and in my previous post, we know we can get the day of the week; it’s just a simple pivot table. I have included the year dimension as well in the query for reasons you will see later.

Oops! When I go to the Pivot tab, the check box is grayed out! What is THAT all about?

Well, back on the columns tab, we see that the week and year columns are being interpreted as numbers (well, they are numbers) and the date field as text. The pivot operation needs to be told the year and week fields are text, and in order to do the day-of-week conversion, the date column needs to be recognized as a date. A simple fix – just select each field and click the Text/Date button desired. Now we can pivot!

Changing the date format to our abbreviated day of week format (‘ddd’), we can see a nice summary of numbers with weekly rows. Use the Advanced script command Sort Column Labels, Custom Order to put the weekdays in the order we want and we have our calendar view.

Now we can save our work and flip into Excel to apply a simple conditional formatting so that the patterns pop out of the screen. And using an Excel formula, we can convert the year and week numbers into a date to improve readability. An impressive report chock full of insights! And with Next Analytics, we can refresh it week after week with no effort.

By combining the data download and a few simple analytic transformations in Next Analytics for Excel, we have converted a meandering trend line into a display that is rich in patterns.

Download the workbook and try it with your own website data. An Excel 2003 version is also available.