Welcome Guest, you are in: • Login

QVSource - The QlikView API Connector (Docs)


This new version of the Google Analytics Connector uses the latest version 3 of the Google Analytics API which returns JSON data which is much more compact (in some cases up to 50% smaller) than the xml API used by the initial version of this connector.

It also uses OAuth meaning you never need to store your username and password in QVSource.

NOTE: This connector can also be used for getting Google AdWords related data.

Upgrading from Previous Version

We would recommend regenerating your load scripts when upgrading to this new version although in general the feed url and main column names should remain the same (for the Data table).

Usage

You should first authenticate with the connector by clicking this button:

Image

You will be prompted to enter your username and password and then to allow access to your Google Analytics data:

Image

Once you have accepted this you should find a token has been entered into QVSource for you:

Image

Using A Prebuilt Data Feed

This feature is available in version 1.0.3 of the Connector or later.

If it is your first time using the connector the easiest way to start getting data is to use the prebuilt queries:

Image

First select the web profile you would like data for, then you can select a date range:

Image

And finally select one of the queries:

Image

You can then move to the 'Connector Data' tab and view the 'Data' table:

Image

You can then copy the load script for this using the 'QlikView Load Script' tab as with any other connector.

Building A Data Feed Manually

This is the most powerful option and gives you full flexibility over the dimensions and metrics returned.

You can build a feed url using the Data Feed Query Explorer. Don't forget to click the 'Authorize Access' button (pictured below) before building your feed as it will allow you to select your site IDs and other items from the dropdowns:

Image

NOTE: Some users find that on trying to authenticate they are returned to a blank page and an error message (this seems to be a known issue). Please be aware that this web page is a Google service and not in any way related to QVSource so is not something we are able to offer support for. If you do experience a problem then we would recommend trying a different browser, for example Firefox or Chrome.

You can now use this page to build a feed.

Image

Please keep the following in mind:

* You can only have up to 7 dimensions in your Query - click here here to find out more.
*We recommend setting the max-results to the maximum value of 10000 to improve download times. This does not mean the results are limited to the first 10000 rows, just that each page of requests will pull down the maximum possible.
*You should ensure that when you click the 'Get Data' button above that data is returned in this web interface before proceeding.
* The Dimensions & Metrics Reference document gives a description of all the dimensions and metrics as well as providing a feature to show valid combinations of these.

Once you have built your feed and tested it (using the 'Get Data' button) you should copy the generated url, as highlighted below:

Image

And then paste this into the Feed URL:

Image

You should now move to the 'Connector Data' tab and click on the table named 'Data'.

Image

You should now be able to copy the QlikView load script into your QlikView application.

Using Custom Variables

If you would like to use custom variables you will need to replace the ga:customVarValue(n) or ga:customVarName(n) with, for example ga:customVarValue1 or ga:customVarName3 - i.e. replace (n) with an integer. So you might have something like this:

Image

Loading Goal Data

QVSource provides a Goals table which lists all the goals defined in your GA account.

You can build a feed which pulls data on these goals using the feed builder and in particular the ga:goal??????? metrics. Note that many of these metrics require replacement of '(n)' with the actual numerical id of the goal. For example goal(n)Starts would need to be something like goal2Starts in your feed url.

The following sample script loops through all the goals defined and for each one requests a years worth of data. Notice how the goal and profile IDs are substituted with real values in the for loop.


GoogleAnalyticsConnectorV2_Goals:
load
	id as Goals_id,
	accountId as Goals_accountId,
	webPropertyId as Goals_webPropertyId,
	internalWebPropertyId as Goals_internalWebPropertyId,
	profileId as Goals_profileId,
	name as Goals_name,
	value as Goals_value,
	active as Goals_active,
	type as Goals_type,
	created as Goals_created,
	updated as Goals_updated,
	urlDestinationDetails_url as Goals_urlDestinationDetails_url,
	urlDestinationDetails_caseSensitive as Goals_urlDestinationDetails_caseSensitive,
	urlDestinationDetails_matchType as Goals_urlDestinationDetails_matchType,
	urlDestinationDetails_firstStepRequired as Goals_urlDestinationDetails_firstStepRequired,
	urlDestinationDetails_steps_number as Goals_urlDestinationDetails_steps_number,
	urlDestinationDetails_steps_name as Goals_urlDestinationDetails_steps_name,
	urlDestinationDetails_steps_url as Goals_urlDestinationDetails_steps_url
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV2/?table=Goals&format=qvx]
(qvx);


LET noRows = NoOfRows('GoogleAnalyticsConnectorV2_Goals');

set errormode=0;

