Promo Center

50% off for new user

Direct Mail-46% off

Learn More

Dimension grouping funnel function (finder_group_funnel)

Updated at: 2025-01-20 02:05

The dimension grouping funnel function allows you to group and display results based on different dimensions and specify the associated properties of events. For example, you can group results by day, country, or IP address to achieve finer-grained funnel analysis. A user can only be in one group. If the user does not belong to a group, the user is assigned to the unreach group.

Limits

Only Hologres V2.2.32 and later support the dimension grouping funnel function.

Precautions

To use funnel functions, you must execute the following statement as a superuser to install an extension:

CREATE extension flow_analysis; --Install the extension.
  • The extension is installed at the database level. For each database, you need to install the extension only once.

  • By default, the extension is loaded to the public schema. The extension cannot be loaded to other schemas.

finder_group_funnel

This function is used to group specified events based on the selected dimension and calculate funnel results.

Function description

  • Syntax

    finder_group_funnel(window, start_timestamp, step_interval, step_numbers, num_events, attr_related, group_event_index,time_zone,is_relative_window, server_timestamp, client_timestamp, group_dimension, prop1, prop2, ..., check_event1, check_event2...)
  • Parameters

    Parameter

    Required

    Description

    Parameter

    Required

    Description

    window

    Yes

    The window for statistical analysis. Unit: milliseconds.

    start_timestamp

    Yes

    The start time of the statistical analysis. The TIMESTAMP and TIMESTAMPTZ types are supported.

    step_interval

    Yes

    The duration of a step size, which is the granularity for conversion calculation and analysis. Unit: seconds.

    step_numbers

    Yes

    The number of steps that need to be analyzed. For example, step_interval=86400 (one day), step_number=7 specifies the funnel data within seven days starting from the time specified by the start_timestamp parameter.

    num_events

    Yes

    The number of events that need to be analyzed.

    attr_related

    Yes

    Specifies whether the event has associated properties. The parameter values are UINT8-type numbers. If the values are represented in binary, the ith place is 1, which indicates that the i+1th event has associated properties. In most cases, attr_related is used in conjunction with prop. If you set the attr_related parameter to 1, the number of prop expressions that you need to enter must be the same as the number of 1 that you configured.

    group_event_index

    Yes

    The event based on which grouping is performed.

    For example, if you set the group_event_index parameter to 1, grouping starts as soon as the first event is reached. If you set the group_event_index parameter to 2, grouping starts when the second event is reached. The conversions that fail to reach the second event are assigned to the unreach group.

    time_zone

    Yes

    The time zone that corresponds to the input time. The parameter values are of the TEXT type and must be in a standard time zone format, such as Asia/Shanghai. The output results are affected only if you set the is_relative_window parameter to true. In this case, the results are displayed based on the time zone.

    is_relative_window

    Yes

    Specifies whether the window is a multi-calendar day window. The default value is false. If you set the parameter to true, the following limits are imposed on other parameters:

    • window: The parameter value must be an integer multiple of 86,400,000.

    • step_interval: The parameter value must be 86400, which indicates that one observation step size is one day.

    Note

    Calendar day refers to the period of time from 00:00:00 to 23:59:59 of each day. The first calendar day ranges from the event time to 23:59:59, and the following calendar days are whole days. In most cases, you can use the calendar day as a window to observe the daily funnel data to implement refined business operations.

    server_timestamp

    Yes

    The server time when the event occurred. The TIMESTAMP and TIMESTAMPTZ types are supported. This parameter is used to calculate the event slot or step to which the event belongs when the function is running.

    client_timestamp

    Yes

    The client time when the event occurred. The TIMESTAMP and TIMESTAMPTZ types are supported. The data type of this parameter must be the same as that of the start_timestamp parameter. This parameter is used to sort data when the function is running.

    group_dimension

    Yes

    The dimension used for grouping. For example, if you select the channel_id field for grouping, the grouped data is displayed based on the value of the channel_id field. If you want to use multiple dimensions for grouping, you can use the concat function to connect the dimensions. You can use only fields of the TEXT type for grouping.

    prop

    No

    The associated properties of the event. The data type of all properties must be the same. Otherwise, the comparison cannot be performed.

    check_event

    Yes

    The list of conversion events that need to be analyzed. Events that meet the conditions are considered valid events and participate in conversion analysis within the duration defined by the window. For example, if you want to analyze three events, enter EventName = 'E0001', EventName = 'E0002', EventName = 'E0003'.

  • Returned result

    An encoded result of the BINARY type is returned. You must use the finder_group_funnel_res function to decode the result.

