User and sequence analytics plugins

• 1: active_users_count plugin
• 2: activity_counts_metrics plugin
• 3: activity_engagement plugin
• 4: activity_metrics plugin
• 5: funnel_sequence plugin
• 6: funnel_sequence_completion plugin
• 7: new_activity_metrics plugin
• 8: rolling_percentile plugin
• 9: rows_near plugin
• 10: sequence_detect plugin
• 11: session_count plugin
• 12: sliding_window_counts plugin
• 13: User Analytics

1 - active_users_count plugin

Calculates distinct count of values, where each value has appeared in at least a minimum number of periods in a lookback period.

Useful for calculating distinct counts of “fans” only, while not including appearances of “non-fans”. A user is counted as a “fan” only if it was active during the lookback period. The lookback period is only used to determine whether a user is considered active (“fan”) or not. The aggregation itself doesn’t include users from the lookback window. In comparison, the sliding_window_counts aggregation is performed over a sliding window of the lookback period.

Syntax

T | evaluate active_users_count(IdColumn, TimelineColumn, Start, End, LookbackWindow, Period, ActivePeriodsCount, Bin , [dim1, dim2, …])

Parameters

Returns

Returns a table that has distinct count values for IDs that have appeared in ActivePeriodCounts in the following periods: the lookback period, each timeline period, and each existing dimensions combination.

Output table schema is:

Examples

Calculate weekly number of distinct users that appeared in at least three different days over a period of prior eight days. Period of analysis: July 2018.

let Start = datetime(2018-07-01);
let End = datetime(2018-07-31);
let LookbackWindow = 8d;
let Period = 1d;
let ActivePeriods = 3;
let Bin = 7d;
let T =  datatable(User:string, Timestamp:datetime)
[
    "B",      datetime(2018-06-29),
    "B",      datetime(2018-06-30),
    "A",      datetime(2018-07-02),
    "B",      datetime(2018-07-04),
    "B",      datetime(2018-07-08),
    "A",      datetime(2018-07-10),
    "A",      datetime(2018-07-14),
    "A",      datetime(2018-07-17),
    "A",      datetime(2018-07-20),
    "B",      datetime(2018-07-24)
];
T | evaluate active_users_count(User, Timestamp, Start, End, LookbackWindow, Period, ActivePeriods, Bin)

Output

A user is considered active if it fulfills both of the following criteria:

• The user was seen in at least three distinct days (Period = 1d, ActivePeriods=3).
• The user was seen in a lookback window of 8d before and including their current appearance.

In the illustration below, the only appearances that are active by this criteria are the following instances: User A on 7/20 and User B on 7/4 (see plugin results above). The appearances of User B are included for the lookback window on 7/4, but not for the Start-End time range of 6/29-30.

2 - activity_counts_metrics plugin

Calculates useful activity metrics for each time window compared/aggregated to all previous time windows. Metrics include: total count values, distinct count values, distinct count of new values, and aggregated distinct count. Compare this plugin to activity_metrics plugin, in which every time window is compared to its previous time window only.

Syntax

T | evaluate activity_counts_metrics(IdColumn, TimelineColumn, Start, End, Step [, Dimensions])

Parameters

Returns

Returns a table that has the total count values, distinct count values, distinct count of new values, and aggregated distinct count for each time window. If Dimensions are provided, then there’s another column for each dimension in the output table.

The following table describes the output table schema.

Examples

Daily activity counts

The next query calculates daily activity counts for the provided input table.

let start=datetime(2017-08-01);
let end=datetime(2017-08-04);
let window=1d;
let T = datatable(UserId:string, Timestamp:datetime)
[
'A', datetime(2017-08-01),
'D', datetime(2017-08-01),
'J', datetime(2017-08-01),
'B', datetime(2017-08-01),
'C', datetime(2017-08-02),
'T', datetime(2017-08-02),
'J', datetime(2017-08-02),
'H', datetime(2017-08-03),
'T', datetime(2017-08-03),
'T', datetime(2017-08-03),
'J', datetime(2017-08-03),
'B', datetime(2017-08-03),
'S', datetime(2017-08-03),
'S', datetime(2017-08-04),
];
 T
 | evaluate activity_counts_metrics(UserId, Timestamp, start, end, window)

