Funnel and Retention Functions

windowfunnel

The windowfunnel function is used to search for an event list within a sliding time window and calculate the maximum length of the event list that matches the conditions. DataArts Fabric SQL sequentially performs the longest ordered match starting from the first event based on the user-defined event list and returns the maximum match length. Once a match fails, the entire matching process terminates. The details are as follows:

Assuming the window is sufficiently large:

• Condition events are c1, c2, and c3, and the user data is c1, c2, c3, c4. Ultimately, c1, c2, c3 are matched, and the function returns 3.
• Condition events are c1, c2, and c3, and the user data is c4, c3, c2, c1. Ultimately, c1 is matched, and the function returns 1.
• Condition events are c1, c2, and c3, and the user data is c4, c3. No events are matched, and the function returns 0.

Syntax

1

windowFunnel(window, mode, timestamp, cond1, cond2, ..., condN)

Input parameters

• window: bigint type. The size of the sliding time window, indicating the duration in seconds from the first event onward.
• mode: text type. Only the default mode is currently supported. Using other modes will result in an error. Default mode means attempting to match as many events as possible starting from the first event within the same window period.
• timestamp: the time range during which an event occurs, supporting timestamp without time zone, timestamp with time zone, date, int, and bigint types.
• cond: variable-length Boolean array. It indicates which step of the event the current Tuple's data satisfies. DataArts Fabric SQL only supports 1 to 32 conditions. Numbers outside this range will cause an error.

Return values:

level: int type. It indicates the maximum length of the event list that matches the conditions.

retention

The retention function takes a set of conditions as parameters to analyze whether an event meets these conditions.

Syntax

1

retention(cond1, cond2, ..., cond32);

Input parameters

cond: variable-length boolean array, maximum length 32, used to indicate whether an event meets specific conditions. DataArts Fabric SQL only supports 1 to 32 conditions. Numbers outside this range will cause an error.

Return values:

retention condition: tinyint array type. An expression of the returned results, with the same length as the input parameter cond. If both cond1 and condi conditions are satisfied, the i-th value of the return is 1. Otherwise, it is 0.

range_retention_count

Description: Records the retention status of each user. This function returns an array, which can be used as the input parameter for the range_retention_sum function.

Syntax

1

range_retention_count(is_first, is_active, dt, retention_interval, retention_granularity, output_format)

Input parameters

• is_first: indicates whether the initial behavior is met. The value is of the boolean type. true means it meets the initial behavior, and false means it does not meet the initial behavior.
• is_active: indicates whether the retention behavior is met. The value is of the boolean type. true means it meets the retention behavior, and false means it does not meet the retention behavior.
• dt: indicates the date when the behavior occurred. The value type is date.
• retention_interval: indicates the retention interval in array type, supporting up to 15 retention intervals. For example, ARRAY[1,3,5,7,15,30].
• retention_granularity: indicates the retention analysis granularity in text type, including day, week, and month.
• output_format: indicates the output format in text format and supports two types: normal (default) and expand (to obtain daily retention details).

Return value: User retention status BIGINT array.

range_retention_sum

Description: Aggregates and calculates the daily (weekly/monthly) retention rate of all users.

Syntax

1

range_retention_sum(range_retention_count_result)

Input parameter: return value of the range_retention_count function.

Return value: text array of user retention statistics.