When monitoring web sites, it is easy to get lost in the day to day details, and every now and then you need to step back just a bit and take a look at a larger picture. Usually longer term trends are viewed as a single line chart or page views or visits, and it doesn’t offer much insight into what aspects of the site have made it go up or down. Multi-line charts can add some additional information, but they can quickly become confusing as more rows are added. Using Nextanalytics with Excel’s conditional formatting, you can easily create reports that make trends and usage patterns immediately visible. The reports are easily refreshed and will automatically adapt as your data changes so you can spend your time interpreting the results.

The first challenge in building a trend report is in picking the time interval to report on. I will strongly recommend you work with weeks as opposed to days or months. Web traffic changes quickly, so you need to keep the intervals small, but the weekday/weekend variability on most web sites makes daily reporting appear lumpy and trends are difficult to perceive. In contrast, even though reporting on a monthly basis is the norm, when you report in monthly intervals as well, you spend a lot of time explaining how one month had fewer shopping days or more holidays or special events, and then trying to extract meaningful trends.

With Google Analytics, reporting by week is made easier since the Google API provides a week-of-the-year number as part of the data feed. Using Nextanalytics’ automatic pivot capability, you can immediately obtain weekly trends for any dimension or combination of dimensions available. The nice thing about this format is that it can be open-ended – you can choose to view as many rows of content as you desire.

As the number of rows increases, it becomes increasingly important how you choose to order them so that likely comparisons will be near each other. You could sort on the most recent week so you’d see the most popular items now and how they fared previously. You could also sort by the sum total of all the weeks displayed, which brings up the slow-and-steady items that might otherwise be overlooked. My preference is to order them by the peak, or maximum from any individual week. That’s helps to keep previously popular items near the top so you remember what was highest and can compare it to what is highest. To do this with Nextanalytics for Excel, go to the Analytics tab and select Column Summary/Column maximum and Sort Numerically/Sort First Column (Descending). Now the results appear in the worksheet in the desired order.

We built a few trend reports as examples of this powerful approach – they can be downloaded from the link on this page. They include:

Country Trend – This report shows the ten countries with the largest number of visits in any given week over the past three months. Sorting by the peak number of visits keeps previous spikes in traffic visible even after the popularity drops off, which may happen when site navigation changes. To keep things in perspective, the remaining countries are grouped into an ‘Other’ row at the bottom.

Day of the Week Trend – As you would expect, this report shows you how the traffic varies by day-of-the-week from week-to-week, and in this case it is normalized to a percentage of the busiest day’s visits. It clearly shows how weeks with holidays or special events are affected, as well as trends over time. A side benefit of the report is that it contains a cell for every day for the entire three month period, allowing a relative comparison of any two days.

Time of Day Trend – This report lets you see if the visitors to your site are consistently coming to your site at the same time of the day. It helps to understand if your AdWords campaigns should be targeted to specific time periods, or when the best time for site maintenance would be.

Top Pages by Week – Rather than wade through a list of top URL’s or confusing page titles, Nextanalytics allows you to replace them with meaningful names and also permits grouping of selected pages into categories for more meaningful reporting. It also makes it possible to combine an old URL and a new URL into a common description to make web site changes transparent.