Output

3 - activity_engagement plugin

Calculates activity engagement ratio based on ID column over a sliding timeline window.

The activity_engagement plugin can be used for calculating DAU/WAU/MAU (daily/weekly/monthly activities).

Syntax

T | evaluate activity_engagement(IdColumn, TimelineColumn, [Start, End,] InnerActivityWindow, OuterActivityWindow [, dim1, dim2, …])

Parameters

Returns

Returns a table that has a distinct count of ID values inside an inner-scope window, inside an outer-scope window, and the activity ratio for each inner-scope window period for each existing dimensions combination.

Output table schema is:

Examples

DAU/WAU calculation

The following example calculates DAU/WAU (Daily Active Users / Weekly Active Users ratio) over a randomly generated data.

// Generate random data of user activities
let _start = datetime(2017-01-01);
let _end = datetime(2017-01-31);
range _day from _start to _end  step 1d
| extend d = tolong((_day - _start)/1d)
| extend r = rand()+1
| extend _users=range(tolong(d*50*r), tolong(d*50*r+100*r-1), 1) 
| mv-expand id=_users to typeof(long) limit 1000000
// Calculate DAU/WAU ratio
| evaluate activity_engagement(['id'], _day, _start, _end, 1d, 7d)
| project _day, Dau_Wau=activity_ratio*100 
| render timechart 

:::image type=“content” source=“media/activity-engagement-plugin/activity-engagement-dau-wau.png” border=“false” alt-text=“Graph displaying the ratio of daily active users to weekly active users as specified in the query.”:::

DAU/MAU calculation

The following example calculates DAU/WAU (Daily Active Users / Weekly Active Users ratio) over a randomly generated data.

// Generate random data of user activities
let _start = datetime(2017-01-01);
let _end = datetime(2017-05-31);
range _day from _start to _end  step 1d
| extend d = tolong((_day - _start)/1d)
| extend r = rand()+1
| extend _users=range(tolong(d*50*r), tolong(d*50*r+100*r-1), 1) 
| mv-expand id=_users to typeof(long) limit 1000000
// Calculate DAU/MAU ratio
| evaluate activity_engagement(['id'], _day, _start, _end, 1d, 30d)
| project _day, Dau_Mau=activity_ratio*100 
| render timechart 

:::image type=“content” source=“media/activity-engagement-plugin/activity-engagement-dau-mau.png” border=“false” alt-text=“Graph displaying the ratio of daily active users to monthly active users as specified in the query.”:::

DAU/MAU calculation with additional dimensions

The following example calculates DAU/WAU (Daily Active Users / Weekly Active Users ratio) over a randomly generated data with additional dimension (mod3).

// Generate random data of user activities
let _start = datetime(2017-01-01);
let _end = datetime(2017-05-31);
range _day from _start to _end  step 1d
| extend d = tolong((_day - _start)/1d)
| extend r = rand()+1
| extend _users=range(tolong(d*50*r), tolong(d*50*r+100*r-1), 1) 
| mv-expand id=_users to typeof(long) limit 1000000
| extend mod3 = strcat("mod3=", id % 3)
// Calculate DAU/MAU ratio
| evaluate activity_engagement(['id'], _day, _start, _end, 1d, 30d, mod3)
| project _day, Dau_Mau=activity_ratio*100, mod3 
| render timechart 

:::image type=“content” source=“media/activity-engagement-plugin/activity-engagement-dau-mau-mod3.png” border=“false” alt-text=“Graph displaying the ratio of daily active users to monthly active users with modulo 3 as specified in the query.”:::

4 - activity_metrics plugin

Calculates useful metrics that include distinct count values, distinct count of new values, retention rate, and churn rate. This plugin is different from activity_counts_metrics plugin in which every time window is compared to all previous time windows.

Syntax

T | evaluate activity_metrics(IdColumn, TimelineColumn, [Start, End,] Window [, dim1, dim2, …])

Parameters

Returns

The plugin returns a table with the distinct count values, distinct count of new values, retention rate, and churn rate for each timeline period for each existing dimensions combination.

