Hologres:finder_group_funnel

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

Return value

An encoded result of type BINARY. Decode it with finder_group_funnel_res and finder_group_funnel_text_group.

Example

The following example groups funnel results by province over a 3-day window. User 1111 is from Beijing and user 2222 is from Zhejiang.

Step 1: Create the test table and insert data.

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', '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');

Step 2: Group results by province over a 3-day window.

SELECT
    id,
    UNNEST(
        finder_group_funnel(
            86400000 * 3,                                              -- 3-day window
            EXTRACT(epoch FROM TIMESTAMP '2024-01-02 00:00:00')::BIGINT,
            86400,                                                     -- step_interval: 1 day
            3,                                                         -- step_numbers: 3 days
            4,                                                         -- num_events: 4 events
            0,                                                         -- attr_related: no associated properties
            1,                                                         -- group_event_index: group on first event
            'Asia/Shanghai',
            FALSE,
            event_time,
            event_time,
            province,                                                  -- group_dimension
            event = 'registration',
            event = 'logon',
            event = 'payment',
            event = 'exit'
        )
    ) AS result
FROM finder_group_funnel_test
GROUP BY id;

The result is BINARY-encoded. Each row shows the user ID and their group label (province value or unreach):

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

To decode the detailed funnel progress for each group, use finder_group_funnel_res.

Syntax

finder_group_funnel_res(finder_group_funnel(...))

Parameters

Return value

An integer array showing the furthest event reached in the overall window and in each step interval.

Example

Using the table from the finder_group_funnel example, decode each user's per-day funnel progress:

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 = 'register',
                event = 'logon',
                event = 'pay',
                event = 'exit'
            )
        ) AS result
    FROM finder_group_funnel_test
    GROUP BY id
) a;

Result:

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

Reading the result array: Each array has 1 + step_numbers elements. The first element is the furthest event reached across the entire window; the remaining elements are the furthest event reached within each step interval (day, in this case).

For user 1111 with result {4,4,3,2}:

Syntax

finder_group_funnel_text_group(finder_group_funnel(...))

Parameters

Return value

The decoded group label as text — the dimension value (for example, Beijing) or unreach.

Example

The following query decodes both the group label and per-day funnel progress in a single pass:

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 = 'payment',
                event = 'exit'
            )
        ) AS result
    FROM finder_group_funnel_test
    GROUP BY id
) a;

Result:

  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)

Syntax

funnel_rep(step_number, num_events, funnel_res)

Parameters

Return value

A string array in the format {"overall","step1","step2",...}. Each element is a comma-separated list of user counts that reached events 1 through N:

• The first element is the overall count across the full window.

• Each subsequent element is the count within that step interval.

Example

Using the data from the finder_group_funnel example, calculate how many users reached each event across a 3-day window with a 1-day step:

-- 3-day window, 1-day step: count users reaching each event per day.
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 = 'payment',
            event = 'exit'
        ) AS funnel_res
    FROM finder_group_funnel_test
    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)

Reading this result with step_numbers=3 and num_events=4:

Scenario 1: Group funnel results by dimension over a multi-day window

Goal: An e-commerce team wants to compare how users from Beijing, Shanghai, and Zhejiang convert through a 4-step flow (registration → logon → payment → exit) within a 3-day rolling window, with daily breakdowns. The team expects Beijing users to show the highest completion rate across all days.

Step 1: Create the test table and insert data.

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', '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'),

(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', 'payment',      'Shanghai', 'Shanghai'),
(3333, '2024-01-02 00:00:03', 'payment',      'Shanghai', 'Shanghai'),
(3333, '2024-01-02 00:00:04', 'exit',         'Shanghai', 'Shanghai');

Step 2: Query daily funnel data grouped by province.

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 = 'Register',
                    event = 'Log on',
                    event = 'Payment',
                    event = 'Exit'
                )
            ) AS result
        FROM finder_group_funnel_test_1
        GROUP BY id
    ) a
) b
GROUP BY key;

Result:

   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)

Beijing's user completed all 4 events on day 1 and continued converting on days 2 and 3. Shanghai's user also completed all 4 events but only on day 1 (no follow-up activity). Zhejiang's user reached only 3 of 4 events.

Scenario 2: Group funnel results by calendar day

Goal: The same team wants to observe funnel data aligned to calendar-day boundaries (midnight to midnight) rather than a rolling elapsed-time window. This matches how business reporting periods are typically defined. With a rolling window, the exit event on 2024-01-05 might still fall within 3 days of the first event; with calendar-day boundaries, it does not.

Step 1: Create the test table and insert data.

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', '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');

Step 2: Query with calendar-day boundaries (`is_relative_window=TRUE`).

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, -- calendar-day window
                    event_time, event_time,
                    province,
                    event = 'register',
                    event = 'logon',
                    event = 'pay',
                    event = 'exit'
                )
            ) AS result
        FROM finder_group_funnel_test_2
        GROUP BY id
    ) a
) b
GROUP BY key;

Result:

   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)

With calendar-day boundaries, user 1111 (Beijing) completes only 3 of 4 events within the 3-day window. Their exit event occurs on 2024-01-05, which falls outside the calendar-day window starting 2024-01-02 (covering January 2, 3, and 4). Under a rolling window, the same event would be captured because it falls within 3 × 86,400 seconds of the first event.