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:
select funnel(<field>, step filters, showHealth=true, health="NORMAL"), * from transactions
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 aggregationquery.
There is still a maximum of 1,000 results in the UI - Data screen. If you are usingADQLvia 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)