Documentation Experience Platform Query Service Guide

Query Service overview

Last update: October 10, 2024

Topics:
Queries

CREATED FOR:

User
Developer

Adobe Experience Platform ingests data from a wide variety of sources. A major challenge for marketers is to make sense of this data to gain insights about their customers. To query data in Platform, you can use standard SQL and Adobe Experience Platform Query Service. You can use Query Service to join any dataset in the data lake and capture the query results as a new dataset for use in reporting, machine learning, or for ingestion into Real-Time Customer Profile. This document provides an overview of the role of Query Service within Experience Platform.

You can use Query Service to connect the online-to-offline customer journey and understand omni-channel attribution for your brand. The following video shows how an experience business can use Query Service to address key use cases and how Query Service works.

Transcript

Hi there. Adobe Experience Platform ingest data from a wide variety of sources. And major challenge for marketers is making sense of this data to gain insights about the customers. In this video, let’s get a quick overview of how Query Service helps brands to connect the online to offline customer journey, and understand Omni-channel Attribution. We will also cover some of the common use cases for Query Service in Adobe Experience Platform. Today, we are in a place where experiences are everything we do, across physical and digital channels. Take an example, such as going on a holiday. Now, the experience begins when you first start planning for a vacation. Choosing the right location, right offers for travel, getting the best deal for your dream resort, to finding the right tours. All of that is now part of the experience. Experience has evolved from making a discreet, even delightful to making an entire journey engaging and compelling. For brands, the big question is around, “How do you deliver that level of experiences?” But what is the secret sauce to deliver a customer experiences that exceed their expectations during every point of the journey? There are three steps to create relevant, satisfying, and valued experiences. First, it starts with measuring customer engagement. Measuring, understanding and analyzing customer journeys allows you to gain a holistic view of your customers and the context behind their every action. Second, discover unique high value audiences. You cannot duplicate your best customers, but you can find more like them. You need to uncover these audiences before your competitors do. The key lies in using your current customer data to locate others with similar attributes. Lastly, we need to deliver seamless one-to-one personalized experiences across touchpoints. What you need is an experienced architecture that makes the right insights available at the right time to make the right decisions. So what are the issues with Generic Cloud Data Platforms? When we talk about experienced architecture, there are two systems. The Operational or the left-brain, and the Analytical or the right brain. In order to shape customer experiences in Real-Time, or near Real-Time, brands need access to Unified Customer View. This Unified Customer View resides in the operational system and supports low latency access patterns. For example, mobile applications or kiosks need quick access to Unified Customer View data to make personalization decision in milliseconds. Analytical systems, on the other hand, it is totally a different kind of data scale scenario. The objective is to analyze large amounts of data to perform Advanced Analytics and to train Machine Learning Models. Trying to use an Operational Scale System that is designed for low latency against an ML workload would not scale because you cannot get access to large amounts of data in a reasonable time. Instead, analytics workloads require a different access pattern. They are typically housed in a Data Lake or a Data Warehouse. But having two separate system creates a lot of challenges. Adobe Experience Platform is built from ground up to be an operational and analytical system. Whether you want to access and use data for Operational or Analytical use cases, it is just a matter of configuration. That being said, Experience Platform is not here to replace your Data Warehouse, unless you want to. We are creating a fast symbiotic complimentary relationship between Operational and Analytical use cases. From a packaging standpoint, there are three flavors of Adobe Experience Platform that you can buy. Collection. Trusted, robust, and most complete streaming data infrastructure that helps simplify Customer Experience data collection and streamline its delivery. Intelligence. Adobe Experience Platform ingest data from a wide variety of sources. Adobe Experience Platform Query Service allows you to use standard SQL to query data in platform, helping marketers better understand their customers. Activation allows brands to create a complete customer profile using both Behavioral and Attribute data from any data source to be used across each organization within the enterprise. At a high level, there are four capability groups under the Experience Platform Intelligence Umbrella. The first is SQL exploration and procreation, which captures the ability provided to bring Customer Experience Data into one place for analysis, exploration, and experimentation using ANSI SQL and for persisting the derived data set into Data Lake. The second capability group captures the capabilities we provide to seamlessly create and visualize key customer metrics within Experience Platform and share these insights with key business decision-makers. Adobe Experience Platform supports third-party integration that allows access to data stored in Experience Platform for dashboarding and reporting. Third capability is, bring or build, and operationalize your own model which captures the AI or machine learning tools, assets, pre-built templates, and frameworks that support the rapid development, training, and tuning of Machine Learning Models with the very key functionality for customers to be able to bring their existing Machine Learning Models. And operationalize those models on top of the rich Omni-channel data in platform. The fourth is, AI or Machine Learning driven insights for personalization via profile enrichment, which are capabilities for applying insights derived from the operationalized Machine Learning workflows to include activation workflows in Adobe and non-Adobe destinations. Here are some of the high-level use cases where a customer will start to use Adobe Experience Platform Intelligence. With SQL Explorer, we can run ad hoc SQL queries against raw customer data as soon as it lands in Adobe Experience Platform for Omni-channel analysis. It is typically used for verification, exploration and experimentation of experience events, leveraging the native SQL Query Editor or third-party SQL tool of choice. Adobe-defined Functions are prebuilt functions in Adobe Experience Platform Query Service that help perform common business related tasks on Experience Event data. These include functions for Sessionization and Attribution like those found in Adobe Analytics. Adobe Experience Platform Query Service provides several built-in Spark SQL functions to extend SQL functionality. Check out the documentation for Spark SQL functions that are supported by Query Service. PostgreSQL is a command-line interface that comes installed when you install PostgreSQL on your machine. You can use any PostgreSQL compliant tool to access Experience Platform data sets and perform data exploration. You are ultimately able to experiment and validate data quickly before operationalizing that data in Customer Journey Analytics or in a dashboard available in Adobe Experience Platform. With the SQL preparation, you can quickly prepare Experience Data for downstream analytical consumption in Adobe Customer Journey Analytics, Data Science Workspace and BI dashboards, with the ability to schedule, manage and monitor SQL preparation jobs on large petabyte scale data sets using UI or API. Adobe Experience Platform allows third party applications that uses PostgreSQL protocol to connect to Query Service. This integration helps marketers to create and view a visualize representation of customer data in their favorite business intelligence tool to make useful decisions based off it. Adobe Experience Platform Query Service facilitates that by allowing you to use standard SQL to create data in platform. Adobe Experience Platform Query Service facilitates that by allowing you to use standard SQL to query data in platform. -

