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.
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 2: Activity Level Result
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 added to cart
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 Query
Figure 4: Product Interaction Result
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 Query
Figure 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 identifier
Figure 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 Query
Figure 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 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 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 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:
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 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 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.