26 Jun 2011

Merging Google Analytics with QlikView

6 Comments Dashboards, Google Analytics, QlikView, Web Analytics

Recently I read an interesting blog-post about how to merge Google Analytics data with a Data Warehouse. Integrating data from your website with Data Warehouse data opens up a whole new range of optimization possibilities. It allows you to add customer data, purchase data or demographic data  from your organisation to your website data. Another example could be measuring offline sales generated through your website. In my previous post I showed how easy it is to pull Google Analytics data in Qlikview. This post I will dedicate on showing how to create a key between website and website data, allowing you to associate Google Analytics with offline data in QlikView.

Setting up the primary key.

In the example above you see a Qlikview Dashboard pulling data through two different sources, Google Analytics and CRM data. In order to produce this type of holistic view of our channels we first need to create a (primary) key between the different data sets. Once the key exists Qlikview will automatically associate the data.

The first step is enabling visitor id tracking in the Google Analytics reports (see screenshot below). For a detailed description of the technique presented in this post please read the orginal post written by Justin Cutroni. The drop down boxed below contains a modified Google Analytics script that will extract a unique ID from the Google Analytics cookies and make this available in the Google Analytics reports and Google Analytics API. in order to enable visitor ID tracking replace your original Google Analytics tracking script on your site with the code below.

Make sure to also rename the  ’UA-10246298-10′ in the code with your own Google Analytics account number. I attached a screenshot here showing you where you can find this number.

 

Modifed Google Analytics tracking script

<script type="text/javascript">

