Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Event Analytics: How to Define User Sessions With SQL

DZone's Guide to

Event Analytics: How to Define User Sessions With SQL

Automatically defined user sessions are good to start with, but as your company grows, you’ll want to have your own session definitions based on your event data.

· Big Data Zone ·
Free Resource

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

Many of “out-of-the-box” analytics solutions come with automatically defined user sessions. It’s good to start with, but as your company grows, you’ll want to have your own session definitions based on your event data. Analyzing user sessions with SQL gives you flexibility and full control over how metrics are defined for your unique business.

What Is a Session and Why Should I Care?

A session is usually defined as a group of interactions that one user executes within a given time frame on your app. Usually, that time frame defaults to 30 minutes. One session includes any events that a user completes in your app before leaving; for example, visiting pages, downloading materials, or performing actions.user sessions

Having sessions in place, we’ll be able to answer questions like:

  • How much time did users spend on the app?
  • What is the bounce rate?
  • What are the most used areas of the app?
  • Where are the users who make target actions coming from?
  • Where are users spending most of their time?

Defining User Sessions Correctly

To define a user session, we need to have an event table in our database with at least user_id and timestamp.

Usually, you will have a lot of additional information in the event tables such as event type, name, device info, referrer, and much more. All these properties are going to be very useful to give more context to our sessions and to build things such as an attribution model.

Note: In this post, we’re going to use window functions, so the following example will not work with MySQL. Also, all these queries are dedicated to Redshift database.

Step 1

The first step for defining user sessions with SQL is to find out the difference between the timestamp of the current row and the previous one by user_id. We will use the LAG function to accomplish this. This will give us an inactivity time between events.

SELECT
*
, DATEDIFF(minutes, lag(timestamp) over (PARTITION BY user_id order by timestamp), timestamp) as inactivity_time
FROM events

The inactivity_time for the first event is NULL since it’s the first event and we don’t have anything before it.

Step 2

We can use inactivity_time to group events into sessions based on 30-minute intervals of inactivity. First, we’ll select all events where inactivity_time is either NULL or more than 30 minutes, which means that it is the first event in the session. Based on this first event, we define session_start_at, which is the timestamp of the first event. We use the ROW_NUMBER function to calculate session sequence, which is used in session_id.

SELECT
      event.user_id || '-' || row_number() over(partition by event.user_id order by event.timestamp) as session_id
      , event.user_id
      , event.timestamp as session_start_at
      , lead(timestamp) over(partition by event.user_id order by event.timestamp) as next_session_start_at
    FROM
      (SELECT
            e.user_id
     , e.timestamp
             , DATEDIFF(minutes, LAG(e.timestamp) OVER(PARTITION BY e.user_id ORDER BY e.timestamp), e.timestamp) AS inactivity_time  
          FROM events AS e
      ) as event
    WHERE (event.inactivity_time > 30 OR event.inactivity_time is null)

We can save this table as a sessions data mart to use it in our future queries. Once we have this table, it’s easy to answer the user analytics questions we outlined in the beginning. For example, to calculate average session duration, we can use the following SQL.

SELECT
  COUNT(*) AS sessions_count,
  AVG(duration) AS average_session_duration
FROM (
  SELECT session_id
        , DATEDIFF(minutes, MIN(events.timestamp), MAX(events.timestamp)) AS duration
  FROM sessions
  LEFT JOIN events on events.user_id = sessions.user_id
        AND events.timestamp >= events.session_start_at
        AND (events.timestamp < sessions.next_session_start_at OR sessions.next_session_start_at is null)
  GROUP BY 1
)

As you see, we join events table to sessions to map every event to its session. It allows us to get the end of each session, which is the max timestamp of the event within the given session. More complex calculations of session duration could optionally include a window of inactivity as well.

Hortonworks Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

Topics:
big data ,sql ,tutorial ,event analytics ,data analytics ,sessions

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}