I'm using FirebaseAnalytics data exported to BigQuery to analyze our app.
To check user transitions, I write sql like below.
with users_with_screen_view_and_session_id as (
SELECT
user_id,
user_pseudo_id,
event_date,
event_timestamp,
event_name,
platform,
case
when event.key = 'firebase_screen'
and event.value.string_value is not null then event.value.string_value
when event.key = 'firebase_screen'
and event.value.int_value is not null then cast(event.value.int_value as string)
else null
end as firebase_screen,
case
when event.key = 'ga_session_id'
and event.value.string_value is not null then event.value.string_value
when event.key = 'ga_session_id'
and event.value.int_value is not null then cast(event.value.int_value as string)
else null
end as session_id
FROM
`{my_project}.analytics_{my_id}.events_*` as main
,unnest(event_params) as event
WHERE _TABLE_SUFFIX BETWEEN '20191001' AND '20191001'
and event_name = 'screen_view'
),
users_with_screen_view_agg_firebase_screen as (
select
user_id,
user_pseudo_id,
event_date,
event_name,
platform,
event_timestamp,
max(firebase_screen) as firebase_screen,
max(session_id) as session_id
from
users_with_screen_view_and_session_id
group by
user_id,
user_pseudo_id,
event_date,
event_name,
platform,
event_timestamp
)
select
*
from
users_with_screen_view_agg_firebase_screen
;
I thought every event have ga_session_id, but in this query result there are some rows that's ga_session_id is null.
In what case does ga_session_id value become null?
The image of query result is here. enter image description here