Important: To see the latest version of the document click here

Denodo Templates for Google Analytics - Quick Use Guide

Last modified on: 31 Jul 2023

Overview

Google Analytics is an analytics service that enables you to measure traffic and engagement across your websites and apps. It is a new kind of property with different reports than what you're used to seeing in Universal Analytics properties:

Collects both website and app data to better understand the customer journey

Uses event-based data instead of session-based
Includes privacy controls such as cookieless measurement, and behavioral and conversion modeling
Predictive capabilities offer guidance without complex models
Direct integrations to media platforms help drive actions on your website or app

You can configure Denodo to retrieve data from Google Analytics by creating JSON data sources and base views in Denodo using the Google Analytics Data API.

As Google Analytics manages a very large amount of data and views, it may be hard work to add all the required data sources and base views in Denodo. In order to make this process easier and faster, we distribute templates: a set of predefined VQL and properties files. You just have to configure a few parameters in the properties file and import these files into your Denodo server to get access to Google Analytics.

These Denodo Templates for Google Analytics are adapted to Google Analytics 4.

The Google Analytics templates

The Google Analytics templates consist of three VQL scripts which contain the predefined Google Analytics reports.

google_analytics_ga4_common_templates_denodo.vql. This script contains common templates with predefined custom reports. It is mandatory to import it. The scripts before depend on it.
google_analytics_ga4_migration_templates_denodo.vql. This script contains some templates for migration from Google Universal Analytics to Google Analytics 4.

google_analytics_ga4_templates_denodo.vql. This script contains reports adapted to the new features of Google Analytics 4.

Please note: importing these scripts will create a database called analytics4 in your Virtual DataPort installation, and will replace any existing database previously existing with that exact name.

Getting OAuth 2.0 credentials

In order to get the Google Analytics credentials to configure the Google Analytics templates, you must follow these steps:

Create a project in the Google Cloud Console.
Enabling Google Analytics Data API v1.
Filling the OAuth consent
Create OAuth 2.0 credentials.

Create a project in the Google Cloud console

Enable APIs

Enable Google Analytics Data API

OAuth 2.0 consent screen 1

OAuth 2.0 consent screen 2

OAuth 2.0 consent screen 3

Create credentials

Create OAuth client id

OAuth 2.0 credentials created

Once you have the OAuth 2.0 credentials generated, you can use these credentials to get the access token and the refresh token through the OAuth 2.0 wizard of the VDP.

OAuth 2.0 credentials wizard option in VDP

OAuth 2.0 credentials wizard

You have to add three scopes: https://www.googleapis.com/auth/analytics.edit, https://www.googleapis.com/auth/analytics and https://www.googleapis.com/auth/analytics.readonly.

Importing artifacts

In order to make the final views data more readable to the final user, the Google Analytics Templates make use of the pivotregister function. Because of this, before importing the VQL into Denodo, you have to download the Denodo Xtrafuncs for VDP DenodoConnect component from the Denodo support site.

You must add at least the denodo-xtrafuncs-vdp-pivot-{version}-jar-with-dependencies.jar extension, included in the dist folder of the downloaded component, using the Extension management option of the Virtual Data Port Admin Tool. The minimum version of the XtraFuncs needed is 20201209.

Before importing the VQLs in Denodo, you have to set the following parameters defined in the google_analytics_templates_denodo.properties file:

client_id: the client id generated in the Google Cloud Console in the previous step.

client_secret: the client secret generated in the Google Cloud Console in the previous step.

access_token:: provided by VDP Wizard for OAuth 2.0.

refresh_token: provided by VDP Wizard for OAuth 2.0.

property_id: A Google Analytics GA4 property identifier whose events are tracked. You must import as many google_analytics_templates_denodo.vql scripts as properties you have defined in your Google Analytics tool to get data of all of them. Each google_analytics_templates_denodo.properties will have a different property_id parameter value.