Example

This example describes how to use the finder_group_funnel function.

  1. Prepare the finder_group_funnel_test table and insert data into the table.

    CREATE TABLE finder_group_funnel_test(id INT, event_time TIMESTAMP, event TEXT, province TEXT,city TEXT);
    
    INSERT INTO finder_group_funnel_test VALUES 
    (1111, '2024-01-02 00:00:00', 'Registration', 'Beijing','Beijing'), 
    (1111, '2024-01-02 00:00:01', 'Logon', 'Beijing','Beijing'), 
    (1111, '2024-01-02 00:00:02', 'Pay', 'Beijing','Beijing'), 
    (1111, '2024-01-02 00:00:03', 'Exit', 'Beijing','Beijing'), 
    
    (1111, '2024-01-03 00:00:00', 'Registration', 'Beijing','Beijing'), 
    (1111, '2024-01-03 00:00:01', 'Logon', 'Beijing','Beijing'), 
    (1111, '2024-01-03 00:00:02', 'Pay', 'Beijing','Beijing'), 
    
    (1111, '2024-01-04 00:00:00', 'Registration', 'Beijing','Beijing'), 
    (1111, '2024-01-04 00:00:01', 'Logon', 'Beijing','Beijing'), 
    
    
    (2222, '2024-01-02 00:00:00', 'Registration', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:00', 'Logon', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:01', 'Pay', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:03', 'Pay', 'Zhejiang','Hangzhou');
  2. To group and display results by the province field, run the following command:

    SELECT
        id,
        UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE, event_time, event_time, province, event = 'Registration', event = 'Logon', event = 'Pay', event = 'Exit')) AS result
    FROM
        finder_group_funnel_test
    GROUP BY
        id;

    The following result is returned: The value of result is the encoded result. You must use the finder_group_funnel_res function to decode the result. For more information, see finder_group_funnel_res.

      id  | result      
    ------+-----------------
     2222 | Zhejiang
     2222 | unreach
     1111 | Beijing
     1111 | unreach
    (4 rows)

finder_group_funnel_res

This function is used to decode the funnel details in the BINARY result returned by the finder_group_funnel function.

Function description

  • Syntax

    finder_group_funnel_res(finder_group_funnel())
  • Parameters

    finder_group_funnel(): This function is used to group specified events based on the selected dimension and calculate funnel results. For more information, see finder_group_funnel.

  • Returned result

    The decoded result is returned.

Example

In this example, the result of the example for finder_group_funnel is decoded to display the funnel details of each user. Run the following command:

SELECT
    id,
    finder_group_funnel_res (result) AS res
FROM (
    SELECT
        id,
        UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE, event_time, event_time, province, event = 'Registration', event = 'Logon', event = 'Pay', event = 'Exit')) AS result
    FROM
        finder_group_funnel_test
    GROUP BY
        id) a; 

The following result is returned:

 id  |    res    
------+-----------
 1111 | {4,4,3,2}
 1111 | {0,0,0,0}
 2222 | {3,3,0,0}
 2222 | {0,0,0,0}
(4 rows)

finder_group_funnel_text_group

This function is used to decode the grouping field in the BINARY result returned by the finder_group_funnel function. This function is usually used in combination with the finder_group_funnel_res function.

Function description

  • Syntax

    finder_group_funnel_text_group(finder_group_funnel())
  • Parameters

    finder_group_funnel(): This function is used to group specified events based on the selected dimension and calculate funnel results. For more information, see finder_group_funnel.

  • Returned result

    The decoded result is returned.

Example

In this example, the result of the example for finder_group_funnel is decoded to display the funnel results, the final reached event, and the final reached event within each step size for each grouped user. Run the following command:

SELECT
    id,
    finder_group_funnel_text_group (result) AS key,
    finder_group_funnel_res (result) AS res