Using Query Service

To analyze your data, create and execute SQL queries with either the Query Service user interface or the RESTful API.
With the Query Service UI you can write, execute, and schedule queries, view previously executed queries, and access queries saved by users within your organization. You can also test out your queries before executing them on your wider dataset with the Query Editor. See the Query Service UI guide for an overview of the UI functionality.

The RESTful API provides a similar experience. You can use the Query Service API to programmatically write and execute queries, create and save templates for queries that you wish to adapt, or schedule queries for automated execution. See the Query Service developer guide for more information on using the Query Service API.

To quickly get started using Query Service features, you are recommended to read the following documents:

Query Service and Experience Platform services

Query Service interacts and can be used with multiple Experience Platform services. To make the most out of Query Service’s capabilities, you should become familiar with these services and how they interact with Query Service. The Experience Platform documentation landing page provides summaries and links to the platform’s capabilities.

Data Science Workspace

Adobe Experience Platform Data Science Workspace uses machine learning and artificial intelligence to gain insights from data stored within Experience Platform. Data scientists can use the Data Science Workspace to build recipes based on record and time-series data about customers and their activities. These recipes facilitate predictions such as buying propensity and recommended offers that the individual is likely to appreciate and use. You can use SQL within Data Science Workspace by integrating Query Service into JupyterLab to explore, transform, and analyze Adobe Analytics data. Read the Data Science Workspace overview and the Jupyter Notebook connection guide for more information about how Data Science Workspace interacts with Query Service.

Segmentation Service

Use the Adobe Experience Platform Segmentation Service to divide your customers into smaller groups that share similar traits. These audiences can then be evaluated to provide better analysis on your Real-Time Customer Profile data. You can use Query Service to run queries on this audience data within the data lake and provide the analysis. Read the Segmentation Service overview and the Profile Query Language (PQL) guide for more information on how to analyze audiences.

Use cases

Query Service provides a flexible approach to your data processing that serves many purposes. Among others, it can ease the burden of segmentation from marketers, and help generate actionable audiences and meaningful business insights. The following use cases offer more in-depth examples of the power of Query Service.

Adobe Analytics browse abandonment

