CODEWITHSUNDEEP
sqlfirebasegoogle-cloud-platformgoogle-bigqueryfirebase-analytics
Shantanu Jain
Shantanu Jain

Reputation: 87

How to find how much time user takes to do 'NowPlayingActivity' in one session using Bigquery and Firebase data?

Here's how my data looks like:

enter image description here

enter image description here

Now let's say a user in 'row 1' has started the 'ga_session_id' 1605744304 at certain 'event_timestamp'. Let's say this time as X.

Now, after certain time duration (pic 2nd) in row 2, that user did the 'NowPlayingActivity' in 'event_params.value.string_value'. Let's say this time as Y.

I want to know what is the time he took to do 1st "NowPlayingActivity" in a session by subtracting Y and X.

This has to be done for all 'user_pseudo_id' (a different column not shown in the images).

Note: ga_session_id is a unique id for every session a user does and it remains same until a session ends. It can come in event_params.key for many event_names.

Any help will be highly appreciated.

Thanks and Regards.

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Hmmm . . . This sounds like unnest() with filtering. I think you can use:

select t.*,
       (select t.event_timestamp - min(ep.value.int_value)
        from unnest(t.event_params) ep
        where ep.key = 'NowPlayingActivity'
       ) as time_to_first
from t;

This makes some assumptions about the types of columns, but the idea should work even if some things needs to be cast.

Upvotes: 1

Related Questions