Native Google Analytics Data Connector

The Google Analytics Data Connector is used to read data from the Google Analytics Data API and make it available in the CData Virtuality Server in the relational form. In this way, you can query Google Analytics Data via SQL and write complex queries that are directed to multiple different data sources. For example, a single SQL query can join tables from MySQL and combine them with data coming from Google Analytics Data.

Connector Configuration

Metadata

Before issuing the SQL queries to the Google Analytics Data, we need to configure a connection using the SYSADMIN.createConnection() procedure.

The connection can be created in several ways:

via client email and private key:

CODE

CALL SYSADMIN.createConnection("name" => 'analytics_data', "jbossCLITemplateName" => 'ga4', "connectionOrResourceAdapterProperties" =>  'authType="service_account",privateKeyId=<private key Id>,privateKey=<private key>,clientEmail=<client email>,clientId=<client id>,timeout=100') ;; 
CALL SYSADMIN.createDataSource("name" => 'analytics_data', "translator" => 'ga4', "modelProperties" => '', "translatorProperties" => '', "encryptedModelProperties" => '', "encryptedTranslatorProperties" => '') ;;

via OAuth2 providing a refreshToken:

CODE

CALL SYSADMIN.createConnection("name" => 'analytics_data', "jbossCLITemplateName" => 'ga4', "connectionOrResourceAdapterProperties" =>  'authType="authorized_user",clientSecret=<client secret>,refreshToken=<refresh token>,clientId=<client Id>,timeout=100') ;; 
CALL SYSADMIN.createDataSource("name" => 'analytics_data', "translator" => 'ga4', "modelProperties" => '', "translatorProperties" => '', "encryptedModelProperties" => '', "encryptedTranslatorProperties" => '') ;;

via OAuth2 providing an authCode and redirectUri:

CODE

CALL SYSADMIN.createConnection("name" => 'analytics_data', "jbossCLITemplateName" => 'ga4', "connectionOrResourceAdapterProperties" =>  'authType="authorized_user",clientSecret=<client secret>,clientId=<client Id>,authCode=<auth code>,redirectUri=<redirect URI>,timeout=100') ;; 
CALL SYSADMIN.createDataSource("name" => 'analytics_data', "translator" => 'ga4', "modelProperties" => '', "translatorProperties" => '', "encryptedModelProperties" => '', "encryptedTranslatorProperties" => '') ;;

Usage

The Google Analytics Data data is represented as a relational table account and procedures listProperties, getMetricsDimensions and runReport in the schema named as the alias name given in createDatasource().

SELECT Query

To query these tables, a normal SQL select query like the following can be used:

CODE

SELECT * FROM analytics_data.accounts

Examples

CODE

SELECT 
"name", "display_name", "region_code", "create_time", "update_time", "deleted"
FROM "analytics_data.accounts";

To get properties, use the listProperties() stored procedure. Use parent:accounts/ with an account name from accounts table in filter parameter:

CODE

CALL "analytics_data.listProperties"(
    "filter" => 'parent:accounts/38353695',
    "showDeleted" => true
);;

To get metrics and dimensions, use the getMetricsDimensions stored procedure

To get the default metrics and dimensions, use 0 value for the propertyId parameter:

CODE

CALL "ga4_ds.getMetricsDimensions"(
    "propertyId" => 0
);;

To get the default and custom metrics and dimensions, use the appropriate propertyId returned by the listProperties() stored procedure:

CODE

CALL "ga4_ds.getMetricsDimensions"(
    "propertyId" => 327177138
);;

runReport Procedure

To query actual data from an Analytics account, you can use the runReport stored procedure

runReport with default metrics and dimensions:

CODE

SELECT city,linkUrl,activeUsers,checkouts FROM "analytics_data.runReport"(
    "propertyId" => 327177138,
    "startDate" => '2020-08-19',
    "endDate" => '2023-08-19',
    "metrics" => 'activeUsers,checkouts',
    "dimensions" => 'city,linkUrl',
    "orderBy" => '[{dimension=city,CASE_INSENSITIVE_ALPHANUMERIC},{metric=activeUsers}]',
    "descOrder" => true,
    "dimensionsFilter" => '[{city,BEGINS_WITH,L},{city,ENDS_WITH,s},{linkUrl,CONTAINS,linkedin}]',
    "dimensionsExcludeFilter" => '[{city=Paris,Berlin}]',
    "metricsFilter" => 'activeUsers>=2,activeUsers<3000',
    "currencyCode" => 'USD',
    "keepEmptyRows" => true,
    "offset" => 0,
    "maxResults" => 5000
);;