Output table schema is:

Notes

Retention Rate Definition

Retention Rate over a period is calculated as:

where the # of customers returned during the period is defined as:

Retention Rate can vary from 0.0 to 1.0 A higher score means a larger number of returning users.

Churn Rate Definition

Churn Rate over a period is calculated as:

where the # of customer lost in the period is defined as:

Churn Rate can vary from 0.0 to 1.0 The higher score means the larger number of users are NOT returning to the service.

Churn vs. Retention Rate The churn vs. retention Rate is derived from the definition of Churn Rate and Retention Rate. The following calculation is always true:

Examples

Weekly retention rate and churn rate

The next query calculates retention and churn rate for week-over-week window.

// Generate random data of user activities
let _start = datetime(2017-01-02);
let _end = datetime(2017-05-31);
range _day from _start to _end  step 1d
| extend d = tolong((_day - _start)/1d)
| extend r = rand()+1
| extend _users=range(tolong(d*50*r), tolong(d*50*r+200*r-1), 1)
| mv-expand id=_users to typeof(long) limit 1000000
//
| evaluate activity_metrics(['id'], _day, _start, _end, 7d)
| project _day, retention_rate, churn_rate
| render timechart

Output

:::image type=“content” source=“media/activity-metrics-plugin/activity-metrics-churn-and-retention.png” border=“false” alt-text=“Table showing the calculated retention and churn rates per seven days as specified in the query.”:::

Distinct values and distinct ’new’ values

The next query calculates distinct values and ’new’ values (IDs that didn’t appear in previous time window) for week-over-week window.

// Generate random data of user activities
let _start = datetime(2017-01-02);
let _end = datetime(2017-05-31);
range _day from _start to _end  step 1d
| extend d = tolong((_day - _start)/1d)
| extend r = rand()+1
| extend _users=range(tolong(d*50*r), tolong(d*50*r+200*r-1), 1)
| mv-expand id=_users to typeof(long) limit 1000000
//
| evaluate activity_metrics(['id'], _day, _start, _end, 7d)
| project _day, dcount_values, dcount_newvalues
| render timechart

Output

:::image type=“content” source=“media/activity-metrics-plugin/activity-metrics-dcount-and-dcount-newvalues.png” border=“false” alt-text=“Table showing the count of distinct values (dcount_values) and of new distinct values (dcount_newvalues) that didn’t appear in previous time window as specified in the query.”:::

5 - 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.

6 - funnel_sequence_completion plugin

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)

Parameters

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

Exploring Storm Events

The following query checks the completion funnel of the sequence: Hail -> Tornado -> Thunderstorm Wind in “overall” time of 1hour, 4hours, 1day.

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

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

• scan operator

7 - new_activity_metrics plugin

Calculates useful activity metrics (distinct count values, distinct count of new values, retention rate, and churn rate) for the cohort of New Users. Each cohort of New Users (all users, which were first seen in time window) is compared to all prior cohorts. Comparison takes into account all previous time windows. For example, for records from T2 to T3, the distinct count of users will be all users in T3 who weren’t seen in both T1 and T2. The plugin is invoked with the evaluate operator.

Syntax

TabularExpression | evaluate new_activity_metrics(IdColumn, TimelineColumn, Start, End, Window [, Cohort] [, dim1, dim2, …] [, Lookback] )

Parameters

Returns

Returns a table that contains the following for each combination of ‘from’ and ’to’ timeline periods and for each existing column (dimensions) combination:

• distinct count values
• distinct count of new values
• retention rate
• churn rate

Output table schema is:

• from_TimelineColumn - the cohort of new users. Metrics in this record refer to all users who were first seen in this period. The decision on first seen takes into account all previous periods in the analysis period.
• to_TimelineColumn - the period being compared to.
• dcount_new_values - the number of distinct users in to_TimelineColumn that weren’t seen in all periods prior to and including from_TimelineColumn.
• dcount_retained_values - out of all new users, first seen in from_TimelineColumn, the number of distinct users that were seen in to_TimelineCoumn.
• dcount_churn_values - out of all new users, first seen in from_TimelineColumn, the number of distinct users that weren’t seen in to_TimelineCoumn.
• retention_rate - the percent of dcount_retained_values out of the cohort (users first seen in from_TimelineColumn).
• churn_rate - the percent of dcount_churn_values out of the cohort (users first seen in from_TimelineColumn).

