Window funnel function - Index and Query| Alibaba Cloud Documentation Center

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.

Notice If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.


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 function	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 window funnel function uses the following algorithm:

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.

For example, you specify a sliding time window of 100 seconds and the following pattern for the event chain: Event 1, Event 2, Event 3, Event 4, and Event 5. However, the events in the event chain occur in the following sequence: Event 1, Event 2, Event 4, Event 5, Event 1, and Event 3. In the sliding time window, the maximum number of consecutive events is 2. A value of 2 indicates the sequential relationship between Event 1 and Event 2.

Notice

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: ARRAY['A', 'B', 'C'] ARRAY[event_id='A', event_id='B', event_id='C']

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: pv: Browse the information of a commodity. cart: Add a commodity to the online shopping cart. buy: Purchase a commodity.
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

Calculate the conversion rate of the preceding promotional activity.

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