Getting started: Engagement

All of your closed session data points are stored in fact_session_closes. From here, you can calculate a number of engagement metrics such as average session length. For example, here is how you would calculate average session by day, only considering sessions greater than 10 seconds:

set app_id=12345;
set lookback_period_in_days=-30;

SELECT client_date,
       AVG(session_length) as avg_session_length
FROM fact_session_closes
WHERE app_id = $app_id
AND client_date>=dateadd(day, $lookback_period_in_days, current_date())
AND session_length>10
GROUP BY client_date

In the above example, you can calculate median session length by replacing AVG with MEDIAN.

You can also count sessions using Localytics’ session length buckets:

set app_id=12345;
set data_since_date='2016-01-01';

SELECT (CASE
          WHEN session_length_bucket = 1 then '0 - 10 seconds'
          WHEN session_length_bucket = 2 then '11 to 30 seconds'
          WHEN session_length_bucket = 3 then '1 - 3 minutes'
          WHEN session_length_bucket = 4 then '3 to 10 minutes'
          WHEN session_length_bucket = 5 then '10 to 30 minutes'
          WHEN session_length_bucket = 6 then '30 to 60 minutes'
          WHEN session_length_bucket = 7 then '> 1 hour'
        END) AS session_length_bucket,
       COUNT(*) as closes
FROM FACT_SESSION_CLOSES
WHERE APP_ID = $app_id
AND CLIENT_DATE>=$data_since_date
GROUP BY 1
ORDER BY 1;

Advanced Tip: Sometimes you will want to create custom session length buckets. In the above example, if you wanted to create a session bucket between 10 and 15 seconds, you could add the following to the CASE statement: WHEN session_length>10 AND session_length<=15 then '10-15 seconds'.