activity_metrics plugin

Applies to: ✅ Azure Data Explorer

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.

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

Learn more about syntax conventions.

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:

Retention Rate Definition

Retention Rate over a period is calculated as:

number of customers returned during the period / (divided by) number customers at the beginning of the period

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

number of customers at end of period - (minus) number of new customers acquired during the period

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:

number of customers lost in the period / (divided by) number of customers at the beginning of the period

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

number of customers at the beginning of the period - (minus) number of returning customers during the period

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:

[Retention Rate] = 100.0% - [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

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