CODEWITHSUNDEEP
sqlgoogle-bigquery
EurikaIam
EurikaIam

Reputation: 136

BigQuery and Google Analytics SQL query - expanded question

I am trying to expand on my answered question here. so, given the data:

session_id  hit_timestamp   url event_category
1           11:12:23        url134      event1
1           11:14:23        url2234     event2
1           11:16:23        url_target  null
2           03:12:11        url2344     event1
2           03:14:11        url43245    event2
3           09:10:11        url5533     event2
3           09:09:11        url_target  null
4           08:08:08        url64356    event2
4           08:09:08        url56456    event2
4           08:10:08        url_target  null

And the current result as below:

session_id  event1  event2  target
1           1       1       1
2           0       0       0
3           0       0       0
4           0       2       1

I would like to expand on the given result to reflect on those cases where target is equal zero. Would I be able to also annotate those cases with the count number of events regardless of checking dates?

So the new intended result would be as below:

session_id  event1  event2  target
1           1       1       1
2           1       1       0
3           0       0       0
4           0       2       1

I am particularly interested about session_id=2 where there are number of events happening, with no url_target being visited. Finally, session_id=3 is also another case where I am not sure how to handle! As it has one event (event2), but it was done after visiting the url_target. Maybe I should denote it as target=2, as being a special case. But, if this is difficult with SQL, then I would discard it from the result and keep it as zeros, as the intended result table above.

Thanks a lot in advance for any contributions.

Upvotes: 1

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

From what you describe, you want conditional logic. This should work:

select session_id,
       countif((target_hit_timestamp > hit_timestamp or target_hit_timestamp is null) and category = 'event1') as event1,
       countif((target_hit_timestamp > hit_timestamp or target_hit_timestamp is null) > hit_timestamp and category = 'event2') as event2,
       countif(url like '%target') as target
from (select t.*,
             min(case when url like '%target' then hit_timestamp end) over (partition by session_id) as target_hit_timestamp
      from t
     ) t
group by session_id

The target_hit_timestamp is NULL if there is no target URL.

Upvotes: 1

Related Questions