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 sankey diagrams 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

The following examples show how to use the funnel_sequence plugin to analyze storm events. The 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.