for i=0 to $(noRows)-1

	let id = peek('Goals_id',$(i),'GoogleAnalyticsConnectorV2_Goals');
	let profileId = peek('Goals_profileId',$(i),'GoogleAnalyticsConnectorV2_Goals');

	GoogleAnalyticsConnectorV2_Data:
	load
		'$(profileId)' as Goals_profileId,
		dim_campaign as Data_dim_campaign,
		dim_source as Data_dim_source,
		dim_keyword as Data_dim_keyword,
		dim_adGroup as Data_dim_adGroup,
		dim_date as Data_dim_date,
		makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Data_date_qv,
		metric_pageviews as Data_metric_pageviews,
		metric_goal2Starts as Data_metric_goal2Starts,
		metric_goalStartsAll as Data_metric_goalStartsAll,
		metric_goal2Completions as Data_metric_goal2Completions,
		metric_goalCompletionsAll as Data_metric_goalCompletionsAll
	FROM
	[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV2/?table=Data&feed=https%3a%2f%2fwww.googleapis.com%2fanalytics%2fv3%2fdata%2fga%3fids%3dga:$(profileId)%26dimensions%3dga%253Acampaign%252Cga%253Asource%252Cga%253Akeyword%252Cga%253AadGroup%252Cga%253Adate%26metrics%3dga%253Apageviews%252Cga%253Agoal2Starts%252Cga%253AgoalStartsAll%252Cga%253Agoal$(id)Completions%252Cga%253AgoalCompletionsAll%26start-date%3d2011-08-23%26end-date%3d2012-08-23%26max-results%3d50&format=qvx]
	(qvx);
	
next

Scripting Tips

If you find you are getting an time out error for your request (e.g. 'The operation has timed out') then it might be because of the volume of the date you are requesting.

For example let's say you are requesting a month's worth of data - try splitting it into a series of daily requests in your load script and see if this solves your time out issue.

Possible Error Responses

This page lists the error codes which the API might return. If you are seeing errors when using this connector it is a good idea to see if you can relate it to one of the errors on this page first as it might indicate the cause.

Troubleshooting

If you are not getting the data you expect or feel you are getting data conflicting with that in another table, either via QVSource or the Google Analytics interface, please see the Google Analytics section on the troubleshooting page. In our experience this is usually due to a misunderstanding of the dimensions and metrics or due to the sampling used by Google Analytics.

Live/Realtime Data

Unfortunately it appears that the Google Analytics API does not yet return 'real time' data (as is now available in the Google Analytics web interface), however this does seem to be a commonly requested feature which is being considered by Google (see issue 154 tracked here) for latest status.

API Usage Limits

Please note that the Google Analytics API will limit you to 10,000 API calls per day per profile and this is reset at midnight pacific time.

It is unlikely that you will need anywhere near this number of calls but please keep this in mind and note that if you start getting 403 errors or seeing a message similar to the following in your QVSource responses or the event logs:
{"domain":"global","reason":"dailyLimitExceeded","message":"Quota Error: profileId ga:XXXXXXX has exceeded the daily request limit."}

It could be because you have gone over this limit. Please note that if you have other users hitting the same profile through the Google Analytics API from other (non QVSource) applications it will also contribute towards usage of this quota.

We have heard it might be possible to request Google to raise this per profile per day limit on a per user/profile limit.

You can see full details on the quota limits here.

API Status

Please note, web APIs in general, are not always 100% reliable. If you are experiencing issues with the data returned by the connector please check the official Analytics Status Dashboard.

Demo Application

You can find a demo application showing how to load data from multiple Google Analytics Profiles here.

We also have a more complete Google Analytics application here, which you can view it online here.

This blog post from notas bi also has a downloadable demo application.

Other Resources

The Google Analytics Blog is a great resource if you work a lot with this API - for example here is a useful post on Mastering Unique Visitors in the API.

This blog post from notas bi shows this connector in use and has a downloadable demo application.

Change Log

1.0.3.8 - 26/02/13
  • Feed and pacific time now logged with API call logs.
  • quotaUser parameter now passed (using hash of licence ID). This could help with user quota limits if multiple apps are making requests to the Google Analytics API from the same API address.
  • Enforces no more than 10 concurrent requests if being used in async mode.

1.0.3.7 - 22/01/13
  • max-results parameter should now always correctly be set to 1 for QueryInfo table (to maximise performance).
  • Minor refactoring and performance improvements.
  • Fixed issue where parameter name MaxNumberOfRows was being used instead of MaxNumberOfPages in some branches. Please use MaxNumberOfPages in your URL IF you need this.

1.0.3.6 - 22/01/13
  • MaxNumberOfPages parameter now correctly placed into request URL when not set to -1.

1.0.3.5 - 09/07/12
  • FeedUrl parameter now correctly included in QlikView script for QueryInfo table.

1.0.3.4 - 05/07/12
  • defaultPage column now added to WebProfiles table even if it is not returned back in the data as one user experienced.
  • In case of error from request the connector will now make a second attempt after a short delay (< 1 second).

1.0.3.3 - 15/06/12
  • Added Accounts table.
  • Default timeout (actually at QVSource level) increased to cater for long running queries.

1.0.3.2 - 16/04/12
  • Fixed logging issues where API calls were being logged twice and being placed at top instead of end of log file.

1.0.3.1 - 03/04/12
  • Minor fix.

1.0.3 - 03/04/12
  • Now waits a mimimum of 110 milliseconds betweeen requests to stay inside Google's maximum 10 requests per second quota policy.
  • New prebuilt feed selector UI added.

1.0.2 - 16/03/12
  • Code refactoring to use SDK helpers to support API connectivity.

1.0.1 - 14/03/12
  • Added WebProfiles table.

1.0.0 - 09/03/12
  • Taken out of beta, this will now be commercially available and supported and recommended over the previous version.

0.8.0 - 02/03/12
  • First release.
© Copyright 2011 - 2013 Industrial CodeBox Ltd