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:
- For each of the above traffic sources, what were visits and revenue by day?
- Which days of the week got the most traffic?
- 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:
13 thoughts on “Google Analytics Tip: Trending Data Over Time”
Thanks for posting this, very useful.
I think that the lack of trending over time is one of the factors which stops Google Analytics being considered a serious tool for analysts to use to diagnose the causes of problems, and gather insights for data-driven changes to their sites. Instead it's currently more suited to allowing marketers to generate graphs designed to impress their bosses without providing any truly actionable information.
Interesting perspective. GA's data trending functionality is a bit hidden, so you're right that could influence analyst views of the tool. Thanks for the comment!
Turns out that further investigation proves that I'm at least partially wrong (this happens to me quite often), I'd never come across the Google Analytics API (particularly the query explorer) before today.
Point 8 in Avinash's post http://www.kaushik.net/avinash/google-analytics-tutorial-8-valuable-tips-to-hustle-with-data/ has some good alternative tips for anyone regularly needing to extract bulk data from Google Analytics.
Good link. I really like Excellent Analytics for sucking all the data out of GA directly into Excel.
Thank the lord this post is here ive been seraching all afternoon for a fix, thank You!!
You're welcome, glad it helped! 🙂
I was looking for a way to convert GA "date" data into a normal date format - and you just hit the nail right on the head! Thank you! Thank you! Thank you!
These custom reports are aMAZing. You just saved me hours of my life. Thanks!!
haha, glad I could help :).
Ana for Marketing MVP of the year! Who's with me?
You are a life saver. How come my Google consultant never taught me this?
hahah, thank you for the comment, Lou!
You solved my problem! I want to press all available social media icons to like and plus among other. They are not there. I feel strangely unsatisfied.
hahaha I'm sorry! I actually used to have those buttons - this is a good reminder to add them back!
Comments are closed.