When you want to customize the way you view your Metrics and Dimensions from Google Analytics, you can choose to create a custom schema/View as opposed to one of the standard Views in Google Analytics.
To create a custom View, you can execute the Stored Procedure CreateCustomSchema, with details documented here: https://cdn.cdata.com/help/DAJ/odbc/pg_GoogleAnalytics4sp-createcustomschema.htm
The general structure of the SQL statement should resemble:
EXEC CreateCustomSchema @TableName=’<the name of your View>’, @Dimensions=’<dimensions separated by commas>’, @Metrics=’<at least one metric, separated by commas>’
With additional parameters detailed in the documentation.
Certain editions of the Google Analytics Connector offer some streamlined ways to use this functionality, not requiring an explicit EXEC statement.
For instance, if you are using the CData Google Analytics ODBC Driver or Power BI Connector, you can instead use the “Define New Table” option. You can find this option in the bottom left corner of the Data Model > Object Explorer section of the ODBC DSN Configuration Window.
You must click on the name of your Schema (i.e. UniversalAnalytics or GoogleAnalytics4) in order to activate this option.
If you are using SQL Gateway, be sure to exit and reopen SQL Gateway for the schema changes to take effect.
Similarly, when using the Excel Add-In, the Edit Query window contains an option for creating a custom schema. In the Edit Query interface, click the “Select...” option to the right of the “Table or View” box. Then, in the dialog box that opens, click “New Table...” to define your custom schema.
The custom schema files, once created, are recorded as RSD files on your machine. The location of these files is specified in the Location connection property, which can be modified by the user.