activity_metrics 插件activity_metrics plugin

• 10/29/2020
• • o
  • v

根据当前阶段窗口与前一阶段窗口计算有用的活动指标（不同的计数值、新值的不同计数、保留率和流失率）（与 activity_counts_metrics 插件不同，在该插件中，每个时间窗口都与所有以前的时间窗口进行比较）。Calculates useful activity metrics (distinct count values, distinct count of new values, retention rate, and churn rate) based on the current period window vs. previous period window (unlike activity_counts_metrics plugin in which every time window is compared to all previous time windows).

T | evaluate activity_metrics(id, datetime_column, startofday(ago(30d)), startofday(now()), 1d, dim1, dim2, dim3)

语法Syntax

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

参数Arguments

• T ：输入表格表达式。T : The input tabular expression.
• IdColumn ：列的名称，其 ID 值表示用户活动。IdColumn : The name of the column with ID values that represent user activity.
• TimelineColumn：表示时间线的列的名称。TimelineColumn : The name of the column that represent timeline.
• Start ：（可选）带有分析开始时段值的标量。Start : (optional) Scalar with value of the analysis start period.
• End ：（可选）带有分析结束时段值的标量。End : (optional) Scalar with value of the analysis end period.
• Window：带有分析窗口时段值的标量。Window : Scalar with value of the analysis window period. 既可以是数字/日期/时间/时间戳值，也可以是 week/month/year 中的一个字符串（在这种情况下，这些时段分别为 startofweek/startofmonth/startofyear）。Can be either a numeric/datetime/timestamp value, or a string which is one of week/month/year, in which case all periods will be startofweek/startofmonth/startofyear accordingly.
• dim1, dim2, ... ：（可选）维度列的列表，用于切分活动指标计算。dim1 , dim2 , ...: (optional) list of the dimensions columns that slice the activity metrics calculation.

返回Returns

返回一个表，该表包含每个时间线时段和每个现有维度组合的不同计数值、新值的不同计数、保留率和流失率。Returns a table that has the distinct count values, distinct count of new values, retention rate, and churn rate for each timeline period and for each existing dimensions combination.

输出表架构如下：Output table schema is:

说明Notes

*保留率定义 _*Retention Rate Definition _

一段时间内 Retention Rate 的计算方法为：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

其中的 # of customers returned during 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 可以在从 0.0 到 1.0 的范围内变化Retention Rate can vary from 0.0 to 1.0
分数越高表示返回用户的数量越大。The higher score means the larger amount of returning users.

*流失率定义 _*Churn Rate Definition _

一段时间内 Churn Rate 的计算方法为：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

其中的 # of customer lost in 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 customers at the end of the period

Churn Rate 可以在从 0.0 到 1.0 的范围内变化。分数越高，表示不返回到服务的用户数量越多。Churn Rate can vary from 0.0 to 1.0 The higher score means the larger amount of users are NOT returning to the service.

*流失率与保留率 _*Churn vs. Retention Rate _

派生自 Churn Rate 和 Retention Rate 的定义，以下公式始终成立：Derived from the definition of Churn Rate and Retention Rate, the following is always true:

[Retention Rate] = 100.0% - [Churn Rate][Retention Rate] = 100.0% - [Churn Rate]

示例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 

不同的值和不同的“新”值Distinct values and distinct 'new' values

下一个查询计算每周的不同值和“新”值（上一个时间窗口中没有出现的 ID）。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