- Average Document Interactive Time for India
SELECT starttime, AVG(useraction.documentInteractiveTime) AS "DOM Interactive"
FROM usersession
WHERE country = "India"
GROUP BY starttime
- Average Network Time for India
SELECT starttime, AVG(useraction.networkTime) AS "Network time"
FROM usersession
WHERE country = "India"
GROUP BY starttime
- Top Server Response Time by Country
SELECT country, AVG(useraction.serverTime) AS "Servertime"
FROM usersession
GROUP BY country limit 5
- Top Visually Complete Time by Country
SELECT country, AVG(useraction.visuallyCompleteTime) AS "Visually Complete"
FROM usersession
GROUP BY country limit 5
- Frustrated User Actions by Country
SELECT country, COUNT(useraction.apdexCategory)
FROM usersession
WHERE useraction.apdexCategory = "FRUSTRATED"
GROUP BY country limit 5
- Apdex Distribution Breakdown
SELECT apdexCategory, COUNT(apdexCategory)
FROM useraction
WHERE apdexCategory != "UNKNOWN"
GROUP BY apdexCategory
- Specific Page Load Count by Country
SELECT country, COUNT(useraction.name)
FROM usersession
WHERE useraction.name = "Loading of page /"
GROUP BY country
- Specific Page Load Count by Specific Country
SELECT starttime, COUNT(useraction.name)
FROM usersession
WHERE useraction.name = "Loading of page /" AND country = "India"
GROUP BY starttime
- User Session Count for an Application
SELECT DISTINCT DATETIME(starttime, 'MM-dd', '1d'), COUNT(*)
FROM usersession
WHERE useraction.internalApplicationId = "APPLICATIONID"
- Apdex Distribution Breakdown filtered by Application
SELECT apdexCategory, COUNT(apdexCategory)
FROM useraction
WHERE apdexCategory != "UNKNOWN" AND application="appnamehere"
GROUP BY apdexCategory
- User actions and application with HTTP error counts
SELECT DISTINCT useraction.name AS "User Action Name", useraction.application AS "Application", SUM(httpRequestsWithErrors) AS "HTTP Errors"
FROM useraction WHERE httpRequestsWithErrors IS NOT NULL
GROUP BY useraction.application, useraction.name, httpRequestsWithErrors
ORDER BY sum(httpRequestsWithErrors) DESC
LIMIT 20
- Applications with HTTP errors
SELECT DISTINCT useraction.application AS "Application", SUM(httpRequestsWithErrors) AS "HTTP Errors"
FROM useraction
WHERE httpRequestsWithErrors IS NOT NULL
GROUP BY useraction.application, httpRequestsWithErrors
ORDER BY sum(httpRequestsWithErrors) DESC
LIMIT 20
- Count bounce rate of each pages
SELECT useraction.name as "Page Name", COUNT(*) as "Page Views", COUNT(DISTINCT usersession.internalUserId) AS "Unique Page Views", AVG(useraction.duration) As "Average Session", count(bounce) as "# Bounce sessions" FROM usersession WHERE bounce IS true GROUP BY useraction.name
- Users browser type and its version
SELECT COUNT(browserMajorVersion) as 'User Session Count', browserMajorVersion as 'Browser Version' FROM usersession GROUP BY browserMajorVersion
- Sales Funnel
SELECT FUNNEL(useraction.name='xxxxx' AS "Visits Store", useraction.name= 'xxxxxx' AS "Views Product", useraction.name = 'xxxxx' AS "Starts Checkout", useraction.name= 'xxxxx' AS "Offer Upsells", useraction.name = 'xxxxx' AS "Complete Purchase") FROM usersession
- Counting specific JS error on an entire application grouped per hour
select HOUR(usersession.startTime), usererror.name, count(usererror.name) from usererror where name="Script error." group by usererror.name,HOUR(usersession.startTime) order by usererror.name ASC