view_name_prefix: you can configure the name prefix of every view and folder created in the VDP in order to be able to relate VDP views to Google Analytics properties. The max length of this parameter is 10 characters.

redirect_uri: the OAuth 2.0 redirect uri configured.

The google_analytics_ga4_templates_denodo.vql contains predefined reports with configurable dimensions. For this it is necessary to complete the following properties:

traffic_dimension: A valid GA4 dimension related to Lifecycle Traffic Acquisition report. Default value is sessionSource. Other possible values are: sessionMedium, sessionSourceMedium, sourcePlatform, sessionSourcePlatform, sessionCampaignName, sessionCampaignId, sessionDefaultChannelGroup.

user_acquisition_dimension: A valid GA4 dimension related to Lifecycle User acquisition report. Default value is firstUserMedium. Other possible values are: firstUserMedium, firstUserSource, firstUserSourceMedium, firstUserSourcePlatform, firstUserCampaignId, firstUserCampaignName, firstUserDefaultChannelGroup, firstUserGoogleAdsAdGroupName, firstUserGoogleAdsAdNetworkType.

event_dimension: A valid GA4 dimension related to Lifecycle Engagement Events report. Default value is eventName.

conversion_dimension: A valid GA4 dimension related to Lifecycle Engagement Conversions report. Default value is eventName.

pages_screens_dimension: A valid GA4 dimension related to Lifecycle Engagement Pages and screens report. Default value is unifiedScreenClass. Other possible values are: unifiedPagePathScreen, unifiedScreenName, contentGroup.

page_dimension: A valid GA4 dimension related to Lifecycle Engagement Landing page report. Default value is landingPage.

user_dimension: A valid GA4 dimension related to User Demographics details report. Default value is country. Other possible values are: city, region, language, interests, userAgeBracket, userGender.

tech_dimension: A valid GA4 dimension related to User Tech details report. Default value is deviceCategory. Other possible values are: browser, operatingSystem deviceModel, operatingSystemVersion, operatingSystemWithVersion, platform, platformDeviceCategory, screenResolution.

You will need to import a google_analytics_ga4_templates_denodo.vql script for each new dimension you want to report on. The following properties will allow you to configure the name prefix of every view created in order to be able to relate the VDP views with the GA4 dimension:

traffic_view_prefix: the name prefix of the Lifecycle Traffic acquisition report view. The max length of this parameter is 10 characters.

user_acquisition_view_prefix: the name prefix of the Lifecycle User acquisition report view. The max length of this parameter is 10 characters.

event_view_prefix: the name prefix of the Lifecycle Engagement Events report view. The max length of this parameter is 10 characters.

conversion_view_prefix: the name prefix of the Lifecycle Engagement Conversions report view. The max length of this parameter is 10 characters.

pages_screens_view_prefix: the name prefix of the Lifecycle Engagement Pages and screens report view. The max length of this parameter is 10 characters

page_view_prefix: the name prefix of the Lifecycle Engagement Landing page report view. The max length of this parameter is 10 characters

user_view_prefix: the name prefix of the User Demographics details report view. The max length of this parameter is 10 characters.

tech_view_prefix: the name prefix of the User Tech details report view. The max length of this parameter is 10 characters.

Configuration properties

Once you have set the properties file, first you have to import both the properties and the google_analytics_ga4_common_templates_denodo.vql file using the Import option of the VDP Administration Tool:

Import option in VDP

Import wizard in VDP

Now, you can import the same properties again and the google_analytics_ga4_templates_denodo.vql script or google_analytics_ga4_migration_templates_denodo.vql or both. These two scripts are independent of each other.

Import wizard in VDP

Refresh

After the importation finishes, refresh the Server Explorer by selecting File > Refresh and you will be able to see the data sources and views for accessing to Google Analytics:

Google Analytics common data sources and views

Google Analytics new GA4 views

Google Analytics GA4 migration views

Google Analytics templates

