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 are divided in two different script files:
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.
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
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.
The google_analytics_administration_templates_denodo.vql and the properties file, configure access to the following administration entities of Google Analytics:
Before importing the VQL in Denodo, you have to set the following parameters defined in the google_analytics_templates_denodo.properties file:
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
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
Now, you can execute queries over the Google Analytics views that have been created:
Custom dimensions query
Custom dimensions query results
Google Analytics provides three APIs to allow users to access to report data:
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.
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.
Real time data source and views
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 data source and views
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 data sources and views
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:
Now, you can execute the predefined Analytics reports:
Analytics campaign report query
Analytics campaign report query results
The Google Analytics Reporting API v4 gives you access to the power of the Google Analytics platform. The API provides these key features:
To use the Analytics Reporting API v4 to request data, you must construct a ReportRequest object, which has these minimum requirements:
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.
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
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 :
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