Home > Tableau, Web Analytics > Omniture visits report in Tableau – part 1

Omniture visits report in Tableau – part 1

To recreate the Omniture visits report in Tableau was quite difficult so I’ve broken the post into 2 parts. The result will be published on Tableau Public and I’ll supply the link to the report at the end of the post.

I spent some time recently trying to replicate web traffic visit data from Google Analytics 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.

Initally I thought rereating this chart would be quite straightforward…it actually 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 in a variety of ways but 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 as I couldn’t find an alternative way to match the selected month with 4 week previous either using data blending or calulated 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 the chart to be created. A self join is required on the data – when importing the data choose the Custom SQL option once you’ve selected the spreadsheet. The self join enables the 4 weeks prior data to be shown alongside the selected time period data.

My worskeet is called GATest so 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 available here)

Month Selector Parameter

For the user to select the month/year we need to create a parameter. The result of this parameter will also be used in some calculated fields. 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 user selected month we need to create a calculated field and use the result of the Date Selector parameter we have just created.

Name the calculated field SelectedMonthVisits and enter this formula:

SUM(IIF(month([Day]) = month([DateSelector]) and year([Day]) = year([DateSelector]),[VisitCount],0 ))

What this is doing is saying if the month and year of the DateSelector is the same as the Day then sum the VisitCount.

The Omniture visits report continues here