EXPLORE 200: Exploring Behavioral Data - Case Study with Adobe Analytics Data

Prerequisites

You need to make sure you complete this module that ingests Adobe Analytics web data into the Platform:

PRE 107: Ingesting JSON Test Data into Adobe Experience Platform

And of course, you should have:

PRE 105: DBVisualizer SQL Editor Setup

Scenario

We are going to ingest LUMA data into our test environment. This is a fictitious online store created by Adobe

The fastest way to understand what is happening on the website is to check the Products tab. There are 3 categories of products for different (and all) personas. You can browse them. You authenticate yourself and also can add items to a cart. The data that we are ingesting into the Platform is the test website traffic data that conforms to the Adobe Analytics schema.

We need to run some analytical queries on this dataset.

Exploratory 1-Dimensional Queries

The goal of this exercise is to explore every column of the dataset individually so that we get a deep understanding of the columns. Once we understand each column, we can then build 2-dimensional and even n-dimensional queries.

Let us first retrieve all the results:

SELECT * FROM luma_web_data;

You can see that there are complex nested objects. Instead of going into the XDM schemas, we can query the data in place by using to_json.

Let us dig into the web JSON object (or XDM field group):

Explore web Object

SELECT to_json(web) FROM luma_web_data;

Let us dig one level deeper into webPageDetails. We will use the dot notation to access any field in the hierarchy.

SELECT web.webPageDetails FROM luma_web_data;

We can apply to_json again:

SELECT web.webPageDetails FROM luma_web_data;

pageViews is an object. Let us access the elements of that array

SELECT to_json(web.webPageDetails.pageViews) FROM luma_web_data;

You will get the following:

We can access the value by:

SELECT web.webPageDetails.pageViews.value FROM luma_web_data

And you will get:

Explore marketing Object

Let us work on the marketing object:

SELECT to_json(marketing) FROM luma_web_data;

The results show information about campaigns:

Explore channel Object

If you execute the following code:

SELECT to_json(marketing), to_json(channel) FROM luma_web_data;

You will observe that there is duplication of data across these fields. marketing object truly has a campaign name while the other fields are present in the channel object.

Let us extract the channel type that is in the type field of the channel object as it has values such as search, email, and social.

The code for this will be:

SELECT channel._id AS tracking_code, regexp_extract(channel._type, '[^/]+$', 0) AS channel_type, channel.mediaType AS channel_category FROM luma_web_data

The result will be:

Note the usage of the regular expression that is extracting the last word in the _type field that looks like _type":"https://ns.adobe.com/xdm/channel-types/XXX"

• regexp_extract(channel._type, '[^/]+$', 0): This is the main part of the query where you use the regexp_extract function to perform regular expression extraction.

  • channel._type: This specifies the JSON field "_type" inside the channel JSON object.

  • '[^/]+$': This is a regular expression pattern. Let's break it down:

    • [^/]: This part matches any character except a forward slash ("/").

      • +: This indicates that the previous pattern ([^/]) should occur one or more times consecutively.

      • $: This anchors the pattern to the end of the string.

  • 0: This argument specifies the group index to return. In this case, 0 means that the entire match (the matched string) will be returned.

Explore ProductListItems Array Object

Let us access the ProductListItems array:

SELECT to_json(productListItems) FROM luma_web_data;

Hint: A single page view for Add to Cart event will have multiple product items.

To access the first elements of this array, use the following:

SELECT productListItems[0] FROM luma_web_data;

Arrays offer themselves to even more interesting SQL queries. Arrays can be exploded i.e. each element of the array can be put into a separate row of a new table and other columns/fields will be duplicated:

SELECT explode(productListItems) FROM luma_web_data;

Hint: You can also unnestfunction instead of explode.

Explore commerce Object

Let us now explore the commerce object:

SELECT to_json(commerce) FROM luma_web_data;

commerce object shows some commerce-related actions such as checkouts that thewebPageDetails object does not have.

Let us reformat this object so that we can extract the commerce event types such as productViews, productListAdds and checkouts as strings. I want to do this because I want to use GROUP BY on these event types later on. The fact that some of them are populated while some are not indicates that this is a nested structure and we will have no choice but to look at the commerce object itself in the XDM schema.

First, let us extract these fields as strings:

SELECT (CASE
        WHEN commerce.checkouts.`value`==1 THEN 'checkouts'
        WHEN commerce.productViews.`value`==1 THEN 'productViews'
        WHEN commerce.productListAdds.`value`==1 THEN 'productListAdds'
    END) AS commmerce_event_type
FROM luma_web_data

The results are:

Note: The syntax of commerce.checkouts.`value`==1. Here value has got two single opening quotation marks. This is to avoid conflict of value as a RESERVED keyword. The same will apply for commerce.`order`.* as well.

But our string-based approach has a serious flaw. If you check the field group commerce, you will see a lot of commerce event types. There is no guarantee that we will only see the 3 eveent types that we identified above:

To extract an arbitrary field name of the structs present in the commerce object, we will use:

SELECT commerce_event_type[0] AS commerce_event_type FROM (SELECT json_object_keys(to_json(commerce)) AS commerce_event_type FROM luma_web_data);

The result will be:

Note the following:

1. json_to_keys extracts the top-level keys of the JSON objects present in commerce.

2. to_json converts the JSON object to a string.

