UDFs and Macros

Overview

Use kt_session to Return the Number of Sessions for Each User

The kt_session call will calculate the length of each session for each user. This example query is composed of three parts. The query in its entirety is shown as follows:

SELECT kt_session(calc_session.s, calc_session.evt_lst,2) AS (s, session_number, session_length)
FROM (
   SELECT session_set.s, collect_set(session_set.utc_timestamp) evt_lst
   FROM (
      SELECT total_list.s, total_list.utc_timestamp 
      FROM (
         SELECT pgr.s, pgr.utc_timestamp
         FROM XYZ_pgr pgr
         WHERE pgr.month = 201205 AND pgr.s != ""

         UNION ALL

         SELECT evt.s, evt.utc_timestamp
         FROM XYZ_evt evt
         WHERE evt.month = 201205 and evt.s != ""
      ) total_list 
   )session_set
   GROUP BY session_set.s
) calc_session 
ORDER BY s, session_number DESC

The above query can be broken down into three distinct queries; two inner queries and one outter query:

Inner Query #1

SELECT total_list.s, total_list.utc_timestamp 
FROM (
   SELECT pgr.s, pgr.utc_timestamp
   FROM XYZ_pgr pgr
   WHERE pgr.month = 201205 AND pgr.s != '' ''

   UNION ALL

   SELECT evt.s, evt.utc_timestamp
   FROM XYZ_evt evt
   WHERE evt.month = 201205 and evt.s != '' ''
) total_list

As we know that custom events, evt, and page requests, pgr, are the two messages types that contribute to a user's session, we must combine both of these tables into 1 master table. The information we need is the user ID and timestamp:

Inner Query #2

SELECT session_set.s, collect_set(session_set.utc_timestamp) evt_lst
FROM (
   SELECT total_list.s, total_list.utc_timestamp 
   FROM (
      SELECT pgr.s, pgr.utc_timestamp
      FROM XYZ_pgr pgr
      WHERE pgr.month = 201205
      AND pgr.s != '' ''

      UNION ALL

      SELECT evt.s, evt.utc_timestamp
      FROM XYZ_evt evt
      WHERE evt.month = 201205 and evt.s != '' ''
   ) total_list
   ORDER BY utc_timestamp DESC
) session_set
GROUP BY session_set.s

Using COLLECT_SET() and GROUP BY, we will collect the unique timestamps in an array for each user.

Outer Query

Now, using the Upsight Analytics UDF kt_session, we can find out the session length for each session of each user, assuming that for a single session the interval between messages received is less than 2 minutes.

Get "Total sessions / Total time spent per user"

Sample Result:

In order to get the total number of sessions per user, or the total time spent per user, we must wrap around the original kt_sessions example with an aggregate MAX() or SUM(). Here is an example query:

-- Total sessions/ Total time spent per user 
-- Replace all text enclosed with ## ## (example ##month_in_YYYYMM_format## could be 201303) using the following instructions:
-- Replace ##APP1_pgr##, ##APP1_evt## with the name of your applications you are interested in reviewing, for example: demogame_pgr, demogame_evt
-- Replace ##month_in_YYYYMM_format## with the month you are interested in querying, for example 201303
-- Replace ##timeout_in_minutes_integer## with the desired timeout, for example a value of 2 would mean that when a user is inactive for 2 minutes or more, the session would end
-- For mobile applications we recommend setting the timeout to 2 minutes and for web applications we recommend using 30 minutes.

