How to calculate Session and Session duration in Firebase Analytics raw data

0 votes

How to calculate Session Duration in Firebase analytics raw data which is linked to BigQuery?

I have used the following blog to calculate the users by using the flatten command for the events which are nested within each record, but I would like to know how to proceed with in calculating the Session and Session duration by country and time.

(I have many apps configured, but if you could help me with the SQL query for calculating the session duration and session, It would be of immense help)

Jun 5, 2018 in Tableau by ghost
• 1,800 points
9,176 views

1 answer to this question.

0 votes

First you need to define a session - in the following query I'm going to break a session whenever a user is inactive for more than 20 minutes.

The following query finds all sessions and their lengths:

#standardSQL

SELECT app_instance_id, sess_id, MIN(min_time) sess_start, MAX(max_time) sess_end, COUNT(*) records, MAX(sess_id) OVER(PARTITION BY app_instance_id) total_sessions,
   (ROUND((MAX(max_time)-MIN(min_time))/(1000*1000),1)) sess_length_seconds
FROM (
  SELECT *, SUM(session_start) OVER(PARTITION BY app_instance_id ORDER BY min_time) sess_id
  FROM (
    SELECT *, IF(
                previous IS null 
                OR (min_time-previous)>(20*60*1000*1000),  # sessions broken by this inactivity 
                1, 0) session_start 
                #https://example.com/
    FROM (
      SELECT *, LAG(max_time, 1) OVER(PARTITION BY app_instance_id ORDER BY max_time) previous
      FROM (
        SELECT user_dim.app_info.app_instance_id
          , (SELECT MIN(timestamp_micros) FROM UNNEST(event_dim)) min_time
          , (SELECT MAX(timestamp_micros) FROM UNNEST(event_dim)) max_time
        FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160601`
      )
    )
  )
)
GROUP BY 1, 2
ORDER BY 1, 2

answered Jun 5, 2018 by Atul
• 10,240 points

Related Questions In Tableau

0 votes
1 answer

How to calculate profit when cost and revenue are in same column?

You can create the following calculated fields: Profit: SUM(IIF([account_category] ...READ MORE

answered Apr 10, 2018 in Tableau by ffdfd
• 5,550 points
1,900 views
0 votes
1 answer

How to dynamically determine and categorize duplicate value in Tableau?

Apparently you define a duplicate records as ...READ MORE

answered Apr 13, 2018 in Tableau by xyz
• 1,560 points
7,888 views