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