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!
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:
Wow! That is a fantastic achievement. Can’t wait to see the final paper and the presentation.
Hope to see you there Charlie. You did an awesome write up on Google Analytics access in your blog: http://www.sasanalysis.com/2011/11/when-google-analytics-meets-sas.html
I’m just not bright enough to figure out the authentication.
I can’t wait to see you and Angela present this paper! I know that the SAS Tech Report and SAS Users Group readers will want to read all about it!
Good luck with Google Analytics. I can’t figure it out yet either. If you figure it out, I’d love to read your tips.
~Waynette
Thanks Waynette!
I’m hoping some SAS coding genius comes to our rescue.
Tricia and Angela good work.
Your paper seems to be a step ahead to WPS in SAS.And loved to see you there but I am not invited so, send me your response received. best of luck
Saurabh:
Sorry to hear you won’t be attending. We are discussing maybe breaking it down into some video podcast for those who cannot make it. I’ll have to over my camera shyness.
Tricia
Hi,
My knowledge in SAS BI tools is limited. Till whatever presented here, your paper looks very interesting. I am looking forward to the final version.
I have one of my papers selected for SAS GloFo 2012. I am presenting it in ‘Beyond the Basics’ section. See you at Orlando …
- Kanthan.
Kanthan:
Awesome!
Please feel free to tell the readers more about your paper.
See you in Orlando!
Tricia
I had two papers accepted.
http://blog.cswenson.com/2012/01/sas-global-forum.html
Yeah!!!! Finally I get to meet my SAS Macro hero!
If you guys have not been over to Chris’s awesome macro site then you have really missed a treat.
Looking forward to seeing the paper at SAS GloFo 2012! Since the Google data includes the date, I am interested in applying time series anlaysis techniques. My paper on Time Series Cluster Analysis has been accepted at GloFo 2012 – it describes how to identify different stages of devleopment in an evolving system. It would be interesting to use the Google Analytics data with the technique descibed in my paper to identify patterns over time: seasonality, increased traffic following a new release, measuring the impact of SAS events like Global Forum on site traffic etc.
Woot! Woot! For Your Paper acceptance.
Yeah that sounds like some great information. I have been trying to do some week-over-week trending since I only have 4 mths of data – however not much being revealed. Looks like I need to attend your session and pick up some tips!
[...] In a past article, I discussed how I extracted the data from Google Analytics using Excellent Analytics. The following figure shows my query in the Excellent Analytics tool, the data results, and the results in SAS Enterprise Guide. The data consists of the date, post path and title (with URL), source (how person came to site), and visitor type. It’s probably obvious why I would want the date, post, and source but what is not so obvious is visitor type. Visitor Type allows me analyze what topics bring new visitors to the site as well as the topics that retain visitors. [...]
Great post! Congratulations on being invited. I was invited to speak also, and will be presenting “SAS Usage Patterns”. We’re trying to collect our own data for the presentation, but we want data about the way people are using SAS. We have a survey that if you or anyone else would fill out, then it would be very helpful to the presentation. (We’re throwing in a drawing for an iTunes gift card, too.) Here’s the link to the survey.
http://www.zoomerang.com/Survey/WEB22EFFVCDX97
Would appreciate the help.
Cool! Looking forward to it!
You need to post this on LinkedIn also.
http://www.linkedin.com/groups/SAS-Global-Forum-4000846
Hello! Just want to say thank you for this interesting article! =) Peace, Joy.