funnel_sequence plugin

Calculates distinct count of users who have taken a sequence of states, and the distribution of previous/next states that have led to/were followed by the sequence. The plugin is invoked with the evaluate operator.

Syntax

T | evaluate funnel_sequence(IdColumn, TimelineColumn, Start, End, MaxSequenceStepWindow, Step, StateColumn, Sequence)

Parameters

Returns

Returns three output tables, which are useful for constructing a sankey diagram for the analyzed sequence:

• Table #1 - prev-sequence-next dcount

  • TimelineColumn: the analyzed time window
  • prev: the prev state (may be empty if there were any users that only had events for the searched sequence, but not any events prior to it).
  • next: the next state (may be empty if there were any users that only had events for the searched sequence, but not any events that followed it).
  • dcount: distinct count of IdColumn in time window that transitioned prev –> Sequence –> next.
  • samples: an array of IDs (from IdColumn) corresponding to the row’s sequence (a maximum of 128 IDs are returned).

• Table #2 - prev-sequence dcount

  • TimelineColumn: the analyzed time window
  • prev: the prev state (may be empty if there were any users that only had events for the searched sequence, but not any events prior to it).
  • dcount: distinct count of IdColumn in time window that transitioned prev –> Sequence –> next.
  • samples: an array of IDs (from IdColumn) corresponding to the row’s sequence (a maximum of 128 IDs are returned).

• Table #3 - sequence-next dcount

  • TimelineColumn: the analyzed time window
  • next: the next state (may be empty if there were any users that only had events for the searched sequence, but not any events that followed it).
  • dcount: distinct count of IdColumn in time window that transitioned prev –> Sequence –> next.
  • samples: an array of IDs (from IdColumn) corresponding to the row’s sequence (a maximum of 128 IDs are returned).

Examples

Exploring storm events

The following query looks at the table StormEvents (weather statistics for 2007) and shows which events happened before/after all Tornado events occurred in 2007.

// Looking on StormEvents statistics: 
// Q1: What happens before Tornado event?
// Q2: What happens after Tornado event?
StormEvents
| evaluate funnel_sequence(EpisodeId, StartTime, datetime(2007-01-01), datetime(2008-01-01), 1d,365d, EventType, dynamic(['Tornado']))

Result includes three tables:

• Table #1: All possible variants of what happened before and after the sequence. For example, the second line means that there were 87 different events that had following sequence: Hail -> Tornado -> Hail

• Table #2: shows all distinct events grouped by the previous event. For example, the second line shows that there were a total of 150 events of Hail that happened just before Tornado.

• Table #3: shows all distinct events grouped by next event. For example, the second line shows that there were a total of 143 events of Hail that happened after Tornado.

Now, let’s try to find out how the following sequence continues:
Hail -> Tornado -> Thunderstorm Wind

StormEvents
| evaluate funnel_sequence(
               EpisodeId,
               StartTime,
               datetime(2007-01-01),
               datetime(2008-01-01),
               1d,
               365d,
               EventType, 
               dynamic(['Hail', 'Tornado', 'Thunderstorm Wind'])
           )

Skipping Table #1 and Table #2, and looking at Table #3, we can conclude that sequence Hail -> Tornado -> Thunderstorm Wind in 92 events ended with this sequence, continued as Hail in 41 events, and turned back to Tornado in 14.