Query Service Tricks in Adobe Experience Platform (Part 3: Using Adobe-Defined Functions [Attribution])

[#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 second 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.

Adobe-defined functions: Attribution

Recreating entry- and exit Dimensions

Another thing that is missing from the Analytics Data Connector data is some of my beloved Props functionality. In the data we get, there is no entry- or exit dimension for any of our props, which is of course really sad. As a reminder: Those dimensions hold the first and last value a dimension received in a session, with the useridentry page and exit page is a prime example.

Luckily, we can use another Adobe Defined Function for this. They have built some functions that are intended to do attribution that we can use for that use case. In particular, we are going to use the ATTRIBUTION_FIRST_TOUCH function, which looks like this:

ATTRIBUTION_FIRST_TOUCH(timestamp, [channel_name], column) OVER ([partition] [order] [frame])

We already know most of the parameters from before. What is new are the channel_name and column values, which tell Query Service which column we want to use for attribution. In our case, we want to put the pagecolumn in there and modify the frame from before like this:

attribution_first_touch(timestamp, '', page) OVER (PARTITION BY userid, session_nr ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).value "Session_entry_page"

Let’s look at our partition a bit closer. As you can see, I included both the userid and the session_nr, so we only look for the entry page during the actual session. In addition to that, the frame is now defined as, BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, which means we look at the complete data for the whole session. By removing the CURRENT ROWlimitation, we can get the first page of a session without the need to know it on the first hit of the session.

If we wanted to receive the last page of a session instead, we just need to use the ATTRIBUTION_LAST-TOUCH function instead:

attribution_last_touch(timestamp, '', page) OVER (PARTITION BY userid, session_nr ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).value "Session_exit_page"

This will do the same as the previous function, but take the last found value instead of the first. But there again are some cool use cases beyond those. Consider this example:

attribution_first_touch(timestamp, '', campaign) OVER (PARTITION BY userid ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).value "User_entry_campaign"

With this example, we remove the session limitation (in the PARTITION BY part) to get the first campaign value for a user, even before the user used it! That way we get entry- and exit dimensions, but on a user level, without session restrictions! As a Props fanboy I get really excited by this. What comes to your mind for this functionality?

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 2 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 23, 2020