QV Source and Google Analytics – Retrieving Data

I have been fortunate enough to work with a new product from Industrial Code Box.  QVSource is a great tool that allows you to connect QlikView with all sorts of web-based data services.  There are some very interesting connectors including Facebook, Twitter, Klout, LinkedIn, OData,  Infochimps and YouTube available with this tool.

But the connector that I have most used so far is the QVSource connector for Google Analytics.  Google rules the web statistics space as it does anywhere else they enter.  And included with Google Analytics is the ability to feed data directly through an API.  QVSource makes use of this feed and converts it into QlikView script that any developer can use to harness your Web Analytics with the power of QlikView.

The first step for creating an application is to open up QVSource and configure the connector.

 You will enter the GA user and credential.  Then click the URL build link.

This will open up the GA Data Query Feed Explorer.  You must be logged in to a gmail account that has read access to the GA account.  If you click in the “click to list your accounts” box and nothing happens, make sure:

• Your gmail account has the correct access.
• Try Logout and Reauthorize.
• Or use Google Chrome as your browser.  The accounts box does not authorize correctly when using IE, even if you are signed into gmail.

Then you can use different combinations of dimensions and expressions to design the queries you need.  Keep in mind that GA only allows a maximum of 7 dimensions and 10 expressions.  Also, GA does not provide a unique user ID (against their policy).  So it can be a bit of a challenge to get down to the lowest detail possible.  As we know, QlikView breathes deeply in that granular level.  I have found that creating multiple queries can be effective if most of the dimensions are repeated to form a key and then you can cycle through any other needed dimensions.  In my case, I used latitude, longitude, date and hour as a key for all my queries.  That allowed me to bring in other dimensions 3 at a time.  Disregard the note that says max 10,000 records.  The query window is limited by 10,000, but the API call is not.

Once you find a query you want to use, simply copy the URL that forms above the query box and paste it into the URL box in QVSource.  Then select the Connector Data tab.  Select Data and then click the QlikView Load Script tab.  You will now see the load script you will need to use in your QlikView application.

Simply paste your script into QlikView and reload.  One thing to remember is that QVSource will need to be open any time you reload the application.  I found it helpful to open QVSource using Windows scheduler so that it works like a service.  Alternatively, you could use QlikView server to open the program before the reload runs.

You now have Google Analytics as a source for QlikView.  I am having lots of fun duplicating GA visualizations and surpassing them with my own creations.  And the client I have developed the QlikView document for is ecstatic with the results.  We are now talking about adding in Twitter and Facebook Page statistics to get a more integrated Web Strategy application.