topic Question about connecting Google's web analytics API and QlikView in QlikView

Question about connecting Google's web analytics API and QlikView

Fri, 19 Jun 2009 03:04:37 GMT

Hey all,

I am working on web analytics here at QlikTech and am wondering if anyone else has tried to hook Google Analytic's API into QlikView. Google's analytics is certainly a great value but I am really interested to see how much more powerful the data can be when tied with QlikView.

anyway, if you have any thoughts please let me know, send me a PM or add below.

Thanks and look forward to exploring this

Jonathan Moody
Global Online Lead Gen
QlikTech International

Question about connecting Google's web analytics API and QlikView

Fri, 19 Jun 2009 06:14:40 GMT

It's on my team's roadmap, but we havent' begun implementation. GA's API is really new, so I'd actually be surprised if people had full integration yet (at least synced up with a major application that has sales data, etc.).

Question about connecting Google's web analytics API and QlikView

Wed, 24 Jun 2009 00:24:10 GMT

Yea I gree completely with the above. I like that GA's API is new and we can take advantage of moving quick and efficiently.

Imagine combining CRM and GA data for that elusive "All-in-one" dashboard.

Heck, while we are at it let's integrate social media metrics and activity as well...

Jon,,

Question about connecting Google's web analytics API and QlikView

Wed, 24 Jun 2009 01:53:06 GMT

"Heck, while we are at it let's integrate social media metrics and activity as well..."

Yes please...

Question about connecting Google's web analytics API and QlikView

disqr_rm — Fri, 06 Nov 2009 07:24:41 GMT

Anyone had luck with it?

I am trying to make authentication (token) working via Java Script but no luck so far.

Any ideas to use GA APIs directly into QlikView and extract data periodically - with every reload?

Question about connecting Google's web analytics API and QlikView

Tue, 15 Dec 2009 20:38:32 GMT

See this extremely simple example how to read data from Google Analytics.

http://community.qlik.com/media/p/90513.aspx

Re: Question about connecting Google's web analytics API and QlikView

chrisbrain — Sat, 19 Dec 2009 02:32:10 GMT

