# EXPLORE 202: Exploring Product Analytics with Data Distiller

## Prerequisites

You need to make sure you complete this module and its prerequisites:

{% content-ref url="explore-200-exploring-behavioral-data-with-data-distiller-a-case-study-with-adobe-analytics-data" %}
[explore-200-exploring-behavioral-data-with-data-distiller-a-case-study-with-adobe-analytics-data](https://data-distilller.gitbook.io/adobe-data-distiller-guide/unit-2-data-distiller-data-exploration/explore-200-exploring-behavioral-data-with-data-distiller-a-case-study-with-adobe-analytics-data)
{% endcontent-ref %}

## **Scenario Recap**

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

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2F6truaKqY41U0A2QO8pze%2FScreen%20Shot%202023-08-28%20at%208.50.53%20AM.png?alt=media&#x26;token=7ab53270-4e4a-4dab-856a-119b6edd1f35" alt=""><figcaption><p>Luma website</p></figcaption></figure>

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.&#x20;

## Most Popular Products by Web Page Traffic Volume

{% code overflow="wrap" %}

```sql
SELECT Product.`name`AS ProductName, WebPageName, count(WebPageName) AS WebPageCounts FROM (SELECT WebPageName, explode(productListItems) AS Product FROM  Adobe_Analytics_View) 
GROUP BY WebPageName, Product.`name`
ORDER BY WebPageCounts DESC
```

{% endcode %}

We just exploded i.e. created a row for each item in `productListItems` and then aggregated the web page count. Then we grouped by web page and product name.

The results are:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FW0Fsqo8IqCPti6iawjPR%2FScreen%20Shot%202023-08-30%20at%2010.55.27%20PM.png?alt=media&#x26;token=68be4b3f-d5c9-483f-8708-2ca5a7af99ac" alt=""><figcaption><p>Most popular products by web page traffic volume</p></figcaption></figure>

## Most Popular Products by Revenue

First, let us find the most popular products by price totals for all possible commerce event types:

{% code overflow="wrap" %}

```sql
SELECT Product.`name`AS ProductName, SUM(Product.priceTotal) AS ProductRevenue,  WebPageName, count(WebPageName), commerce_event_type FROM (SELECT WebPageName, explode(productListItems) AS Product, commerce_event_type FROM  Adobe_Analytics_View) 
GROUP BY WebPageName, Product.`name`, commerce_event_type
ORDER BY ProductRevenue DESC
```

{% endcode %}

Here are the results:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FIyCA5HVILiWgaZiCqwod%2FScreen%20Shot%202023-08-30%20at%2011.14.16%20PM.png?alt=media&#x26;token=7f83f03e-6b9d-4b48-beba-14cdc4e25e61" alt=""><figcaption><p>Product revenue across all commerce event types</p></figcaption></figure>

If you inspect the `webPageName` or `commerce_event_type`,you will observe that "order" is the event type we are looking for.

{% code overflow="wrap" %}

```sql
SELECT Product.`name`AS ProductName, round(SUM(Product.priceTotal)) AS ProductRevenue,  WebPageName, count(WebPageName), commerce_event_type FROM (SELECT WebPageName, explode(productListItems) AS Product, commerce_event_type FROM  Adobe_Analytics_View) 
WHERE commerce_event_type='order'
GROUP BY WebPageName, Product.`name`, commerce_event_type
ORDER BY ProductRevenue DESC
```

{% endcode %}

We used `round` to round up the decimals and filtered by the `order` commerce event type.

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FVPvkoqNNX3sR8PNpcQkT%2FScreen%20Shot%202023-08-30%20at%2011.17.53%20PM.png?alt=media&#x26;token=34db8b0f-9968-4577-afe9-0f3e1d4793df" alt=""><figcaption><p>Most popular products are not necssarily the most popular web pages.</p></figcaption></figure>

## Funnel Analysis

I am now curious as to what are the different stages that my customers are going through on my website:

{% code overflow="wrap" %}

```sql
SELECT commerce_event_type AS Customer_Stages, COUNT(commerce_event_type) FROM  Adobe_Analytics_View 
GROUP BY commerce_event_type 
```

{% endcode %}

We get the following:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FhuBlP0rNdAscDjTYaytk%2FScreen%20Shot%202023-08-31%20at%2010.08.27%20AM.png?alt=media&#x26;token=363c41af-4f21-4542-8ef0-ea6caa29f92b" alt=""><figcaption><p>Funnel stages as indicated by commerce event types.</p></figcaption></figure>

The decrease in the page counts for the various stages shows what we would have expected. Notice some weird things about the data: Luma customers do seem very eager to add items to their wishlist (at least 33% conversion from viewing a page), at least 50% of those that add to a wishlist seem to checkout and 50% of them do place an order. If there was one thing I would fix, I would fix the checkout-to-order conversion rate to be higher.&#x20;

But wait, how can someone checkout without adding items to a cart?

And that information is there in `WebPageName` query:

```sql
SELECT WebPageName, COUNT(WebPageName) AS WebPageCounts 
FROM Adobe_Analytics_View 
WHERE WebPageName IN ('order', 'checkout', 'addToCart')
GROUP BY WebPageName
ORDER BY WebPageCounts DESC;
```

The results are:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FEEGkccdlK222jlyFs5Af%2FScreen%20Shot%202023-08-31%20at%2012.37.13%20PM.png?alt=media&#x26;token=86d4bebb-6653-4100-856b-6cd1cf1e6a8d" alt=""><figcaption><p>WebPageName query gives infromation about addToCart.</p></figcaption></figure>

I chose `order`, `checkout` and `addToCart` because all the other web pages are just product pages. Note that the numbers for `checkout` and `order` match perfectly with our commerce query. The web page column does not have information about the `ProductListAdds.` As an analyst, you may assume that the data is to be trusted but here in this example, it did not make sense that an add-to-cart step was missing.&#x20;

Let us put these funnel stages together in a query:

{% code overflow="wrap" %}

```sql
SELECT commerce_event_type AS Funnel_Stage, COUNT(commerce_event_type) AS Count
FROM Adobe_Analytics_View 
GROUP BY commerce_event_type 

UNION ALL

SELECT WebPageName AS Funnel_Stage, COUNT(WebPageName) AS Count
FROM Adobe_Analytics_View 
WHERE WebPageName IN ('order', 'checkout', 'addToCart')
GROUP BY WebPageName

ORDER BY Count DESC;
```

{% endcode %}

The results will be:\ <br>

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FzaDrS0OCu89U6PSrc2lB%2FScreen%20Shot%202023-08-31%20at%202.59.46%20PM.png?alt=media&#x26;token=5241a533-8b17-4540-a723-c1a4abaf0a14" alt=""><figcaption><p>Unioning of two datasets gets us all the stages possible.</p></figcaption></figure>

The results show that ProductListAdds is indeed equivalent to "addToCart". ProductListAdds is not the addition to the product wish list as we had assumed. Our analysis is helping us reconcile the differences in the data modeling present in the data.