Hologres:Funnel functions

Display funnel information by day

The data in the public event datasets on GitHub in the preceding scenario description is used in this example. If you want to analyze the funnel data in which user events match the order of the specified events within a specified period of time and view the grouping result by day, you can specify the following conditions in the SQL statement:

• Time window: 1 hour, which equals 3,600 seconds.

• Time period: 2023-07-23 to 2023-07-25, 3 days in total

• Event order: CreateEvent > PushEvent.

• Grouping interval: 1 day, which equals 86,400 seconds. The window funnel analysis results are displayed by day.

• The value of the type field is of the TEXT type. The value of the event_bits field in the range_funnel function must be a 32-bit bitmap. Therefore, you must use the bit_construct function to convert the value of the type field into a bitmap.

-- Result without decoding
SELECT
        actor_id,
        range_funnel (3600, 2, '2023-07-23', '2023-07-25', 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 >= '2023-07-23' AND ds <='2023-07-25') tt GROUP BY actor_id ORDER BY actor_id ;

The following content is a part of the returned result:

actor_id	| result
----------+------
17	      |{432658022400,432680140800,432702259202,9223372036854775554}
47      	|{432658022402,432680140800,432702259200,9223372036854775554}
235       |{432658022401,432680140800,432702259200,9223372036854775553}

Description of the result field:

• If the value of the result field is empty, the behavior of the user does not match the condition within any interval.

• If the value of the result field contains data, the data is an undecoded array that includes the overall funnel result by day.

In this example, the range_funnel_time and range_funnel_level functions are used to decode the result that is returned by the range_funnel function in the preceding example. The decoding results are displayed by user ID. Sample SQL statement:

SELECT actor_id,
to_timestamp(range_funnel_time(result)) AS res_time, -- Decode the event time.
range_funnel_level(result) AS res_level -- Decode the event level.
FROM (
    SELECT actor_id, result, COUNT(1) AS cnt FROM (
        SELECT actor_id, 
        UNNEST(range_funnel (3600, 2, '2023-07-23', '2023-07-26', 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 >= '2023-07-23' AND ds <='2023-07-25'
        ) a
        GROUP BY actor_id
    ) a
    GROUP BY actor_id ,result
) a
ORDER BY actor_id ,res_time limit 10000;

The following content is a part of the returned result, from which you can obtain the level and number of matches per user per day:

actor_id	| res_time	        | res_level	
----------------+-----------------------+-----------
17      	|2023-07-23 08:00:00	| 0	        
17      	|2023-07-24 08:00:00	| 0        
17      	|2023-07-24 08:00:00    | 2	 
17              |\N	                | 0
47	        |2023-07-23 08:00:00	| 0
47	        |2023-07-24 08:00:00	| 0
47	        |2023-07-25 08:00:00	| 2
47	        |\N	                | 2 
235	        |2023-07-23 08:00:00	| 1
235	        |2023-07-24 08:00:00	| 0
235	        |2023-07-25 08:00:00	| 0
235	        |\N	                | 1

After you obtain the funnel result of each user by day, you can further explore the data based on your business requirements. For example, you can execute the following SQL statement to view the daily step size summary and total summary data, where the next level contains the data of the previous level:

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, -- Decode the event time.
    range_funnel_level(result) AS res_level, -- Decode the event level.
        cnt 
    FROM (
        SELECT result, COUNT(1) AS cnt FROM (
            SELECT actor_id, 
            UNNEST(range_funnel (3600, 2, '2023-07-23', '2023-07-26', 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 >= '2023-07-23' AND ds <='2023-07-25'
            ) 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;

The following content shows the query result:

• \N indicates the result aggregated for multiple days.

• The cnt field includes the summary data of each level. The next level contains the data of the previous level. For example, if the value of the res_level field is 2 and the value of the cnt field is 1, only one user goes through steps 1 and 2.

res_time	        |res_level	|  cnt
------------------------+---------------+------
2023-07-23 08:00:00	|1	        |114455
2023-07-23 08:00:00	|2	        |56155
2023-07-24 08:00:00	|1	        |154616
2023-07-24 08:00:00	|2	        |71200
2023-07-25 08:00:00	|1	        |178662
2023-07-25 08:00:00	|2	        |81779
\N	                |1	        |390262
\N	                |2	        |188125

Count different events that occur at the same time as different conversions

If you set the mode parameter in the range_funnel function to 1, different events that occur at the same time are counted as different conversions. Sample statements:

CREATE extension flow_analysis;

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

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, -- Decode the event time.
    range_funnel_level(result) AS res_level, -- Decode the event 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;

The following output shows that different events that occur at the same time are counted as different conversions.

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

Perform statistics by day with the time window set to multiple calendar days

In actual scenarios, you may need to analyze conversion rates across intervals. In this case, you can set the use_interval_window parameter in the range_funnel function to true. In this example, the time window is set to multiple calendar days.

-- Perform statistics by day with the time window set to multiple calendar days.
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');

Calculate conversion rates when the time window is set to multiple calendar days.

-- The time window is set to three calendar days.
    SELECT 
    to_timestamp(range_funnel_time(result)) AS res_time, -- Decode the event time.
    range_funnel_level(result) AS res_level, -- Decode the event 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;

The following output shows the daily conversion status.

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)