This browse abandonment example centers on using Adobe Analytics data to create a particular actionable audience. Query Service accommodates complex logic for segmentation to calculate various personalized attributes for use downstream, or to greatly simplify how you build out your audiences.

Generate insights with custom dashboards

With Adobe Experience Platform, you can ingest, store, structure, and pull all stored datasets — including behavioral, CRM, and point-of-sale data. Using Experience Platform’s Query Service, you can query on these datasets and answer specific questions about the business and then start generating impactful insights. Learn how to build and manage custom dashboards where you can create, add, and edit bespoke widgets to visualize key metrics with user-defined dashbaords. You can even customize your own Real-Time CDP reports for your marketing and KPI use cases by using SQL queries with the Real-Time Customer Data Platform Insights Data Models.

Next steps and additional resources

By reading this document, you have been introduced to Query Service and how it functions within the greater scope of Experience Platform. To continue learning about Query Service features, you are recommended to rad the following documents:

The Query Service developer guide: For more information on interacting with various endpoints within the Query Service API.
The Query Service user interface guide: For more information on using the Query Editor and UI.
The Query Service clients overview: For a comprehensive list of external clients to connect with Query Service.

To better prepare yourself to run queries, watch the following video. This video shares tips and best practices for running queries in the query editor interface, PSQL clients, business intelligence (BI) solutions, and the HTTP API.

Transcript

In this video, you’ll learn how to explain data usage patterns and query service.

Consuming data through Query Service can happen in a couple of ways to different mechanisms. We already discussed the ability to launch queries to the Query Editor UI which is available inside Adobe Experience Platform. The ability to use external tools and support Postgres like PSQL does with a command line editor. The ability to use BI-tools and also the ability to use the Customer Journey Analytics Module, which will bring Analysis’ Workspace to Adobe Experience Platform. Additionally, query service offers an HTTP API, which allows brands to consume query service from inside their own applications. Let’s zoom in a bit deeper on each of those. First of all, the Query Editor which is available natively inside Adobe Experience platform, has the goal of helping business analysts to its query developments, analysis and exploration. The Query Editor is an interactive tool for developing and testing queries. It offers a set of interesting features, like automatic syntax highlighting, SQL keyword auto-complete, table and field auto-complete, and also error detection. It’s an interactive environment which means that you can’t close your browser when executing a query as it’s query will then be dropped. Your browser window needs to remain active for the total duration of the query. Next is the PSQL Client. The PSQL Client can and should be used for query development, analysis and exploration as well. PSQL is a command line interface which is installed together with Postgres and it makes it easy to connect from an external environment to Query Service for testing and development purposes. Many brands use BI-solutions to deliver data driven inside and an easy to consume visual representation. Thanks to query service, brands no longer have to implement and maintain lengthy data import transformation and export processes. And can now easily connect from their preferred BI-environments directly to Adobe Experience Platform. These BI-solutions can consume data sets from platform but aren’t intended to refresh dashboards by consuming full data sets every couple of minutes. The preferred and scale level way of consuming data from a BI-solution is to consume data sets that have been populated to a scheduled queries on data sets that have been prepared by in CTAS commands. Query Service also offers an HTTP API, which offers brands the ability to run queries and get query results as part of a brands operational process. These APIs are fully documented on this link. Lastly, a couple of important tips and best practices. When working with XDM Schema fields, the way to do that is to use either dot-notation or the bracket-notation. Interactive Query Execution has a couple of requirements. First of all, the maximum time an Interactive Query can run is 10 minutes. It will also return a maximum of 50 000 rows. And the brand can have a maximum of 5 concurrent queries.

The limit of 50 000 can be bypassed by specifying the limit parameter as part of the query. But even then, the maximum timeout remains 10 minutes. These limits apply to the Query Editor UI, PSQL and BI-solutions. These limits do not apply to the Query Service HTTP API which has no limits, and which handles all requests on a first come, first serve basis and captures results in a data sets. Query Service offers brands multiple ways of interacting with data and as such, caters for every need. The Query Editor UI in Adobe Experience Platform makes query development a lot easier. With CTAS, insights can be written back to Platform and can be consumed by Data Science Workspace, Real Time Customer Profile and BI-solutions. And finally, the Query Service API allows brands to interact with Query Service from inside an application. With that, you should now be able to explain the data usage patterns in Query Service.