range_funnel

range_funnel calculates funnel conversion results within a sliding time window and breaks results down by a custom grouping interval—for example, daily or hourly conversion counts across a multi-day analysis period.

How it works

range_funnel processes event sequences for each user and counts how far each user progresses through the funnel steps you define:

The function scans each user's events and looks for the first event in the chain.
From that starting event, it opens a time window of the configured length and checks whether subsequent events in the chain occur within it.
For each grouping interval in the analysis period, the function records the deepest step reached.
Results are returned as an encoded BIGINT[] array, with one entry per interval plus an overall summary entry. Use range_funnel_time and range_funnel_level to decode the results.

Unlike windowFunnel, which returns a single aggregated result for the entire period, range_funnel returns both the per-interval breakdown and the overall total. It also supports funnels where the same event appears more than once in the chain.

Matching logic for repeated events:

Chain: c1 → c2 → c3. User events: c1, c2, c1, c3. Result: 3 (all three steps matched).
Chain: c1 → c1 → c1. User events: c1, c2, c1, c3. Result: 2 (only two matching c1 events found).

Prerequisites

Before you begin, ensure that you have:

Hologres V2.1 or later
Superuser access to the database

Install the flow_analysis extension by running the following statement as a superuser:

CREATE extension flow_analysis;

The extension is installed at the database level—install it once per database. It is always loaded into the public schema and cannot be moved to another schema.

Limitations

range_funnel requires Hologres V2.1 or later. The use_interval_window and mode parameters require Hologres V2.2.30 or later, or V3.0.17 or later. The range_funnel_time and range_funnel_level decoding functions require Hologres V2.1.6 or later.

Function reference

Syntax

range_funnel(window, event_size, range_begin, range_end, interval, event_ts, event_bits[, use_interval_window[, mode]])

Parameters

Parameter	Type	Required	Description
`window`	INTERVAL	Yes	The length of the time window, in seconds. The window starts from the first matching event. Set to `0` to align windows to calendar boundaries (for example, midnight each day). When `use_interval_window=true`, the value represents the number of time periods (including the current one) to include in the window.
`event_size`	INT	Yes	The total number of events in the funnel chain.
`range_begin`	TIMESTAMPTZ / TIMESTAMP / DATE	Yes	The start of the analysis period.
`range_end`	TIMESTAMPTZ / TIMESTAMP / DATE	Yes	The end of the analysis period.
`interval`	INTERVAL	Yes	The length of each grouping interval, in seconds. The analysis period is divided into consecutive intervals of this length, and funnel analysis runs independently on each.
`event_ts`	TIMESTAMP / TIMESTAMPTZ	Yes	The timestamp of each event. Calculated from 00:00, so the time component may not reflect the actual wall-clock time. Use this parameter for day-level or week-level trend analysis.
`event_bits`	Bitmap (INT32)	Yes	A bitmask representing which events occurred. Each bit position (from least significant to most significant) represents one event. Supports up to 32 events. Use `bit_construct` to build this value from boolean expressions.
`use_interval_window`	TEXT	No	Specifies whether to use the interval length to define the time window. Default: `false`. When set to `true`, the `window` parameter specifies how many time periods (including the current one) the window spans—useful for cross-interval conversion analysis. Requires V2.2.30+ or V3.0.17+.
`mode`	TEXT	No	Controls how simultaneous events are handled. Default: `'0'`. Requires V2.2.30+ or V3.0.17+. See Mode options below.

Mode options

The mode parameter determines how the function handles events that occur at the exact same timestamp.

Value	Behavior	Event chain example
`'0'` (default)	When multiple events occur at the same timestamp, one is randomly selected as the conversion; the rest are discarded.	Chain: A → B → C. Events: A at t=1, B at t=1, C at t=2. Only one of A or B counts—the chain can reach at most step 1 or step 2 depending on which is selected.
`'1'`	Different events at the same timestamp each count as a separate conversion. Identical events at the same timestamp are not supported in this mode.	Chain: A → B → C. Events: A at t=1, B at t=1, C at t=2. Both A and B count—the chain reaches step 3 (A matches step 1, B matches step 2, C matches step 3).

