Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Google Analytics in QlikView by using QVSource

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

18 Replies
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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
Not applicable
Author

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.

Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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!

example.jpg