Funnels from DQL on the new dashboards

[Julius_Loman]

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:

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")   
| filter startsWith(event.provider,"myapp")

// Create boolean fields if step exists in a session
| summarize {
    `add_to_cart` = countIf(event.type=="add_to_cart")>0,
    `place_order` = countIf(event.type=="place_order")>0,
    `payment` = countIf(event.type=="payment")>0
  }, by: { session }
// Summarize events
| summarize 
  funnel = array (
    record(step="Add to cart", count=countIf(`add_to_cart`)),
    record(step="Place order", count=countIf(`add_to_cart` and `place_order`)),
    record(step="Payment", count=countIf(`add_to_cart` and `place_order` and `payment`))
  )
// Expand events into separate records
| expand funnel
| fieldsFlatten funnel
| fieldsRemove funnel

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 

[DanielS]

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

[krzysztof_hoja]

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.