The Google Analytics Data API v1 gives programmatic access to Google Analytics 4 (GA4) report data through several methods.

The VQL configures access to some predefined Google Analytics reports and allows users to create base Google Analytics reports with the metric and the dimension they prefer in order to create customized reports tailored to their own specifications.

Note: We have used “View1” as the value of the view_name_prefix configuration parameter in the examples shown below.

GA4 common templates

The imported google_analytics_ga4_common_templates_denodo.vql script includes views with custom Google Analytics reports and real time reports. The data source defined in this script is necessary for the other two template scripts.

Real time reports

${view_name_prefix}_ga4_real_time: Returns a customized report of realtime event data for your property. Events appear in realtime reports seconds after they have been sent to Google Analytics. Realtime reports show events and usage data for the periods of time ranging from the present moment to 30 minutes ago (up to 60 minutes for Google Analytics 360 properties). Required parameters are:

dimensions : The dimension requested for your report.
metrics: the metric requested for your report.

Query with real time view

Now, you can execute queries over the Google Analytics views that have been created. In this example, we are going to create a real time report to see the activeUsers by city:

Real time query

Real time query result

Custom reports

${view_name_prefix}_ga4_custom_report: Returns a customized report of your Google Analytics event data. Reports contain statistics derived from data collected by the Google Analytics tracking code. The data returned from the API is as a table with columns for the requested dimensions and metrics. Required parameters are:

dimensions: a valid Google Analytics dimension.
metrics: a valid Google Analytics metric.
startdate: start date for fetching Google Analytics data.
enddate: end date for fetching Google Analytics data.

Query with custom report view

Now, you can execute queries on the Google Analytics views that have been created. In this example, we are going to create a custom report to see the sessions by browser between 2022-12-01 and 2022-12-31:

Custom report query

Custom report query results

Custom pivot reports

${view_name_prefix}_ga4_custom_pivot_report: Returns a customized pivot report of your Google Analytics event data. In a pivot report, dimensions are only visible if they are included in a pivot. Required parameters are:

dimensions: a valid Google Analytics dimension.
metrics: a valid Google Analytics metric.
startdate: start date for fetching Google Analytics data.
enddate: end date for fetching Google Analytics data.
limit_0:The number of unique combinations of dimension values to return in this pivot.
dimension_pivot1: Dimension name for visible columns in the report response.
dimension_pivot2: Dimension name for visible columns in the report response.

Query with custom pivot report view

Now, you can execute queries on the Google Analytics views that have been created. In this example, we are going to create a custom pivot report to see the sessions by deviceCategory and date between 2022-12-01 and 2022-12-31:

Custom pivot report query

Custom pivot report query results

GA4 migration templates

Predefined reports

The imported google_analytics_ga4_migration_templates_denodo.vql script includes views with custom Google Analytics reports. These views are a migration from Google Universal Analytics to Google Analytics 4.

All of them have two required parameters: startdate and enddate:

${view_name_prefix}_ga4_browser: this report uses the following parameters:

dimension: browser.
metrics: averageRevenuePerUser. bounceRate, engagementRate, conversions, sessionConversionRate, totalUsers, averageSessionDuration, userEngagementDuration, totalRevenue.

${view_name_prefix}_ga4_device_category: this report uses the following parameters:

dimension: device_category.
metrics: sessions, bounceRate, totalUsers, averageSessionDuration, screenPageViewsPerSession, eventCount, userEngagementDuration, averageRevenuePerUser, totalRevenue .

${view_name_prefix}_ga4_event_name: this report uses the following parameters:

dimension: event_name.
metrics: eventCount, sessions, eventsPerSession, averageSessionDuration, screenPageViewsPerSession, bounceRate, totalUsers.

${view_name_prefix}_ga4_first_user_campaign: this report uses the following parameters:

dimension: firstUserCampaign.
metrics: sessions, bounceRate, totalUsers, averageSessionDuration, screenPageViewsPerSession, eventCount, userEngagementDuration, averageRevenuePerUser, totalRevenue.