Examples

The following sample dataset shows which users seen on which days. The table was generated based on a source Users table, as follows:

Users | summarize tostring(make_set(user)) by bin(Timestamp, 1d) | order by Timestamp asc;

Output

The output of the plugin for the original table is the following:

let StartDate = datetime(2019-11-01 00:00:00);
let EndDate = datetime(2019-11-07 00:00:00);
Users 
| evaluate new_activity_metrics(user, Timestamp, StartDate, EndDate-1tick, 1d) 
| where from_Timestamp < datetime(2019-11-03 00:00:00.0000000)

Output

Following is an analysis of a few records from the output:

• Record R=3, from_TimelineColumn = 2019-11-01, to_TimelineColumn = 2019-11-03:

  • The users considered for this record are all new users seen on 11/1. Since this is the first period, these are all users in that bin – [0,2,3,4]
  • dcount_new_values – the number of users on 11/3 who weren’t seen on 11/1. This includes a single user – 5.
  • dcount_retained_values – out of all new users on 11/1, how many were retained until 11/3? There are three values ([0,2,4]), while count_churn_values is one (user=3).
  • retention_rate = 0.75 – the three retained users out of the four new users who were first seen in 11/1.

• Record R=9, from_TimelineColumn = 2019-11-02, to_TimelineColumn = 2019-11-04:

  • This record focuses on the new users who were first seen on 11/2 – users 1 and 5.
  • dcount_new_values – the number of users on 11/4 who weren’t seen through all periods T0 .. from_Timestamp. Meaning, users who are seen on 11/4 but who weren’t seen on either 11/1 or 11/2 – there are no such users.
  • dcount_retained_values – out of all new users on 11/2 ([1,5]), how many were retained until 11/4? There’s one such user ([1]), while count_churn_values is one (user 5).
  • retention_rate is 0.5 – the single user that was retained on 11/4 out of the two new ones on 11/2.

Weekly retention rate, and churn rate (single week)

The next query calculates a retention and churn rate for week-over-week window for New Users cohort (users that arrived on the first week).

// Generate random data of user activities
let _start = datetime(2017-05-01);
let _end = datetime(2017-05-31);
range Day from _start to _end step 1d
| extend d = tolong((Day - _start) / 1d)
| extend r = rand() + 1
| extend _users=range(tolong(d * 50 * r), tolong(d * 50 * r + 200 * r - 1), 1) 
| mv-expand id=_users to typeof(long) limit 1000000
// Take only the first week cohort (last parameter)
| evaluate new_activity_metrics(['id'], Day, _start, _end, 7d, _start)
| project from_Day, to_Day, retention_rate, churn_rate

Output

Weekly retention rate, and churn rate (complete matrix)

The next query calculates retention and churn rate for week-over-week window for New Users cohort. If the previous example calculated the statistics for a single week - the following query produces an NxN table for each from/to combination.

// Generate random data of user activities
let _start = datetime(2017-05-01);
let _end = datetime(2017-05-31);
range Day from _start to _end step 1d
| extend d = tolong((Day - _start) / 1d)
| extend r = rand() + 1
| extend _users=range(tolong(d * 50 * r), tolong(d * 50 * r + 200 * r - 1), 1) 
| mv-expand id=_users to typeof(long) limit 1000000
// Last parameter is omitted - 
| evaluate new_activity_metrics(['id'], Day, _start, _end, 7d)
| project from_Day, to_Day, retention_rate, churn_rate

Output

Weekly retention rate with lookback period

The following query calculates the retention rate of New Users cohort when taking into consideration lookback period: a tabular query with set of Ids that are used to define the New Users cohort (all IDs that don’t appear in this set are New Users). The query examines the retention behavior of the New Users during the analysis period.

