SAS BI Dashboard for Google Analytics Data
Rising star business intelligence blogger, Tricia Aanderud, is on fire with her SAS BI blog posts over at http://www.bi-notes.com/author/tricia.
Her post today is quite excellent and worth reading through in detail. It’s available here at http://www.bi-notes.com/2012/02/dashboard-sas-bi-google-analytics-data. I’m cross-posted it here:
Tricia Aanderud is SAS BI evangelist, enthusiastic innovator & hired gun, pursuing design tips, programming tricks & other mishmashes to share. Tricia is the author of the upcoming book Building Business Intelligence Using SAS: Content Development Examples which helps new and experienced users of SAS BI to get up to speed quickly. She invites you to connect to her on Linked In or follow her on Twitter using one of the icons below:
Web Site: http://www.bi-notes.com
Quick Dashboard Tour
This dashboard has two indicators, a line graph that shows the traffic over time and a spark table that contains specific data about site visitors. As the user drags the slider and clicks, the data in spark table updates. I left the Week variable in the Spark Chart to help you understand what was happening. Also in the Last Wk Delta, New Visitors (%) and New Visitor Delta columns, you can see the various gauges available.
Creating an Interactive Dashboard
For several weeks I’ve been discussing using Google Analytics with the SAS Business Intelligence tool set to get more value out of the data. When you can understand how the trend has changed over time, it helps you determine your next steps. In a previous post I showed you the basics for creating a SAS BI Dashboard and in this post I’m just going to show you the highlights for how I created the above dashboard.
Preparing the Dashboard Data
For the SAS BI Dashboard, you can use several data sources, such as a SAS dataset or SAS information map. [Read more: Here's how I extracted the Google Analytics data.] The dashboard uses two indicators: Chart with slider prompt and a Spark Table. With some planning, I was able to use the same data for both indicators.
It was important that I had the week over week differences to determine the growth rate. If you notice, the traffic is divided into Segments based on the source field from Google. You may have read Dominick Frasso’s article last week about the importance of using advanced segments based on the ideas of Avinash Kaushik, author of Web Analytics 2.0. Instead of using the Segments from Google Analytics, I wrote some code to parse the source variable and create the segment variable. Here’s a snippet to give you an idea what was done and the inset shows the results.
The week over week difference was important, so this code determines the growth rate by Segment for weekly and new visitor traffic. So after creating the data (1) I used the SQL query (2) to create the data for the Spark Table. Notice that all of this data is summarized, which is a requirement for using the SAS BI Dashboard.
Adding a One-Size-Fits-All Range
If you notice, the three new columns are all based on percentage growth. Basically meaning, growth over N percentage is good and under N is bad. This means I can create one range that I am able to use multiple times instead of trying to create 3 separate ranges. You can see how the range was setup in the following figure. All values from 0% to 100% are covered by this range.
In the figure inset, you can see the Range in use. For this range, anything over 60% is green or exceptional is how I like to think about it. Truthfully, probably any increase over 20% is exceptional for the small amount of traffic on my blog.
Tip! It might be worth it to create several ranges like this one that can be reused in multiple dashboards, you would just need to copy the range to the new folder. [Angela discussed dashboard naming conventions here.]
Creating the Dashboard Indicators
After you have your data and range available, create two indicators for your dashboard.
Creating the Traffic by Weekly Visits Indicator
The first indicator created was the Chart with slide prompt indicator. In this figure you can see the end result along with what settings I used to create it. Using the data table above, I created a SQL query to sum the visits variable by week. This way the line graph shows the total traffic for each week along the X-axis. Again – reusing to keep things lean.
When I added the indicator to the Dashboard, I was able to change it’s displayed name to Traffic by Weekly Visits. [See Angela Hall's blog post Removing the Object Name from the BI Dashboard for more details about indicator names.]
Creating Segments Over Time Indicator
What I like about Spark Tables is that I can add as many columns as I please. One thing to note about this Spark Table, I did not group anything. All of the variables were grouped by segment and week during the data creation step.
- For this dashboard indicator, a new column for each variable (NewPCT, PCT_DIFF, PCT_NEW) was added.
- After adding the columns, select your data source variable. (Notice it uses the label not the variable name – so New Visitor Delta is PCT_NEW.)
- Add the range you created above. You can also have a separate range for each column is you prefer.
- Select the desired gauge. In the drop-down you can see a partial listing.
I showed several different ones in this example so you could get some ideas. For a real dashboard, I would not be so glamorous or you might say busy. This example is just for your edification.
Creating the Dashboard
Once you have created the two indicators, add them to a new or existing dashboard.
Don’t be alarmed: Note that when the Spark Table is added to the dashboard, it shows the data for all weeks and the line chart does not have the slider.This is normal – nothing is wrong. When you view the dashboard, the indicators will come to life.
Adding Indicator Interactions – the Fun!
To add the interaction, do this:
- Position the two indicators as desired on your dashboard.
- Select the Interaction icon from the tool bar.
- In the Set Up Indicator Interactions window, select the indicator and columns to influence. In this case, the Week variable is what you want to use as a filter.
Refer to the SAS Support site for complete documentation on creating a dashboard.
- Creating SAS BI Dashboard Indicator Interactions