${view_name_prefix}_ga4_first_user_medium: this report uses the following parameters:

dimension: firstUserMedium.
metrics: sessions, bounceRate, totalUsers, averageSessionDuration, screenPageViewsPerSession, eventCount, userEngagementDuration, averageRevenuePerUser, totalRevenue.

${view_name_prefix}_ga4_first_user_source: this report uses the following parameters:

dimension: firstUserSource.
metrics: averageRevenuePerUser, bounceRate, engagementRate, screenPageViews, sessionConversionRate, totalUsers, averageSessionDuration, userEngagementDuration, totalRevenue.

${view_name_prefix}_ga4_first_user_source_medium: this report uses the following parameters:

dimension: firstUserSourceMedium.
metrics: averageRevenuePerUser, bounceRate, engagementRate, screenPageViews, sessionConversionRate, totalUsers, averageSessionDuration, userEngagementDuration, totalRevenue.

${view_name_prefix}_ga4_landing_page: this report uses the following parameters:

dimension: landingPage.
metrics: averageSessionDuration, screenPageViewsPerSession, sessions, userEngagementDuration, averageRevenuePerUser, totalUsers, totalRevenue, eventCount, bounceRate.

${view_name_prefix}_ga4_page_path: this report uses the following parameters:

dimension: pagePath.
metrics: averageRevenuePerUser, bounceRate, engagementRate, screenPageViews, sessionConversionRate, totalUsers, averageSessionDuration, userEngagementDuration, totalRevenue.

${view_name_prefix}_ga4_session_campaign_id: this report uses the following parameters:

dimension: sessionCampaignId.
metrics: averageRevenuePerUser, bounceRate, engagementRate, eventCount, engagedSessions, totalUsers, averageSessionDuration, userEngagementDuration, totalRevenue.

Query with predefined reporting views

Now, you can execute the predefined Google Analytics reports. In the example below the ${view_name_prefix}_ga4_event_name view has been executed.

Analytics eventName report query

Analytics eventName report query results

GA4 new features templates

The imported google_analytics_ga4_templates_denodo.vql script includes views which return predefined Google Analytics reports adapted to the new features of Google Analytics 4. These views have fixed metrics while the dimension can be configurable in the configuration.properties file.

All of them have two required parameters: startdate and enddate:

${traffic_view_prefix}_ga4_traffic: returns the predefined Lifecycle Traffic Acquisition report with the following parameters:

metrics: newUsers, engagedSessions, engagementRate, sessionsPerUser, averageSessionDuration, eventCount, conversions, totalRevenue.
dimension: configurable with the traffic_dimension parameter. Default value is sessionSource.

${user_acquisition_view_prefix}_ga4_user_ac: returns the predefined Lifecycle User Acquisition report with the following parameters:

metrics: newUsers, engagedSessions, engagementRate, sessionsPerUser, averageSessionDuration, eventCount, conversions, totalRevenue.
dimension: configurable with the user_acquisition_dimension parameter. Default value is firstUserMedium.

${user_view_prefix}_ga4_users: returns the predefined User Demographics details report with the following parameters:

metrics: activeUsers, newUsers, engagedSessions, sessionsPerUser, userEngagementDuration, eventCount, conversions, totalRevenue.
dimension: configurable with the user_dimension parameter. Default value is country.

${conversion_view_prefix}_ga4_conversion: returns the predefined Lifecycle Engagement Conversions report with the following parameters:

metrics: conversions, totalUsers, totalRevenue.
dimension: configurable with the event_dimension parameter. Default value is eventName.

${event_view_prefix}_ga4_events: returns the predefined Lifecycle Engagement Event report with the following parameters:

metrics: eventCount, totalUsers, eventCountPerUser, totalRevenue
dimension: configurable with the event_dimension parameter. Default value is eventName.

