BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Tools

SAS BI Dashboard Rocks My Google Analytics Data Analysis

Submitted by on 2012-02-01 – 7:32 AM

I have to say I’m in love with the new SAS BI Dashboard and it’s many features. It really brings the Google Analytics to life and helps me understand how the Website traffic changed over time along with the key influences. The following figure gives you an idea of how you can integrate dashboard indicators for an interactive user experience. The best part is how easy it is.  Once I understood the how the feature worked, this indicators below took less than an hour to setup and most of that was deciding what data I wanted to see.   [More articles about Google Analytics here.]

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.

sas bi dashboard making indicators interactive

 

 

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.

create data in sas bi enterprise guide

 

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.

sas bi dashboard data source in sas enterprise guide

Data created in SAS Enterprise Guide

 

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.]

sas bi dashboard range one size fits all

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.]  

sas bi dashboard slider indicator

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.

  1. For this dashboard indicator, a new column for each variable (NewPCT, PCT_DIFF, PCT_NEW) was added.
  2. 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.)  
  3. Add the range you created above.  You can also have a separate range for each column is you prefer.
  4. 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.

sas bi dashboard spark table indicator new columns and ranges

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:

  1. Position the two indicators as desired on your dashboard.
  2. Select the Interaction icon from the tool bar. 
  3. 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.

 sas bi dashboard indicator interactions

Creating SAS BI Dashboard Indicator Interactions

 

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

 

The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: , , , ,

No Comment »

1 Pingbacks »