EXPLORE 202: Exploring Product Analytics with Data Distiller

Prerequisites

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

Scenario Recap

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.

Most Popular Products by Web Page Traffic Volume

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

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:

Most Popular Products by Revenue

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

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

Here are the results:

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

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

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

Funnel Analysis

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

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

We get the following:

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.

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

And that information is there in WebPageName query:

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:

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.

Let us put these funnel stages together in a query:

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;

The results will be:

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.