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.
- 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).
- 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.
- 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.
- 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.
- The Trend by Week requires two settings.
- Ensure the Display Settings show Week as the Spark X axis so the line is plotted correctly.
- 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 my SAS 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!
Tricia Aanderud, president of And Data, Inc., provides SAS consulting services to corporations who need assistance understanding how to transform their data into meaningful charts, reports, and dashboards. Tricia has been an enthusiastic SAS user since 2002 and has presented papers at the SAS Global Forum and other industry conferences. Connect to her on LinkedIn or follow her on Twitter using one of the icons below:
[...] 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 [...]