Question about connecting Google's web analytics API and QlikView

[#1]

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

[#2]

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

[disqr_rm]

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;
}

[#4]

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,

[disqr_rm]

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

[#6]

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,

[#7]

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

[disqr_rm]

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.

[disqr_rm]

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

[#10]

Hi Rakesh,

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

Thanks for you great support.

Best Regards,

Asim AKin