// Generate random data of user activities
let _lookback = datetime(2017-02-01);
let _start = datetime(2017-05-01);
let _end = datetime(2017-05-31);
let _data = range Day from _lookback to _end step 1d
    | extend d = tolong((Day - _lookback) / 1d)
    | extend r = rand() + 1
    | extend _users=range(tolong(d * 50 * r), tolong(d * 50 * r + 200 * r - 1), 1) 
    | mv-expand id=_users to typeof(long) limit 1000000;
//
let lookback_data = _data | where Day < _start | project Day, id;
_data
| evaluate new_activity_metrics(id, Day, _start, _end, 7d, _start, lookback_data)
| project from_Day, to_Day, retention_rate

Output

8 - rolling_percentile plugin

Returns an estimate for the specified percentile of the ValueColumn population in a rolling (sliding) BinsPerWindow size window per BinSize.

The plugin is invoked with the evaluate operator.

Syntax

T | evaluate rolling_percentile(ValueColumn, Percentile, IndexColumn, BinSize, BinsPerWindow [, dim1, dim2, …] )

Parameters

Returns

Returns a table with a row per each bin (and combination of dimensions if specified) that has the rolling percentile of values in the window ending at the bin (inclusive). Output table schema is:

Examples

Rolling 3-day median value per day

The next query calculates a 3-day median value in daily granularity. Each row in the output represents the median value for the last 3 bins (days), including the bin itself.

let T = 
    range idx from 0 to 24 * 10 - 1 step 1
    | project Timestamp = datetime(2018-01-01) + 1h * idx, val=idx + 1
    | extend EvenOrOdd = iff(val % 2 == 0, "Even", "Odd");
T  
| evaluate rolling_percentile(val, 50, Timestamp, 1d, 3)

Output

Rolling 3-day median value per day by dimension

Same example from above, but now also calculates the rolling window partitioned for each value of the dimension.

let T = 
    range idx from 0 to 24 * 10 - 1 step 1
    | project Timestamp = datetime(2018-01-01) + 1h * idx, val=idx + 1
    | extend EvenOrOdd = iff(val % 2 == 0, "Even", "Odd");
T  
| evaluate rolling_percentile(val, 50, Timestamp, 1d, 3, EvenOrOdd)

Output

9 - rows_near plugin

Finds rows near a specified condition.

The plugin is invoked with the evaluate operator.

Syntax

T | evaluate rows_near(Condition, NumRows, [, RowsAfter ])

Parameters

Returns

Every row from the input that is within NumRows from a true Condition, When RowsAfter is specified, returns every row from the input that is NumRows before or RowsAfter after a true Condition.

Example

Find rows with an "Error" State, and returns 2 rows before and after the "Error" record.

datatable (Timestamp:datetime, Value:long, State:string )
[
    datetime(2021-06-01), 1, "Success",
    datetime(2021-06-02), 4, "Success",
    datetime(2021-06-03), 3, "Success",
    datetime(2021-06-04), 11, "Success",
    datetime(2021-06-05), 15, "Success",
    datetime(2021-06-06), 2, "Success",
    datetime(2021-06-07), 19, "Error",
    datetime(2021-06-08), 12, "Success",
    datetime(2021-06-09), 7, "Success",
    datetime(2021-06-10), 9, "Success",
    datetime(2021-06-11), 4, "Success",
    datetime(2021-06-12), 1, "Success",
]
| sort by Timestamp asc 
| evaluate rows_near(State == "Error", 2)

Output

10 - sequence_detect plugin

Detects sequence occurrences based on provided predicates. The plugin is invoked with the evaluate operator.

Syntax

T | evaluate sequence_detect (TimelineColumn, MaxSequenceStepWindow, MaxSequenceSpan, Expr1, Expr2, …, Dim1, Dim2, …)

Parameters

Returns

Returns a single table where each row in the table represents a single sequence occurrence:

• Dim1, Dim2, …: dimension columns that were used to correlate sequences.
• Expr1TimelineColumn, Expr2TimelineColumn, …: Columns with time values, representing the timeline of each sequence step.
• Duration: the overall sequence time window

Examples

The following query looks at the table T to search for relevant data from a specified time period.

