Home » BI Tools

Is Google Analytics and SAS BI a Good Subject?

Submitted by on January 9, 2012 – 7:00 am 15 Comments

gI’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.

sas-global-forum-sas-bi-data-from-website

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!!!!!!).  

Update Jul 2013: I started using Clicky.Com to collect my blog data. The API is much easier to access and pull data into my SAS BI system through a batch process.

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:

sas-global-forum-sas-bi-data-3

If you have the exact (no-thinking-required) SAS code to extract Google Analytics data – please share with me.  :mrgreen:

 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.

sas-global-forum-sas-bi-data-4

 

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.

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 is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC. She has written several books about SAS, presented papers at many SAS conferences, and has over 10 years of SAS programming experience. Contact her for assistance with your next project.

Tags: , , ,

15 Comments »

  • moubreunusers says:

    Hello! Just want to say thank you for this interesting article! =) Peace, Joy.

  • Cool! Looking forward to it!

    You need to post this on LinkedIn also.
    http://www.linkedin.com/groups/SAS-Global-Forum-4000846

  • Chuck Kincaid says:

    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.

  • 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!

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

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

  • 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

  • Kanthan:

    Awesome!

    Please feel free to tell the readers more about your paper.

    See you in Orlando!

    Tricia

  • Kanthan says:

    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.

  • Saurabh says:

    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

  • Thanks Waynette!

    I’m hoping some SAS coding genius comes to our rescue.

  • 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

  • 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. :-(

  • charlie huang says:

    Wow! That is a fantastic achievement. Can’t wait to see the final paper and the presentation.

3 Pingbacks »