How to smooth out weekend visitors in Google Analytics Klips
If you check your weekly visitors over the past 30/60/90 days, you will notice a dizzying pattern: visits suddenly plummet on Saturday and abruptly rise on Monday. This is an extremely common scenario facing web analytics professionals that makes it difficult to see the pattern behind the "seesaw" graph you are viewing. The seesaw effect adversely impacts analysis, especially for non-web analytics professionals such as senior management and other stakeholders.
A brief Google search revealed that the most common solution is to simply exclude weekends from your data. I won't lie: this distresses me. Discounting 2 days worth of data for each week is a sloppy and ineffective solution. Enough said?
Our solution is to apply the Moving Average function to the data set in question. This function "smooths" out data sets based on the previous n values. The end result is a data set that represents the trend over time much more accurately, and that is easier to analyze and interpret.
Here's the same data as the Klip above, this time using the moving average for the past 7 days.
Trends and patterns are the friends of web analytics
Stakeholders, clients, and senior managers will find it much easier to recognize the pattern and trends in web traffic in the second Klip, as opposed to the first one. Case in point: notice the slight dip in traffic in late March and early April. While the trajectory is generally positive, this dip begs explanation and further analysis. By using the moving average function, it's much easier to pinpoint problem areas.
The how to
This Klip is very simple to put together. Aside from selecting to build a bar/line chart, here's what you need to know:
Google Analytics Data Source
Select the option to create a Google Analytics data source and select the following options from the menu:
Slice and Moving Average
To assign your data to the Klip, use the Slice function to remove the column headers. Next, wrap the Slice function with a Moving Average function. You can fill in any value you want for n, but I recommend a value greater than 7 to adequately smooth out the data set.
Dates on the X Axis
The last part is to provide dates for the X Axis. If you created the same data source as I did, then your first and second columns will represent the month and day, respectively. We need to do two things: combine these values together, and tell the Klip to treat them as a date.
1. Combine the values
To combine the date values, we will use the Concat function. In the first data element, select the first column of data using a Slice function to eliminate the column header, and then do the same thing for the second element.
2. Recognize the values as dates
Once the data is assigned to the Klip, open the Properties panel. Select Date/Time from the Formats as menu. Then from the Input menu, select Custom... and then type MMdd in the field provided. Next, select Custom... from the Output menu and then type MMM [space] dd in the field provided. And, voila! your dates are accurately represented in the Klip.
This method applies to many other data sets, not just web analytics. In particular, the moving average function is great for any data set that has regular, recurring fluctuations. It also works to help expose trends in large data sets with lots of high and low points.