${page_view_prefix}_ga4_pages: returns the predefined Lifecycle Engagement Landing page report with the following parameters:

metrics: sessions, activeUsers, newUsers, averageSessionDuration, conversions, totalRevenue.
dimensions: configurable with the page_dimension parameter. Default value is landingPage.

${pages_screens_view_prefix}_ga4_screens: returns the predefined Lifecycle Engagement Pages and screens report with the following parameters:

metrics: screenPageViewsPerSession, activeUsers, screenPageViewsPerUser, averageSessionDuration, eventCount,conversions, totalRevenue.
dimension: configurable with the pages_screens_dimension parameter. Default value is unifiedScreenClass.

${tech_view_prefix}_ga4_tech: returns the predefined User Tech details report with the following parameters:

metrics: activeUsers, newUsers, engagedSessions, engagementRate, sessionsPerUser, userEngagementDuration, eventCount, conversions, totalRevenue.
dimension: configurable with the tech_dimension parameter. Default value is deviceCategory.

Query with GA4 dimension configurable reporting views

Newly imported predefined reports can now be executed. In the example below the ${traffic_view_prefix}_ga4_traffic view has been executed with the following configured parameters value:

traffic_view_prefix=ss
traffic_dimension=sessionSource

Traffic Acquisition report query

Traffic Acquisition report query results

Appendix I. How to use Google Analytics Data API v1

The Google Analytics Data API v1 gives you access to report data in Google Analytics platform.

To use the Analytics Data API v1 to request data, you can construct a RunReportRequest object which has these minimum requirements:

A valid entry in the dateRangesfield.
At least one valid entry in the dimensions field.
At least one valid entry in the metrics field.

Here you can see the complete RunReportRequest object.

{

"dimensions": [

{

object (Dimension)

}

"metrics": [

{

object (Metric)

}

"dateRanges": [

{

object (DateRange)

}

"dimensionFilter": {

object (FilterExpression)

"metricFilter": {

object (FilterExpression)

"offset": string,

"limit": string,

"metricAggregations": [

enum (MetricAggregation)

"orderBys": [

{

object (OrderBy)

}

"currencyCode": string,

"cohortSpec": {

object (CohortSpec)

"keepEmptyRows": boolean,

"returnPropertyQuota": boolean

}

Example. Create a report showing eventCount and sessions in a Safari browser in VDP

First, you have to create a new JSON datasource using the VDP menu right option and set HTTP Client as data route.

Create JSON data source option in VDP

JSON data source configuration

Finally, you have to configure the data source authentication. You must use the OAuth 2.0 credentials created in your Google Cloud Console.

Requires one of the following OAuth scopes:

JSON data source authentication

JSON data source OAuth 2.0 credentials wizard

Once the JSON data source is created, it is time to create the base view to get the Google Analytics report data. Use the Create base view button for this.

Create base view option

Use properties/YOUR_PROPERTY_ID:runReport as Relative URL in the base view configuration wizard and use the following json object as the Post body (you can customize it as you want):

{

"dateRanges": [

{"endDate": "2022-12-31", "startDate": "2022-12-01"}

"metrics": [

{"name": "sessions"},

{"name": "eventCount"}

"dimensions": [

{ "name": "browser" }

"dimensionFilter": {

"filter": {

"fieldName": "browser",

"stringFilter": {

"value": "Safari"

}

Now, you can execute the created base view.

Base view query

Base view query results

The response body of the API request is an instance of a RunReportResponse object. The structure is defined in the dimensionheaders and metricheaders objects which describe the dimensions and metrics and their data types in the report. The values of the dimensions and metrics are specified in the rows field.

This is the JSON response for the sample report created above:

{

"dimensionHeaders": [

{

"name": "browser"

}

"metricHeaders": [

{

"name": "sessions",

"type": "TYPE_INTEGER"

{

"name": "eventCount",

"type": "TYPE_INTEGER"

}

"rows": [

{

"dimensionValues": [

{