• User Manuals /
  • Denodo Templates for Google Analytics - Quick Use Guide

Denodo Templates for Google Analytics - Quick Use Guide

Last modified on: 05 Oct 2020

Download original document


Overview

Google Analytics is a free web analytics tool offered by Google to help you analyze your website traffic.

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

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.

The Google Analytics templates

The Google Analytics templates are divided in two different script files:

  • google_analytics_administration_templates_denodo.vql
  • google_analytics_report_templates_denodo.vql

The first one contains the data sources and base views related to the Google Analytics administration entities. The second contains the predefined Google Analytics reports.

Please note: importing these scripts will create a database called analytics in your Virtual DataPort installation, and will drop 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 API Console.
  • Enabling the Analytics API.
  • Create OAuth 2.0 credentials.

Create a project in the Google API console

Enable APIs

 Enable Google Analytics API

                  Create credentials button

Create credentials step 1

Create credentials step 2

OAuth 2.0 credentials generated

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

Google Analytics Administration templates

The google_analytics_administration_templates_denodo.vql and the properties file, configure access to the following administration entities of Google Analytics:

  • Account Summaries
  • Accounts
  • AdWords Links
  • Custom Data Sources
  • Custom Dimensions
  • Custom Metrics
  • Experiments
  • Filters
  • Goals
  • Profile Filter Links
  • Remarketing Audiences
  • Segments
  • Unsampled Reports
  • Uploads
  • Views
  • Web Properties

Importing artifacts

Before importing the VQL 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 API Console in the previous step.

  • client_secret: the client secret generated in the Google API 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.

  • view_id: the id of the Google Analytics view which you want to get data. You must import as many google_analytics_report_templates_denodo.vql scripts as views you have defined in your Google Analytics tool if you want to get data of all of them. Each google_analytics_report_templates_denodo.properties will have a different view_id parameter value.

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

Note: Both view_id and view_name_prefix configuration parameters are just needed for  google_analytics_report_templates_denodo.vql not for google_analytics_administration_templates_denodo.vql

Configuration properties

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

Import option in VDP

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 Management data sources and views

  • bv_account_summaries: Lists account summaries to which the user has access.

  • bv_accounts: Lists all accounts to which the user has access.

  • bv_adwords_links: Lists webProperty-Google Ads links for a given web property.

  • bv_custom_data_sources: Lists custom data sources to which the user has access.

  • bv_custom_dimensions: Lists custom dimensions to which the user has access.

  • bv_custome_metrics: Lists custom metrics to which the user has access.

  • bv_experiments: Lists experiments to which the user has access.

  • bv_filters: Lists all filters for an account.

  • bv_goals: Lists goals to which the user has access.

  • bv_profile_filter_links: Lists all profile filter links for a profile.

  • bv_remarketing_audiences: Lists remarketing audiences to which the user has access.

  • bv_segments: Lists segments to which the user has access.

  • bv_unsampled_reports: Lists unsampled reports to which the user has access.

  • bv_uploads: Lists uploads to which the user has access.

  • bv_views: Lists views (profiles) to which the user has access.

  • bv_web_properties: Lists properties to which the user has access.

Query with administration views

Now, you can execute queries over the Google Analytics views that have been created:

        Custom dimensions query

Custom dimensions query results

Google Analytics report templates

Google Analytics provides three APIs to allow users to access to report data:

  • Realtime API: Get user activity occurring on a property right now. Real time reports are updated within seconds.

  • Multi-Channel Funnels API: Get conversion path data which shows user interactions with various traffic sources over multiple sessions prior to converting. Analyze how multiple marketing channels influence conversions.

  • Reporting API: Allow users to access report data in Google Analytics.

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.

Importing artifacts

Before importing the VQL into Denodo, you have to add  the google-analytics-api-storedprocedures-8.0-{version}.jar extension included in the lib folder of the distribution using the Extension management option of the VDP Administration Tool:

    Extension management option

In order to import the google_analytics_report_templates_denodo.vql, you have to follow the same steps explained for importing the google_analytics_administration_templates_denodo.vql.

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

Realtime

     Real time data source and views

  • ${view_name_prefix}_real_time: Returns real-time data for a view (profile). Required parameters are:
  •  metrics: a comma-separated list of Analytics metrics.
  • dimensions: a comma-separated list of real-time dimensions.

Query with Real Time view

Now, you can execute queries over the Google Analytics views that have been created:

        Real time query

Real time query results

Real time query filtering by dimension:

Real time query with filter

Real time query with filter results

