To recreate the Omniture visits report in Tableau was quite difficult so I’ve broken the post into 2 parts. The result is on Tableau Public and I’ll supply the link to the report at the end of the post.
I tried to recreate the Google Analytics visit chart in the style of the Omniture visits report. I like the Omniture visits report as it shows how you’re tracking in the current month vs how you were tracking exactly 4 weeks earlier – i.e. compares Monday with Monday, Friday with Friday, etc. It also shows how you were performing vs last year.
The user selects a month and year then the X axis displays the day number of each day of that month (ie. 1-30 for Apr, 1-31 for May). The chart then has bars showing the visit count for each day of the selected month/year. On the same chart there are 2 lines – 1 showing the visits from 4 weeks prior (a more accurate MoM trend) and 1 showing the visits from the same month previous year to see a YoY performance.
Initially I thought recreating this chart would be quite straightforward. In the end it turned out to be very challenging. To begin I downloaded some data from GA into a spreadsheet, a simple 2 column download with Day and Visit Count. I imported this file into Tableau and then attempted to create the Omniture report. However I kept running into difficulties trying to line up the 4 week prior data with the current month on the same chart. I attempted a number of different ways to do this using both calculated fields and data blending without success.
Eventually I had to write some custom SQL to do this. I couldn’t find an alternative way to match the selected month with 4 week previous either by using data blending or calculated fields.
In this post I’m going to run through step by step how I re-created the Omniture report in Tableau.
Importing the data
This is the most important part – modelling the data in the correct way to enable creating the chart. To do this we use a self-join. While importing the data select spreadsheet and choose the Custom SQL option. The self join enables showing the 4 weeks prior data alongside the selected time period.
My worskeet is called GATest and the SQL is as follows:
SELECT [GATest$].[Day] AS [Day], [GATest$].[VisitCount] AS [VisitCount], [GATest$1].[VisitCount] AS [WeeksPriorVisitCount] FROM [GATest$] left JOIN [GATest$] [GATest$1] ON [GATest$].[Day] = dateadd('ww',4,[GATest$1].[Day])
(I’ve written a post on the Tableau quirks of dateadd in Tableau/SQL)
Month Selector Parameter
For the user to select the month/year we need to create a parameter. Some calculated fields use the result of this parameter. I like to custom format the date Display Format as mmm yy.
Once the parameter is created right click on the parameter and Show Parameter Control.
Calculated Field to show the selected months visit data
To show the visit data for the selected month create a calculated field using the result of the Date Selector parameter.
Name the calculated field SelectedMonthVisits and enter this formula:
SUM(IIF(month([Day]) = month([DateSelector]) and year([Day]) = year([DateSelector]),[VisitCount],0 ))
This calculation is saying if the month and year of the DateSelector is the same as the Day then sum the VisitCount.