This topic describes funnel analysis functions.
FUNNEL_COUNT
Syntax
funnel_count(<Event sending timestamp:bigint>, <Event window range in milliseconds:bigint>, <Event ID:int/varchar>, <Event ID list:varchar>)Description
-
Filters all data within the range of the time window.
-
Sorts the filtered events based on the timestamp. For example, e1 is sent at t1, e2 is sent at t2, e3 is sent at t3, and e4 is sent at t4.
-
Returns the maximum number of ordered, matched events from the first event based on the event list. If a match fails, the entire match ends.
Assume that you want to query events e1, e2, and e3, and the user data is e1, e2, and e4. The matched events are e1 and e2. Therefore, the function returns 2.
Assume that you want to query events e1, e2, and e3, and the user data is e2, e1, and e3. The matched event is e1. Therefore, the function returns 1.
Assume that you want to query events e1, e2, and e3, and the user data is e4 and e3. No events are matched. Therefore, the function returns 0.
You can use the FUNNEL_COUNT function to query the longest matched path from the start event to the end event. This allows you to analyze the conversion rate. If you call the FUNNEL_COUNT function from an intermediate event or only on a few events, the conversion rate is not affected.
Example
select uid, funnel_count(event_time, 10, event_id, '1,3,6,7')
as max_ordered_match_length
from (
SELECT * FROM VALUES
(1,2,1),
(3,4,1),
(1,4,3),
(1,5,3),
(2,5,3),
(3,6,3),
(4,1,1),
(4,11,3),
(1,7,6)
t1 (
uid, event_time, event_id
)
)as tmp
group by uid;| uid | max_ordered_match_length |
+------+--------------------------+
| 1 | 3 |
| 3 | 2 |
| 2 | 0 |
| 4 | 1 |
-- User 1 triggers event 1 at time point 2, event 3 at time points 4 and 5, and event 6 at time point 7. Based on the concerned event list that contains event 1, event 3, event 6, and event 7, the matched events are event 1, event 3, and event 6. Therefore, the function returns 3.
-- User 3 triggers event 1 at time point 4 and event 3 at time point 6. Based on the concerned event list that contains event 1, event 3, event 6, and event 7, the matched events are event 1 and event 3. Therefore, the function returns 2.
-- User 2 triggers event 3 at time point 5. Based on the concerned event list that contains event 1, event 3, event 6, and event 7, no events are matched. Therefore, the function returns 0.
-- User 4 triggers event 1 at time point 1 and event 3 at time point 11. Based on the event list that contains event 1, event 3, event 6, and event 7, the matched event is event 1. Therefore, the function returns 1. Event 3 is not matched because it is triggered beyond the range of the time window.FUNNEL_SUM
Syntax
funnel_sum(<The longest event matching link that is calculated by using the funnel_count function:int}, and <The total number of events:int>)Description
The FUNNEL_SUM function is also an aggregate function. It is used to perform secondary aggregation based on the results that are obtained by using the FUNNEL_COUNT function. The FUNNEL_SUM function is used to match the number of consecutive events that each user completes with the number of events that you want to query.
Example
select funnel_sum(max_ordered_match_length, 4)
as conversion_link
from (
SELECT * FROM VALUES
(1,3), --- Results from the preceding example
(3,2),
(2,0),
(4,1)
t1 (
uid, max_ordered_match_length
)
)as tmp| conversion_link |
+-----------------+
| [3, 2, 1, 0] |
-- User 1 completes three consecutive events, user 3 completes two, user 4 completes one, and user 2 completes zero. If you want to query the number of users who complete four consecutive events, execute the following statement:
-- funnel_sum(max_ordered_match_length, 4) = [3, 2, 1, 0],
-- Three users (user 1, user 2, and user 3) complete one or more events.
-- Two users (user 1 and user 3) complete two or more events.
-- One user (user 1) completes three events.
-- No users complete four or more events.
-- Therefore, the numbers of users who complete one, two, three, and four events are three, two, one, and zero.