Denodo Templates for Adobe Analytics - Quick Use Guide
You can translate the document:
Overview
Adobe Analytics is an analytics service provided by Adobe Systems Incorporated. It enables the owner to monitor and analyze web traffic and can be used to keep track of user behavior. Adobe Analytics APIs offer limitless ways to integrate your most important customer data into key business processes.
You can configure Denodo to retrieve data from Adobe Analytics by creating JSON data sources and base views in Denodo using the Adobe Analytics APIs.
As Adobe Analytics manages a very large amount of data, 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 Adobe Analytics.
The Adobe Analytics templates
The VQL configures access to some predefined Adobe Analytics reports and allows users to create base Adobe Analytics reports with the metric and the dimension they prefer in order to create customized reports tailored to their own specifications.
The adobe_analytics_templates_denodo.vql script file contains the data sources and views with the predefined Adobe Analytics reports.
Please note: importing this script will create a database called adobe 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 Adobe Analytics credentials to configure the Adobe Analytics templates, you must follow these steps:
- Create a project in Adobe Developer Console.
- Add the Analytics API to your project.
- Create OAuth 2.0 credentials.
Create a project in the Adobe Developer Console
Add APIs
Add Adobe Analytics API
Choose API authentication
Select product profile
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
- Token endpoint URL: https://ims-na1.adobelogin.com/ims/token/v2
- Scopes: the following scopes are needed in order the final views work properly:
- Openid
- AdobeID
- read_organizations
- additional_info.projectedProductContext
- additional_info.job_function
- offline_access.
Importing artifacts
In order to make the final views data more readable to the final user, the Adobe 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 Design Studio. The minimum version of the XtraFuncs needed is 20201209.
Extension management option in VDP
Before importing the VQL in Denodo, you have to set the following parameters defined in the adobe_analytics_templates_denodo.properties file:
- client_id: the client id generated in the Adobe Developer Console in the previous step.
- client.secret: the client secret generated in the Adobe Developer Console in the previous step.
- access.token: provided by VDP Wizard for OAuth 2.0.
- company.id: your Adobe company id.
- report.suite.id: the id of the Adobe Analytics report suite which you want to get data. You must import as many adobe_analytics_report_templates_denodo.vql scripts as report suites you have defined in your Adobe Analytics Workspace if you want to get data from all of them. Each adobe_analytics_report_templates_denodo.properties will have a different report.suite.id parameter value.
- report.suite.prefix: you can configure the name prefix of every report suite created in the VDP in order to be able to relate VDP views to Adobe Analytics report suites. The max length of this parameter is 10 characters.
Configuration properties
Once you have set the properties file, you only have to import both the properties and the adobe_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 import operation finishes, refresh the Explorer by selecting File > Refresh and you will be able to see the data sources and views for accessing to Adobe Analytics:
Note: We have used “suite1” as the value of the report.suite.id configuration parameter in the examples shown below.
Campaign Performance
Predefined reports to know which campaigns are driving the most revenue.
Campaign Performance data sources and views
- ${report.suite.prefix}_campaign_tracking_code_report: Returns Analytics data. Required parameter is:
- datarange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_tracking_code_flow_report: Returns Analytics data. Required parameter is:
- datarange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
Query example
Now, you can execute queries on the Adobe Analytics views that have been created:
Querying suite1_campaign_tracking_code_report
suite1_campaign_tracking_code_report query results
Content Consumption
Predefined reports to know which content is being consumed most and is engaging users.
Content Consumption data sources and views
- ${report.suite.prefix}_page_report: Returns Analytics data. Required parameter is:
- daterange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_site_section_report:
- daterange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
Query example
Now, you can execute queries on the Adobe Analytics views that have been created:
Querying suite1_page_report view
suite1_page_report query results
Generic
Base view which lets you make a custom report with the metric and dimension you choose.
Generic data sources and views
- ${report.suite.prefix}_custom_report: Returns Analytics data. Required parameters are:
- daterange: Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- metric1: an available metric of your Adobe report suite.
- dimension: an available dimension of your Adobe report suite.
Query example
Now, you can execute queries on the Adobe Analytics views that have been created:
Querying suite1_custom_report view
suite1_custom_report query results
Metrics and dimensions
In the Metrics and Dimensions folder there are some views to get the list of available metrics and dimensions defined in your Adobe report suite. To make your own reports using the generic template view, you have to use the id field of these views as the parameters metric1 and dimension.
Metrics and dimensions views
- ${report.suite.prefix}_dimensions: Returns available dimensions of your Adobe report suite.
- ${report.suite.prefix}_metrics: Returns available metrics of your Adobe report suite.
Query example
suite1_dimensions query results
suite1_metrics query results
Marketing Ecommerce
Predefined reports to know the effectiveness of your marketing on ecommerce.
Marketing Ecommerce data sources and views
- ${report.suite.prefix}_marketing_channel_report: Returns Analytics data. Required parameters is:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_sales_by_region_report: Returns Analytics data. Required parameters is:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
Query example
Now, you can execute queries on the Adobe Analytics views that have been created:
Querying suite1_page_sales_by_region_report view
suite1_page_sales_by_region query results
People Metrics
Predefined report to know people interacting with your brand.
People Metrics data sources and views
- ${report.suite.prefix}_site_section_people_report: Returns Analytics data. Required parameters is:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
Query example
Now, you can execute queries on the Adobe Analytics views that have been created:
Querying suite1_site_section_people_report view
suite1_site_section_people_report query results
Product Retail
Predefined reports to know which products are performing the best.
Product Retail data sources and views
- ${report.suite.prefix}_product_report: Returns Analytics data. Required parameters are:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
Query example
Now, you can execute queries on the Adobe Analytics views that have been created:
Querying suite1_product_report view
suite1_product_report query results
Web Acquisition
Predefined reports detailing the top traffic drivers.
Web Acquisition data sources and views
- ${report.suite.prefix}_entry_page_report: Returns Analytics data. Required parameters are:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_referrer_report: Returns Analytics data. Required parameters are:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_referring_domain_report: Returns Analytics data. Required parameters are:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_search_engine_report: Returns Analytics data. Required parameters are:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_search_keyword_report: Returns Analytics data. Required parameters are:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_tracking_code_report: Returns Analytics data. Required parameters are:
- dateRange: the reporting period. Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
Query example
Now, you can execute queries on the Adobe Analytics views that have been created:
Querying suite1_referring_domain_report view
suite1_referring_domain_report query results
Web Technology
Predefined reports to know which technologies users are using to access your website.
Web Technology data sources and views
- ${report.suite.prefix}_browser_report: Returns Analytics data. Required parameters are:
- dateRange: Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_countries_report: Returns Analytics data. Required parameters are:
- dateRange: Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_mobile_device_report: Returns Analytics data. Required parameters are:
- dateRange: Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_operating_systems_report: Returns Analytics data. Required parameters are:
- dateRange: Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
- ${report.suite.prefix}_regions_report: Returns Analytics data. Required parameters are:
- dateRange: Must have this format: yyyy-MM-ddThh:mm:ss.SSS/yyyy-MM-ddThh:mm:ss.SSS.
Query example
Now, you can execute queries on the Adobe Analytics views that have been created:
Querying suite1_countries_report view
suite1_countries_report query results
Appendix I. How to simplify the data range format in views
You can create a derived view over the final views to divide the daterange text parameter in two timestamp parameters: startdate and enddate.
Below you can see an example of creating a derived view over ${report.suite.prefix}_browser_report.
First, click on the three vertical points located on the right side of the ${report.suite.prefix}_browser_report view and select New -> Selection.
Create new Selection view option in VDP
You have to create two new view parameters in the new view:
- startdate: timestamp.
- enddate: timestamp.
Create view parameters option
Now, you have to relate both view parameters with the daterange original parameter in the Where conditions tab using the following condition:
concat(formatdate('yyyy-MM-dd''T''hh:mm:ss.SSS', startdate), '/', formatdate('yyyy-MM-dd''T''hh:mm:ss.SSS', enddate))
Where condition tab
Finally, remove the datarange column from the Output tab and click save:
Output tab
Now, you can execute your new view:
New view query
New view query results