topic Re: Funnels from DQL on the new dashboards in DQL

Create Funnels in New Dashboards Using DQL Queries

Julius_Loman — Wed, 17 Dec 2025 13:23:40 GMT

Hello,

Are there any recommendations on how to have funnel-like visualisations from DQL source? Typically business events now, probably user sessions soon.

I'm looking for some practice close to what funnels in USQL currently are, which means:

count only consecutive events per correlation id (e.g. session)
visualization (probably bar chart or pie chart is now the only way)

Bizflow app is not an option in my case for several reasons:

It is not flexible enough (only allows to select events by event.type without any further conditions)
It is not possible to pin the funnel on a dashboard

Update:
The best I was able to do at the moment follows:

Example in the playground environment.

If a specific order of steps is required:

data record(timestamp = now()-30m, session="session1", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-25m, session="session1", `event.provider`="myapp", `event.type`="place_order"), record(timestamp = now()-20m, session="session1", `event.provider`="myapp", `event.type`="payment"), record(timestamp = now()-30m, session="session2", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-25m, session="session2", `event.provider`="myapp", `event.type`="place_order"), // this will not be counted in the funnel record(timestamp = now()-30m, session="session_without_first_event", `event.provider`="myapp", `event.type`="place_order"), record(timestamp = now()-25m, session="session_without_first_event", `event.provider`="myapp", `event.type`="payment"), // session with incorrect order record(timestamp = now()-20m, session="session_with_incorrect_order", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-25m, session="session_with_incorrect_order", `event.provider`="myapp", `event.type`="place_order"), record(timestamp = now()-30m, session="session_with_incorrect_order", `event.provider`="myapp", `event.type`="payment") | filter startsWith(event.provider,"myapp") | sort session, timestamp asc // Summarize event by session into an array, so we have steps in an array for each session | summarize steps = collectArray(event.type), by:{ session } // Create position of each step | fieldsAdd `add_to_cart` = arrayIndexOf(steps,"add_to_cart"), `place_order` = arrayIndexOf(steps,"place_order"), `payment` = arrayIndexOf(steps,"payment") // Replace -1 with null so we can compare order | fieldsAdd `add_to_cart` = if(`add_to_cart`>=0, `add_to_cart`, else: null), `place_order` = if(`place_order`>=0, `place_order`, else: null), `payment` = if(`payment`>=0, `payment`, else: null) // Summarize and create a funnel array of steps | summarize funnel = array ( record(step="Add to cart", count=countIf(`add_to_cart`>=0)), record(step="Place order", count=countIf(`add_to_cart`>=0 and `place_order`>`add_to_cart`)), record(step="Payment", count=countIf(`add_to_cart`>=0 and `place_order`>`add_to_cart` and `payment`>`place_order`)) ) // // Expand events into separate records | expand funnel | fieldsFlatten funnel | fieldsRemove funnel

Example in the playground

Does anyone know a better solution? @KlausEnzenhofer

Re: Funnels from DQL on the new dashboards

DanielS — Tue, 09 Sep 2025 23:51:45 GMT

Great Work @Julius_Loman . I've only added percentage to the code:

data record(timestamp = now()-30m, session="session1", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-25m, session="session1", `event.provider`="myapp", `event.type`="place_order"), record(timestamp = now()-20m, session="session1", `event.provider`="myapp", `event.type`="payment"), record(timestamp = now()-30m, session="session2", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-25m, session="session2", `event.provider`="myapp", `event.type`="place_order"), // this will not be counted in the funnel record(timestamp = now()-30m, session="session_without_first_event", `event.provider`="myapp", `event.type`="place_order"), record(timestamp = now()-25m, session="session_without_first_event", `event.provider`="myapp", `event.type`="payment"), // session with incorrect order record(timestamp = now()-20m, session="session_with_incorrect_order", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-25m, session="session_with_incorrect_order", `event.provider`="myapp", `event.type`="place_order"), record(timestamp = now()-30m, session="session_with_incorrect_order", `event.provider`="myapp", `event.type`="payment") | filter startsWith(event.provider,"myapp") | sort session, timestamp asc // Summarize event by session into an array, so we have steps in an array for each session | summarize steps = collectArray(event.type), by:{ session } // Create position of each step | fieldsAdd `add_to_cart` = arrayIndexOf(steps,"add_to_cart"), `place_order` = arrayIndexOf(steps,"place_order"), `payment` = arrayIndexOf(steps,"payment") // Replace -1 with null so we can compare order | fieldsAdd `add_to_cart` = if(`add_to_cart`>=0, `add_to_cart`, else: null), `place_order` = if(`place_order`>=0, `place_order`, else: null), `payment` = if(`payment`>=0, `payment`, else: null) // Summarize and create a funnel array of steps | summarize total_paso_0 = toDouble(countIf(`add_to_cart`>=0)), funnel = array ( record(step="Add to cart", count=countIf(`add_to_cart`>=0)), record(step="Place order", count=countIf(`add_to_cart`>=0 and `place_order`>`add_to_cart`)), record(step="Payment", count=countIf(`add_to_cart`>=0 and `place_order`>`add_to_cart` and `payment`>`place_order`)) ) // // Expand events into separate records | expand funnel | fieldsFlatten funnel | fieldsRemove funnel // Calculate percentage | fieldsAdd percentage = round((toDouble(`funnel.count`)/total_paso_0)*100, decimals: 2) // Select, rename, and order the columns | fields step = `funnel.step`, count = `funnel.count`, percentage

Re: Funnels from DQL on the new dashboards

krzysztof_hoja — Wed, 10 Sep 2025 17:52:34 GMT

Here is my idea for more generic query where "ifs" for each step are not needed for 1st case, so order is not obligatory (repetitions are allowed):

data record(timestamp = now()-30m, session="session0", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-30m, session="session1", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-25m, session="session1", `event.provider`="myapp", `event.type`="place_order"), record(timestamp = now()-20m, session="session1", `event.provider`="myapp", `event.type`="payment"), record(timestamp = now()-30m, session="session2", `event.provider`="myapp", `event.type`="add_to_cart"), record(timestamp = now()-25m, session="session2", `event.provider`="myapp", `event.type`="place_order"), // this will not be counted in the funnel record(timestamp = now()-30m, session="session_without_first_event", `event.provider`="myapp", `event.type`="place_order"), record(timestamp = now()-25m, session="session_without_first_event", `event.provider`="myapp", `event.type`="payment") // Summarize event by session into an array, so we have steps in an array for each session | summarize steps = collectArray(event.type), by:{ session } | fieldsAdd stepsOrder=array(record(id="add_to_cart", name="Add to cart"), record(id="place_order", name="Place order"), record(id="payment", name="Payment")) | fieldsAdd p = stepsOrder[][id] | fieldsAdd p = arrayRemoveNulls(iCollectArray(if(arrayIndexOf(steps, p[])>-1, record(name=stepsOrder[][name], index=iIndex())))) | filterOut iAny(p[][index]!=iIndex()) | expand p | summarize { cnt = count() }, by: { index=p[index], name=p[name] } | sort index asc

Funnel definion is an array (stepsOrder) with records carrying identification of step and its name. Order in array also defines oder is steps in funnel.

Fist step is to collect step ids for each session.

Next step is get array of identified steps:

if(arrayIndexOf(steps, p[])>-1, record(name=stepsOrder[][name], index=iIndex()))

If step was present we get record with step name and its index.

If we eliminate nulls from the table and intermediate step was missing, index remembered will be different actual in the table. This can be used to eliminate sessions with holes

| filterOut iAny(p[][index]!=iIndex())

Last part is to expand and count steps. Because we were checking template vs. actual steps, duplicates are not counted. And finally we can just sort by step index.

Re visualization: IMHO most appropiate way to show funnel out of existing ones is "categorical chart". It shows data in order as provided, so funnel should be also visible

Note: I used string as id as in original example, but it does not have to be simple type. In more advanced cases we can construct id as a complex record in our funnel definition and in actual data. Finding such ids in array works too.

I will also think about case where order os steps is important.