I also posted demo for this here - this one uses a helper component from a QlikView macro to fetch the data from Google Analytics (and there's a similar demo for downloading data from Google Spreadsheets), although I really like the approach of the above sample which does it all from the load script.

This has now been replaced by a much more versatile solution for connecting QlikView to APIs and other difficult-to-load datasources:

http://www.qvsource.com/wiki/MainPage.ashx

Question about connecting Google's web analytics API and QlikView

Tue, 26 Jan 2010 14:16:50 GMT

Dear Jonathan,

I use GA API to fetch data into qlikview, but it is very limited. The maximum rows supported by API is 50000 rows but I cannot fetch more than 6000 rows. I also put the longtitude and latitude points on to the google map. It looks like cool but the limitations makes it unusable. Because you cannot be sure that you fetch all the data or not. If you have any solution for that I will more than happy to get your support.

Best Regards,

Question about connecting Google's web analytics API and QlikView

disqr_rm — Tue, 26 Jan 2010 14:47:37 GMT

You can always loop through dates and fetch reports per day in that loop. While looping you can keep your QVD accumulated, so at the end you have QVD with all the record you need. ASAIK GA will not restrict you calling API hundreds of times. I have tested for 180 days atleast and seems to be fine.

Hope this helps.

Rakesh

Question about connecting Google's web analytics API and QlikView

Wed, 27 Jan 2010 11:09:33 GMT

Dear Rakesh,

Thanks for your advice. Would you please send a sample file. Because I'm still in doubt for getting all of the data without gap. Because the API cannot know the maximum row number or the index number of the row. Your sample file will be helpful.

Best Regards,

Question about connecting Google's web analytics API and QlikView

Tue, 02 Feb 2010 08:10:25 GMT

Dear Rakesh,

I'm looking forward to receive your sample about creating loop to fetch all data from google analytics.

It is important for me. Thanks for your support.

Best Regards,

Asim Akin

Question about connecting Google's web analytics API and QlikView

disqr_rm — Tue, 02 Feb 2010 15:33:06 GMT

Hi Asim,

Below if the portion of code in "JScript" which shows you how you can loop through and build URL for each daya to down load data for that day. This sample loops for 10 days in past starting yesterday.

Hope this helps you.

Rakesh


for (var dt=1; dt<=10; dt++)
{
 var today = new Date();
 var curDate = new Date(today.getYear(), today.getMonth(), today.getDate()-dt);
 var myDate = curDate.getFullYear() + "-" + zeroPad(curDate.getMonth() + 1,2) + "-" + zeroPad(curDate.getDate(),2);

 var startdate = myDate;
 var enddate = myDate;
 var dim = "ga:medium";
 var metrics = "ga:visits";
 var profileid;
 profileid = "YOUR PROFILE ID";

 var GetDataUrl = "https://www.google.com/analytics/feeds/data?ids=" + profileid + "&dimensions=" + dim + "&metrics=" + metrics + "&start-date=" + startdate + "&end-date=" + enddate + "&start-index=1&prettyprint=true";

 xmlhttp.open("GET", GetDataUrl, false);
 xmlhttp.setRequestHeader("Authorization", "GoogleLogin auth=" + AUTH);
 xmlhttp.send();

 writeToFile(xmlhttp.responseText);
}

Just in case if you need zeroPad function, here it is:


function zeroPad(num,count)
{
 var numZeropad = num + '';
 while(numZeropad.length < count)
 {
 numZeropad = "0" + numZeropad;
 }
 return numZeropad;
}

Question about connecting Google's web analytics API and QlikView

Wed, 03 Feb 2010 07:51:56 GMT

Hi Rakesh,

I use GoogleAnalytics client API to fetch the rows. So the above code will not be useful for me. Would you please check attached file and inform me how can fetch rows without any gap.

Please fill the below parameters before reloading the QV file.


// Google Analytic settings
SET gUserName=''; //Google Analytics username
SET gPassword=''; //Google Analytics password
SET gProfileId1=''; //Google Analytics ID for your website

Best Regards,

Question about connecting Google's web analytics API and QlikView

disqr_rm — Wed, 03 Feb 2010 16:34:02 GMT

oh! I see. In that case try this:


// Date period
LET gDateStart = makedate(2010, 1, 21);
LET gDateEnd = makedate(2010, 1, 25);
LET NoOfDays = gDateEnd - gDateStart;
For dt = 0 to NoOfDays
 Let myDate = text(date(gDateEnd - dt, 'YYYY-MM-DD'));
 Let myFile = 'visits-' & myDate & '.csv';
 EXECUTE java -jar GoogleAnalyticsClient-20100127.jar -u $(gUserName) -p $(gPassword) -f $(gProfileId1) -d $(gDimensions1) -m $(gMetrics1) -s '$(myDate)' -e '$(myDate)' --max-results=5000 --filters=$(filters1) -o '$(myFile)';
Next

For others: Client jar file details are here http://support.gooddata.com/forums/66514/entries/65253

Question about connecting Google's web analytics API and QlikView

Thu, 04 Feb 2010 09:12:40 GMT

Dear Rakesh,

Thanks for your support. The code worked successfully. But there are still miss rows in the fetched ones. This cannot be fetch all of the rows for a day. The reason of this is about the limitation of API. It can only fetch 10K rows even if they told they can fetch 50K rows. I still need support to fetch all of the rows. By the way, how can I load the csv file into a qvd file.

Hope to hear you soon.

Kind Regards,

Question about connecting Google's web analytics API and QlikView

Tue, 09 Feb 2010 09:25:51 GMT

Dear Rakesh,

Is it possible to use "-startindex" parameter to prevent missing any data from google analytics. Before using this parameter we should check the latest row number of the csv file and use this parameter with +1. But I don't know how can I do that. Would you please help me about it.

Hope to hear you soon.

Best Regards,

Asim Akin

Question about connecting Google's web analytics API and QlikView

disqr_rm — Tue, 09 Feb 2010 15:32:31 GMT

The 10K row limit seems to be from the java client I believe. The API is documented for 50K rows per fetch and should work fine with that.

I think you may have to explore other options, like having your JScript in the QVW itself or building your own connector / client for GA. Have a look here http://code.google.com/p/gadataextractor/source/browse/#svn/trunk and if works for you don't forget to give credit to Eric, the original developer. Project home is http://code.google.com/p/gadataextractor/

Hope this helps.

Question about connecting Google's web analytics API and QlikView

disqr_rm — Tue, 09 Feb 2010 15:44:00 GMT

Other option to build a logic as follows (kinda pseudo code):

Let vStartIndex = 1;
let vMaxResults = 10000;
Loop at dates
Loop until GA returns data
Call GA with --start-index = vStartIndex and --max-results = vMaxResults;
Check if returned XML had 10k records, otherwise this was last block. In that case, exit.
let vStartIndex = vStartIndex + 10000;
End loop
End Loop

Question about connecting Google's web analytics API and QlikView

Thu, 18 Feb 2010 14:00:03 GMT

Question about connecting Google's web analytics API and QlikView

Mon, 22 Feb 2010 13:15:35 GMT

Hi Rakesh,

Would you please help me for the qv file instead of psedeu code.

Thanks for you great support.

Best Regards,

Asim AKin