Advanced QueriesCData JDBC Driver for Google Analytics 2019 - Build 19.0.7354
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 driver, 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 driver 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 driver and follow the steps below:
- Add an attr tag in the <rsb:info> section to add a column.
- Add the following attributes. Any of the existing fields can serve as an example.
Attribute Name Attribute Value name Set this to a dimension or metric as defined in the API documentation. xs:type Set this to the data type. other:dimension If you want to define a column for a dimension, set this to "true". other:metric If you want to define a column for a metric, set this to "true".
- To use the new files, set the Location connection property to the folder containing the script files.