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
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
i
th place is 1, which indicates that thei+1
th event has associated properties. In most cases, attr_related is used in conjunction with prop. If you set theattr_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 thegroup_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 theis_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.
NoteCalendar day refers to the period of time
from 00:00:00 to 23:59:59
of each day. The first calendar day ranges from theevent 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 thechannel_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.
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');
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
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.
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');
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
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');
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)