Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

PLEASE NOTE:
  • This Connector also work with Google's Univeral Analytics.
  • This connector also now has tables for extracting Multi-Channel Funnel Reports to QlikView and Qlik Sense as well as tables for Unsampled Reports also (although they may not yet be shown in the screen shots below).



NOTE:
  • This connector can also be used for getting Google AdWords related data (although we also have a dedicated Connector).
  • This connector can also be used for getting Google DoubleClick Campaign Manager data via new dimensions and metrics added by Google in August 2014 (although we also have a dedicated Google DFP Connector).


Upgrading from Previous Version

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

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

If it is your first time using the connector the easiest way to start getting data is to use a prebuilt query using the DataFromTemplateQuery table. You might first need to click the highlighted link below to populate the list of web profiles you have access to:

Image

You can now select the web profile you would like data for, pick a query, select a date range and then run the 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 and also see the notes below.
* The connector will automatically set 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. In the background the connector will loop through as many pages of data as it can to get all the results.
* 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. You should now be able to run the table, check the data and copy the QlikView load script into your QlikView application.

Image

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.


GoogleAnalyticsConnectorV3_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/GoogleAnalyticsConnectorV3/?table=Goals&format=qvx]
(qvx);


LET noRows = NoOfRows('GoogleAnalyticsConnectorV3_Goals');

set errormode=0;

for i=0 to $(noRows)-1

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

	GoogleAnalyticsConnectorV3_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/GoogleAnalyticsConnectorV3/?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

Live/Realtime Data

QVSource has now been whitelisted for access to the Google Analytics Realtime API. The RealtimeData table allows you to access this.

To use access the realtime dimensions and metrics, you will need to manually work these into a feed url (similar to how the standard 'Data' table works).

For example, to get the number of current active visitor types you would enter:
ids=ga:XXXXXX&dimensions=ga:visitorType&metrics=ga:activeVisitors

Into the Realtime Query input highlighted below (replacing ga:XXXXXX with your own profile ID) and then run the table.

Image

Note that currently it appears this API will only return one page of results and this appears to be a maximum of 1000 results. You can include the max-results parameter in your query if you need more than this but we are not currently sure what this maximum is. We have made a feature request to try to get this clarified.

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.

Getting More Than 7 Dimensions or 10 Metrics

As noted above, the Google Analytics API limits you to no more than 7 dimensions or 10 metrics. We can not provide a way around this and recommend that you do some research on the web to find some of the ways others use to get around this (to some degree).

There appear to be a couple of approaches. The first is creative filtering, which this blog post explains in more detail. The idea is you take a dimension which has a limited subset of values such as ga:isMobile (which can be yes or no) and make two queries to the Google Analytics API. The first with the filter set to 'no' and the second with it set to 'yes'. In this way you do not actually need to include the dimension isMobile in your request as you know from the filter that this value is either 'yes' or 'no' (and therefore free up this dimension to be replaced by something else).

A second approach is to make two (or more) queries that contain exactly the same dimensions, but different metrics, then join them together, using the dimensions as the join key. One simple example would be to create two queries that both have the fields Year and Month and summarize data to the month level. If you join the data from these two queries using using both the Year and Month columns as the joins, then the resulting table will have all the columns from the first two queries.

Another possible technique (which we have not yet explored) is to investigate the use of new custom dimensions. It might be possible, for example, to create a dimension which contains a unique GUID for each log entry made to Google Analytics. It might then be possible to use this to link data from different queries as long as the query contains this new custom GUID.

Unsampled Reports

We have added UnsampledReportsInsert, UnsampledReportsList and UnsampledReportsGet tables (only accessible to Google Analytics Premium users) in version 1.1.0 of this connector (available in QVSource 1.6.0.6 and later).

You will need to first use the UnsampledReportsInsert table first to request a report to be generated. As noted in the table description, you will need to supply this table with a management.unsampledReport JSON object with end-date, metrics, start-date and title properties and optionally dimensions, filters and segment properties (see also this page).

For example:

{
          "title": "A Test Report",
          'start-date': '2015-01-01',
          'end-date': '2015-05-19',
          'metrics': 'ga:pageviews,ga:bounces',
          'dimensions': 'ga:browser',
          'filters': 'ga:bounces>=100',
          'segment': 'gaid::-1'
}

This will then return an id which you can use in conjunction with the UnsampledReportsGet table to monitor and wait until the status column is COMPLETED. Alternatively you can check the UnsampledReportsList table when your application reloads to check for any reports not yet loaded into the application.

Once this is the case and assuming the the report has been sent to Google Drive (the only scenario supported at present however, as noted here, reports can also be delivered to Google Cloud Storage), you should be able to use the GetRawFileByIdAsText table in the Google Drive Connector to load the report directly into QlikView or Qlik Sense - supplying it with the value from the driveDownloadDetails_documentId column from either the UnsampledReportsGet or UnsampledReportsList tables.

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

Unexpected Data

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. A common solution to avoid sampling is to request data over smaller periods - for example instead of requesting data for a month at a time request it over a day or a week at a time. Note that you can use the QueryInfo table in QVSource to ascertain whether the results would be sampled - you could even use this in your QlikView script to set a warning in the QlikView application or perhaps send an alert email using the Notification Connector.

Can't Authenticate

