Page Comparison

Number of user errors, per user

from demo.ecommerce.data
where isnotnull(clientIpAddress)
select str(clientIpAddress) as clientIp
where startswith(clientIp, "98.2")
select ifthenelse(statusCode>=400 and statusCode<500,1.0,0.0) as clientError
group every 1h by clientIp
select sum(clientError) as clientErrors

We consider user errors those events where the HTTP code = 40x

Number of application errors, per module

from demo.ecommerce.data where isnotnull(clientIpAddress) select decode(true, uri->"addtocart","addtocart", uri->"purchase","purchase", uri->"product.screen","product_details", uri->"category.screen","category_details", uri->"view","checkout", "browse") as applicationModule select ifthenelse(statusCode>=500,1.0,0.0) as applicationError group every 1h by applicationModule select sum(applicationError) as moduleErrors

Counting the HTTP codes = 50x per module of the application

Number of visits to the e-commerce site

from demo.ecommerce.data where isnotnull(clientIpAddress) select str(clientIpAddress) as clientIp group every 1h select round(hllppcount(clientIp)) as totalUsers

We will simplify this by assuming each distinct clientIpAddress is a single visit to the website

Visit / sales conversion rate

from demo.ecommerce.data where isnotnull(clientIpAddress) select str(clientIpAddress) as clientIp select decode(true, uri->"addtocart","addtocart", uri->"purchase","purchase", uri->"product.screen","product_details", uri->"category.screen","category_details", uri->"view","checkout", "browse") as applicationModule select ifthenelse(applicationModule="purchase" and method="POST" and statusCode=200,1.0,0.0) as completedPurchase group every 1h by clientIp select max(completedPurchase) as completedPurchase group every 1h select sum(completedPurchase) as totalPurchases select count() as visits select round(totalPurchases/visits*100,1) as conversionRate

Count the total number of purchases by the visits to any part of the website.

Average ticket value

from demo.ecommerce.data where isnotnull(clientIpAddress) select str(clientIpAddress) as clientIp select decode(true, uri->"addtocart","addtocart", uri->"purchase","purchase", uri->"product.screen","product_details", uri->"category.screen","category_details", uri->"view","checkout", "browse") as applicationModule select ifthenelse(applicationModule="purchase" and method="POST" and statusCode=200,1.0,0.0) as completedPurchase where completedPurchase>0 group every 1h select round(avg(timeTaken)/10,1) as averageTicket

For the sake of this example, and to give an arbitrary value to sales, we will assume the ticket price per sale corresponds to the value of the timeTaken column divided by 10