Adobe Experience Platform Now Offers the Power and Ease of Query Service

[#1]

Authors: Dave McNamee (#dmcnamee), Anil Malkani, and Jody Arthur

Adobe Experience Platform Query Service was released in June 2019. The following blog post describes our architectural approach and provides examples.

Every day, enterprises continue to amass terabytes of customer data in their data lake and data warehouses. Despite the promise these data stores hold for improving customer experiences, enterprises struggle with accessing and analyzing their data across a heterogeneous data landscape in a unified way.

Adobe is a leader in helping enterprises use their data to build compelling and personalized experiences and continues to create new ways to empower its customers through Adobe Experience Platform. Query Service is a SQL-based query engine with built-in analytical capabilities and serves as a foundation component for many of Adobe’s internal applications. In June 2019, Adobe made this powerful tool available for our customers.

We originally developed Query Service to access and work with our own interactional and transactional data stored in different databases within Adobe and analyze them together to provide the best possible experiences for Adobe Experience Platform customers.

Now, Adobe Experience Platform customers can use Query Service directly as an engine for multichannel analysis and reporting, and support key integrations for building customized reports and visualizations for different teams throughout their enterprise to guide in their decisioning. Query Service gives marketers, data engineers, and data scientists the power to use SQL to find insights in their data in Adobe Experience Platform.

Query Service provides enhanced capabilities for Adobe Experience Platform users

With Query Service embedded in Adobe Experience Platform, users now have a unified technology with which they can now more easily create the queries necessary to develop customer profiles and perform customer journey analytics across any data ingested into Adobe Experience Platform, including Adobe Experience Cloud solution data such as Adobe Campaign, Analytics, Audience Manager, Target, and Advertising Cloud, in addition to first-party data that they bring to Adobe Experience Platform.

We built Query Service to be serverless and capable of autoscaling in order to handle queries at the scale of the experience businesses. Our primary goal with this release is to help our data customers shorten the journey from raw data to insights with batch processing and eventually, to provide interactive reporting capabilities for operational KPIs.

Figure 1: Adobe Experience Platform Query Service Overview

Query Service architecture

Query Service is based on SQL — the query language of choice for Adobe Experience Platform customers. As shown in the figure below, Adobe Experience Customers generally have two types of workloads when interacting with Query Service:

• On-demand analysis, which is used primarily for ad hoc data analysis
• Batch or scheduled analyses to support, for example, time-based reporting (daily, weekly and/or monthly, etc.) or historical reporting (year-over-year, month-over-month, etc.)

Figure 2: Adobe Experience Platform Query Service High-Level Architecture

In keeping with Adobe’s API-first approach to development, Query Service provides a REST API for scheduled workloads and the OpenAPI Documentation. Our API gives our customers the power to create and execute non-interactive queries, create query templates for later use, and to create calendar-based schedules for query execution.

Query Service also supports the PostgreSQL command-line client, which can be used for both BI use cases as well as for database management. And, its integration with RStudio gives Adobe Experience Platform users extensive power to run SQL for generating reporting data for a variety of use cases.

With Query Service, customers can use different data management tools such as Dbvisualizer, Aqua Data Studio, and others to work with their data in Adobe Experience Platform. And, with the ability to connect business intelligence tools like Power BI, Tableau, Looker and others, customers can gain actionable insights from any data on Adobe Experience Platform.

Resource management

Ensuring good performance is always a top concern at Adobe. We built Query Service to be the cost-efficient engine that scales to the necessary resources for managing dynamic workloads. It auto-scales the resources based on the workload being executed by a tenant at any given time

Query language support

Query Service can be used to analyze hierarchical datasets to Adobe solution data or customer-provided data, or join across both sets. For example, with the analytic event schema shown below, Query Service can be used to query for all the event details including content details, transaction details, tracking code for attribution and others.

Figure 3: Adobe Experience Platform Query Service hierarchical experience event schema

Data storage format

Query Service is tied to our data storage format, enabling it to use additional metadata information maintained along with the dataset, such as dataset statistics, file-level pruning, column statistics, and column dictionary. Query service will push down filter predicate and column projection depending on the query plan and optimize the overall query processing experience.

How our customers are using Query Service

One of the key design principles of Adobe Experience Platform Query Service is to allow our customers to accomplish a variety of use cases while still being able to use the same interface for issuing those queries. The following examples are just two of the ways our customers are using Query Service.

One scenario might be a marketer who is interested in tracking products viewed versus products ordered by unique visitors to better understand their conversion rates. In Query Service, this sort of query could be written as:

Example 1: Querying hierarchical datasets in Adobe Experience Platform Query Service

One of the additional benefits of using Adobe Experience Platform Query Service is the access to SQL extension as Adobe Defined Functions — a set of convenience functions for common analysis tasks that would otherwise take a data analyst many lines of code to accomplish. These functions extend SQL by providing pre-built functions are built specifically to make it easy for users to address Experience Analytics use-cases such as attribution, sessionization, and others, thereby to provide users more powerful analytical querying capabilities and to make their queries easier to build and execute.

Another common scenario would be to track the last Tracking Code, such as page names to determine what is driving customers to some specific content. In the example below, the customer can use our ATTRIBUTION_LAST_TOUCH extension and simplify the logic of the query:

Example 2: Querying using the analytic extension on a hierarchical dataset in Adobe Experience Platform Query Service

Query Service makes it easier than ever to leverage your data in Adobe Experience Platform

At Adobe, providing the best customer experience is baked into everything we do. We know that in addition to innovative tools and capabilities, Adobe Experience Platform customers now have access to query editor embedded in their user interface that makes authoring queries and tracking query history convenient. The query editor allows for query execution from your favorite browser and also provides autocomplete of SQL, table names — a SQL reference to the datasets in data lake — and hierarchical column names using full dot notation. This also makes it easier for users to get documentation for the columns (especially the extension customized by customers) as they are authoring their queries.

Query editor also provides proactive error detection and a query log page in the UI that provides a history of query execution. Within the UI, users can also schedule workloads and easily connect to their BI tools for reporting needs.

Query Service comes with an extensive library of highly detailed user documentation, including a Getting Started Guide, sample queries for Adobe Analytics and Adobe Target data, documentation for Adobe Defined Functions, and a full reference of the supported built-in SQL functions.

Conclusion

With the release of Query Service as part of Adobe Experience Platform, Adobe has successfully leveraged this innovative technology to improve the performance of our platform while providing our customers a powerful new way to interact with and leverage their own data. To learn more, visit us here.

Originally published: Aug 09, 2019