Kategorien
Allgemeines Google Analytics Qlik

Loading Google Analytics with QlikView

General

To keep reports compatible, the following dimensions are alway loaded as a minimum:

  • ga:browser
  • ga:deviceCategory
  • ga:pagePath
  • ga:date

Add Measures

Now as the dimensions are set, you can add up to 5 measures to be grouped by and displayed.

QlikView Load Script

Trace loading the values day by day;

FOR zz = 2012 TO 2015
 FOR ii=1 TO 12 //monat
  FOR dd=1 TO 31 //tag

// Date period
LET jj = num($(ii),'00');
LET ee = num($(dd),'00');
LET ff = num($(dd),'00');

SET gDateStart='$(zz)-$(jj)-$(ee)';
SET gDateEnd='$(zz)-$(jj)-$(ff)';

TRACE ab hier beginnt die Schleife;

GoogleAnalyticsConnectorV3_DataFromQueryURI:
LOAD 
	'DE' AS Land,
	*;
LOAD
	dim_browser AS Browser,
	dim_deviceCategory AS Device,
	dim_pagePath AS Path,
	dim_date,
	makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as date_qv AS ReportingDate,
	metric_users,
	metric_sessions,
	metric_pageviews,
	metric_exits,
	metric_exitRate
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromQueryURI&appID=&feed=https%3a%2f%2fwww.googleapis.com%2fanalytics%2fv3%2fdata%2fga%3fids%3dga%2
%26start-date%3d$(gDateStart)
%26end-date%3d$(gDateEnd)
%26metrics%3dga%253Ausers%252Cga%253Asessions%252Cga%253Apageviews%252Cga%253Aexits%252Cga%253AexitRate%26dimensions%3dga%253Abrowser%252Cga%253AdeviceCategory%252Cga%253ApagePath%252Cga%253Adate]
(qvx);

TRACE ende der schleife;

STORE GoogleAnalyticsConnectorV3_DataFromQueryURI INTO '$(PATH_DATASTAGING)\GOOGLEANALYTICS\01DUMPFORMAT_USER_SESSION_PAGEVIEW_EXITS_EXITRATE$(zz)_$(jj)_$(ee).qvd' (qvd);

DROP TABLES GoogleAnalyticsConnectorV3_DataFromQueryURI;

  NEXT; //next day
 NEXT; //next monat
NEXT; //next jahr

If you don’t have QVSource running, you can use the query explorer and the url with the (1hour) token included to load the data like this. Then I recommend that you put this token into a variable to which will be passed to the script while you wanna load it.

Enhancement

If you have multiple sites you wanna load, just pack the hole load script into a sub and pass over at least the parameter:

  • gAppId

Then use this variable in your load url.

At least you can use an inline table to define your “websites” and then loop through all you sites:

GoogleId:
LOAD * INLINE [
    AppId, Land
    123, mydomain.CH
    456, mydomain.DE
    789, mydomain.AT
];

FOR I = 0 TO 2
	LET vGAppId = peek('AppId',I,'GoogleId');
	LET vCountryId = peek('Land',I,'GoogleId');

	CALL LoadGoogleId(vGAppId, vCountryId);
NEXT;

 

Schreibe einen Kommentar