Hologres:Property association funnel function (finder_funnel)

Background information

The finder_funnel function can be used to associate events by property. For example, you configure a funnel in gaming scenarios, and the funnel includes the following steps: logon to a game, entering the game, playing the game, game level settlement, and logout from the game. In addition, you specify the "country" property for each event. In this case, you can use this property as the association ID to ensure that the conversion in each step is performed based on the same property value. The properties used for association in different steps in a funnel can be the same or different. However, the properties used for association must be of the same type.

Limits

Only Hologres V2.2.32 or later and Hologres V3.0.12 or later support the finder_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_funnel (property association funnel function)

This function is used to calculate the performance of each user within a specific time window and supports association analysis in specified event dimensions.

• Function syntax:

  finder_funnel(window, start_timestamp, step_interval, step_numbers, num_events, attr_related, time_zone, is_relative_window, server_timestamp, client_timestamp, 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 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_numbers=7 indicates 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	Indicates whether the event has associated properties. The parameter values are INT-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, the attr_related parameter must be used together with the prop parameter. The number of prop expressions that you need to enter must be the same as the number of 1 configured in binary. Note If the attr_related parameter is set to 0, the event does not have associated properties.
  time_zone	Yes	The time zone that corresponds to the input time. The parameter values are of the TEXT type and must use a standard time zone format, such as Asia/Shanghai. Only when the is_relative_window parameter is set to true, the output results are affected, which means that the results are displayed based on the time zone.
  is_relative_window	Yes	Indicates whether the window is a multi-calendar day window. The default value is false. If this parameter is set 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 time period from 00:00:00 to 23:59:59 of each day. The first calendar day starts from the event time and ends at 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 the time specified by this parameter must be the same as that specified by the start_timestamp parameter. This parameter is used to sort data when the function is running.
  prop	Yes	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 events that need to be analyzed. Within a duration defined by the window parameter, events that meet specific conditions are considered valid events and participate in conversion analysis. For example, if you want to analyze three events, enter EventName = 'E0001', EventName = 'E0002', EventName = 'E0003'.

• Returned result

  The returned result of this function is {<The nth event that is finally reached>, <The nth event that is finally reached at each step>}.

  Note

  The nth event that is finally reached indicates the event whose place number is the highest among all the steps.

funnel_rep (funnel result set function)

The funnel_rep function allows you to aggregate the funnel results calculated by using the finder_funnel function. For more information, see Dimension grouping funnel function (finder_group_funnel).

Sample data

You must execute the following statements to prepare data first:

CREATE TABLE finder_funnel_test_1(id INT, event_time TIMESTAMP, event TEXT, province TEXT,city TEXT);

INSERT INTO finder_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', 'Payment', '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', 'Payment', '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', 'Payment', 'Zhejiang','Hangzhou'), 
(2222, '2024-01-02 00:00:03', 'Payment', 'Zhejiang','Hangzhou');

Examples

-- The query window is three days, the step size is 3 and is split by day, and a total of four events participate in conversion. 
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 = 'Payment', event = 'Exit') AS funnel_res
FROM
    finder_funnel_test_1
GROUP BY
    id;

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

SELECT
    id,
    finder_funnel (86400000 * 3, EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT, 86400, 3, 4, 3, 'Asia/Shanghai', FALSE, event_time, event_time, province, province, event = 'Registration', event = 'Logon', event = 'Payment', event = 'Exit') AS funnel_res
FROM
    finder_funnel_test_1
GROUP BY
    id;

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

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, 3, 'Asia/Shanghai', FALSE, event_time, event_time, province, province, event = 'Registration', event = 'Logon', event = 'Payment', event = 'Exit') AS funnel_res
    FROM
        finder_funnel_test_1
    GROUP BY
        id) a;

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