Return value

range_funnel returns a BIGINT[] array. Each element is a 64-bit encoded integer:

Bits 8–63 (56 bits): the start timestamp of the interval.
Bits 0–7 (8 bits): the funnel step reached in that interval.

Decode the array with range_funnel_time and range_funnel_level before interpreting results. A NULL entry in the decoded output (\N) represents the aggregate across all intervals.

Decoding functions

Use UNNEST to expand the array, then wrap each element with range_funnel_time or range_funnel_level.

Function	Input	Output	Description
`range_funnel_time(element)`	One `BIGINT` element from the `range_funnel` array	`TIMESTAMPTZ`	Decodes the interval start time.
`range_funnel_level(element)`	One `BIGINT` element from the `range_funnel` array	`BIGINT`	Decodes the funnel step reached (0 = no match, N = matched N steps).

Syntax

range_funnel_time(range_funnel())
range_funnel_level(range_funnel())

range_funnel_time and range_funnel_level require Hologres V2.1.6 or later.

Examples

Analyze daily conversions over a multi-day period

This example uses the public GitHub event dataset to track how many users progressed from CreateEvent to PushEvent each day over a three-day period.

Analysis parameters:

Time window: 1 hour (3,600 seconds)
Analysis period: 2024-01-29 to 2024-01-31 (3 days)
Conversion path: CreateEvent → PushEvent
Grouping interval: 1 day (86,400 seconds)

The type column contains text values, but event_bits requires a 32-bit bitmap. Use bit_construct to convert the text field to a bitmap.

Step 1: Run the funnel query (encoded output)

SELECT
    actor_id,
    range_funnel(3600, 2, '2024-01-29', '2024-01-31', 86400, created_at::TIMESTAMP, bits) AS result
FROM (
    SELECT
        actor_id,
        created_at::TIMESTAMP,
        type,
        bit_construct(a := type = 'CreateEvent', b := type = 'PushEvent') AS bits
    FROM hologres_dataset_github_event.hologres_github_event
    WHERE ds >= '2024-01-29' AND ds <= '2024-01-31'
) tt
GROUP BY actor_id
ORDER BY actor_id;

Sample output (encoded):

actor_id  | result
----------+------------------------------------------------------------
17        | {436860518400,436882636800,9223372036854775552}
47        | {436860518400,436882636800,9223372036854775552}
235       | {436860518401,436882636800,9223372036854775553}

An empty result means the user's events did not match the funnel within any interval.

Step 2: Decode results by user and day

SELECT actor_id,
    TO_TIMESTAMP(range_funnel_time(result)) AS res_time,  -- Interval start time
    range_funnel_level(result) AS res_level               -- Funnel step reached
FROM (
    SELECT actor_id, result, COUNT(1) AS cnt FROM (
        SELECT actor_id,
            UNNEST(range_funnel(3600, 2, '2024-01-29', '2024-01-31', 86400, created_at::TIMESTAMP, bits)) AS result
        FROM (
            SELECT actor_id, created_at::TIMESTAMP, type,
                bit_construct(a := type = 'CreateEvent', b := type = 'PushEvent') AS bits
            FROM hologres_dataset_github_event.hologres_github_event
            WHERE ds >= '2024-01-29' AND ds <= '2024-01-31'
        ) a
        GROUP BY actor_id
    ) a
    GROUP BY actor_id, result
) a
ORDER BY actor_id, res_time
LIMIT 10000;

Sample output:

actor_id  | res_time              | res_level
----------+-----------------------+-----------
17        | 2024-01-29 08:00:00+08 | 0
17        | 2024-01-30 08:00:00+08 | 0
75        | 2024-01-29 08:00:00+08 | 2
75        | \N                    | 2
76        | 2024-01-29 08:00:00+08 | 0
76        | 2024-01-30 08:00:00+08 | 1
141       | 2024-01-29 08:00:00+08 | 2
141       | \N                    | 2
211       | 2024-01-30 08:00:00+08 | 1
235       | 2024-01-30 08:00:00+08 | 0
235       | \N                    | 1

