I am trying to find time between LevelStart and LevelComplete events for a user through BigQuery. Just for reference I am using Firebase and have exported data to BigQuery.
I have run the following query on BigQuery.
SELECT
e.event_name,
e.event_timestamp,
ep.value as LevelNo
FROM
`analytics_353710281.events_20240110` e,
UNNEST(e.event_params) as ep
WHERE
e.event_name IN ('LevelStart', 'LevelComplete', 'LevelFailed')
AND e.user_pseudo_id = 'b24e4ff743034ae7fe3a6cba3cc77045'
AND ep.key = 'LevelNo';
This user_pseudo_id belongs to me and I have tested the app very well and have verified if all the events are sent to Firebase properly. The events being sent to Firebase are fine.
Upon Running the Query I got the following data
As you can see I have played and completed 4 Levels. The problem I am facing is LevelStart time for Level2 is Less then LevelComplete time for Level1. This is not possible, user plays level1 then completes it and then plays level2. I have tried finding the difference in seconds by the following formula
(LevelCompletTime-LevelStartTime)/1000000)
This gives me time in seconds for which user has played the level.
My Question: How is it possible that the time stamps of Level2Start is less than Level1Complete? What is it that I am doing wrong here? How can I fix it if it is not fixed I will not be able to get data accurately for each level.
Looking forward for your responses. Thanks