3. commerce_event_type[0] extracts the first and only element of this array.

4. Note that different structs in the commerce object have different values. Page view type structs will have a value equal to 1 while purchase type structs will have purchase totals. This extraction only works for extracting the commerce event types but does not extrapolate to the metadata of those events.

Alternatively, we could have simplified this query by avoiding the outer SELECT query by simply doing the following which will help us later:

SELECT json_object_keys(to_json(commerce))[0] AS commerce_event_type FROM luma_web_data

Explore endUserIDs Object

Let us also check the endUserIDs

SELECT to_json(endUserIDs) FROM luma_web_data;

We can extract the email addresses by using:

SELECT endUserIDs._experience.emailid.id FROM luma_web_data;

We can extract the mcids by using

SELECT endUserIDs._experience.mcid.id FROM luma_web_data;

The results are:

Create a Semi-Flat View of the Adobe Analytics Data

Let us take the queries that we built and put them all together to create a SQL query that creates a somewhat flat structure of the data i.e. we will not expand ProductListItems.

CREATE TEMP TABLE Adobe_Analytics_View AS
SELECT _id AS event_id, 
       `timestamp` AS `TimeStamp`,   
       endUserIDs._experience.mcid.id AS mcid_id,
       endUserIDs._experience.emailid.id  AS email_id,
       web.webPageDetails.`name` AS WebPageName,
       json_object_keys(to_json(commerce))[0] AS commerce_event_type,
       productListItems AS productListItems,
       marketing.campaignName AS campaignName,
       channel._id AS campaign_tracking_code, 
       regexp_extract(channel._type, '[^/]+$', 0) AS channel_type,
       channel.mediaType AS channel_category
FROM luma_web_data;

SELECT * FROM  Adobe_Analytics_View 

Note the following:

1. We have assembled all the 1-dimensional SELECT queries into a view.

2. The view is semi-flat because ProductListItems is not flattened i.e. put into separate rows or columns.

3. We use a CREATE TEMP TABLE to store this view instead of materializing the view immediately because we want TEMP tables to be cached in Data Distiller for fast exploration.

Tip: If you want fast exploration of data in ad hoc query engine, just create a TEMP TABLE with the data that you want to explore. Remember that these temp tables are wiped after the user session ends as the cache is ephemeral.

Warning: If you are on and off, DBVisualizer will disconnect from Data Distiller. In that case, it will complain that the temp table does not exist because your session needs to be reestablished. In such situations where you cannot maintain the connectivity for long periods of time, you are better off just using CREATE TABLE which will materialize the data onto the Data Lake.

If you decide to use CREATE TABLE:

CREATE TABLE Adobe_Analytics_View AS
SELECT _id AS event_id, 
       `timestamp` AS `TimeStamp`,   
       endUserIDs._experience.mcid.id AS mcid_id,
       endUserIDs._experience.emailid.id  AS email_id,
       web.webPageDetails.`name` AS WebPageName,
       json_object_keys(to_json(commerce))[0] AS commerce_event_type,
       productListItems AS productListItems,
       marketing.campaignName AS campaignName,
       channel._id AS campaign_tracking_code, 
       regexp_extract(channel._type, '[^/]+$', 0) AS channel_type,
       channel.mediaType AS channel_category
FROM luma_web_data;

SELECT * FROM  Adobe_Analytics_View 

Regardless of what you use, the results of the query look like:

With this view, you are now set to do any kind of analysis. The methodology shown above can be applied to any schemas we get from the Adobe Apps.

Appendix: Adobe App Schemas to Explore

The skills that you have learned in this module should set you up for success with any complex dataset that you will come across in the Adobe ecosystem.

Adobe Journey Optimizer

If you are interested in Adobe Journey Optimizer, you should explore this module:

Adobe Commerce

You should also explore Adobe Commerce with Adobe Experience Platform integration. Specifically, you need to be aware of this:

1. The Adobe Commerce field groups (JSON fields as a group) are very similar to that of Adobe Analytics schema.

2. There are some field groups that are unique to Adobe Commerce because of the nature of the storefront setup.

3. There can always be custom events that are unique to an industry or implementation.

Adobe Campaign

You can bring Campaign V8 delivery logs into the Adobe Experience Platform.

Marketo Engage

You can bring in all of these datasets from Marketo:

You can also bring in custom activity data as well.

Appendix: Array Operations

These are the array functions supported in Data Distiller:

• size() to determine the number of elements in a list (array)

• The bracket [] notation to access specific elements in arrays

• transform() to apply a transformation to all elements in an array

• explode() to transform elements in a list into single rows

• posexplode() to transform elements in a list into single rows along with a column for the index the element had in the original list

• array_contains() to determine if an array contains a specific element

• array_distinct() to remove duplicates from an array

• array_except() to subtract to arrays

• array_intersect() to determine the intersection (overlapping elements) of two arrays

• array_union() to determine the union of two arrays without duplicates

• array_join() to concatenate the elements of an array using a delimiter

• array_max() to get the largest element from an array

• array_min() to get the smallest element from an array

• array_position() to a specific element from an array counting starting with 1

• array_remove() to remove a specific element from an array

• array_repeat() to repeat the elements of an array a specific number of times

• array_sort() to sort an array

• arrays_overlap() to check if two arrays have at least one common element