We have had a couple of users find that in the authenticate screen, shown above, everything is greyed out/disabled, preventing them from completing the authentication.

The solution to this appears to be to simply press F5 to refresh the embedded browser, causing it to become enabled or to use the "Authenticate (Manual)" tab in the authentication dialog.

Alternatively. some users have found that the authentication screen is completely blank (white). If you experience this please first ensure that you can log in to the Google Analytics website using Internet Explorer. Users with the issue usually find that they experience the same problem in Internet Explorer due to security settings on their computer. Some users have also resolved it by running QVSource as an administrator although this should not be necessary.

Once this has been resolved and you can log into the Google Analytics Website from Internet Explorer it should also be possible to log in to the connector from QVSource.

insufficientPermissions Response

If you see the following error message in the response from QVSource (i.e. in the UI or when you rest a QVSource request in a browser): {"domain":"global","reason":"insufficientPermissions","message":"User does not have sufficient permissions for this profile."}

It might be because either:
  • The user you have authenticated with in the QVSource Connector does not have access to the web profile you are trying to load data from. To check this, log into Google Analytics via your browser with the same user account and verify that it does indeed have accesss.
  • You have specified an incorrect web profile in your QVSource request. A common cause of this is using a value from the accountId column in the WebProfiles table of the connector where you should be using a value from the id column.

userRateLimitExceeded Response

If you are finding that you are periodically getting load script errors with this connector we would suggest turning on logging, recreating the issue and then examining the log file for errors. If you find the following: {"domain":"global","reason":"userRateLimitExceeded","message":"Quota Error: User Rate Limit Exceeded."}

We would suggest first trying to put a short (e.g. half a second) sleep period in your load script between successive calls to the API. If this does not work please contact us with details of how you are using QVSource (for example, whether you are running multiple reloads in parallel).

API Usage Limits

Please note that for the 'data' tables in QVSource (the 'DataFromQueryURI', 'DataFromTemplateQuery' and 'RealtimeData' tables at time of writing), the Google Analytics API will limit QVSource to making 50,000 API calls per day per profile and this is reset at midnight pacific time. Each API call can retrieve up to 10,000 rows of data.

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 and specific error codes here.

NOTE That there is also a rate limit (maximum number of requests per second per IP address) but QVSource should prevent this from being exceeded. If however you do experience the following error: {"domain":"global","reason":"userRateLimitExceeded","message":"Quota Error: User Rate Limit Exceeded."}

We would suggest first trying to put a short (e.g. half a second) sleep period in your load script between successive calls to the API. If this does not work please contact us with details of how you are using QVSource (for example, whether you are running multiple reloads in parallel).

Google Analytics API

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.

API Changelog

You can view the Google Analytics API Change Log here where you will find details on updates, improvements and changes including additions of new dimensions and metrics.

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.

We would also recommend you follow the API posts on the Google Analytics Blog where you will find some useful tips and updates.

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.1.4 - 06/11/15
  • Added sampleSize and sampleSpace columns to DataFromQueryURIQueryInfo table.

1.1.3 - 26/08/15
  • &max-results=10000 is now added to a query if the max-results parameter is not already present (previously it would only ensure that max-results was set to 10000 only if the max-results parameter was at least present).

1.1.2 - 25/06/15
  • Accounts table now requests data in pages of 100 results each.

1.1.1 - 06/06/15
  • Removed note about samplingLevel parameter from tables which take a feed URL as this is now included as an input parameter on Google's feed builder page.

1.1.0 - 19/05/15
  • Added UnsampledReportsInsert, UnsampledReportsList and UnsampledReportsGet tables (only accessible to Google Analytics Premium users). Note at present we only support customers who have set this up to export the reports to Google Drive and the Google Drive Connector now has a new GetRawFileByIdAsText table to retrieve these more easily.

1.0.7 - 29/01/15
  • Added new CustomDimensions and CustomMetrics tables.

1.0.6 - 30/07/14
  • Added note about manually adding samplingLevel= parameter to DataFromQueryURIQueryInfo and DataFromQueryURI tables.

1.0.5 - 24/06/14
  • Added UnsampledReport placeholder table to new API feature (http://analytics.blogspot.co.at/2014/06/new-google-analytics-premium-feature.html).

1.0.4 - 15/05/14
  • Now enforces a minimum time between requests of 100ms (to attept to resolve the rare userRateLimitExceeded which was otherwise resolved by adding a small sleep in the load script).

1.0.3 - 10/05/14
  • Query UI for Data and RealtimeData tables can now begin with ?ids= or just ids=.

1.0.2 - 30/01/14
  • WebProfiles table should now page correctly.
  • Fixed minor bug with incorrect max value for progress updates.
  • Fixed help link.

1.0.1 - 12/11/13
  • Now automatically updates old feed urls (e.g. https://www.google.com/analytics/feeds/data?...) to new (e.g. https://www.googleapis.com/analytics/v3/data/ga?...).

1.0.0 - 23/09/13
  • First release.
  Name Size
- Accept Permissions.png 29.01 KB
- Authenticate Button.png 27.20 KB
- Authenticate.png 20.25 KB
- feed url.png 125.41 KB
- GA Token.png 32.29 KB
- realtime table.png 40.31 KB
- Template Query 1.png 37.90 KB
- Template Query 2.png 152.20 KB


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd