Google Analytics in QlikView by using QVSource - Qlik Community

Anonymous · ‎2012-01-12

Hi all,

Is there anyone who can help me with the development of Google Analytics in QlikView by using QVSource?

The question is: I am loading all the articles which are published on our website into QlikView from 2009. You can imagine this is a big load.

Is it possible to load the data from 2009 until today once (it will not change anymore) and with every reload, load the new data into it, without reloading from 2009? And after that the same for the next day, and so on.

Any suggestions?

Kind regards,

Henco

Anonymous · ‎2012-01-12

I have created a similar solution.

I did a query for each date and and then looped over the periode (365 queries per year).

I put a small sleep of 1-2 sec between each query cause Google dont allow that many queries per hour. That gave me the oppotunity to do a full load in 6 hours.

In the following loads i substitute the last 3 days with new data, cause Google is not always up to date. I use a incremental technic for that part.

Hope this is a help and have fun. I learned a lot from this case and QCSouce worked ok.

Anonymous · ‎2012-01-12

Hi Henrik,

Thanks for your answer. Could you please share your script on how to do this?

I understand your solution, but don't know how to create it. Thanks in advance.

Kind regards,

Henco

chrisbrain · ‎2012-01-13

Hi Henrik,

Are you looking for a solution to store the data into, a QVD file?

So perhaps in the first load you have a feed url something like (this is probably a lot simpler than your feed url):

https://www.google.com/analytics/feeds/data?ids=ga%XXXXXXX&dimensions=ga%3Adate&metrics=ga%3Abounces...start-date=2009-01-01&end-date=2012-01-13&max-results=10000

Then the results are stored in a QVD, then each day afterwards you just load the last days data and load it into a QVD? So the next day the feed might look like:

https://www.google.com/analytics/feeds/data?ids=ga%XXXXXXX&dimensions=ga%3Adate&metrics=ga%3Abounces...start-date=2012-01-13&end-date=2012-01-14&max-results=10000

And so on.

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense

Anonymous · ‎2012-01-13

The code is protected, so that is not possible. Sorry.

I use "Sleep 3000" between each query in the full load - If that is not enough you can adjust it.

In the incremental load i don't use sleep.

About incremental load i use the date as incremental seperator.

When i send queries against google, I only ask for data for one date. So you need to set filter on Start-date and End-date.

Report Inappropriate Content · ‎2012-01-13

Hi, Henco - just providing an alternative to Henrik's (good) solution.

We use Qlikview as a lightweight data import tool (like Microsoft's SSIS), as well as a reporting tool. What I mean by that is - while we're pulling in data (from Google Analytics, or a similar API) and we'd like to keep it, we insert it in to a database/datawarehouse as part of the QlikView script. The benefits of that are the speed of querying a database for lots of data (instead of slowly and regularly querying the API for lots of static historical data), and in addition that data is available for other applications and Qlikview reports, not just the single report.

Something you might want to think about.

chrisbrain · ‎2012-01-16

Hey lbastmaster, Interesting! Could you give a little more info on how you are inserting it into a database/datawarehouse using your QlikView script? This sounds like a really useful technique .

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense

Anonymous · ‎2012-01-16

Hi Henrik,

Thanks for your reply. Am I understanding it right that you do it in this way:

First do a full load of al your data and store that in a QVD file? After that you reload every single day and store that also in the QVD? Do you use the same feed URL's as Chris mentioned?

Kind regards,

Henco

Report Inappropriate Content · ‎2012-01-16

Hey, Chris - sure, I'll give you an example.

We're using QVSource to pull Facebook Insights data for 500+ domains (and growing) of ours, and report out daily Likes, Shares, etc., for the last 12 months. Rather than refresh ALL that data out of Insights daily (which would take hours each day), after we load in new data that we want to keep, we store it in tables in a local database. We only actually query Insights data for the last 2-3 days worth of data, update the database table with those results, then use the data from the table for the actual Qlikview report.

Just like to you can do a SQL SELECT statement in the QV scripts, you can do a SQL INSERT (or UPDATE, DELETE, etc.).

Anonymous · ‎2012-01-17

Hi all,

I read a lot about incremental reload and storing the data in a QVD file, but unfortunately I can't translate it to my own document. I hope someone of you can help me out.

I use QVSource to load the data into QlikView. So, in the document I got a FROM statement which contains the Google feed url with a certain daterange. What I would like to do is load the data for 2009, 2010, 2011 and 2012 untill today's date into a QVD file. After that I would like to add every new day into the QVD file. In another QlikView document I use that table to get my data from.

I added a simple shot of the current situation and hope anyone of you can tell me how to get this done.

Thanks in advance!