Welcome Guest, you are in: Login

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

NOTE: You can also use the Google Analytics Connector to extract some Google AdWords related data as discussed here.


Usage

Authentication

The first thing to do is authenticate:

Image

After clicking the 'Authenticate' button you should sign in with the account you wish to use, you then need to allow QVSource permission to access your AdWords data:

Image

Once authenticated with Google you'll be taken back to QVSource and you'll see the authentication box filled with a token.

Image

Client ID

In the report tables below, you will also need to specify a Client ID - you should be able to find this in the "My Client Centre" of your Google AdWords account, highlighted below. It should have a format something like XXX-XXX-XXXX.

Image

Report Building

There are two ways you can retrieve AdWords data into QlikView or Qlik Sense using QVSource. These are explained below.

Using The AdWords Query Language (AWQL)

You can use AWQL to define a report query, and use this in conjunction with the AdHocReportFromAWQL table in QVSource.

For example, using the docs here which give an overview of AWQL in conjunction with the detailed list of available reports here we could build the following query:

SELECT Date, Clicks FROM CAMPAIGN_PERFORMANCE_REPORT DURING 20100101,20130101

As shown below.

Image

We can then run the table and generate the QlikView load script in the usual way.

Using an XML Report Definition

You can also generate a report definition in XML. This probably takes a little more time and skill than the AWQL method,

An example of an AdHoc report can be found here.

Image

Highlighted are some key components.
  • The Fields are the columns that are returned.
  • The Predicates are the conditions i.e. give me the values when conditions are met.
  • Next is the report type, which defines the Fields available.
  • The download format must XML.

This page gives a list of reports that are available and are defined in the report (above) as the 'Report Type'. So, on looking at the 'Ad Performance Report', you'll see all the fields in the example listed.

Image

The example had a Predicate based on the status. If we look further down we can see the 'Status' field and in the last column we can see the options available, which are used in the example

Image

Using the example and the documentation pages along with the XSD for report definitions, you should be able to build up the report definition that you need.

Once you have, copy the definition and paste it into QVSource using the AdHocReportFromXML table.

In the example below we use the following XML:


<reportDefinition>
  <selector>
    <fields>CampaignId</fields>
    <fields>Impressions</fields>
    <fields>Clicks</fields>
    <fields>Cost</fields>
    <predicates>
      <field>Clicks</field>
      <operator>GREATER_THAN</operator>
      <values>5</values>
    </predicates>
    <dateRange>
      <min>20100201</min>
      <max>20150301</max>
    </dateRange>
  </selector>
  <reportName>Custom Adgroup Performance Report</reportName>
  <reportType>ADGROUP_PERFORMANCE_REPORT</reportType>
  <dateRangeType>CUSTOM_DATE</dateRangeType>
  <downloadFormat>XML</downloadFormat>
</reportDefinition>

Image

We can then run the table and generate the QlikView load script in the usual way.

A Note About Currency (Money Fields)

Please note, as per the docs:

Fields of type Money are returned in micro currency units (micros), e.g.: $1.23 will come back as 1230000 (1.23 x 1,000,000). Micro amounts always refer to the account's local currency.

When filtering on money fields, you will have to provide the value in micros. For example, WHERE AverageCpc > 1000000 will return rows where the AverageCpc is greater than $1 (one unit of the account currency).


Looking up Country Location Names

If you are using the CountryCriteriaId field in the Geo Performance Report, you should be able to use this page to generate a CSV file allowing you to map between the numeric IDs of the location and their string names.

Troubleshooting

This page highlights some important reporting concepts, some of which may be considered non-intuitive.

Timeouts

As noted here - The report download request may time out on extremely large data sets. There is no explicit data size limit; however, due to a variety of factors, the server may return an error if the report is too large.

If you encounter time outs or errors, try a shorter date range or use predicates to break up the report request into multiple, smaller requests. For example, instead of running a single report for all campaigns, you could submit multiple requests that each filter for a subset of Campaign IDs.

Change Log

1.1.6 - 11/03/16
  • Added ManagedCustomers table.

1.1.5 - 08/03/16
  • Added Customers table.
  • Added API Call logging to the ReportDefinition table.

1.1.4 - 07/02/16
  • Minor internal refactoring.

1.1.3 - 03/02/16
  • Upgraded to v201601 objectf the API. See https://developers.google.com/adwords/api/docs/reference/#v201601 for notes on relevant updates to reporting.

1.1.2 - 16/12/15
  • Added extra note to Report tables about looking up territory names from numerical IDs.

1.1.1 - 07/12/15
  • Minor refactoring of web exception handling.

1.1.0 - 12/10/15
  • Upgraded to v201509 of the AdWords API, please see https://developers.google.com/adwords/api/docs/reference/#v201509 and http://googleadsdeveloper.blogspot.co.uk/2015/10/announcing-v201509-of-adwords-api.html for any items which may affect your report queries.

1.0.9 - 13/07/15
  • Upgraded to v201506 of API (https://developers.google.com/adwords/api/docs/reference/#v201506), please read Reporting section of migration guide (https://developers.google.com/adwords/api/docs/guides/migration/v201506) for any items which may affect your report queries.
  • Developer token input removed (our own token is now embedded in the connector).

1.0.8 - 05/06/15
  • Added new ReportDefinition table.

1.0.7 - 01/06/15
  • Upgraded to v201502 of API (https://developers.google.com/adwords/api/docs/reference/#v201502).
  • AdHocReportFromXML now ensures XML is present.
  • Timeout when running reports increased from 3 minutes to 30 minutes.
  • Upgraded to new OAuth2 framework.

1.0.6 - 13/11/14
  • Upgraded to v201409 of API.
  • 'Exclude Report Header' and 'Exclude Report Summary' checkboxes added (as supported by latest version of API).

1.0.5 - 22/09/14
  • XML Report Definition now validated to be correct XML before being accepted as input parameter.

1.0.4 - 21/07/14
  • Upgraded to latest API version (v201406) - https://developers.google.com/adwords/api/docs/reference/#v201406
  • Now uses new OAuth scope.

1.0.3 - 02/04/14
  • Added notes to both report tables.

1.0.2 - 10/03/14
  • Upgraded to API version v201402. This is two increments in version so please check the Report related updates here https://developers.google.com/adwords/api/docs/reference/#v201309 and here https://developers.google.com/adwords/api/docs/reference/#v201402.
  • Minor tweak to authentication code.
  • AWQL input now multi line.

1.0.1 - 30/08/13
  • Developer token no longer embedded in load script.

1.0.0 - 30/08/13
  • First version of V2 of this connector.


(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