T | evaluate sequence_detect(datetime_column, 10m, 1h, e1 = (Col1 == 'Val'), e2 = (Col2 == 'Val2'), Dim1, Dim2)

Exploring Storm Events

The following query looks on the table StormEvents (weather statistics for 2007) and shows cases where sequence of ‘Excessive Heat’ was followed by ‘Wildfire’ within 5 days.

StormEvents
| evaluate sequence_detect(
               StartTime,
               5d,  // step max-time
               5d,  // sequence max-time
               heat=(EventType == "Excessive Heat"), 
               wildfire=(EventType == 'Wildfire'), 
               State
           )

Output

11 - session_count plugin

Calculates the session count based on the ID column over a timeline. The plugin is invoked with the evaluate operator.

Syntax

TabularExpression | evaluate session_count(IdColumn, TimelineColumn, Start, End, Bin, LookBackWindow [, dim1, dim2, …])

Parameters

Returns

Returns a table that has the session count values for each timeline period and for each existing dimensions combination.

Output table schema is:

Examples

For this example, the data is deterministic, and we use a table with two columns:

• Timeline: a running number from 1 to 10,000
• Id: ID of the user from 1 to 50

Id appears at the specific Timeline slot if it’s a divider of Timeline (Timeline % Id == 0).

An event with Id==1 will appear at any Timeline slot, an event with Id==2 at every second Timeline slot, and so on.

Here are 20 lines of the data:

let _data = range Timeline from 1 to 10000 step 1
    | extend __key = 1
    | join kind=inner (range Id from 1 to 50 step 1 | extend __key=1) on __key
    | where Timeline % Id == 0
    | project Timeline, Id;
// Look on few lines of the data
_data
| order by Timeline asc, Id asc
| take 20

Output

Let’s define a session in next terms: session considered to be active as long as user (Id) appears at least once at a timeframe of 100 time slots, while session look-back window is 41 time slots.

The next query shows the count of active sessions according to the above definition.

let _data = range Timeline from 1 to 9999 step 1
    | extend __key = 1
    | join kind=inner (range Id from 1 to 50 step 1 | extend __key=1) on __key
    | where Timeline % Id == 0
    | project Timeline, Id;
// End of data definition
_data
| evaluate session_count(Id, Timeline, 1, 10000, 100, 41)
| render linechart 

12 - sliding_window_counts plugin

Calculates counts and distinct count of values in a sliding window over a lookback period, using the technique described in the Perform aggregations over a sliding window example. The plugin is invoked with the evaluate operator.

Syntax

T | evaluate sliding_window_counts(IdColumn, TimelineColumn, Start, End, LookbackWindow, Bin , [dim1, dim2, …])

Parameters

Returns

Returns a table that has the count and distinct count values of Ids in the lookback period, for each timeline period (by bin) and for each existing dimensions combination.

Output table schema is:

Example

Calculate counts and dcounts for users in past week, for each day in the analysis period.

let start = datetime(2017 - 08 - 01);
let end = datetime(2017 - 08 - 07); 
let lookbackWindow = 3d;  
let bin = 1d;
let T = datatable(UserId: string, Timestamp: datetime)
    [
    'Bob', datetime(2017 - 08 - 01), 
    'David', datetime(2017 - 08 - 01), 
    'David', datetime(2017 - 08 - 01), 
    'John', datetime(2017 - 08 - 01), 
    'Bob', datetime(2017 - 08 - 01), 
    'Ananda', datetime(2017 - 08 - 02),  
    'Atul', datetime(2017 - 08 - 02), 
    'John', datetime(2017 - 08 - 02), 
    'Ananda', datetime(2017 - 08 - 03), 
    'Atul', datetime(2017 - 08 - 03), 
    'Atul', datetime(2017 - 08 - 03), 
    'John', datetime(2017 - 08 - 03), 
    'Bob', datetime(2017 - 08 - 03), 
    'Betsy', datetime(2017 - 08 - 04), 
    'Bob', datetime(2017 - 08 - 05), 
];
T
| evaluate sliding_window_counts(UserId, Timestamp, start, end, lookbackWindow, bin)

Output

13 - User Analytics

This section describes Kusto extensions (plugins) for user analytics scenarios.

||||