Query Service Tricks in Adobe Experience Platform (Part 1: Writing Queries and Storing Derived Datasets)

[#1]

Authors: Frederik Werner, Akintunde Ajayi, and Nikhil Maddirala

Frederik Werner is a German Analytics Lead and Data Scientist working in and writing about Web Analytics and Online Marketing Technology. In this first post of a four-part series, Frederik will provide the overview and get-started tips on Adobe Experience Platform Query Service. He gives examples of his favorite tricks using Query Service and specifically for customer journey analysis use cases in Customer Journey Analytics. It also includes how to write back data to Adobe Experience Platform.

Query Service is a feature of Adobe Experience Platform. Adobe Experience Platform provides SQL analytics and AI/ML capabilities that enable enterprises to generate and operationalize customer intelligence for driving improved personalized customer experiences.

How Query Service works and interacts with Adobe Experience Platform

I recently got the chance to dive into Query Service in Adobe Experience Platform and Customer Journey Analytics. That means that I now have all my Adobe Analytics data in Adobe Experience Platform, ready for Query Service to enrich it. So what actually is Query Service? Adobe’s Experience Platform offers some unique functionality to process and enrich data. With Query Service, Adobe practically wipes the floor with tools like Big Query. It provides a SQL interface to interact with datasets stored in Adobe Experience Platform. This interface can be used from Adobe Experience Platform UI or with a normal SQL client. On the surface level, it utilizes a PSQL syntax for commands, offering the framework regarding syntax and a base level of functionality, like Joins or Unions. Below that surface, Adobe utilizes Spark SQL for some commands beyond normal PSQL to accommodate the big-data-like environment (which also means our queries will take a while to process). To provide some additional functionality there are some Adobe Defined Functions (documented on two pages, here and here) that offer some advanced functions most relevant to Adobe Analytics users

Figure 1: If you like SQL, Query Service will feel right at home for you

All our datasets in Adobe Experience Platform are represented as tables in Query Service. To start crunching our data, we need to find the table name for those datasets, which is shown on the dataset detail page in Adobe Experience Platform:

Figure 2: Finding our dataset’s table name

Writing data back to Adobe Experience Platform

Once we have run all our fancy queries we will want to store the result in Adobe Experience Platform. This is possible but a bit complicated, depending on your use case. If you only want to store the result for later use, you can just click on a saved or logged query and select “Output dataset”:

Figure 3: Storing Data from Query Service in Adobe Experience Platform

This opens a dialog to name our dataset and runs the query again, but stores the output instead of just displaying it. Since all datasets in Adobe Experience Platform need a schema, Query Service will create an Adhoc XDM schema:

Figure 4: Adhoc Schema in Adobe Experience Platform

I find this to be a double-edged sword. While it is very convenient to be able to store data on the fly, we can neither see nor edit the schema. Tools like Customer Journey Analytics need a defined schema to know which columns hold the timestamp and identity for the Person ID. There are two ways we can tackle this issue.

One way is to create a new dataset and insert our resulting data into that dataset. The official documentation describes the syntax like this:

INSERT INTO table_name select_query

Easy enough, right? Yes, but there is something we need to watch out for. We need to closely match the schema associated with that dataset. I actually needed some support from Adobe to find out how Query Service expects the structure to be since all our custom defined fields in a schema end up in a namespace unique to our company.

So our schema might look like this:

Figure 5: Sample Schema

Because of this, we need to put all our fields in the mycompanyid namespace to match the schema. We need to do this using a STRUCT structure to our query:

INSERT INTO testdataset
SELECT
STRUCT(...) as _mycompanyid,
monotonically_increasing_id() "_id",
'custom' "eventType",
sessiondata.timestamp "timestamp"
FROM

Within that STRUCT, we need to put our columns in exactly the right order with exactly the right type. This is also true for the second way to store data, which is by creating a new dataset with an existing schema like this:

CREATE TABLE enrichedstructtest WITH (schema='Enriched TS Schema Demo') AS (SELECT STRUCT(...) as _mycompanyid ...)

Now we have a new dataset called enrichedstructtest schema "Enriched TS Schema Demo". Figuring this out can take quite some time since Adobe Experience Platform is not always super helpful with its error messages. While syntax errors return immediately, type errors are much harder to find, since they only show up like this:

Figure 6: Non-helpful Query Service error

I was quite puzzled when I received that [Failed to get results of query Reason: [Writing job aborted.]] error for the first time. But there is a (quite hidden) way to find the reason. You need to head to Monitoring -> Set the filter to “Only ingest failures” -> Click you last executed query. It will not have a dataset name, since the data could not be written:

Figure 7: Debugging Writing Job errors in Experience Platform

Once you click the Failures link, you have to quickly take a screenshot of the following page, since it will autoclose itself within seconds (due to the missing dataset. Thanks, Adobe…) automatically. If you are fast enough, you end up with two contradicting statements:

Figure 8: Make up your mind, Adobe!

On the left side, Adobe asks us to contact support. But on the right side, they actually tell us what went wrong. In this case, I tried to write a boolean value into a string field (for demo purposes, of course, I would never actually make this mistake…), which is not allowed. Once we fix this, our query goes through without problems, or you will get a new error message.

Wrap Up

I hope you found this article helpful. You should be able to create some advanced fields with Query Service and write it back to an existing or new dataset. Also, you now know how to find out what went wrong if datasets can’t be written back successfully. There will be 3 more subsequent blogs with my other favorite tips using Query Service.

Will I ever return to normal Adobe Analytics after all this Query Service and Customer Journey Analytics work? Yes, of course. It’s still my main driver for day-to-day analysis. But with all the exciting information I can get out of my boring old data I will gradually spend more and more time in Query Service and Customer Journey Analytics in the future.

This blog originally appeared The Full Stack Analyst on 10/21/2020.

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 Meetups.

References

1. Adobe Experience Platform
2. Adobe Experience Platform Query Service Tutorials
3. Adobe Experience Platform Query Service Web Page
4. Customer Journey Analytics Web Page

Related Blogs

• Build an Optimal Online Experience: Enrich Unified Profile with Query Service
• Digging into Adobe Experience Platform’s Experience Data Model to More Fully Understand the Power of...

Originally published: Dec 10, 2020