SELECT session_data.s, MAX(session_data.session_number) AS total_sessions, ROUND(SUM(session_data.session_length)/60,1) AS total_timespent_minute
FROM
(
   SELECT kt_session(calc_session.s, calc_session.evt_lst,##timeout_in_minutes_integer##) AS (s, session_number, session_length)
   FROM (
      SELECT session_set.s, collect_set(session_set.utc_timestamp) evt_lst
      FROM (
         SELECT total_list.s, total_list.utc_timestamp 
         FROM (
            SELECT pgr.s, pgr.utc_timestamp
            FROM ##APP1_pgr## pgr
            WHERE pgr.month = ##month_in_YYYYMM_format##
            AND pgr.s != ""

            UNION ALL

            SELECT evt.s, evt.utc_timestamp
            FROM ##APP1_evt## evt
            WHERE evt.month = ##month_in_YYYYMM_format## and evt.s != ""
         ) total_list 
      )session_set
      GROUP BY session_set.s
   ) calc_session 
   ORDER BY s, session_number DESC
)session_data
GROUP BY session_data.s

Get "Total sessions / Total time spent per user per day"

Sample Result:

There are two things to keep in mind when writing this query.

1. kt_sessions() returns session data per session, so we need to wrap around the final query with a SUM() and a GROUP BY s to get the total time spent per user and a MAX() to get the total number of sessions per user.

2. The other thing that we have to think about is grouping it by user and day. Since kt_session() UDF doesn't take day as a bucket, we will need to rework our inner queries a bit.

Right before we apply the kt_session UDF, we would usually have the timestamps for each user organized in an array per user. At this stage, we should organize the timestamps by user and day (instead of just users), like so:

In the outer query, instead of doing the normal kt_session(), you can then use the function CONCAT_WS to combine the ymd and s into 1 bucket and later use the SPLIT() function to separate the line, like so:

SELECT SPLIT(session_data.ymd__s, '__')[0] AS s,
   SPLIT(session_data.ymd__s, '__')[1] AS ymd,
   MAX(session_data.session_number) AS total_sessions,
   ROUND(SUM(session_data.session_length/60),1) AS total_timespent_minute
FROM (
   SELECT kt_session(CONCAT_WS('__', calc_session.s,calc_session.ymd),
      calc_session.evt_lst, 2) AS (ymd__s, session_number,
      session_length
)

Example Query:

-- Total sessions/ Total time spent per user per day
-- Replace all text enclosed with ## ## (example ##month_in_YYYYMM_format## could be 201303) using the following instructions:
-- Replace ##APP1_pgr##, ##APP1_evt## with the name of your applications you are interested in reviewing, for example: demogame_pgr, demogame_evt
-- Replace ##month_in_YYYYMM_format## with the month you are interested in querying, for example 201303
-- Replace ##timeout_in_minutes_integer## with the desired timeout, for example a value of 2 would mean that when a user is inactive for 2 minutes or more, the session would end
-- For mobile applications we recommend setting the timeout to 2 minutes and for web applications we recommend using 30 minutes.

SELECT SPLIT(session_data.ymd__s, '__')[0] AS s,
   SPLIT(session_data.ymd__s, '__')[1] AS ymd,
   MAX(session_data.session_number) AS total_sessions,
   ROUND(SUM(session_data.session_length/60),1) AS total_timespent_minute
FROM (
   SELECT kt_session(CONCAT_WS('__', calc_session.s, calc_session.ymd), calc_session.evt_lst, ##timeout_in_minutes_integer##) AS (ymd__s, session_number, session_length)
   FROM (
      SELECT session_set.s, FROM_UNIXTIME(CAST(session_set.ts AS BIGINT), 'yyyy-MM-dd') ymd, collect_set(session_set.ts) evt_lst
      FROM (
         SELECT total_list.s, total_list.ts
         FROM (
            SELECT pgr.s, pgr.ts
            FROM ##APP1_pgr## pgr
            WHERE pgr.month = ##month_in_YYYYMM_format##
            AND pgr.s != ""

            UNION ALL

            SELECT evt.s, evt.ts
            FROM ##APP1_evt## evt
            WHERE evt.month = ##month_in_YYYYMM_format##
            AND evt.s != ""
         ) total_list
      ) session_set
      GROUP BY session_set.s, FROM_UNIXTIME(CAST(session_set.ts AS BIGINT), 'yyyy-MM-dd')
   ) calc_session
) session_data
GROUP BY SPLIT(session_data.ymd__s, '__')[0],SPLIT(session_data.ymd__s, '__')[1]
ORDER BY s

Use the "Dynamic Month" macro to select a specific time period

Dynamically select the month partition in your WHERE clause. This will increase the speed and performance of your query. The macro is especially useful if you have scheduled queries where you are only interested in metrics from a specific time period (for example, the most recent month).

Select data from 2 months ago:

WHERE month = $months_ago(2))

Select data from this month, 2 months ago, and 3 months ago:

WHERE month in ( $(months_ago(0)), $(months_ago(2)), $(months_ago(3)) )