# EXPLORE 200: Exploring Behavioral Data with Data Distiller - A 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: {% content-ref url="../prep-501-ingesting-json-test-data-into-adobe-experience-platform" %} [prep-501-ingesting-json-test-data-into-adobe-experience-platform](https://data-distilller.gitbook.io/adobe-data-distiller-guide/prep-501-ingesting-json-test-data-into-adobe-experience-platform) {% endcontent-ref %} And of course, you should have: {% content-ref url="../unit-1-getting-started/prep-400-dbvisualizer-sql-editor-setup-for-data-distiller" %} [prep-400-dbvisualizer-sql-editor-setup-for-data-distiller](https://data-distilller.gitbook.io/adobe-data-distiller-guide/unit-1-getting-started/prep-400-dbvisualizer-sql-editor-setup-for-data-distiller) {% endcontent-ref %} ## **Scenario** We are going to ingest LUMA data into our test environment. This is a [fictitious online store ](https://luma.enablementadobe.com/content/luma/us/en.html)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: ```sql 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 ```sql SELECT to_json(web) FROM luma_web_data; ```

to_json is able to get information about the various fields within the web object.

Let us dig one level deeper into `webPageDetails.` We will use the dot notation to access any field in the hierarchy. ```sql SELECT web.webPageDetails FROM luma_web_data; ```

We can apply `to_json` again: ```sql SELECT web.webPageDetails FROM luma_web_data; ```

to_json can be applied at any level of the hierarchy.

`pageViews` is an object. Let us access the elements of that array ```sql SELECT to_json(web.webPageDetails.pageViews) FROM luma_web_data; ``` You will get the following:

We can access the value by: ```sql SELECT web.webPageDetails.pageViews.value FROM luma_web_data ``` And you will get:

value=1 indicates that there was a single page view.

### Explore `marketing` Object Let us work on the `marketing` object: ```sql SELECT to_json(marketing) FROM luma_web_data; ``` The results show information about campaigns:

marketing object gives information about the campaign association.

### Explore `channel` Object If you execute the following code: ```sql 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.

Duplication of fields in marketing and channel objects.

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: {% code overflow="wrap" %} ```sql SELECT channel._id AS tracking_code, regexp_extract(channel._type, '[^/]+$', 0) AS channel_type, channel.mediaType AS channel_category FROM luma_web_data ``` {% endcode %} 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: ```sql SELECT to_json(productListItems) FROM luma_web_data; ```

ProductListItems captures product information about the items added to cart or even browsed.

{% hint style="info" %} **Hint:** A single page view for ***Add to Cart*** event will have multiple product items. {% endhint %} To access the first elements of this array, use the following: ```sql SELECT productListItems[0] FROM luma_web_data; ```

Accessing the first element of an array.

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: ```sql SELECT explode(productListItems) FROM luma_web_data; ```

EXPLODE on the ProductListItems object. Array elements are put in separate rows.

{% hint style="info" %} **Hint:** You can also **`unnest`**function instead of **`explode`**`.` {% endhint %} ### Explore `commerce` Object Let us now explore the `commerce` object: ```sql SELECT to_json(commerce) FROM luma_web_data; ```

`commerce` object shows some commerce-related actions such as checkouts that the`webPageDetails` 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: ```sql 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:

Extracting field names by using CASE logic.

{% hint style="info" %} **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. {% endhint %} 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:

commerce object contains a lot of commerce object types.

To extract an arbitrary field name of the structs present in the `commerce` object, we will use: {% code overflow="wrap" %} ```sql 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); ``` {% endcode %} The result will be:

Results of using the json_keys_object function retrieves all the possible field names of the structs in the commerce object.

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: {% code overflow="wrap" %} ```sql SELECT json_object_keys(to_json(commerce))[0] AS commerce_event_type FROM luma_web_data ``` {% endcode %} ### Explore `endUserIDs` Object Let us also check the `endUserIDs` ```sql SELECT to_json(endUserIDs) FROM luma_web_data; ```

endUserIDs contains email and mmcid as the identities of the person.

We can extract the `email addresses` by using: ```sql SELECT endUserIDs._experience.emailid.id FROM luma_web_data; ```

Emails extracted from the ednUserIDs object.

We can extract the `mcids` by using ```sql SELECT endUserIDs._experience.mcid.id FROM luma_web_data; ``` The results are:

mcids extracted from the ednUserIDs object.

## 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.` ```sql 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. {% hint style="info" %} **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. {% endhint %} {% hint style="danger" %} **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. {% endhint %} If you decide to use `CREATE TABLE:` ```sql 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: {% content-ref url="broken-reference" %} [Broken link](https://data-distilller.gitbook.io/adobe-data-distiller-guide/unit-2-data-distiller-data-exploration/broken-reference) {% endcontent-ref %} ### 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](https://experienceleague.adobe.com/docs/commerce-merchant-services/experience-platform-connector/fundamentals/update-xdm.html?lang=en) (JSON fields as a group) are very similar to that of Adobe Analytics schema. 2. There are [some field groups](https://experienceleague.adobe.com/docs/commerce-merchant-services/experience-platform-connector/event-forwarding/events.html?lang=enhttps://experienceleague.adobe.com/docs/commerce-merchant-services/experience-platform-connector/event-forwarding/events.html?lang=en) that are unique to Adobe Commerce because of the nature of the storefront setup. 3. There can always be[ custom events ](https://experienceleague.adobe.com/docs/commerce-merchant-services/experience-platform-connector/event-forwarding/custom-events.html?lang=en)that are unique to an industry or implementation. ### Adobe Campaign You can bring [Campaign V8 delivery logs](https://experienceleague.adobe.com/docs/experience-platform/sources/ui-tutorials/create/adobe-applications/campaign.html?lang=en) into the Adobe Experience Platform. ### Marketo Engage You can[ bring in](https://experienceleague.adobe.com/docs/experience-platform/sources/ui-tutorials/create/adobe-applications/marketo.html?lang=en) all of these datasets from Marketo:

You can also bring in [custom activity data](https://experienceleague.adobe.com/docs/experience-platform/sources/ui-tutorials/create/adobe-applications/marketo-custom-activities.html?lang=en) 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