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)

Google Blog on using Firebase and big query

Jun 5, 2018 in Tableau by ghost
• 1,790 points
2,845 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.

Now, to find all sessions with SQL you can use a trick described at https://blog.modeanalytics.com/finding-user-sessions-sql/.

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://blog.modeanalytics.com/finding-user-sessions-sql/
    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

enter image description here

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 Atul
• 5,530 points
81 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 12, 2018 in Tableau by xyz
• 1,550 points
1,992 views
0 votes
1 answer

How to calculate the percent of records within a group in tableau?

 You can click the measure SUM(Number of Records) and ...READ MORE

answered May 8, 2018 in Tableau by Atul
• 5,530 points
218 views
0 votes
1 answer

How to return the string between 5th and 6th Spaces in a String

In Tableau 9 you can use regular ...READ MORE

answered Jun 26, 2018 in Tableau by Atul
• 5,530 points
49 views
0 votes
1 answer

BigQuery Credential Problems when Accessing Google Sheets Federated Table

This is something Tableau should fix. The ...READ MORE

answered Jun 5, 2018 in Tableau by Atul
• 10,240 points
81 views
0 votes
1 answer

SQL Statement to fetch sum(salary) for employees

Hi Preeti, Try this, SELECT DEPARTMENT, SUM(SALARY) FROM EMPLOYEES ...READ MORE

answered May 22 in Tableau by anonymous
• 28,040 points
24 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
2,041 views
+1 vote
1 answer
0 votes
1 answer

How to count occurrence of value and percentage of a subset in tableau public?

Although it sounds like a fairly easy ...READ MORE

answered Jun 5, 2018 in Tableau by Atul
• 10,240 points
3,229 views
0 votes
1 answer

How to calculate weekly average from daily data?

You can create these calculated fields: Unique Weeks ...READ MORE

answered Jun 26, 2018 in Tableau by Atul
• 10,240 points
209 views