Analytics search - ADQL SELECT funnel Query

[Chitra.Lal]

We now have a funnel query possible apart from using the Funnel creation from UI widget. A few examples of how it can be used are shown below: 

General syntax: 

select funnel(<field>, step filters, showHealth=true, health="NORMAL"), * from transactions

 Examples: 

SELECT funnel(transactionName, responseTime < 90, showHealth=true, health="NORMAL") FROM transactions
SELECT funnel(requestGUID, userExperience!='SLOW', userExperience='NORMAL') FROM transactions
SELECT funnel(userExperience, responseTime > 0) FROM transactions LIMIT 1000

 

 If you wish to use the funnel query, here are some key points that need to be considered: 

1. funnel by default does a COUNT DISTINCT where as a simple query like (SELECT ip, pagename , count(*) FROM browser_records WHERE pagename='Landing Page') does not.

2. Also this second query by default limits the results to the top 10 values, If you want to see more values, use the LIMIT clause, something like below 

Note: Remember though, there’s a limit to the maximum number of rows returned for an aggregation query. 

There is still a maximum of 1,000 results in the UI - Data screen. If you are using ADQL via the API then you can get a maximum of 20,000 results for non-aggregated queries and (I believe) 1,000 results for aggregated queries (thats 2k total regardless of how many levels of aggregation).

To see the visualization of this funnel query result, click on "Basic" (There is a link on the top right of this ADQL search box, named "Basic"). When you click on that after getting the result for your query, that will show you the funnel diagram and to return back to the data view similarly click on "Advanced" option. Refer to the attached screenshots for clarity.

Known issues/Limitations related to funnel:

1. Funnel Queries are timing out on high cardinality fields on OA (Already Fixed in 4.4)

Query like below:

SELECT funnel(requestGUID, userExperience IN ("NORMAL", "ERROR")) FROM transactions

is timing out (be it funnel widget or adql query) while this below query runs just fine.

SELECT funnel(userExperience, userExperience IN ("NORMAL", "ERROR")) FROM transactions

2. Funnel query if followed by fields in Select statement does not work (Fixed in 4.4)

 

A query like this does not work as of now.

SELECT funnel(userExperience, responseTime > 0), eventTimestamp, userExperience, application, transactionName, responseTime, segments.tier, segments.node FROM transactions LIMIT 1000

However, query like below Works:

SELECT funnel(userExperience, responseTime > 0) FROM transactions LIMIT 1000

 

3. Funnel query kills performance 

Executing this query kills performance in OA43 - resulting all data nodes to get around 99% CPU

Unable to find source-code formatter for language: bash. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml

SELECT funnel(segments.userData.queryStringADQL, userExperience IN ("SLOW", "VERY_SLOW", "STALL"), segments.userData.queryStringADQL *_LIKE_* "SELECT") FROM transactions WHERE application = "prd43-analytics" AND transactionName = "/events/query" LIMIT 2000

AND wildcard

Unable to find source-code formatter for language: bash. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml

SELECT funnel(segments.userData.queryStringADQL, userExperience IN ("SLOW", "VERY_SLOW", "STALL"), segments.userData.queryStringADQL = _"SELECT*"_) FROM transactions WHERE application = "prd43-analytics" AND transactionName = "/events/query" LIMIT 100