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 the procedure creates a new schema file, which exposes the content as though it were any other queryable table.

Set the Location connection property to a preferred path. A folder (bearing the name of the schema this procedure is run from) will be created inside the Location, if it doesn't exist already. The schema files will be placed in that child folder. If the aforementioned child folder already exists inside the Location, the schema files will be placed there.

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 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".

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