function _uGC(l,n,s) {
if (!l || l==”" || !n || n==”" || !s || s==”") return “-”;
var i,i2,i3,c=”-”;
i=l.indexOf(n);
i3=n.indexOf(“=”)+1;
if (i > -1) {
i2=l.indexOf(s,i); if (i2 < 0) { i2=l.length; }
c=l.substring((i+i3),i2);
}
return c;
}

// REPLACE UA-10246298-10 WITH OWN ACCOUNT!
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-10246298-10']);
var a = _uGC(document.cookie, “__utma=”, “;”);
var id = a.split(“.”);
_gaq.push(['_setCustomVar',
5,
'VisitorID',
id[1],
1
]);

_gaq.push(['_trackPageview']);

(function() {
var ga = document.createElement(‘script’); ga.type = ‘text/javascript’; ga.async = true;
ga.src = (‘https:’ == document.location.protocol ? ‘https://ssl’ : ‘http://www’) + ‘.google-analytics.com/ga.js’;
var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(ga, s);
})();

function populateHiddenFields(f) {
f.visitorid.value = id[1];
alert(‘visitorid=’+f.visitorid.value);
return false;
}

</script>

Please note that the technique above is not against the privacy policies of Google Analytics as we only use information that is already available in Google Analytics. I do however recommend to cleary state in your privacy policy on your site that your track user bahaviour and might connect historical web site data to registrations.

Pass key through to CRM system

Now we have enabled a visitor ID in the Google Analytics interface we can pass it through to our CRM system. The basic idea is that we will pass the Google Analytics visitor ID stored in the cookie through a hidden field in the lead regitration form. A more detailed description of this technique can be found in the original blogpost UPDATED: Integrating Google Analytics with a CRM written by Justin Cutroni. The code below is a shortened version of Cutronu’s technique which only passes the visitor ID through to our CRM system. The reason is that with the visitor ID we can pull any type of data through the Google Analytics API in retrospect.

<form method="POST" name='contactform' onSubmit="populateHiddenFields(this);">
<input type='hidden' name='visitorid' />
<input type='submit' value='Show GA Info' />
</form>

Click the button see it in action:

Create QlikView script with QVSource

Now that we have set-up the primary key we can start pulling data from the two data sources in our QlikView dashboard. The tool we will use for pulling data from Google Analytics is QVSource. QVSource is a powerfull QlikView add-on allowing you to pull data from a wide variety of sources like, Linked-in, Facebook, Twitter and more. If you haven’t worked with QVSource before I recommend watching the video called Google Analytics Online Offline Data Integration with Qlikview to get a better understanding of how it works.

In our example we will pull the following data from Google Analytics: visitor ID’s (stored in ga:customVarValue5), keyword and source data. This will allow us to measure which keywords and external traffic sources people use to find our site that drive most offline sales.

The next step is to copy and paste the data feed URL in the Connector Settings tab in the QVSource application and go to the Connector Data. Here you can generate the QlikView load statement for your QlikView Dashboard.

Once the Qlikview code is generated copy and paste it into the QlikView dashboard and reload the data. In this simple example below we can see that QlikView has succesfully loaded all Google Analytics data. No programming required. For each visitor ID we can see which source and keyword the visitor has used, how many pages they viewed and how many times they visited the site.

Next Steps

After finishing the last step you will have the Google Analytics data extraction set-up for your QlikView dashboard. The next steps is obviously mapping in sales data from other data sources and start building the actual dashboard. Below is a screenshot of a QlikView dashboard I am currently working on, where I have mapped in offline sales data from a different data source.

Challenges

The examples in this post give a good high-level overview of the most common way to integrate website data with QlikView. Google Analytics however also has limitations.

When looking closly to the second last screenshot you might have noticed that some visitors in Table Box are shown multiple times. The visitor with id 794364472 for example has both been referred through Linked-in, as well as the keyword “Qlikmetrics”. The later represents a normal search path where visitors can visit a site multiple times during a time period. In the field of Web Analytics this often leads to discussions about campaign attribution, for example should we attribute the sale to the first source or the second? In the dashboard above I have used QlikViews interval match to map the sales to the last know search. This technique is called last campaign attribution. To read how challanging these types of attribution models can become I highly recommend reading the following blogpost from Kevin Hillstrom, author of MineThatData.com called Crutchfiled: An Attribution Nightmare.

Other challanges are that Google Analytics only allows you to pull seven dimensions at a time with an API request. This will inevitably lead to challanges building larger datasets for example when adding more dimensions like, visitor browser, geographical location, campaigns etc. When discussing these type of challanges it might be good to mention that Enterprise Digital Marketing Analytics vendors as Webtrends and Omniture offer more powerfull Data Warehousing possiblities. Some providers even allow you to pull raw web data files directly into your Datawarehouse through FTP.

In the following blogposts I am planning to tackle some of the challanges mentioned above.

written by
Renco Smeding is Head of Analytics at Web Guide Partner, one of Scandinavia’s leading digital marketing agencies. Renco is a veteran in the digital analytics industry having worked with digital marketing, web analytics and business intelligence for some of the top brands in the Scandinavian region.

6 Responses to “Merging Google Analytics with QlikView”

  1. Reply Theo says:

    Really cool stuff.

    I love this type of dashboards. Crazy stuff that you can do with qlickview. I keep watching.

  2. Reply Barry says:

    Hi,

    Nice read, especially interesting to see QVSource in action. I’ve had testing QVSource on my to-do list for quite some time. I guess now I will actually have to give it a try, it looks much easier than the other options for reading web data.

    Kind regards,

    Barry
    http://www.qlikfix.com

  3. Reply rsmeding says:

    Thanks guys, glad you liked the post.

    I agree the tool is pretty cool. I am pretty sure we will see more innovative stuff coming from their direction in the short term.

    Cheers!

    Renco

  4. Reply antonio says:

    Very goog info.
    I have a question and i will very happy if you can respond to me.

    If i cant modify the script that google provide you for creating a primary key, is there any other way to create a logical datamodel in qlikview?
    the api only provide you 7 dimensions and i cant find the way to join all the information.

    thanks.

    • Reply Renco Smeding says:

      Hi,

      In this case I think you are running a bit in the limitations of Google Analytics.

      Google Analytics privacy policy does not allow you to store “personal identifiable information” in their system. Otherwise you could have stored your primary key information in Google Analytics using custom variables and pull the data through the API. The following link gives a good understanding of the idea behind this technique:

      http://cutroni.com/blog/2011/05/18/mastering-google-analytics-custom-variables/

      Depending on your goal I would recommend researching paid solutions like Adobe Omniture DWH or Webtends. Adobe Omniture DWH provides similar techniques for storing primary key information which does not go against their terms of usage. Omniture DWH also allows you to pull raw data files through their FTP which literally contain dozens of dimensions and metrics. This effectively removes the limitation of seven dimensions.

      The disadvantage is there is a price tag to this solution.

      I hope this provides answers to your question!

      BR,

      Renco

  5. Reply Antonio Fernandez says:

    Thank you very much.

    The custom variable is not possible in this project, so i dont know how to do it with qlikview.

    If you have other way to do it, probably playing with the internal tables of qlikview o something similar.
    thanks

Leave a Reply