topic Google Analytics API Incremental data update and Dynamic Date in QlikView

Google Analytics API Incremental data update and Dynamic Date

Anonymous — Mon, 12 Dec 2011 09:09:10 GMT

Hello,

I was looking for a way to have Qlikview do an incremental update of its data coming from Google Analytics.

Currently, when I want to pull in new data, I need to do a refresh of the entire data table. For one site, that's ok, but I expand this to quite a few more, and I don't want Qlikview to spend all day pulling in the entire datasets from Google Analytics (ok, that may be dramatized a bit but you get my point ..)

Also, I was wondering if there is a good way to dynamically change the date range. I would like to open the QV dashboard weekly, hit the "Reload" button, and have QV update the data until the date of the day before without having to change the scripts' "end-date" over and over again.

Any support would be greatly appreciated !

Best regards,

Christophe

Google Analytics API Incremental data update and Dynamic Date

Anonymous — Tue, 03 Jan 2012 09:57:12 GMT

No answers just yet -- is it because it's impossible to do?

Google Analytics API Incremental data update and Dynamic Date

Sat, 11 Feb 2012 18:05:10 GMT

Hi Cristoph,

Why don't you make your code in the script be more dynamic? I mean, you can make the start and end dates be calculated each time the script runs (the qv document is reloaded) and calculate them.

This way, the document would only retrieve from Google the data of the last week or day or month, and the "historical" data you can get it from your own QVD files, populated each time the qv document is reloaded.

Something like:

LET gDateStart= date(today(),'YYYY-MM-DD');

instead of the hardcoded date:

SET gDateStart= '2012-02-11';

Do you have the dates hardcoded in the script?

Have you ever heard about incremental reloads in QlikView using QVD files? If not, check the PDF within this link:

http://bit.ly/wbeGEx

Kind Regards,

Borja

Google Analytics API Incremental data update and Dynamic Date

Anonymous — Mon, 13 Feb 2012 08:05:23 GMT

thx a lot bhs -- I have indeed done it on the mean time like this (forgot to mention it on the forum):

LET vStartDate = '2011-09-01';

LET vEndDate = Year(Today(1)) & '-' & NUM(MONTH(Today(1)),00) & '-' & Date(Today(1),'DD');

when all's stored in the qvd file, I used this:

LOAD * From GoogleAllGoals.qvd (qvd);

Max_Start:

LOAD MAX(Data_date_qv) AS Max_StartDate

Resident GoogleAllGoals;

LET vStartDate = Date(PEEK('Max_StartDate',0,'Max_Start'),'YYYY-MM-DD');

The google query itself would be something like this then:

http://localhost:5555/QVSource/GoogleAnalyticsConnector/?table=Data&username=xxxx&password=xxxxx%3d%3d&feed=https%3a%2f%2fwww.google.com%2fanalytics%2ffeeds%2fdata%3fids%3dga%25xxxxx%26dimensions%3dga%253Adate%252Cga%253Acountry%252Cga%253Aregion%252Cga%253Acity%252Cga%253Asource%26metrics%3dga%253Avisitors%252Cga%253Agoal1Completions%252Cga%253Agoal2Completions%252Cga%253Agoal3Completions%252Cga%253Agoal4Completions%252Cga%253Agoal5Completions%26start-date%3d$(vStartDate)%26end-date%3d$(vEndDate)%26max-results%3d10000&format=html

But thanks again for taking the time to respond !