Website Analytics with SAS Dashboard

Dashboard Spy reader know how excited I get when I discover fellow business intelligence bloggers worth following. Tricia Aanderud is such a resource. I encourage all of you to check out her SAS Business Intelligence blog:

Her SAS Business Intelligence book has been released. Check it out here:

Here is her latest blog post:

SAS BI Dashboard: Measuring the Conversion Rate with Google Analytics

February 8, 2012

Once you start looking at your Google Analytics, it is thrilling to see that visitors are coming to your humble Web site and some even return. Your first inclination is to create fancy charts to show the traffic patterns, compare the patterns by day of week, segment into New and Returning visitors, soon your charts may even be trending on #StuffDataGeeksDo.

In the past few weeks I have been leaning on you to adding more VA-Voom!!! (in the words of Dr. Suess) to your data using the SAS BI Dashboard. [More Google Analytics and SAS BI Dashboard articles]

Why Have a Website?

When I started this blog, the main purpose was to ensure SAS BI users were aware of my book, see my writing style, and determine if I knew enough to have written a reliable book. To this end, I had to measure was how many people followed theMore Book Info link I had prepared. As a introverted data geek, this was a hard for me because I did not want to be the in-your-facebuy-this-now pushy sales lady! What I wanted was for SAS BI users to know that Angela and I had created an awesome resource that would really expand their skillset and answer the many questions we knew they were asking.

In the following figure [click on image to see a larger version], you can see the different methods I tried along with the success rate. Appears that Method 2 worked the best; however attracting more visitors to the site also had an impact. Some notes – when I started Method 2, I kept the sidebar ad. When I started Method 3, I stopped adding the Ask for the Sale line at the end of the posts but did not remove them from older posts, which still got many hits. My conclusion: a combination of all methods provided the best result.

Note: Week 45 Angela and I both attended meetings where we talked about the book – so some web traffic does come from real life. Who knew?

Measuring Conversion Rate with Web Report Studio

Measuring Conversion Rate

I like the method Avinash Kaushik proposed for conversion rate at his Occam’s Razor site. Here is the gist of it, of all the unique visitors to the site, how many clicked the link for more information. Some will argue to use pageviews – but I cannot tell a pageview about the book – only a person. You could probably argue the other side successfully.  So just be consistent with whatever method you use.


Conversion Rate Formula in SAS BI Dashboard

Using SAS BI Dashboard

When you add Google Analytics measurements to your SAS BI dashboard, you can select from a variety of indicators to display your data. It gets confusing very quickly and if you spend anytime reading about dashboard design, there are many opinions about what works and does not work. From my prespective, it all depends on your situation. What fails miserably on one dashboard, rocks another one. Today I wanted to show you some of the different indicators with the same basic information and let you decide what works.

Creating the Indicator Data

Using a SQL Query as the Indicator data, here’s how I created the data table. Visitor Type is a category and the other ones are measurements. For the dashboard, this data is extracted nightly so I used INTNX funciton in the WHERE statement to indicate I wanted the data from the day before.

Tip! Try your SQL query in SAS Enterprise Guide first. The SAS BI Dashboard does not show the log and may provide a cryptic message if you make a mistake.

SQL Query in SAS BI Dashboard

Adding a Range

The goal was anything above 5% was considered green and anything below was a massive failure (red). Here is how I setup the range.

Conversion Rate Range in SAS BI Dashboard

Comparing the Indicators

Conversion Rate Indicators in SAS BI Dashboard

First – this is not about a right or wrong way to present data; rather it is about effective andineffective ways to present data. If your presentation is effective, the user quickly understands the message. In past posts I’ve preached about chart design so let’s just look at the examples.

  • KPI Gauges (1 & 2) - There’s little difference in the information presented in these gauges. This may just be a matter of preference. This method probably is more effective when there are limited gauges on the page. If every other image is a gauge, it’s probably confusing. I would want the gauge to be linked to another dashboard or Web Report Studio report with more trending information.
  • My own experience is when I am looking at the information daily, then I know what the rates were in the past 7 days so trending isn’t a chief concern.
  • Bar Chart with Reference Lines (3) – When I look at this indicator, I instantly see the yellow bar was really way off target and the green bar really exceeded expectations.I don’t see the overall numbers – just the conversion counts. Is that important to the user?  Does it make it more or less effective?
  • Waterfall (4) – This chart contains the total count of visitors and then has them segmented by visitor type. It’s effective because you get an idea of overall visitors to the site, but frankly I have to stop and think about this indicator everytime I use it. I would not choose this indicator for this particular data.
I’d love to know which one you think is better or worse – leave me some comments.

Hey, Do Me a Favor

Building Business Intelligence with SAS was released last week, why don’t you check out how awesome it is and get a copy for your bookshelf at this link!  Then tell others about the wonderful resource you have discovered.   Yours truly, Pushy Sales Lady

Tricia Aanderud  (59 Posts)Tricia Aanderud is SAS BI evangelist, enthusiastic innovator & hired gun, pursuing design tips, programming tricks & other mishmashes to share. Her book Building Business Intelligence with SAS book helps new and experienced users get up to speed quickly on the SAS BI Solution. She invites you to connect to her on Linked In or follow her on Twitter using one of the icons below:

Tricia Aanderud on SAS BI Dashboards: Google Analytics Dashboard Kicked Up a Notch

Dashboard Spy readers know how enthusiastic I can get about smart people in our field. I’m thrilled to bring your attention to Tricia Aanderud, author of the upcoming book Building Business Intelligence Using SAS: Content Development Examples.

