behavioral

3.1k downloads this week 18 stars Extension descriptor

Behavioral analytics functions inspired by ClickHouse (sessionize, retention, window_funnel, window_funnel_events, sequence_match, sequence_count, sequence_match_events, sequence_next_node)

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL behavioral FROM community;
LOAD behavioral;

Example

-- Funnel: how far did each user get through view -> cart -> buy within 1 hour?
SELECT user_id,
       window_funnel(INTERVAL '1 hour', ts, e = 'view', e = 'cart', e = 'buy') AS furthest_step
FROM (VALUES (1, TIMESTAMP '2024-01-01 10:00', 'view'), (1, TIMESTAMP '2024-01-01 10:05', 'cart'),
             (1, TIMESTAMP '2024-01-01 10:20', 'buy'),  (2, TIMESTAMP '2024-01-01 11:00', 'view'),
             (2, TIMESTAMP '2024-01-01 15:00', 'cart')) t(user_id, ts, e)
GROUP BY user_id ORDER BY user_id;

-- Sessions: a new session after 30 minutes of inactivity
SELECT ts, sessionize(ts, INTERVAL '30 minutes') OVER (ORDER BY ts) AS session_id
FROM (VALUES (TIMESTAMP '2024-01-01 10:00'), (TIMESTAMP '2024-01-01 10:10'),
             (TIMESTAMP '2024-01-01 11:00')) t(ts);

About behavioral

Behavioral analytics functions for DuckDB, following ClickHouse's parametric behavioral functions (differentially tested against ClickHouse 26.10):

• sessionize: Session IDs over an ordered window, based on timestamp gaps
• retention: Cohort retention analysis returning boolean arrays
• window_funnel: Conversion funnel step tracking with 6 composable modes
• window_funnel_events: Timestamps of the best funnel chain as LIST(TIMESTAMP)
• sequence_match: Pattern matching over event sequences
• sequence_count: Count non-overlapping pattern matches
• sequence_match_events: Return matched condition timestamps
• sequence_next_node: Find the next event value after a pattern match
• behavioral_version: Diagnostic scalar returning the loaded extension version

Up to 32 event conditions per call. Invalid configuration (unknown modes, malformed patterns, month-based intervals) raises descriptive SQL errors. Built on DuckDB's stable C API; pure Rust, with no unsafe code outside the FFI layer.

Known DuckDB limitation (duckdb/duckdb#26109, affects every C API aggregate, fixed on DuckDB 2.0.0 only): on earlier releases, including the 1.4.5 and 1.5.6 builds, do not call these functions with ORDER BY inside the call, with OVER (), or over a frame written UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING; DuckDB can crash. On 1.3.2 through 1.4.5, OVER (PARTITION BY x) without ORDER BY crashes too. The functions sort by timestamp themselves, so none of these is needed.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings