Google Analytics Tip: Trending Data Over Time

Setting a time frame in reporting is usually where any kind of analysis starts.   You need to view trends over time, or compare to some time prior periods as a benchmark. I personally like to get tons of metrics -- visits, revenue, conversion rate, etc -- all laid out by day or even hour so I can really dig in and understand trends in the business.

So, a little quiz:  How many report tables in GA contain time as a dimension?

Think for a minute...

The answer is... two! 🙂

(Conversions > Ecommerce > Sales Performance and, if you use AdWords, AdWords > Day Parts).

This is obviously very few, and is another difference from tools like Coremetrics and Omniture, in which every report has a "trended" view with date as the primary dimension on the left.

The Components of a Report in Google Analytics

Google Analytics doesn't generally provide time as a dimension, and instead provides 3 views of data for every report:

1) a summary chart that trends up to 2 metrics over time.  Here you can also choose granularity: day, week, month, and sometimes hour.   This is the main way to view data over time.

2) a scorecard that summarizes the data for the selected time period

3) data detail, again summarized for the selected time period

So you can see time is never stated as a dimension (i.e. a column on the left).  This means there are a few common scenarios that can't immediately be answered with the above data views:

1. For each of the above traffic sources, what were visits and revenue by day?
2. Which days of the week got the most traffic?
3. What time of day performs the best in terms of conversion?

I'm assuming you have better things to do than repetitively change the time period and copy down the resulting values for each day (if you don't, that's cool too 😉

Fortunately, there are two ways to more easily extract data from the GA interface.

Method 1: Plot Rows + Export

First, see the checkboxes next to every dimension?  You can click up to six of them (recently increased from just two).

Now click the "Plot Rows" button.  See below for a sample of the results you can get. I've found a lot of people aren't yet aware of this very useful visualization functionality!

If you now click export, you will get a separate data table export for each of the selected dimensions.  The export will still require some manual manipulation to get it into a useful format, though, so if I want to use the data in Excel, I'd usually move on to the next method:

Method 2: Custom Reports

One of GA's best "hidden" features is the host of time parting dimensions available in the custom reports, all listed in the Other category:

Date:  This presents date in the format 20120820 to represent Aug 20, 2012.

Hour of Day: appends hour to the date in the format  2012082010, which would represent Aug 20, 2012 at 10am

Day of Week:  shows a number representing each day of the week.   For reference, 0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, and 6 = Saturday.

Hour: This shows hour of day, represented as a number ranging from 00 to 23.  For reference, 00 = midnight to 1am, 01 = 1am to 2am, 12 = 12pm-1pm, and so on up to 23 = 11pm to midnight.

Month of Year:  Shows month and year in the format 201208, which would represent August, 2012.

Week of Year: Shows year and week number, where week number ranges from 01 to 53 and 01 is the first week of the year.  Weeks in GA range from Sunday through Saturday, so if you're in a year that starts mid-week, you'd get 2 entries, one for each year.   The format is 201233, representing the 33rd week of 2012.

Since a custom report can have up to 2 dimensions in flat table mode, you can now easily trend a dimension over time.  Here are a couple custom reports you can grab that answer the scenarios described above:

1) Visits and revenue by day, for each traffic medium:  https://www.google.com/analytics/web/permalink?uid=bPie9wIOQNivX198sUdCvA

2. Key stats by day of week and hour (two in one - click the tab at the top to toggle).  This will let you filter traffic by time of day: https://www.google.com/analytics/web/permalink?uid=3z4P54O_Q2OQmls429Guhg

I love these reports!  You can be really creative and answer a lot of questions with these -- when to send out that email, the best time to launch a sale, etc.

P.S. One Final Tip

For those of you exporting to Excel...

As mentioned, the date format is 20120820, which doesn't translate nicely to a date in Excel. To produce an Excel-readable date, you can apply the following function to parse and convert the Google Analytics date, where the date is in cell A1:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))