Calculate Session Length from Google Analytics data in BigQuery

How do you calculate session length for website event data that flows via Google Analytics to BigQuery ?

A similar question has been posted & answered here. However, the underlying data structure is very different to my case:

• Our data structure is: project_id.dataset_id.events_* with a separate table for each day, instead of project_id.dataset_id.ga_sessions_*

The way I've tried to get the session length is with the user_engagement event and the engagement_time_msec field:

SELECT 
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
  SUM((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec'))/60.0 as session_length_seconds
FROM `project_id.dataset_id.events_*`
WHERE event_name = 'user_engagement'
GROUP BY 1

But I'm getting NULL values for some sessions: BigQuery Output

I haven't found good documentation from Google on this, so any help or links would be greatly appreciated.

This article explains very well how to calculate session length:

Basically there are two ways:

• Engaged Session Length (using maximum engagement_time_msec) which seems to indicate that engagement_time_msec is a cumulative metrics
• Normal Session Length (using the difference of the maximum & minimum event_timestamp across all events)

Pasting the section of the article here:

Average Session Duration Again this has changed slightly to engaged session duration. This will be lower than your Universal Analytics session duration as it only counts when the tab is in focus. Below I show how to do both.

SELECT
  sum(engagement_time_msec)/1000 #in milliseconds
  /count(distinct concat(user_pseudo_id,ga_session_id)) as ga4_session_duration,
  sum(end_time-start_time)/1000000 #timestamp in microseconds
  /count(distinct concat(user_pseudo_id,ga_session_id)) as ua_session_duration

from(
  SELECT
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as ga_session_id,
   max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec,
   min(event_timestamp) as start_time,
   max(event_timestamp) as end_time
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  where _table_suffix BETWEEN "20210101" and "20210131"
  group by 1,2)