Hologres:finder_funnel

How attr_related works

attr_related uses a binary bitmask to specify which events have associated properties.

For example, with four events (E1, E2, E3, E4):

When attr_related=3 (binary 11), events E1 and E2 are associated by property. Provide two <prop> expressions — one for each event. E3 and E4 are not associated, so no <prop> expressions are needed for them.

Calendar day window

When is_relative_window=true, the window is split into calendar days (00:00:00 to 23:59:59 each day).

When is_relative_window=true, the following constraints apply:

<window> must be an integer multiple of 86,400,000 (milliseconds per day).

<step_interval> must be 86400 (one step = one day).

The first calendar day starts from the event time and ends at 23:59:59 of the same day. Each subsequent day is a full calendar day. Use this mode to observe daily funnel data for refined operations analysis.

Example 1: Basic funnel without property association

A 3-day window split into 3 daily steps, tracking 4 events, with no property association (attr_related=0):

SELECT
    id,
    finder_funnel(
        86400000 * 3,                                           -- 3-day window
        EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT,
        86400,                                                  -- 1-day steps
        3,                                                      -- 3 steps
        4,                                                      -- 4 events
        0,                                                      -- no property association
        '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;

Result:

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

• id=1111, {4,4,3,2}: User 1111 ultimately reached event 4 (Exit). In step 1 (Jan 2), they reached event 4; in step 2 (Jan 3), event 3 (Payment); in step 3 (Jan 4), event 2 (Logon).

• id=2222, {3,3,0,0}: User 2222 reached event 3 (Payment) in step 1, with no further progression.

Example 2: Funnel with property association

Set attr_related=3 (binary 11) so that events 1 (Registration) and 2 (Logon) are associated by the province property. Conversion between these events is tracked only when both events share the same province value.

SELECT
    id,
    finder_funnel(
        86400000 * 3,
        EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT,
        86400,
        3,
        4,
        3,               -- attr_related=3 (binary 11): events 1 and 2 have associated properties
        'Asia/Shanghai',
        FALSE,
        event_time,
        event_time,
        province,        -- prop for event 1 (Registration)
        province,        -- prop for event 2 (Logon)
        event = 'Registration',
        event = 'Logon',
        event = 'Payment',
        event = 'Exit'
    ) AS funnel_res
FROM
    finder_funnel_test_1
GROUP BY
    id;

Result:

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

The results show the highest event each user reached — and the highest event per step — after applying the property association constraint.

Example 3: Aggregate results with funnel_rep

Use funnel_rep to sum up the per-user finder_funnel results into a group-level view:

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;

Result:

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

Example 4: Calendar day window

This example uses a 3-calendar-day window (is_relative_window=TRUE) to observe daily funnel data.

1. Create the table and insert data:

   CREATE TABLE finder_funnel_test_3(id INT, event_time TIMESTAMP, event TEXT, province TEXT, city TEXT);
   
   INSERT INTO finder_funnel_test_3 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', 'Payment',      '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', 'Payment',      'Zhejiang', 'Hangzhou'),
   (2222, '2024-01-02 00:00:03', 'Payment',      'Zhejiang', 'Hangzhou');

2. Run the query with a calendar day window:

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

   Result:

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

   For id=1111: the first calendar day runs from 2024-01-02 00:00:02 to 23:59:59. January 3 and January 4 are the second and third calendar days. January 5 falls outside the 3-day window, so the Exit event is not counted — user 1111 reaches event 3 (Payment) as the final result.