Multi-Channel funnels

    Multi-channel funnels data source and views

  • ${view_name_prefix}_multi_channel_funnels: Returns report data. Every report consists of statistics derived from the data that the tracking code sends back to Analytics, organized as dimensions and metrics. Required parameters are:
  • dimensions : a list of comma-separated dimensions for your Multi-Channel Funnels report.
  • metrics: a list of comma-separated metrics.
  • startdate: start date for fetching Analytics data.
  • enddate: end date for fetching Analytics data.

Query with Multi-Channel Funnels view

Now, you can execute queries over the Google Analytics views that have been created:

Multi-channel funnels query

Multi-channel funnels query result 

Multi-channel funnels query filtering by dimension:

Multi-channel funnels query with filter

Multi-channel funnels query with filter results

Central Reporting

    Central reporting data sources and views

  • ${view_name_prefix}_central base: Returns Analytics data. Required parameters are:
  • dimensions: a valid Analytics dimension.
  • metrics: a valid Analytics metric.
  • startdate: start date for fetching Analytics data.
  • enddate: end date for fetching Analytics data.

Query with reporting view

Now, you can execute queries on the Google Analytics views that have been created:

Central reporting query

Central reporting query results

The imported VQL includes views which return predefined Analytics reports. All of them have two required parameters: startdate and enddate:

  • ${view_name_prefix}_campaign: this report uses the following parameters:
  • dimension: ga:campaign.
  • metrics: ga:sessions, ga:bounceRate, ga:users, ga:avgSessionDuration, ga:pageviewsPerSession, ga:avgPageLoadTime, ga:avgPageDownloadTime, ga:organicSearches.

  • ${view_name_prefix}_device_category: this report uses the following parameters:
  • dimension: ga:deviceCategory.
  • metrics: ga:sessions, ga:bounceRate, ga:users, ga:avgSessionDuration, ga:pageviewsPerSession, ga:avgPageLoadTime, ga:avgPageDownloadTime.

  • ${view_name_prefix}_event_action: this report uses the following parameters:
  • dimension: ga:eventAction.
  • metrics: ga:totalEvents, ga:sessions, ga:sessionsWithEvent, ga:avgSessionDuration, ga:pageviewsPerSession, ga:bounceRate, ga:users.

  • ${view_name_prefix}_event_category: this report uses the following parameters:
  • dimension: ga:eventCategory.
  • metrics: ga:totalEvents, ga:sessions, ga:sessionsWithEvent, ga:avgSessionDuration, ga:pageviewsPerSession, ga:bounceRate, ga:users.

  • ${view_name_prefix}_event_label: this report uses the following parameters:
  • dimension: ga:eventLabel.
  • metrics: ga:totalEvents, ga:sessions, ga:sessionsWithEvent, ga:avgSessionDuration, ga:pageviewsPerSession, ga:bounceRate, ga:users.

  • ${view_name_prefix}_landing_page_path: this report uses the following parameters:
  • dimension: ga:landingPagePath.
  • metrics: ga:avgSessionDuration, ga:pageviewsPerSession, ga:uniquePageviews, ga:avgTimeOnPage, ga:entranceRate, ga:exitRate, ga:bounceRate, ga:users, ga:avgPageLoadTime, ga:avgPageDownloadTime.

  • ${view_name_prefix}_medium: this report uses the following parameters:
  • dimension: ga:medium.
  • metrics: ga:sessions, ga:bounceRate, ga:users, ga:avgSessionDuration, ga:pageviewsPerSession, ga:avgPageLoadTime, ga:avgPageDownloadTime, ga:organicSearches.

  • ${view_name_prefix}_page_path: this report uses the following parameters:
  • dimension: ga:pagePath.
  • metrics: ga:avgSessionDuration, ga:pageviewsPerSession, ga:uniquePageviews, ga:avgTimeOnPage, ga:entranceRate, ga:exitRate, ga:bounceRate, ga:users, ga:avgPageLoadTime, ga:avgPageDownloadTime.

  • ${view_name_prefix}_source: this report uses the following parameters:
  • dimension: ga:source.
  • metrics: ga:sessions, ga:bounceRate, ga:users, ga:avgSessionDuration, ga:pageviewsPerSession, ga:avgPageLoadTime, ga:avgPageDownloadTime, ga:organicSearches.

  • ${view_name_prefix}_source_medium: this report uses the following parameters:
  • dimension: ga:sourceMedium.
  • metrics: ga:sessions, ga:bounceRate, ga:users, ga:avgSessionDuration, ga:pageviewsPerSession, ga:avgPageLoadTime, ga:avgPageDownloadTime, ga:organicSearches.

