The windowFunnel function in ClickHouse is used for funnel analysis, particularly in tracking event sequences within a specified time window. It's valuable for analyzing user behavior, conversion paths, and identifying drop-off points in a sequence of events.
Syntax
windowFunnel(window, [mode], timestamp, cond1, cond2, ..., condN)
Example usage
SELECT
user_id,
windowFunnel(3600)(
timestamp,
action = 'visit',
action = 'add_to_cart',
action = 'purchase'
) AS funnel_level
FROM user_actions
GROUP BY user_id;
Common issues
- Ensure that the timestamp column is properly sorted for accurate results.
- Be mindful of the window size, as very large windows may impact performance.
Best practices
- Use appropriate indexing on the timestamp and condition columns for better performance.
- Consider using the 'strict' mode for stricter event sequence matching.
- Combine with other ClickHouse functions like uniqExact() to get more insights from your funnel analysis.
Frequently Asked Questions
Q: How does the window parameter in windowFunnel function work?
A: The window parameter defines the maximum time difference between the first and last events in the sequence. Events outside this window are not considered part of the same funnel.
Q: Can windowFunnel handle out-of-order events?
A: By default, windowFunnel assumes events are in order. Use the 'strict' mode for stricter ordering requirements or when dealing with potentially out-of-order events.
Q: What's the maximum number of conditions windowFunnel can handle?
A: windowFunnel can handle up to 32 conditions (steps) in the funnel sequence.
Q: How does windowFunnel differ from funnelStep?
A: windowFunnel is more flexible and allows for analyzing event sequences within a time window, while funnelStep is simpler and doesn't consider time windows between events.
Q: Can windowFunnel be used with non-boolean conditions?
A: Yes, windowFunnel can use any expression that returns a boolean result as a condition, not just equality checks.
