Log Service provides the window funnel function. You can use this function to analyze data, such as user behavior, application traffic, and the conversion rate in product promotions. This topic describes the syntax of the window funnel function. This topic also provides examples on how to use the window funnel function.
The following table describes the window funnel function that is supported by Log Service.
Function | Syntax | Description |
---|---|---|
window_funnel function | window_funnel(sliding_window, timestamp, event_id, ARRAY[event_list01, event_list02...]) | Searches for an event chain in a sliding time window and counts the maximum number
of consecutive events that occurred in the event chain.
If the value of the event_id parameter is specified in an event chain, you can use this syntax. |
window_funnel(sliding_window, timestamp, ARRAY[event_id=event_list01, event_id=event_list02...]) | Searches for an event chain in a sliding time window and counts the maximum number
of consecutive events that occurred in the event chain.
If the value of the event_id parameter is not specified in an event chain and you want to use a custom value for the event_id parameter, you can use this syntax for higher flexibility. |
Principle
The window funnel function is used to search for an event chain in a sliding time window and count the maximum number of consecutive events in the event chain. The window funnel function starts the count from the first event in the event chain that you specify, checks the events in sequence, and then returns the maximum number of consecutive events.
- The function starts the count from the first event in the event chain and sets the initial value of the event counter to 1. Then, the sliding time window starts.
- In the sliding time window, if the events in the event chain occur in sequence, the event counter is incremented.
- In the sliding time window, if the sequence of events in the event chain is disrupted, the event counter stops. The search stops and a new search starts. The maximum number of consecutive events in the previous search is counted.
- If multiple values of consecutive events exist in the previous search, the function returns the maximum value. The maximum value indicates the maximum number of consecutive events.
- The function must start the count from the first event in the event chain. For example, if the function starts the count in the sequence of Event 2, Event 3, and Event 4, the function returns 0.
- The function must count all events that occur in the event chain. For example, if Event 4 occurs in the sliding time window and Event 3 does not occur in the sliding time window, this search is excluded from the maximum number of consecutive events.

Syntax
The window funnel function supports the following two syntax:
- If the value of the event_id parameter is specified in an event chain, you can use
the following syntax:
window_funnel(sliding_window, timestamp, event_id, ARRAY[event_list01, event_list02...])
- If the value of the event_id parameter is not specified in an event chain and you
want to use a custom value for the event_id parameter, you can use the following syntax
for higher flexibility:
window_funnel(sliding_window, timestamp, ARRAY[event_id=event_list01, event_id=event_list02...])
Parameters
Parameter | Description |
---|---|
sliding_window | The sliding time window. Unit: seconds. The value of this parameter is of the bigint type. |
timestamp | The timestamp. Unit: seconds. The value of this parameter is of the bigint type. We recommend that you use the built-in __time__ field of Log Service. |
event_id | The name of the log field. The value of this parameter is the name of an event. Example: Event A, Event B, or Event C. The value of this parameter is of the varchar type. |
event_list | The custom event chain. The event chain can contain up to 32 events. The value of
this parameter is of the array type. Examples:
|
Examples
An e-commerce store held a promotional activity and analyzed the conversion effect of the activity by using the window funnel function. The conversion process consists of the following three steps: browse the information of a commodity, add the commodity to the online shopping cart, and then purchase the commodity. The following figure shows a sample log that is collected by Log Service.

Log field | Description |
---|---|
behavior_type | The type of user behavior. Valid values:
|
category_id | The category ID of the commodity. |
item_id | The ID of the commodity. |
timestamp | The point in time at which the user behavior occurred. |
user_id | The ID of the user. |
Example 1
Analyze the purchase behavior of users within 24 hours.
- Query statement
* | SELECT user_id, window_funnel( 86400, timestamp, ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy'] ) AS levels GROUP BY user_id ORDER BY user_id LIMIT 1000
- Query and analysis result
- The value of the levels field for User 24 is 3. The user completes the purchase in sequence. The user browses the information of a commodity, adds the commodity to the online shopping cart, and then purchases the commodity.
- The value of the levels field for User 14 is 2. The user browses the information of a commodity and adds the commodity to the online shopping cart. However, the user does not purchase the commodity.
Example 2
Calculate the number of users for each type of user behavior.
- Query statement
* | SELECT levels, count, sum(count) over( ORDER BY levels DESC ) AS total FROM ( SELECT levels, count(1) AS count FROM ( SELECT user_id, window_funnel( 86400, timestamp, ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy'] ) AS levels FROM log GROUP BY user_id ) GROUP BY levels ORDER BY levels )
- Query and analysis result
- The number of users who browse the information of a commodity is 513,194. The number of users who do not continue their purchase process after they browse the information of the commodity is 138,491.
- The number of users who add the commodity to the online shopping cart is 374,703. The number of users who do not continue the purchase process after they add the commodity to the online shopping cart is 198,642.
- The number of users who purchase the commodity is 176,061.
Example 3
- Absolute conversion rate: the ratio of the number of users who perform a type of user behavior to the total number of users.
- Relative conversion rate: the ratio of the number of users who perform a type of user behavior to the number of users who perform the previous type of user behavior.
- Query statement
* | SELECT *, 100.0 * total /(sum(count) over()) AS "Absolute conversion rate", if( lag(total, 1, 0) over() = 0, 100, (100.0 * total / lag(total, 1, 0) over()) ) AS "Relative conversion rate" FROM ( SELECT levels, count, sum(count) over( ORDER BY levels DESC ) AS total FROM ( SELECT levels, count(1) AS count FROM ( SELECT user_id, window_funnel( 86400, timestamp, ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy'] ) AS levels FROM log GROUP BY user_id ) GROUP BY levels ) ORDER BY levels )
- Query and analysis result
- Table
- Funnel chart
- Table