Query with predefined reporting views

Now, you can execute the predefined Analytics reports:

Analytics campaign report query

Analytics campaign report query results

Appendix I. How to use Google Analytics Reporting v4 API

The Google Analytics Reporting API v4 gives you access to the power of the Google Analytics platform. The API provides these key features:

  • Metric expressions:
    The API allows you to request not only built-in metrics but also combination of metrics expressed in mathematical operations. For example, you can use the expression     ga:goal1completions/ga:sessions to request the goal completions per number of sessions.

  • Multiple date ranges:
    The API allows you in a single request to get data in two date ranges.

  • Cohorts and Lifetime value:
    The API has a rich vocabulary to request Cohort and Lifetime value reports.

  • Multiple segments:
    The API enables you to get multiple segments in a single request.

To use the Analytics Reporting API v4 to request data, you must construct a ReportRequest object, which has these minimum requirements:

  • At least one valid entry in the dateRanges field. If a date range is not provided, the default date range is: {"startDate": "7daysAgo", "endDate": "yesterday"}.

  • At least one valid entry in the metrics field.

Here you can see the complete ReportRequest object.

{
 "viewId": string,
 "dateRanges": [
   {
     object(DateRange)
   }
 ],
 "samplingLevel": enum(Sampling),
 "dimensions": [
   {
     object(Dimension)
   }
 ],
 "dimensionFilterClauses": [
   {
     object(DimensionFilterClause)
   }
 ],
 "metrics": [
   {
     object(Metric)
   }
 ],
 "metricFilterClauses": [
   {
     object(MetricFilterClause)
   }
 ],
 "filtersExpression": string,
 "orderBys": [
   {
     object(OrderBy)
   }
 ],
 "segments": [
   {
     object(Segment)
   }
 ],
 "pivots": [
   {
     object(Pivot)
   }
 ],
 "cohortGroup": {
   object(CohortGroup)
 },
 "pageToken": string,
 "pageSize": number,
 "includeEmptyRows": boolean,
 "hideTotals": boolean,
 "hideValueRanges": boolean
}

There can be a maximum of 5 requests. All requests should have the same dateRanges, viewId, segments, samplingLevel, and cohortGroup.

Example. Create a report showing  pagesviews and sessions in a Chrome 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

Now, you have to configure your data source. The data source uses POST method and the URL is https://analyticsreporting.googleapis.com/v4/reports:batchGet.

Use the following json object as the the data source body (you can customize it as you want):

{

  "reportRequests":

  [

    {

      "viewId": "YOUR_VIEW_ID",

      "dateRanges": [

        {"endDate": "2020-09-30", "startDate": "2020-09-01"}

      ],

      "metrics": [

        {"expression": "ga:pageviews"},

        {"expression": "ga:sessions"}

      ],

      "dimensions": [{"name": "ga:browser"}, {"name": "ga:country"}],

      "dimensionFilterClauses": [

        {

          "filters": [

            {

              "dimensionName": "ga:browser",

              "operator": "EXACT",

              "expressions": ["Chrome"]

            }

          ]

        }

      ]

    }

  ]

}

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 API Console.

Use the https://www.googleapis.com/auth/analytics.readonly and https://www.googleapis.com/auth/analytics scopes.

JSON data source authentication

JSON data source OAuth 2.0 credentials wizard

Once the JSON data source is created, 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

Now, you can execute the created base view.

Base view query

Base view query results

The response body of the API request is an array of Report objects. The Report structure is defined in the ColumnHeader object which describes the dimensions and metrics and their data types in the report. The values of the dimensions and metrics are specified in the data field.

Here is a fragment of the JSON response for the sample report created above:

 

           Fragment of the Report JSON result

Appendix II. Create a derived view over Google Analytics views

You can create derived views over ${view_name_prefix}_multi_channel_funnels and ${view_name_prefix}_real_time views to avoid so many null columns in the result table.

Below you can see an example of creating a derived view over ${view_name_prefix}_real_time.  The following parameters value will be used :

  • dimensions = rt:medium;rt:city
  • metrics = rt:activeUsers

First, right click on the ${view_name_prefix}_real_time view and select New -> Selection.

         Create new Selection view option in VDP

You can set the metrics and dimensions parameters value in the Where Conditions section.

Derived view Where Conditions section

As you know you are using only one metric and two dimensions, you can remove the unused columns in the Output section.

Derived view Output section

Finally, you can query your new view:

Derived view query

Derived view query results