Post Thread

The goal is to identify ecommerce users who show strong purchase intent but ultimately do not complete a purchase. A high-value failed conversion pattern includes users who view a product at least twice, add it to their cart, and then become inactive for a defined period. This group can help product managers target reminder campaigns, estimate how much revenue might be recovered, and identify potential issues such as products being priced too high. Because there is no explicit “abandoned” event, abandonment must be inferred from the absence of activity after the cart addition and a sufficient idle period. Traditional #SQL requires techniques such as NOT EXISTS, self-joins, and window functions to establish that no later activity occurred. MATCH_RECOGNIZE simplifies this by using the end-of-partition anchor $ to ensure the cart addition is the final event, combined with a time filter to define the abandonment timeout.

SELECT 
FROM (
    SELECT 
    FROM web_clickstream
    MATCH_RECOGNIZE (
        PARTITION BY session_id
        ORDER BY event_time
        MEASURES
            FIRST(VIEW.event_time) AS journey_start,
            COUNT(VIEW.*)          AS total_product_views,
            ADD_TO_CART.event_time AS last_activity_time
        ONE ROW PER MATCH
        AFTER MATCH SKIP PAST LAST ROW
        PATTERN (VIEW{2,} ADD_TO_CART $)
        DEFINE
            VIEW        AS event_type = 'VIEW_PRODUCT',
            ADD_TO_CART AS event_type = 'ADD_TO_CART' AND cart_item_count > 0
    )
)
WHERE last_activity_time < current_timestamp() - INTERVAL 30 MINUTES;

https://www.databricks.com/blog/regex-rows-simplifying-pattern-detection-sql-matchrecognize