PLEASE NOTE: We now strongly recommend all users to upgrade to the
new version of this connector.
The Google Analytics connector allows you to get data from the Google Analytics API quickly and easily into QlikView. It can
also be used for getting some Google Adwords related data.
Usage
When you launch the Google Analytics connector you should see something similar to the following:
You should first enter the username and password of your Google Analytics account. You can then test that this is correct by going to the Data tab and testing the 'CanAuthenticate' table:
Building the Data Feed
Once you have done this you can build a feed url using the
Data Feed Query Explorer.
Don't forget to click the authenticate button before building your feed as it will allow you to select your site IDs and other items from the dropdowns:
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.
NOTE: You can only have up to 7 dimensions in your Query - click
here here to find out more.
Once you have built your feed and tested it (using the 'Get Data' button) you should copy the generated url, as highlighted below:
NOTE: 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.
Please note you should ensure that when you click the 'Get Data' button above that data is returned in this web interface before proceeding.
And then paste this into the Feed URL field:
You should now move to the 'Connector Data' tab and click on the table named 'Data'.
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:
Passing in an unencrypted password from QlikView Script
You can also pass in an unencrypted password to the connector from your QlikView load script. For example:
GoogleAnalyticsConnector_Data:
LOAD
id as Data_id,
updated as Data_updated,
title as Data_title,
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_goal1Completions as Data_metric_goal1Completions
FROM
[http://localhost:5555/QVSource/IndustrialCodeBox_GoogleAnalyticsConnector/?table=Data&
feed=..LONG_FEED_HERE_REMOVED_FOR_CLARITY...&
username=[USERNAME_HERE]&
unencryptedPassword=[PASSSWORD_HERE]
(txt, utf8, embedded labels, delimiter is '\t', msq);
This isn't recommended except for in a scenario where you are capturing the user's password in the QlikView application (as for example in our
demo application here) and under no circumstances is it recommended to store this in your QlikView application longer than is needed to make the request.
Scripting Tips
The date is returned from Google Analytics in the form YYYYMMDD. To convert this to a date in your QlikView loadscript you can use the following:
makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Data_dim_dateFor example, in your load script it might look like this:
GoogleAnalyticsConnector_Data:
LOAD
id as Data_id,
updated as Data_updated,
title as Data_title,
dim_source as Data_dim_source,
dim_referralPath as Data_dim_referralPath,
//dim_date as Data_dim_date,
makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as Data_dim_date,
dim_hour as Data_dim_hour,
dim_dayOfWeek as Data_dim_dayOfWeek,
metric_visitors as Data_metric_visitors,
metric_newVisits as Data_metric_newVisits
FROM
....
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 Files
Multiple Profile Demo
We have added a
new demo illustrating the use of the WebProfiles table to enumerate through all the profiles the logged in user has access to extract data for those selected.
Mapping Demo with Video
One of our users (
http://qlikmetrics.com/) has put together a great demo application and video showing how QlikView, QVSource and Google Analytics can be used together.
Thanks
http://qlikmetrics.com/!
Click here for the demo application.
Change Log
1.0.8 - 09/02/12
- If the username or password parameters are passed in the request url but are empty then the values from the QVSource settings will now be used instead.
1.0.7 - 13/01/12
- Large performance boost across all tables.
- More helpful error message now shown when feed url is not specified or when faulty feed url is used.
- Added additional note to max pages input.
- max-results parameter now forced to 10000 when number of pages is set to -1 (i.e. all pages).
1.0.6 - 05/12/11
- Add new "MaxNumberOfPages" parameter to limit number of results returned.
1.0.5 - 19/11/11
- IndustrialCodeBox_GoogleAnalyticsConnector now referred to as GoogleAnalyticsConnector in load script.
1.0.4 - 14/10/11
- Added note to UI reminding user to set max-results=10000.
- Minor internal changes to caching an perfmance. WebProfiles and Segments are no longer cached. Data is cached for 12 hours by default.
1.0.3 - 08/10/11
- Added tables to extract available profiles and segments for user.
- Added table to check if username and password work.
- Can now pass in '&unencryptedPassword=??????' in request url if desired.
- Makes use of new IAddAdditionalScriptBasedFields interface facilities in SDK to add a script based date field.
1.0.2 - 20/08/11
- Handlers characters such as umlauts etc. properly.
- Cache for a particular feed url is now automatically cleared after 12 hours.
1.0.1 - 12/07/11
- Now supports advanced segments.
1.0.0 - 06/07/11
- First official out of beta release.
PLEASE NOTE: We now strongly recommend all users to upgrade to the
new version of this connector.