ADO.NET Provider for Google Analytics

Build 20.0.7654

Advanced Queries

Google Analytics has a very large number of metrics and dimensions that would clutter table definitions, so the table definitions included with the product only list the most commonly used combinations. We offer two alternatives to this design choice: You can use the Dimensions and Metrics columns to request fields that are not in the default table, or you can define your own table.

Using the Dimensions and Metrics Columns

To request additional dimensions or metrics for any existing table, the recommended approach is to define custom schemas; however, you can also set the Dimensions and Metrics inputs in the WHERE clause. Both inputs take a comma-separated list so that you can specify multiple fields at once. The values will be returned in the corresponding Dimensions and Metrics column in the same order that you submitted them. For example, the following query will query the Traffic table for Sessions, the Goal 1 Conversion Rate, and Goal 1 Completions and group these metrics together by the User Age Bracket dimension:

SELECT Sessions, Dimensions, Metrics FROM Traffic WHERE Dimensions='UserAgeBracket' AND Metrics='Goal1ConversionRate,Goal1Completions'
In the results from the query above, the value for UserAgeBracket will be returned in the Dimensions field for each row. The Metrics field will contain a comma-separated value containing the requested metrics for Goal 1.

Defining Custom Schemas

If you routinely need to request fields that are not available on the standard tables that ship with the provider, you may want to define your own custom schema so that you can more easily query for the data you need. The CreateCustomSchema stored procedure can be used to define entirely new tables, or you can modify existing schemas to add the columns you need.

The stored procedure outputs schema files, which have a simple format that makes them easy to edit directly.

Using the CreateCustomSchema Stored Procedure

The provider also offers the CreateCustomSchema stored procedure for creating new table definitions. The stored procedure takes a table name, a comma-separated list of metrics, a comma-separated list of dimensions, and an output folder as inputs. Calling it will create a new schema file that you can query like any other table. You will need to set the Location connection property to the folder containing the new script files in order to access them.

The example stored procedure call below persists the columns of the SELECT query in Using the Dimensions and Metrics Columns:

EXEC CreateCustomSchema TableName='Traffic', Dimensions='UserAgeBracket', Metrics='Sessions,Goal1ConversionRate,Goal1Completions',OutputFolder='C:\Users\Administrator\Desktop'

Edit an Existing Schema Manually

To add fields to an existing schema, open the corresponding .rsd file in the installation directory for the provider and follow the steps below:

  1. Add an attr tag in the <rsb:info> section to add a column.
  2. Add the following attributes. Any of the existing fields can serve as an example.

    Attribute NameAttribute Value
    nameSet this to a dimension or metric as defined in the API documentation.
    xs:typeSet this to the data type.
    other:dimensionIf you want to define a column for a dimension, set this to "true".
    other:metricIf you want to define a column for a metric, set this to "true".

  3. To use the new files, set the Location connection property to the folder containing the script files.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7654