BigQuery- und Google Analytics SQL-Abfrage

Question

Ich versuche, eine Matrix aus einer Tabelle zu erstellen, die aus Google Analytics-Daten in BigQuery importiert wird. Die Tabelle stellt Treffer auf einer Website dar, die neben einigen Eigenschaften wie URL, Zeitstempel usw. Sitzungs-IDs enthalten. Außerdem gibt es einige Metadaten, die auf benutzerdefinierten Aktionen basieren, die wir als Ereignisse bezeichnen. Unten finden Sie ein Beispiel für die Tabelle.

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

Das beabsichtigte Ergebnis sollte ungefähr der folgenden Tabelle entsprechen.

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

Beachten Sie, dass jedes Ereignis, das nicht zu url_target führt, als Nullen einschließlich des Ziels bezeichnet werden sollte. Dies bedeutet, dass die Abfrage den Zeitstempel untersuchen sollte, um zu überprüfen, ob auf Ereignisse url_target folgt, indem der Zeitstempel überprüft wird. Auf event2 folgte beispielsweise nicht "url_target", weshalb wir es als Nullen bezeichnen. Im gleichen Fall in session_id 3, da auf event2 nicht url_target folgte, notieren Sie sich den Zeitstempel von url_target, der vor event2 und nicht danach lag. Daher als Nullen bezeichnet.

Ich würde mich über jede Hilfe beim Erstellen der SQL-Abfrage zur Erstellung dieser Matrix freuen. Ich konnte nur nach session_id gruppieren und dann Zählereignisse mit "count" durchführen, konnte jedoch die SQL-Schreibabfrage nicht finden, die mit dem Zeitstempel übereinstimmt, und andere Felder überprüfen.

Answer 1

Verwenden Sie eine Unterabfrage, um die erste (oder letzte) Zielzeit zu berechnen. Dann verwenden countif()und aggregieren:

select session_id,
       countif(target_hit_timestamp > hit_timestamp and category = 'event1') as event1,
       countif(target_hit_timestamp > 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

Answer 2

Erwägen:

select session_id,
    countif(cnt_url_target > 0 and event_category = 'event1') event1,
    countif(cnt_url_target > 0 and event_category = 'event2') event2,
    countif(url = 'url_target') target
from (
    select t.*,
        countif(url = 'url_target') over(partition by session_id order by hit_timestamp desc) cnt_url_target
    from mytable t
) t
group by session_id