BigQuery- und Google Analytics SQL-Abfrage - erweiterte Frage

Nov 19 2020

Ich versuche hier meine beantwortete Frage zu erweitern . Also, angesichts der Daten:

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

Und das aktuelle Ergebnis wie folgt:

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

Ich möchte das gegebene Ergebnis erweitern, um über die Fälle nachzudenken, in denen das Ziel gleich Null ist. Kann ich diese Fälle auch mit der Anzahl der Ereignisse versehen, unabhängig von den Überprüfungsdaten?

Das neue beabsichtigte Ergebnis wäre also wie folgt:

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

Ich interessiere mich besonders für session_id = 2, wo eine Anzahl von Ereignissen stattfindet, ohne dass url_target besucht wird. Schließlich ist session_id = 3 auch ein anderer Fall, in dem ich nicht sicher bin, wie ich damit umgehen soll! Da es ein Ereignis hat (event2), wurde es aber nach dem Besuch des url_targets gemacht. Vielleicht sollte ich es als Ziel = 2 bezeichnen, als Sonderfall. Wenn dies mit SQL jedoch schwierig ist, würde ich es aus dem Ergebnis verwerfen und es wie in der obigen beabsichtigten Ergebnistabelle als Nullen belassen.

Vielen Dank im Voraus für alle Beiträge.

Antworten

1 GordonLinoff Nov 19 2020 at 22:51

Von dem, was Sie beschreiben, wollen Sie bedingte Logik. Das sollte funktionieren:

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

Dies target_hit_timestampist der NULLFall, wenn keine Ziel-URL vorhanden ist.