FROM (
    SELECT
        id,
        UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE, event_time, event_time, province, event = 'Registration', event = 'Logon', event = 'Pay', event = 'Exit')) AS result
    FROM
        finder_group_funnel_test
    GROUP BY
        id) a;

The following result is returned:

  id  |   key   |    res    
------+---------+-----------
 2222 | Zhejiang    | {3,3,0,0}
 2222 | unreach | {0,0,0,0}
 1111 | Beijing    | {4,4,3,2}
 1111 | unreach | {0,0,0,0}
(4 rows)

Aggregate function for funnel results (funnel_rep)

This function is used to aggregate the calculation results of FINDER_FUNNEL and finder_group_funnel to generate the aggregation results of all users at each layer of the funnel.

Function description

  • Syntax

    funnel_rep(step_number, num_events, funnel_res)
  • Parameters

    Parameter

    Required

    Description

    Parameter

    Required

    Description

    step_number

    Yes

    The number of time slots. The parameter values are of the UINT type. In most cases, the value of this parameter is the same as the value of the step_numbers parameter in the finder_tunnel function.

    For example, step_numbers=7 indicates that seven time slots are observed.

    num_events

    Yes

    The total number of events that participate in the conversion. The parameter values are of the UINT type. In most cases, the value of this parameter is the same as the value of the check_event parameter in the finder_tunnel function.

    funnel_res

    Yes

    The details of all conversion steps generated by each user, which is the output of finder_tunnel.

  • Returned result

    A one-dimensional array whose element type is STRING is returned. The array is in the {"n1,...,nn","m1,...,mn"} format, which represents the overall funnel data and the funnel data within each step size. Both the overall funnel data and the funnel data within each step size are the number of users matched by the 1-Nth event.

Example

This example describes how to calculate the event that each user reaches when the window period is three days and the step size is three days. The data in the example for finder_group_funnel is used. Run the following command:

-- Calculate the event that each user reaches when the window period is three days and the step size is three days. 
SELECT
    funnel_rep (3, 4, funnel_res)
FROM (
    SELECT
        id,
        FINDER_FUNNEL (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 'Asia/Shanghai', FALSE, event_time, event_time, event = 'Registration', event = 'Logon', event = 'Pay', event = 'Exit') AS funnel_res
    FROM
        finder_group_funnel_test
    GROUP BY
        id) a;

The following result is returned:

                funnel_rep                 
-------------------------------------------
 {"2,2,2,1","2,2,2,1","1,1,1,0","1,1,0,0"}
(1 row)

Complete usage example

Scenario 1: Display the funnel results of grouped users based on the multi-day window

Analyze the funnel data within 3 days and the funnel data of each day based on the province dimension when four events sequentially occurred.

  1. To prepare data, run the following commands:

    CREATE TABLE finder_group_funnel_test_1(id INT, event_time TIMESTAMP, event TEXT, province TEXT,city TEXT);
    
    INSERT INTO finder_group_funnel_test_1 VALUES 
    (1111, '2024-01-02 00:00:00', 'Registration', 'Beijing','Beijing'), 
    (1111, '2024-01-02 00:00:01', 'Logon', 'Beijing','Beijing'), 
    (1111, '2024-01-02 00:00:02', 'Pay', 'Beijing','Beijing'), 
    (1111, '2024-01-02 00:00:03', 'Exit', 'Beijing','Beijing'), 
    
    (1111, '2024-01-03 00:00:00', 'Registration', 'Beijing','Beijing'), 
    (1111, '2024-01-03 00:00:01', 'Logon', 'Beijing','Beijing'), 
    (1111, '2024-01-03 00:00:02', 'Pay', 'Beijing','Beijing'), 
    
    (1111, '2024-01-04 00:00:00', 'Registration', 'Beijing','Beijing'), 
    (1111, '2024-01-04 00:00:01', 'Logon', 'Beijing','Beijing'), 
    
    (2222, '2024-01-02 00:00:00', 'Registration', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:00', 'Logon', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:01', 'Pay', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:03', 'Pay', 'Zhejiang','Hangzhou'),
    
    (3333, '2024-01-02 00:00:00', 'Registration', 'Shanghai','Shanghai'), 
    (3333, '2024-01-02 00:00:00', 'Logon', 'Shanghai','Shanghai'), 
    (3333, '2024-01-02 00:00:01', 'Pay', 'Shanghai','Shanghai'), 
    (3333, '2024-01-02 00:00:03', 'Pay', 'Shanghai','Shanghai'),
    (3333, '2024-01-02 00:00:04', 'Exit', 'Shanghai','Shanghai');
  2. Analyze the funnel data within 3 days and the funnel data of each day based on the province dimension when four events sequentially occurred. Run the following command:

    SELECT
        key,
        funnel_rep (3, 4, res) AS ans
    FROM (
        SELECT
            id,
            finder_group_funnel_text_group (result) AS key,
            finder_group_funnel_res (result) AS res
        FROM (
            SELECT
                id,
                UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE, event_time, event_time, province, event = 'Registration', event = 'Logon', event = 'Pay', event = 'Exit')) AS result
            FROM
                finder_group_funnel_test_1
            GROUP BY
                id) a) b
    GROUP BY
        key;

    The following result is returned:

       key   |                    ans                    
    ---------+-------------------------------------------
     Beijing    | {"1,1,1,1","1,1,1,1","1,1,1,0","1,1,0,0"}
     unreach | {"0,0,0,0","0,0,0,0","0,0,0,0","0,0,0,0"}
     Shanghai    | {"1,1,1,1","1,1,1,1","0,0,0,0","0,0,0,0"}
     Zhejiang    | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
    (4 rows)

