BigQuery : Time Stamp of Next Events are Lesser Than Previous Event's Time Stamp

32 Views Asked by At

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

enter image description here

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

0

There are 0 best solutions below