I’m really excited, Angela and I were invited to speak at the SAS Global Forum in Orlando in April. We were asked to talk about the SAS BI package and we have narrowed down our topic to SAS Information Map Studio, SAS Web Report Studio, and, if time allows, the SAS Information Delivery Portal. The point of the presentation is learning about the SAS BI clients – getting the data has proved to be a journey of it’s own.
I’m really excited, Angela and I were invited to speak at the SAS Global Forum in Orlando in April. We were asked to talk about the SAS BI package and we have narrowed down our topic to SAS Information Map Studio, SAS Web Report Studio, and, if time allows, the SAS Information Delivery Portal. The point of the presentation is learning about the SAS BI clients – getting the data has proved to be a journey of it’s own.
Step 1: Need some interesting data …
The hardest part of preparing a teaching presentation is find data that presents some mystery and allows you to tell a story that resonates. Right now the leading topic is along the lines of “How do you let people know you have a book out there? How do you build excitement?” For us, building a companion website (this blog) that gave the users an idea of the book content was our answer. Plus the website would be a continuation of the book information for the users.
The next question is – “How do you know it’s working?” Using the data that Google Analytics collects for this blog, I have been researching what kind of metrics you use for a blog versus an e-commerce site to determine if it is working.
All the Google Knows in One Place
Every time you visit a website, Google is collecting anonymous information. The available data includes visits to the site, time spent on the site, previous site, and so on. Here’s a snap shot from MS Excel to give you can idea of how the data appears. Again – you can see that it really just helps the site owner understand what is happening and does not tell me that Jane Doe visited my site on 12:30 pm today seeking information about load bearing bridges.
Google Analytics has some built in limitations. Since the data is presented in Summary format, there is not any transaction data and thus no common key. I did read about some ways around this limitation. However, I would have to wait several months to have all the data needed to put together some interesting graphs. So I plan to just use the data available as is. Google offers the data as Dimensions (date, visitor type, source, medium, country, and landing page path) and Metrics (visitors, new visits, visits, time on site, etc) but limits how many combinations you can do at once.
Step 2: Access the data …
There are many ways to access the data and Google does a good job locking it down. You have to authenticate at the site and then extract the data. Google recently updated the authentication method. I don’t have enough understanding of how it works to get past the authentication using SAS for the extract. Plus I’m running out of time because I need to get to the report building stage (and paper writing and PowerPoint preparing!!!!!!). As a result, I’m using the free MS Excel Add-In, Excellent Analytics to get the data I want. It works quickly and suits my limited time purpose. Here’s how the tool looks:
If you have the exact (no-thinking-required) SAS code to extract Google Analytics data – please share with me. 
Step 3: Re-Shape the data …
I created three data extracts: Visitors, Goals, and Keywords. Each table focuses on a different part of the website. Visitors is about the traffic, Goals is about if they want more information about the book, and keywords is what search terms they use to get to the site.
Using SAS Enterprise Guide, the data is imported (1). I used a macro to create a common key that I could run against each data set and then later join the tables in Information Map Studio. I could have joined the data in SAS Enterprise Guide – but I wanted to demonstrate the SAS Information Map Studio feature that allows you to join the data tables during the presentation. My join key (2) is a little bit of overkill, but I wanted to be 100% sure I was joining the proper records.
So I completed the Import Data process, now I can load my new tables into SAS Information Map Studio. If this was a project I was doing for a client, I would probably use a stored process to extract- transform-and-load the data. However, I can handle the once a week manual effort.
Are you Going to SAS GloFo? Was your paper accepted?
Let me know if you plan to attend the SAS Global Forum. And if you had a paper accepted – tell us about in the Comments section below! I know Steve had a paper accepted about some fancy data management subject (i.e. you’ll have to ask him). Angela is presenting a second paper about SAS BI Administration with Brent Whitesel. There’s going to be lots of good stuff there – so sign up!