ClickHouse windowFunnel Function

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)

Official Documentation

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

  1. Ensure that the timestamp column is properly sorted for accurate results.
  2. Be mindful of the window size, as very large windows may impact performance.

Best practices

  1. Use appropriate indexing on the timestamp and condition columns for better performance.
  2. Consider using the 'strict' mode for stricter event sequence matching.
  3. 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.

Pulse - Elasticsearch Operations Done Right

Pulse can solve your Elasticsearch issues

Subscribe to the Pulse Newsletter

Get early access to new Pulse features, insightful blogs & exclusive events , webinars, and workshops.

We use cookies to provide an optimized user experience and understand our traffic. To learn more, read our use of cookies; otherwise, please choose 'Accept Cookies' to continue using our website.