When trying to understand a user’s journey through your product, you’ll likely turn to your company’s events data, which records users’ actions like clicks, form fills, purchases, and other product behavior.
But without the context ofthat capture some time-bound record of product usage . This gives you a chance to put your product hat on by analyzing events as part of a journey a user took through your product, rather than a collection of random actions. With your sessionized data, you can do lots of interesting analyses, like a, to help you understand where folks are getting stuck and what actions your Marketing, Sales, or Product team can take to improve conversion 💪.
Below, we’ll walk through how these steps build toward this SQL code that sessionizes your event data… WITH first_pass AS OVER , created_at ) AS minutes_since_prior_event FROM events WHERE user_id IS NOT NULL ) , sessions AS OVER AS next_session_start , ROW_NUMBER OVER AS user_sesssion_sequence_no FROM first_pass WHERE minutes_since_prior_event >30 OR minutes_since_prior_event IS NULL ) SELECT sessions.session_id AS session_id , sessions.
FROM sessions JOIN events ON events.user_id=sessions.user_id AND events.created_at >=sessions.session_start AND ) ELSE CASE WHEN TIMESTAMPDIFF30 OR minutes_since_prior_event IS NULL ) SELECT sessions.session_id AS session_id , sessions.user_id AS user_id , user_sesssion_sequence_no , session_start AS first_event_at , MAX AS last_event_at , CASE WHEN TIMESTAMPDIFF) >0 THEN TIMESTAMPDIFF) ELSE CASE WHEN TIMESTAMPDIFF=sessions.
Here at Omni, we believe in combining the structure of a data model with the freedom of SQL because of cases just like this. Sometimes you need a little SQL to get your analysis going, and then you can bring that transformation into the model to build reusable, governed metrics for your team. If you’re curious to learn more, we’d love to