Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Build an Optimal Online Experience: Enrich Unified Profile with Query Service




Authors: Jaemi Bremner (#Jaemi_Bremner), Clément Delalande and Diptiman Badajena (#Diptiman_Badaje)


In this blog, we investigate how to enrich Adobe Experience Platform Real-Time Customer Profile with indicators calculated from online behavioral data. Using Adobe Experience Platform Query Service SQL functionalities, it is easy to access and analyze raw analytics data to build aggregated key primary indicators (KPIs) which will later be useful for Marketing, Support, and Sales team. The method described below is a production-ready alternative to the computed attributes , which is currently an Adobe Experience Platform alpha feature.

Adobe Experience Platform Query Service empowers any data engineer to analyze raw data collected at different steps of the customer journey. Direct access to the Adobe Experience Platform data lake gives the capability to experience makers to derive meaningful insights from customer browsing sessions on web pages or mobile apps to improve customer experiences.

For this blog, we are describing in the context of an online retailer, who needs like to get KPIs for our website visitors to improve customer experience by getting real-time answers to their queries. Examples are:

  • How often do they browse our website?
  • What kind of product interaction do they have?
  • What product categories are they interested in?

Those indicators can then be made available in the Real-Time Customer Profile and consumed by any application to improve and personalize to deliver meaningful customer experiences. In the example below, we’d like to show those indicators in a call center application, so that operators can best advise customers based on their browsing history and preferences.

Let’s start with building the indicators with Query Service, then we’ll create and populate a Real-Time Customer Profile enabled dataset to store them. Finally, we’ll leverage Scheduled Query APIs to refresh those indicators on a regular basis.

Leverage Query Service to calculate profiles KPIs

In this case, we want to identify our website visitors based on their Experience Cloud ID, but any identity available in the Experience Event for website dataset can be used.

Activity Level

Let’s define our first indicator, which is the activity level, based on the number of pages viewed on the website:

Figure 1: Activity Level QueryFigure 1: Activity Level QueryFigure 2: Activity Level ResultFigure 2: Activity Level Result

Product Interaction

Based on our visitors browsing history, we’d like to determine how far they went in the conversion process, which is composed of three steps :

  • Product viewed
  • Product added to cart
  • Product purchased

We also need the total purchase value on a customer basis, the indicator will help marketing team segment customers based on their purchase value.

Figure 3: Product Interaction QueryFigure 3: Product Interaction Query

Figure 4: Product Interaction ResultFigure 4: Product Interaction Result

Preferred Category

Let’s identify what product category our visitors are interested in. It is useful to personalized customer experience with relevant content across different channels (website personalization, email next best offer…)

Figure 5: Preferred Category QueryFigure 5: Preferred Category Query

Figure 6: Preferred Category ResultFigure 6: Preferred Category Result

Now that we have identified the key indicators, let’s build a XDM schema to structure our data and merge them into a Real-Time Customer Profile.

Create Profile KPIs Schema

In the screenshot below the schema is called Profile KPIs and it uses the salesvelocity tenant. There we add a primary identity, tied to the ECID namespace (1).

Then we create a new mixin called retail_kpis, which has the following attributes (2):

  • activityLevel (String) : It stores either Low / Medium or High based on the number of page views
  • preferredCategory (String) : Determines based on the browsing history what is the most viewed category of the website
  • productViews (Integer) : Contains the number of product page viewed
  • addedToCart (Integer) : Contains the number of products added to cart
  • purchased (Integer) : Contains the number of product purchased
  • purchaseValue (Double) : Contains the total purchase value (sum of all purchases products)
  • addedTimestamp (DateTime): Contains the modification date of the record, this is useful for investigation purpose

Last but not least, let’s enable the schema for Real-Time Customer Profile.

Figure 7: Define ECID as the primary identifierFigure 7: Define ECID as the primary identifier

Figure 8: Add the required attributes and enable schema for a customer profileFigure 8: Add the required attributes and enable schema for a customer profile

Schema is now ready for data ingestion. The next step is to use query service to build a new dataset based on Profile KPIs schema.

Write the query output in a dataset

Create Table as Select statement lets us write the output of a query in a dataset called profile_kpis_dsn. We ensure the table follows the structure of our previously defined XDM schema by specifying the schema name.

The response of the SQL query contains the newly created datasetId.

A select statement on profile_kpis_dsn shows how the hierarchical XDM data are written as the composite value in the table.

Figure 9: Create Table as Select QueryFigure 9: Create Table as Select Query

Figure 10: CTAS ResultFigure 10: CTAS Result

Enable the dataset for profile

Our dataset is now created and populated, however, it is not enabled for Real-Time Customer Profile yet. This can be done in Adobe Experience Platform user interface:

Go to a dataset, select the newly created dataset called profile_kpis_dsn, then toggle Profile.

Keep in mind that any data which was part of the dataset prior to Real-Time Customer Profile activation won’t be taken into account by the Customer Profile Service.

Figure 11: Enable dataset for profile ingestionFigure 11: Enable dataset for profile ingestion

Dataset Refresh Automation

Everything is in place to hydrate the Real-Time Customer Profile with customers KPIs, however, we’d like to make sure our indicators are refreshed on a regular basis so that it reflects in near real-time the customer activity on the website. We can leverage the scheduled query APIs to update the dataset. In the example below, it is refreshed every hour, following the cron table syntax.

Figure 12: Cron table syntaxFigure 12: Cron table syntax

The response contains the schedule_id, which is used as an identifier to work with the scheduled query.

If you’re impatient, you can trigger the scheduled query using the /runs endpoint with a POST request.

Figure 13: Post requestFigure 13: Post request

You can then query the scheduled runs to check their state. In the response, you get the run ID, as well as the link to query the run details. if it is successful, you can access the query output in the API response:

“state”: “SUCCESS”,
 “message”: “Processed Successfully”

Check Aggregates in Customer Profile Service

Once we look at our Profiles in Adobe Experience Platform, we can check the Attributes tab, as it now shows the different indicators available in the Customer Profile Service. Thanks to scheduled query runs, they get refreshed once an hour. The refresh date is displayed in the addedTimestamp attribute.

Figure 14: Customer Profile Attribute View with refreshed indicatorsFigure 14: Customer Profile Attribute View with refreshed indicators

Leverage Indicators for Enhanced Customer Experience

Now, we can leverage those indicators in any app that consumes the Customer Profile Service. In the context of our retailer, it is useful for customer support, as the call center agent can view any customer interaction with the brand, as well as key indicators and user preferences. Here, the customer presented with an opportunity to grow revenues through cross-selling strategy, such as the next-best offer based on the preferred category by a single customer in real-time.Figure 15: Call Center screen with next best offerFigure 15: Call Center screen with next best offer

Learn More with Query Service

Consolidation and creation of a holistic view of the customers from vast and rapidly growing amounts of data (transactional and demographic) is the prerequisite for any modern online experience. Using raw data to identify the products or services specific customers are most likely to be interested in for their next purchase is one of the many indicators which can be processed through Query Service. Following the same logic, you can also calculate RFM or loyalty scores to help marketers define the right targeting criteria and improve customer retention and loyalty. Sharing them later down the road to the external system doesn’t require any extra effort as they are automatically part of the union schema. The richness of Adobe Experience Platform APIs and connectors gives the possibility to quickly refresh data and expose them to any applications, such as Adobe Campaign for one to one communication, or Adobe Target for the website and mobile app personalization.

Finally, what’s true for onions is also true for Adobe Experience Platform: if you can’t find what you’re looking for, it’s definitely worth going a little bit deeper and explore its technical components. Besides giving you more control over your data, Query Service is also a great tool to learn about how datasets are built and populated. We encourage anyone facing platform limits looking at what Query Service can offer, the possibilities are endless and will unlock your next customer use case.

Follow the Adobe Experience Platform Community Blog for more developer stories and resources, and check out Adobe Developers on Twitter for the latest news and developer products. Sign up here for future Adobe Experience Platform Meetup.


  1. Real-Time Customer Profile —

Originally published: 30 Jun, 2020