I tried to recreate the Google Analytics visit chart in Tableau, in the style of the Adobe Analytics (previously 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.
Recreating the omniture visits report in tableau
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 Google Analytics into a spreadsheet, a simple 2 column download with Day and Visit Count.
I brought this data into Tableau and tried 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.
How to line up the selected month with the previous 4 weeks
I attempted a number of different ways to do this using both calculated fields and data blending without success.
Eventually I wrote some custom SQL. 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 with Custom SQL
This is the most important part – modelling the data in the correct way to enable creating the chart. To do this 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/custom SQL)
Create a parameter to Select the month
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 as mmm yy.
Once the parameter is created right click on the parameter and Show Parameter Control.
Use the parameter 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. We could also use DATETRUNC as an alternative to the date parts, and the result would be the same,
This is the chart we are trying to create, the same as the Omniture calendar month visits report.
Now we can begin to create the chart.
Begin by putting the Day pill to the Columns shelf and the calculated field SelectedMonthVisits on to the Rows shelf. Make sure the Day pill is displaying the DAY(Day). The chart should look like this:
Calculated Field for 4 week prior visits AND the selected month last year visits
The next step is to be able to show the visit data for the visits 4 weeks prior AND the data for last year.
Both of these visit counts need to be in the same calculated field as they are both going to be displayed as a line in the final chart.
This 4 weeks prior data was loaded in the Custom SQL self join – it’s the field WeeksPriorVisitCount.
The field for last years visit data is in the VisitCount field.
To begin in our calculated field, which will be called 4WeekPriorVisits, first sum the 4 weeks prior data – the formula being:
SUM(IIF(MONTH([Day]) = MONTH([DateSelector])
and YEAR([Day]) = YEAR([DateSelector])
,[WeeksPriorVisitCount]
,0 ))
Last years visit data also needs to be included in the calculated field.
To do this we need to add another IIF statement in the Else clause selecting the same month last year.
The final formula is:
SUM(IIF(MONTH([Day]) = MONTH([DateSelector])
and YEAR([Day]) = YEAR([DateSelector])
,[WeeksPriorVisitCount]
,IIF(MONTH([Day]) = MONTH([DateSelector])
and YEAR([Day]) = YEAR(DATEADD('year',-1,[DateSelector]))
,[VisitCount]
,0 )))
Build the Chart
Next add the newly created 4WeekPriorVisits pill to the Rows shelf alongside the SelectMonthVists pill.
This will give 2 line charts on top of each other.
Now we have all of the data we need on the chart. Format this to display the data as we would like to see it.
Step 1
Make the chart into a Dual Axis chart. Click the arrow to the right of the 4WeekPriorVisits on the Rows shelf (this should be the right hand pill) and select Dual Axis. The chart should now look like the following:
Step 2
Display the bars for the SelectedMonthVists. To the left of the chart there’s a section called Marks. This defaults to Automatic. Click the arrow to the right of Marks and select Multiple Mark Types.
Scroll through the Mark Type options to find the AGG([SelectedMonthVisits]). Change this from Automatic to Bar.
Step 3
We need to split the 4WeeksPriorVists and last year visits line charts. Select the AGG([4WeeksPriorVisits]) mark type. Drag the Day pill into the Color shelf and make sure it’s set to YEAR([Day]). You might need to edit the colours if the defaults cause some clashing.
Step 4
There are still some formatting tasks we need to do with the Secondary Axis. Right click on the secondary axis and tick Synchronise Axis. Also Format the axis and colour the ticks, font, etc white to hide the secondary axis.
Fix the X axis
Regardless of the month selection, the X axis always shows 31 days. To alter this to show the correct amount of days use a calculated field and some filtering.
Create a calculated field, call it DateFilter and enter this formula:
MONTH([Day]) = MONTH([DateSelector])
//This is needed to make sure the correct number of days are showing in the X axis
//without this the number of days on the X axis is always 31
Drag this DateFilter pill into the Filter shelf and set the value to True. This will mean Apr shows 30 days on the X axis, May 31, etc.
Next steps
This chart is now more or less complete. The tooltips and labelling needs to be tidied up but I’ll leave that part up to you 🙂