Edit

Share via

funnel_sequence_completion plugin

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer

Calculates a funnel of completed sequence steps while comparing different time periods. The plugin is invoked with the evaluate operator.

Syntax

T | evaluate funnel_sequence_completion(IdColumn, TimelineColumn, Start, End, BinSize, StateColumn, Sequence, MaxSequenceStepWindows)

Learn more about syntax conventions.

Parameters

Name Type Required Description
T string ✔️ The input tabular expression.
IdColum string ✔️ The column reference representing the ID. The column must be present in T.
TimelineColumn string ✔️ The column reference representing the timeline. The column must be present in T.
Start datetime, timespan, or long ✔️ The analysis start period.
End datetime, timespan, or long ✔️ The analysis end period.
BinSize datetime, timespan, or long ✔️ The analysis window size. Each window is analyzed separately.
StateColumn string ✔️ The column reference representing the state. The column must be present in T.
Sequence dynamic ✔️ An array with the sequence values that are looked up in StateColumn.
MaxSequenceStepPeriods dynamic ✔️ An array with the values of the max allowed timespan between the first and last sequential steps in the sequence. Each period in the array generates a funnel analysis result.

Returns

Returns a single table useful for constructing a funnel diagram for the analyzed sequence:

  • TimelineColumn: the analyzed time window (bin), each bin in the analysis timeframe (Start to End) generates a funnel analysis separately.
  • StateColumn: the state of the sequence.
  • Period: the maximal period allowed for completing steps in the funnel sequence measured from the first step in the sequence. Each value in MaxSequenceStepPeriods generates a funnel analysis with a separate period.
  • dcount: distinct count of IdColumn in time window that transitioned from first sequence state to the value of StateColumn.

Examples

The following examples show how to use the funnel_sequence_completion plugin to analyze storm events. The query checks the completion funnel of the sequence: Hail -> Tornado -> Thunderstorm Wind in "overall" time of 1hour, 4hours, 1day.

Run the query

let _start = datetime(2007-01-01);
let _end =  datetime(2008-01-01);
let _windowSize = 365d;
let _sequence = dynamic(['Hail', 'Tornado', 'Thunderstorm Wind']);
let _periods = dynamic([1h, 4h, 1d]);
StormEvents
| evaluate funnel_sequence_completion(EpisodeId, StartTime, _start, _end, _windowSize, EventType, _sequence, _periods)

Output

StartTime EventType Period dcount
2007-01-01 00:00:00.0000000 Hail 01:00:00 2877
2007-01-01 00:00:00.0000000 Tornado 01:00:00 208
2007-01-01 00:00:00.0000000 Thunderstorm Wind 01:00:00 87
2007-01-01 00:00:00.0000000 Hail 04:00:00 2877
2007-01-01 00:00:00.0000000 Tornado 04:00:00 231
2007-01-01 00:00:00.0000000 Thunderstorm Wind 04:00:00 141
2007-01-01 00:00:00.0000000 Hail 1.00:00:00 2877
2007-01-01 00:00:00.0000000 Tornado 1.00:00:00 244
2007-01-01 00:00:00.0000000 Thunderstorm Wind 1.00:00:00 155

Understanding the results:
The outcome is three funnels (for periods: One hour, 4 hours, and one day). For each funnel step, a number of distinct counts of are shown. You can see that the more time is given to complete the whole sequence of Hail -> Tornado -> Thunderstorm Wind, the higher dcount value is obtained. In other words, there were more occurrences of the sequence reaching the funnel step.

Related content