Reading the results:

res_level = 0: the user triggered no matching events on that day.
res_level = 1: the user completed step 1 (CreateEvent) but not step 2.
res_level = 2: the user completed both steps within the 1-hour window.
res_time = \N: the aggregate result across the entire analysis period (not a single day).

Row-by-row interpretation:

actor 17: reached level 0 on both Jan 29 and Jan 30—no matching events on either day.
actor 75: reached level 2 on Jan 29 (completed the full CreateEvent → PushEvent path within 1 hour), confirmed by the \N aggregate also showing level 2.
actor 76: reached level 0 on Jan 29 (no match) and level 1 on Jan 30 (CreateEvent occurred but PushEvent did not follow within 1 hour).
actor 235: reached level 0 on Jan 30 and level 1 in the overall aggregate (\N)—meaning a CreateEvent was matched across the period but PushEvent never followed within the window.

Step 3: Summarize daily step counts

This query rolls up the per-user results into a daily step summary. Each res_cnt value is cumulative: level N includes all users who reached at least N steps (because each higher level is a subset of the previous).

SELECT res_time, res_level, SUM(cnt) OVER (PARTITION BY res_time ORDER BY res_level DESC) AS res_cnt
FROM (
    SELECT
        TO_TIMESTAMP(range_funnel_time(result)) AS res_time,  -- Interval start time
        range_funnel_level(result) AS res_level,              -- Funnel step reached
        cnt
    FROM (
        SELECT result, COUNT(1) AS cnt FROM (
            SELECT actor_id,
                UNNEST(range_funnel(3600, 2, '2024-01-28', '2024-01-31', 86400, created_at::TIMESTAMP, bits)) AS result
            FROM (
                SELECT actor_id, created_at::TIMESTAMP, type,
                    BIT_CONSTRUCT(a := type = 'CreateEvent', b := type = 'PushEvent') AS bits
                FROM hologres_dataset_github_event.hologres_github_event
                WHERE ds >= '2024-01-28' AND ds <= '2024-01-30'
            ) a
            GROUP BY actor_id
        ) a
        GROUP BY result
    ) a
) a
WHERE res_level > 0
GROUP BY res_time, res_level, cnt
ORDER BY res_time, res_level;

Sample output:

res_time               | res_level | res_cnt
-----------------------+-----------+---------
2024-01-28 08:00:00+08 | 1         | 131212
2024-01-28 08:00:00+08 | 2         | 62371
2024-01-29 08:00:00+08 | 1         | 172505
2024-01-29 08:00:00+08 | 2         | 79667
2024-01-30 08:00:00+08 | 1         | 198585
2024-01-30 08:00:00+08 | 2         | 90291
\N                     | 1         | 440332
\N                     | 2         | 208942

On 2024-01-28, 131,212 users reached at least step 1 (CreateEvent), and 62,371 of them also completed step 2 (PushEvent) within the 1-hour window. The \N row shows the three-day aggregate: 440,332 users reached step 1, and 208,942 completed the full path.

Count simultaneous different events as separate conversions (mode='1')

By default (mode='0'), if two different events occur at the same timestamp, only one counts. Set mode='1' to count each distinct simultaneous event as a separate conversion.

Create a test table and insert sample data:

CREATE TABLE funnel_test (
    uid INT,
    event TEXT,
    create_time TIMESTAMPTZ
);

INSERT INTO funnel_test VALUES
    (11, 'login', '2024-09-26 16:15:28+08'),
    (11, 'watch', '2024-09-26 16:15:28+08'),  -- same timestamp as login
    (11, 'buy',   '2024-09-26 16:16:28+08'),
    (22, 'login', '2024-09-26 16:15:28+08'),
    (22, 'watch', '2024-09-26 16:16:28+08'),
    (22, 'buy',   '2024-09-26 16:17:28+08');

For uid 11, login and watch occur at the same timestamp. With mode='1', both events count toward the funnel chain.