I look forward to reporting on her book and getting her to contribute some material for you to check out.

Meanwhile, she’s graciously let me cross post the following article – enjoy!

Google Analytics uses data from your Website to prepare a neat looking application where you can explore your data and answer what I consider your first-level questions. First-level questions are the most basic things you might want to know, such as How many? What Time Period? It is the second- and third-level questions where the analysis and action plans come into play. After all, it’s not enough to know how many people visited the site – you need to know what segment attracted them and how do you keep them coming back for more.  [More articles about Google Analytics here]

Reviewing the Google Analytics Dashboard

In the Google Analytics site on the Home tab, you can design a dashboard to review key measurements for your site. This dashboard is easy to use and works great for checking daily traffic. However, these measurements answer your basic questions, such as the counts and percentages. It’s more difficult to use the information for in-depth discovery. For instance, in the following figure I am comparing month over month traffic for the site using my dashboard.

  1. These numbers indicate the total visits for each month. So, what can I learn from these metrics? Traffic to the site increased so that is positive, but wonder what caused it. Also, I’m having to calculate the percentage in my head (very dangerous).
  2. These spark line charts depict the visits each day. Since the site primarily deals with a business related topic, most of the traffic occurs during the week. So, there are peaks and valleys for the weekends. No shocker – I knew that before I looked at this chart.

My point from this exercise is that the dashboard was able to answer my first-level questions: How Many for each Month?  What is more or less? This dashboard does not judge performance, which is what I really need.

Bring Value to the Measurements

Consider the following Spark Table indicator from the SAS BI Dashboard. It uses essentially the same data, but with more valuable detail. From this month-over-month data, I learned more with a single glance. For instance, the largest segment (Top Social Sites) had the most traffic but the traffic from those sites is down 11% from the previous month. However, it is okay because the Organic Search has increased by 41%. (Did all my search engine optimization (SEO) implementation tips work?)  This must account for the 7.8% increase in overall visits.  (I know my total visits do not match the above figure-I applied some additional filtering during the ETL process.)

While the spark lines for this table are not as pretty as the ones for Google Analytics by smoothing the data into weeks, I can actually get a better feel for the site traffic. For instance, the drop in the social sites occurred near the end of the month, as did the Christmas holidays.  The Organic Searches rose during that same time – if I recall it was the week before I started working on the site’s SEO optimization.

This analysis actually helps me understand what I need to do next, which is determine the reason for the drop in the Top Social Sites (I suspect Christmas holidays) and to investigate the Google Keyword search to find out what the users were seeking. There’s obviously more information here to explore – but the point is that the SAS BI Dashboard can bring richer analysis to the data than Google Analytics. In addition, I was able to understand instantly the next steps.

Building a SAS BI Dashboard Spark Table Indicator

In SAS 9.3, SAS introduced the Spark Table indicators for the SAS BI Dashboard. There are essentially three steps to creating a this indicator: prepare the data, build the range (colored arrows), and build the spark table. Here’s a quick overview of the process.

Preparing the Data

A Spark Table indicator groups your data. To provide the data as detail (for the aggregations) and partially summarized, I used PROC SQL. To create the Prev Month column (see above figure), I summarized the month and previous month counts to create a PCT_DIFF column. Then I joined the main table with the temp table so I could add the PCT_DIFF column. The following figure shows the data table in SAS Enterprise Guide and then as the data source in the SAS BI Dashboard. There are other methods available to access the data, such as SAS Stored Process or using a SAS Information Map.

Note: This table (ga_data.ga_data_sparkline) is not registered in the metadata because it is only used for this purpose and to help keep the metadata slim.

Building a Range

Not all of the SAS BI Dashboard indicators use a range. A range allows you to control the red and green arrows and the values that cause the changes. For these arrows, the PCT_DIFF value is used to judge the colors. Since my blog site traffic is small there are usually huge fluctuations in the percentage difference, I consider any change over 5% to be positive. Otherwise it’s yellow (basically unchanged). This method may not work for your organization, so obviously use measurements that make sense for your data.

This figure shows how I set up the range. There are three intervals: Below target (< 0), On target (0 – .05), and Above target (>.05).  This is how the range knows how to apply the colors.

Building the Spark Table Indicator

You can have as many columns in your Spark Table as you have data. Here’s a quick reference showing how to build the Spark Table columns.

  1. These three columns (Min Visit, Avg Visit, and Total Mth) all use the same data. The only difference is the Aggregate function chosen.  You’ll see in the figure that I have other choices available.
  2. This Prev Month column uses the minimum, which prevents the aggregation by using only one value.  This causes the range to work properly. Also note this is where I called in the Range create in the previous step. This is the only time it is used.
  3. The Trend by Week requires two settings.
  1. Ensure the Display Settings show Week as the Spark X axis so the line is plotted correctly.
  2. Set the Aggregate function to Spark line.

Completing the Dashboard

Add the Spark Table indicator to the dashboard. In this dashboard, there were two other indicators: Visits and Difference, which were created with Dynamic Text indicators. As you can see, these indicators simply list independent values.  The Difference indicator does have the same range I created above applied to it.  [The SAS Support site has complete documentation on building the Spark Table.]

It’s helpful to know the counts, but it’s even better to analyze the measurements in one little minute.  This article is part of mySAS Global Forum 2012 series that covers the paper Angela Hall and I will be presenting about our Building Business Intelligence with SAS book, which answers your second-level questions about using SAS BI.  We hope to see you there!

You might also likeclose