activity_engagement 插件activity_engagement plugin

在滑动时间线窗口中,根据 ID 列计算活动参与比率。Calculates activity engagement ratio based on ID column over a sliding timeline window.

activity_engagement 插件可用于计算 DAU/WAU/MAU(每日/每周/每月活动)。activity_engagement plugin can be used for calculating DAU/WAU/MAU (daily/weekly/monthly activities).

T | evaluate activity_engagement(id, datetime_column, 1d, 30d)

语法Syntax

T | evaluate activity_engagement(IdColumn, TimelineColumn, [ Start, End,] InnerActivityWindow, OuterActivityWindow [, dim1, dim2, ...])T | evaluate activity_engagement(IdColumn, TimelineColumn, [ Start, End,] InnerActivityWindow, OuterActivityWindow [, 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.
  • InnerActivityWindow :带有内部范围分析窗口时段值的标量。InnerActivityWindow : Scalar with value of the inner-scope analysis window period.
  • OuterActivityWindow :带有外部范围分析窗口时段值的标量。OuterActivityWindow : Scalar with value of the outer-scope analysis window period.
  • dim1, dim2, ... :(可选)维度列的列表,用于切分活动指标计算。dim1 , dim2 , ...: (optional) list of the dimensions columns that slice the activity metrics calculation.

返回Returns

返回一个表,该表对于每个内部范围窗口时段和每个现有维度组合都有(内部范围窗口内 ID 值的不同计数、外部范围窗口内 ID 值的不同计数以及活动率)。Returns a table that has (distinct count of ID values inside inner-scope window, distinct count of ID values inside outer-scope window, and the activity ratio)for each inner-scope window period and for each existing dimensions combination.

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

TimelineColumnTimelineColumn dcount_activities_innerdcount_activities_inner dcount_activities_outerdcount_activities_outer activity_ratioactivity_ratio dim1dim1 .... dim_ndim_n
类型:自 TimelineColumn 起type: as of TimelineColumn longlong longlong Doubledouble .... .... ....

示例Examples

DAU/WAU 计算DAU/WAU calculation

下面的示例通过随机生成的数据计算 DAU/WAU(每日活动用户/每周活动用户比率)。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 

活动参与 dau wau

DAU/MAU 计算DAU/MAU calculation

下面的示例通过随机生成的数据计算 DAU/WAU(每日活动用户/每周活动用户比率)。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 

活动参与 dau mau

使用附加维度计算 DAU/MAUDAU/MAU calculation with additional dimensions

下面的示例使用附加维度 (mod3) 通过随机生成的数据计算 DAU/WAU(每日活动用户/每周活动用户比率)。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 

活动参与 dau mau mod 3