SELECT res_time, res_level, SUM(cnt) OVER (PARTITION BY res_time ORDER BY res_level DESC) AS res_cnt
FROM (
    SELECT
        TO_TIMESTAMP(range_funnel_time(result)) AS res_time,
        range_funnel_level(result) AS res_level,
        cnt
    FROM (
        SELECT result, COUNT(1) AS cnt FROM (
            SELECT uid,
                UNNEST(range_funnel(3600, 3, '2024-09-26', '2024-09-27', 86400, create_time::TIMESTAMP, bits, false, '1')) AS result
            FROM (
                SELECT uid, create_time::TIMESTAMP, event,
                    BIT_CONSTRUCT(a := event = 'login', b := event = 'watch', c := event = 'buy') AS bits
                FROM funnel_test
            ) a
            GROUP BY uid
        ) a
        GROUP BY result
    ) a
) a
GROUP BY res_time, res_level, cnt
ORDER BY res_time, res_level;

Output:

res_time               | res_level | res_cnt
-----------------------+-----------+---------
2024-09-26 08:00:00+08 |         3 |       2
                       |         3 |       2
(2 rows)

Both users reached level 3:

uid 11: login and watch occur at the same timestamp. With mode='1', both count as separate conversions—login matches step 1 and watch matches step 2 simultaneously, then buy matches step 3. The full login → watch → buy chain is matched.
uid 22: events occur at different timestamps, so the chain is matched normally regardless of mode.

Analyze cross-day conversions with a multi-day time window (use_interval_window=true)

When your conversion path spans multiple days, set use_interval_window=true. The window parameter then specifies the number of grouping intervals (calendar days in this example) to include in the sliding window.

Create a test table and insert sample data:

CREATE TABLE funnel_test_2 (
    uid INT,
    event TEXT,
    create_time TIMESTAMPTZ
);

INSERT INTO funnel_test_2 VALUES
    (11, 'login', '2024-09-24 16:15:28+08'),
    (11, 'watch', '2024-09-25 16:15:28+08'),
    (11, 'buy',   '2024-09-26 16:16:28+08'),
    (22, 'login', '2024-09-24 16:15:28+08'),
    (22, 'watch', '2024-09-25 16:16:28+08'),
    (22, 'buy',   '2024-09-26 16:17:28+08');

Both users' events span three separate calendar days. Set window=3 with use_interval_window=true to capture conversions that start on one day and complete within the following two days.

-- Time window: 3 calendar days
SELECT
    TO_TIMESTAMP(range_funnel_time(result)) AS res_time,
    range_funnel_level(result) AS res_level,
    cnt
FROM (
    SELECT result, COUNT(1) AS cnt FROM (
        SELECT uid,
            UNNEST(range_funnel(3, 3, '2024-09-24', '2024-09-27', 86400, create_time::TIMESTAMP, bits, true, '1')) AS result
        FROM (
            SELECT uid, create_time::TIMESTAMP, event,
                BIT_CONSTRUCT(a := event = 'login', b := event = 'watch', c := event = 'buy') AS bits
            FROM funnel_test_2
        ) a
        GROUP BY uid
    ) a
    GROUP BY result
) a;

Output:

res_time               | res_level | cnt
-----------------------+-----------+-----
2024-09-26 08:00:00+08 |         0 |   2
                       |         3 |   2
2024-09-24 08:00:00+08 |         3 |   2
2024-09-25 08:00:00+08 |         0 |   2
(4 rows)

Row-by-row interpretation:

2024-09-24, level 3: Both users started with login on Sep 24. With a 3-day window, the function looks ahead through Sep 24, 25, and 26—covering watch (Sep 25) and buy (Sep 26). All three steps are matched, so both users reach level 3 starting from this day.
2024-09-25, level 0: watch occurs on Sep 25, but login (step 1) did not occur on Sep 25—no chain starts here.
2024-09-26, level 0: buy occurs on Sep 26, but again no chain starts on this day because login is absent.
`\N`, level 3: The overall aggregate confirms both users completed the full funnel across the period.