Scenario 2: Display the funnel results of users grouped by calendar day based on the multi-calendar day window

  1. To prepare data, run the following commands:

    CREATE TABLE finder_group_funnel_test_2(id INT, event_time TIMESTAMP, event TEXT, province TEXT,city TEXT);
    
    INSERT INTO finder_group_funnel_test_2 VALUES 
    (1111, '2024-01-02 00:00:02', 'Registration', 'Beijing','Beijing'), 
    (1111, '2024-01-02 00:00:03', 'Logon', 'Beijing','Beijing'), 
    
    (1111, '2024-01-03 00:00:04', 'Pay', 'Beijing','Beijing'), 
    
    (1111, '2024-01-05 00:00:01', 'Exit', 'Beijing','Beijing'), 
    
    
    (2222, '2024-01-02 00:00:00', 'Registration', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:00', 'Logon', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:01', 'Pay', 'Zhejiang','Hangzhou'), 
    (2222, '2024-01-02 00:00:03', 'Pay', 'Zhejiang','Hangzhou');
  2. Analyze the funnel data within 3 days and the funnel data of each calendar day based on the province dimension when four events sequentially occurred. Run the following command:

    SELECT
        key,
        funnel_rep (3, 4, res) AS ans
    FROM (
        SELECT
            id,
            finder_group_funnel_text_group (result) AS key,
            finder_group_funnel_res (result) AS res
        FROM (
            SELECT
                id,
                UNNEST(finder_group_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 0, 1, 'Asia/Shanghai', TRUE, event_time, event_time, province, event = 'Registration', event = 'Logon', event = 'Pay', event = 'Exit')) AS result
            FROM
                finder_group_funnel_test_2
            GROUP BY
                id) a) b
    GROUP BY
        key;

    The following result is returned:

       key   |                    ans                    
    ---------+-------------------------------------------
     unreach | {"0,0,0,0","0,0,0,0","0,0,0,0","0,0,0,0"}
     Zhejiang    | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
     Beijing    | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
    (3 rows)
  • On this page (1, T)
  • Limits
  • Precautions
  • finder_group_funnel
  • Function description
  • Example
  • finder_group_funnel_res
  • Function description
  • Example
  • finder_group_funnel_text_group
  • Function description
  • Example
  • Aggregate function for funnel results (funnel_rep)
  • Function description
  • Example
  • Complete usage example
  • Scenario 1: Display the funnel results of grouped users based on the multi-day window
  • Scenario 2: Display the funnel results of users grouped by calendar day based on the multi-calendar day window
Feedback
phone Contact Us

Chat now with Alibaba Cloud Customer Service to assist you in finding the right products and services to meet your needs.

alicare alicarealicarealicare