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.

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

Syntax

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

Arguments

Name	Type	Required	Description
T	tabular expression	✓	The input tabular expression.
IdColumn	string	✓	The name of the column with ID values that represent user activity.
TimelineColumn	string	✓	The name of the column that represents the timeline.
Start	datetime	✓	Scalar with value of the analysis start period.
End	datetime	✓	Scalar with value of the analysis end period.
Window	decimal/datetime/timespan	✓	Scalar with value of the analysis window period. Can be either a numeric/datetime/timestamp value, or a string that is one of week/month/year, in which case all periods will be startofweek/startofmonth or startofyear.
dim1, dim2, ...	dynamic		list of the dimensions columns that slice the activity metrics calculation.

Returns

Returns a table that has: total count values, distinct count values, distinct count of new values, and aggregated distinct count for each time window.

Output table schema is:

TimelineColumn	dim1	...	dim_n	count	dcount	new_dcount	aggregated_dcount
type: as of TimelineColumn	..	..	..	long	long	long	long

• TimelineColumn: The time window start time.
• count: The total records count in the time window and dim(s)
• dcount: The distinct ID values count in the time window and dim(s)
• new_dcount: The distinct ID values in the time window and dim(s) compared to all previous time windows.
• aggregated_dcount: The total aggregated distinct ID values of dim(s) from first-time window to current (inclusive).

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)

Timestamp	count	dcount	new_dcount	aggregated_dcount
2017-08-01 00:00:00.0000000	4	4	4	4
2017-08-02 00:00:00.0000000	3	3	2	6
2017-08-03 00:00:00.0000000	6	5	2	8
2017-08-04 00:00:00.0000000	1	1	0	8