runReport with default and custom metrics and dimensions:

CODE

SELECT
	city,
	activeUsers,
	CAST(ARRAY_GET(customDimensions, 1) as string) AS "customEvent:element_type",
	CAST(ARRAY_GET(customDimensions, 2) as string) AS "customEvent:element_id",
	CAST(ARRAY_GET(customMetrics, 1) as integer) AS "conversions:purchase",
	CAST(ARRAY_GET(customMetrics, 2) as integer) AS "conversions:generate_lead",
	CAST(ARRAY_GET(customMetrics, 3) as double) AS "sessionConversionRate:generate_lead",
	CAST(ARRAY_GET(customMetrics, 4) as double) AS "sessionConversionRate:purchase",
	CAST(ARRAY_GET(customMetrics, 5) as double) AS "userConversionRate:generate_lead",
	CAST(ARRAY_GET(customMetrics, 6) as double) AS "userConversionRate:purchase"
FROM "analytics_data.runReport"(
    "propertyId" => 327177138,
    "startDate" => '2010-08-19',
    "endDate" => '2023-08-19',
    "metrics" => 'activeUsers, conversions:purchase, conversions:generate_lead, sessionConversionRate:generate_lead, sessionConversionRate:purchase, userConversionRate:generate_lead, userConversionRate:purchase',
    "dimensions" => 'city, customEvent:element_type, customEvent:element_id',
    "dimensionsFilter" => '[{customEvent:element_type,CONTAINS,o}]',
    "dimensionsExcludeFilter" => '[{customEvent:element_type,ENDS_WITH,sales}]',
    "metricsFilter" => 'sessionConversionRate:generate_lead>0',
    "keepEmptyRows" => false,
    "offset" => 0,
    "maxResults" => 10000
);;

Here is the full list and meaning of the procedure parameters:

Parameter	Description
`propertyId`	Property ID. Available propertyIds can be retrieved by `listProperties` stored procedure
`startDate`	Start date to query data from
`endDate`	End date to query data to
`dateRanges`	Date range. You can specify either one date period using the `startDate` and `endDate` parameters, or several date periods using the `dateRanges` parameter. Format: `[{yyyy-MM-dd,yyyy-MM-dd},{...,...},...]`
`metrics`	Comma-separated list of requested metrics (specified without the 'ga:' prefix)
`dimensions`	Comma-separated list of requested dimensions; may be null or skipped
`orderBy`	Comma-separated list of dimensions or metrics that determine the sort order for Analytics data; may be null or skipped. Format: ^{[{dimension/metric=<value>},{...},..]}. The second parameter can be provided for dimension: _ALPHANUMERIC _{CASE_INSENSITIVE_ALPHANUMERIC} `NUMERIC`
`descOrder`	Boolean descending order. Data is sorted in descending order if the parameter is set to `TRUE` and in ascending order if set to `FALSE` or skipped
`dimensionsFilter`	Comma-separated list of dimension filters; may be null or skipped. Formats: `{city=London,Paris,...} {name=values}` `{city,BEGINS_WITH,Lon} {name,matchType,value}` Match types: `EXACT` `BEGINS_WITH` `ENDS_WITH` `CONTAINS` `FULL_REGEXP` `PARTIAL_REGEXP`
`dimensionsExcludeFilter`	Comma-separated list of exclude dimension filters; may be null or skipped. Format: `[{city=London,Paris},{...},...] [{name=excluded values}, {...}]`
`metricsFilter`	Comma-separated expressions with `> < = <= >=`; may be null or skipped
`currencyCode`	Currency code; may be null or skipped
`keepEmptyRows`	Boolean. Shows empty rows if set to `TRUE`; may be null or skipped
`offset`	Offset of record to start returning data from; may be null or skipped
`maxResults`	Maximum results to return for the request; may be null or skipped

Formally the procedure returns all possible columns (for all possible default metrics and dimensions and 2 more columns - customMetrics and customDimensions. These 2 columns contain arrays of values for custom metrics and dimensions), but for all columns that are not passed with the metrics and dimensions parameters, it returns null. To cut off such redundant columns, use the appropriate projection in the SELECT clause.