activity_metrics 插件activity_metrics plugin

根据当前阶段窗口与前一阶段窗口计算有用的活动指标(不同的计数值、新值的不同计数、保留率和流失率)(与 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:

TimelineColumnTimelineColumn dcount_valuesdcount_values dcount_newvaluesdcount_newvalues retention_rateretention_rate churn_ratechurn_rate dim1dim1 .... dim_ndim_n
类型:自 TimelineColumn 起type: as of TimelineColumn longlong longlong Doubledouble Doubledouble .... .... ....

说明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 RateRetention 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 
_day_day retention_rateretention_rate churn_ratechurn_rate
2017-01-02 00:00:00.00000002017-01-02 00:00:00.0000000 NaNNaN NaNNaN
2017-01-09 00:00:00.00000002017-01-09 00:00:00.0000000 0.1799100449775110.179910044977511 0.8200899550224890.820089955022489
2017-01-16 00:00:00.00000002017-01-16 00:00:00.0000000 0.7443744374437440.744374437443744 0.2556255625562560.255625562556256
2017-01-23 00:00:00.00000002017-01-23 00:00:00.0000000 0.6120967741935480.612096774193548 0.3879032258064520.387903225806452
2017-01-30 00:00:00.00000002017-01-30 00:00:00.0000000 0.6811414392059550.681141439205955 0.3188585607940450.318858560794045
2017-02-06 00:00:00.00000002017-02-06 00:00:00.0000000 0.2781456953642380.278145695364238 0.7218543046357620.721854304635762
2017-02-13 00:00:00.00000002017-02-13 00:00:00.0000000 0.2231726283048210.223172628304821 0.7768273716951790.776827371695179
2017-02-20 00:00:00.00000002017-02-20 00:00:00.0000000 0.380.38 0.620.62
2017-02-27 00:00:00.00000002017-02-27 00:00:00.0000000 0.2955190017016450.295519001701645 0.7044809982983550.704480998298355
2017-03-06 00:00:00.00000002017-03-06 00:00:00.0000000 0.2803877703206560.280387770320656 0.7196122296793440.719612229679344
2017-03-13 00:00:00.00000002017-03-13 00:00:00.0000000 0.3606281547952890.360628154795289 0.6393718452047110.639371845204711
2017-03-20 00:00:00.00000002017-03-20 00:00:00.0000000 0.2880080280983440.288008028098344 0.7119919719016560.711991971901656
2017-03-27 00:00:00.00000002017-03-27 00:00:00.0000000 0.3061349693251530.306134969325153 0.6938650306748470.693865030674847
2017-04-03 00:00:00.00000002017-04-03 00:00:00.0000000 0.3568665377176020.356866537717602 0.6431334622823980.643133462282398
2017-04-10 00:00:00.00000002017-04-10 00:00:00.0000000 0.4950980392156860.495098039215686 0.5049019607843140.504901960784314
2017-04-17 00:00:00.00000002017-04-17 00:00:00.0000000 0.1982968369829680.198296836982968 0.8017031630170320.801703163017032
2017-04-24 00:00:00.00000002017-04-24 00:00:00.0000000 0.06188118811881190.0618811881188119 0.9381188118811880.938118811881188
2017-05-01 00:00:00.00000002017-05-01 00:00:00.0000000 0.2046577275935070.204657727593507 0.7953422724064930.795342272406493
2017-05-08 00:00:00.00000002017-05-08 00:00:00.0000000 0.5173913043478260.517391304347826 0.4826086956521740.482608695652174
2017-05-15 00:00:00.00000002017-05-15 00:00:00.0000000 0.1436672967863890.143667296786389 0.8563327032136110.856332703213611
2017-05-22 00:00:00.00000002017-05-22 00:00:00.0000000 0.1991223258365330.199122325836533 0.8008776741634670.800877674163467
2017-05-29 00:00:00.00000002017-05-29 00:00:00.0000000 0.0634689922480620.063468992248062 0.9365310077519380.936531007751938

活动指标改动和保留

不同的值和不同的“新”值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 
_day_day dcount_valuesdcount_values dcount_newvaluesdcount_newvalues
2017-01-02 00:00:00.00000002017-01-02 00:00:00.0000000 630630 630630
2017-01-09 00:00:00.00000002017-01-09 00:00:00.0000000 738738 575575
2017-01-16 00:00:00.00000002017-01-16 00:00:00.0000000 11871187 841841
2017-01-23 00:00:00.00000002017-01-23 00:00:00.0000000 10921092 465465
2017-01-30 00:00:00.00000002017-01-30 00:00:00.0000000 12611261 647647
2017-02-06 00:00:00.00000002017-02-06 00:00:00.0000000 17441744 10431043
2017-02-13 00:00:00.00000002017-02-13 00:00:00.0000000 15631563 432432
2017-02-20 00:00:00.00000002017-02-20 00:00:00.0000000 14061406 818818
2017-02-27 00:00:00.00000002017-02-27 00:00:00.0000000 19561956 14291429
2017-03-06 00:00:00.00000002017-03-06 00:00:00.0000000 15931593 848848
2017-03-13 00:00:00.00000002017-03-13 00:00:00.0000000 18011801 14231423
2017-03-20 00:00:00.00000002017-03-20 00:00:00.0000000 17101710 10171017
2017-03-27 00:00:00.00000002017-03-27 00:00:00.0000000 17961796 15161516
2017-04-03 00:00:00.00000002017-04-03 00:00:00.0000000 13811381 10081008
2017-04-10 00:00:00.00000002017-04-10 00:00:00.0000000 17561756 11621162
2017-04-17 00:00:00.00000002017-04-17 00:00:00.0000000 18311831 14091409
2017-04-24 00:00:00.00000002017-04-24 00:00:00.0000000 18231823 11641164
2017-05-01 00:00:00.00000002017-05-01 00:00:00.0000000 18111811 13531353
2017-05-08 00:00:00.00000002017-05-08 00:00:00.0000000 16911691 12461246
2017-05-15 00:00:00.00000002017-05-15 00:00:00.0000000 18121812 16081608
2017-05-22 00:00:00.00000002017-05-22 00:00:00.0000000 17401740 10171017
2017-05-29 00:00:00.00000002017-05-29 00:00:00.0000000 960960 756756

活动指标 dcount 和 dcount 新值