How to Calculate Session and Session Duration in Firebase Analytics Raw Data

with timeline as

(

  select 

    user_pseudo_id

    , event_timestamp

    , lag(event_timestamp, 1) over (partition by user_pseudo_id order by event_timestamp) as prev_event_timestamp

  from 

    `YYYYY.analytics_XXXXX.events_*`

  where

    -- at first - a sliding period - how many days in the past we are looking into:

    _table_suffix

           between format_date("%Y%m%d", date_sub(current_date, interval 10 day))

           and     format_date("%Y%m%d", date_sub(current_date, interval 1 day))

) 

, session_timeline as 

(

  select 

    user_pseudo_id

    , event_timestamp

    , case 

        when 

           -- half a hour period - a threshold for a new 'session'

           event_timestamp - prev_event_timestamp >= (30*60*1000*1000)

             or

           prev_event_timestamp is null 

          then 1

          else 0 

      end as is_new_session_flag

  from 

    timeline

)

, marked_sessions as

(

  select 

    user_pseudo_id

    , event_timestamp

    , sum(is_new_session_flag) over (partition by user_pseudo_id order by event_timestamp) AS user_session_id

  from session_timeline

)

, measured_sessions as

(

  select

    user_pseudo_id

    , user_session_id

    -- session duration in seconds with 2 digits after the point

    , round((max(event_timestamp) - min(event_timestamp))/ (1000 * 1000), 2) as session_duration

  from 

    marked_sessions

  group by

    user_pseudo_id

    , user_session_id

  having 

    -- let's count only sessions longer than 10 seconds

    session_duration >= 10

)

select 

  count(1)                          as number_of_sessions

  , round(avg(session_duration), 2) as average_session_duration_in_sec

from 

  measured_sessions

How to calculate Session duration and avg. Session duration in Google Analytics raw data?

You can use the field totals.timeOnSite as described in the ga table schema, something like:

SELECT 
  AVG(totals.timeOnSite) avgTimeOnSite
FROM `project_id.dataset_id.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN "20170701" and "20170702"

---

Related Topics