Big Query GA4 Kundensegmente mit zeitlicher Abhängigkeit mit SQL bilden

R

Rapid_SQL

Neuer Benutzer
Beiträge
4
Hallo zusammen und vorab vielen Dank für Ihre Zeit und Hilfe... Ich habe eine Frage zu Google Analytics Big Query Data. Ich möchte Google Analytics(4)-Daten basierend auf verschiedenen Benutzersegmenten analysieren. Ich habe eine Rohdatentabelle mit user_id, event, timestamp/date. Nun möchte ich drei verschiedene Nutzersegmente aufbauen:
- Benutzer, die event1 und dann event2 ausgelöst haben (basierend auf dem Zeitstempel)
- Benutzer, die Ereignis1 <10 Minuten nach Ereignis2 ausgelöst haben
- Benutzer, die event1 direkt nach event2 ausgelöst haben
Die Rohdaten sind ereignisbezogen, sodass jedes Ereignis eine Zeile und eine normale relationale Datenbank ist.

Ich kann Benutzer abfragen, die Ereignis 1 und 2 hatten, aber ich weiß nicht, wie ich die verschiedenen oben gezeigten Bestellszenarien abfragen soll.

Die grundlegende Abfrage für Benutzer mit beiden Ereignissen wäre:
Wählen
user_pseudo_id
aus >Tabellenname<
wo Ereignis in (Ereignis1,Ereignis2)
Vielen Dank für Ihre Unterstützung, Rapid_SQL 😇🙏
 
Werbung:
Hi zusammen, danke für euren Input. Ich mache mal ein Beispiel, das stark vereinfacht meine Herausforderung darstellt. Ich habe in BigQuery eine Datenbasis, auf die ich per SQL Queries schreiben kann. Beispielsweise kann ich die User_id, das Event und einen Timestamp abfragen. Nun will ich User abgrenzen, die gewissen Kriterien entsprechen:
1) User hat zeitlich nach dem Page_View1 das Event Page_View2 ausgelöst. Hier würde ich bsp. User_1, User_3 und User_4 drinnen haben wollen
2) User hat nach dem Page_View1 als nächstes Event den Page_View2 ausgelöst. Hier würde ich bsp. User_3, User_4 drinnen haben wollen, nicht aber User_1, da dieser dazwischen Page_View3 hatte

User_2 wäre in keinem obigen Szenario drinnen, da das Event Page_View2 zeitlich vor dem Event Page_View1 stattgefunden hat...

User_idEventTimeDate
User_1Page_View1
01:00:00​
20230421
User_1Page_View1
01:05:00​
20230421
User_1Page_View3
01:05:30​
20230421
User_1Page_View2
01:06:00​
20230421
User_2Page_View2
01:00:00​
20230421
User_2Page_View1
01:10:00​
20230421
User_3Page_View1
01:00:00​
20230421
User_3Page_View2
02:00:00​
20230421
User_4Page_View2
01:00:00​
20230421
User_4Page_View1
01:05:00​
20230421
User_4Page_View2
03:00:00​
20230421
User_4Page_View2
01:00:00​
20230421
User_4Page_View2
01:05:00​
20230421
User_4Page_View1
03:00:00​
20230421
 
Rapid_SQL schrieb:
Nun will ich User abgrenzen, die gewissen Kriterien entsprechen:
Zum Vergrößern anklicken....
Ich habe die Daten aus meinem ersten Beispiel nach Deinen Angaben angepasst. Das sind tatsächlich hauptsächlich die Änderungen gemäß Deiner Datenangaben.
Außerdem habe ich eine(1) Where Clause umgestellt für die direkte zeitliche Abfolge und natürlich die Eventnamen angepasst.
Das letze Beispiel aus dem ersten Wurd habe ich so gelassen, es bleibt mangels passener Daten im Ergebnis leer, liefert aber weiterhin Ideen zu den Möglichkeiten, Du möchtest ja Mentoring ;)

MySQL 8.0

| version() | |:----------| | 8.0.30 |
dbfiddle.uk dbfiddle.uk

akretschmer schrieb:
Ich an Deiner Stelle würde die Wahl der Datenbank noch einmal übderdenken.
Zum Vergrößern anklicken....
Diesen Gedanken aus dem Nachbarthread kann ich nur unterstützen.
Auch wenn ich hier eine Lösung mit mysql hinbekommen habe - es mag andere, bessere geben-, mit Postgres hast Du mehr Möglichkeiten, einfachere, elegantere Lösungswege und zuverlässige(re) Ergebnisse(!) (und ich lehne mich mal etwas aus dem Fenster, in diesem Forum auch mehr Support)
 
Ich würde instinktiv zum Join-Ansatz greifen, hat @dabadepdu in seinem letzten Query auch gezeigt. Das Prinzip dabei ist eigentlich immer identisch: Du joinst die Tabelle mit sich selbst und guckst ob nach dem ersten Event das zweite eingetreten oder nicht eingetreten ist. Ich würde normalerweise jetzt Code zum Beispiel schreiben aber das wäre irgendwie redundant.

lead() ist super wenn es funktioniert, manchmal komme ich damit auch nicht so recht weiter. Allerdings ist das Beispiel von @dabadepdu wohl nicht ganz richtig oder ich verstehe es falsch, es müsste
Code: 
over (partition by event_user order by event_ts)
sein, nicht
Code: 
over (order by event_user, event_ts)
 
ukulele schrieb:
lead() ist super wenn es funktioniert, manchmal komme ich damit auch nicht so recht weiter. Allerdings ist das Beispiel von @dabadepdu wohl nicht ganz richtig oder ich verstehe es falsch, es müsste
Zum Vergrößern anklicken....
Lead ist super und funktioniert, zumindest habe ich noch nicht gehört, dass auch das nicht geht in mySQL, zumindest ab Version 8.xy - keine Ahnung.
Ein Partition By ist hier m.E. gerade nicht nötig, weil es um den direkten Nachfolger geht (im Gegensatz zu dem anderen Fall). Und es werden keine Partitionen gebildet / es müssen laut Anforderung keine gebildet werden.
(Zumindest habe ich die etwas undeutliche Anforderung so verstanden, die m.E. nur im Paket (1 und 2) so zu verstehen ist, wie ich es umgesetzt habe.)
Lead mit Order By bringt m.E. genau diesen Fall, eindeutige Reihenfolge und definierte "Distanz", also nicht "nächst bester" Datensatz oder irgendeiner danach, sondern genau der folgende (unter der vorgegebenen Sortierung). Das ist ja genau das Spezifikum, das SQL zu Spreadsheet macht.
(Und wenn man über Dutzend Millionen Datensätze redet, wäre es u.U. sogar performanter als der andere Ansatz mit 2 Scans. Aber wie mySQL optimiert, kann ich eigentlich auch überhaupt nicht sagen- und will ich eigentlich nicht mal wissen)

Ja und es kommt glaub ich das raus, was der TE haben wollte.
Kann sein, dass ich irgendwo Daten falsch übertragen hab, was auch immer. Um sowas zu vermeiden, könnte der TE ja insert scripte liefern, statt copy / paste Daten. Dann würden wir alle garantiert über das Gleiche reden.

Und der TE könnte ja auch mal was dazu sagen. Ich mein, man muss primär erstmal nur auf den RUN Button drücken und das Ergebnis ansehen.
 
Werbung:
Natürlich funktioniert lead() technisch, es funktioniert nur manchmal für mich nicht um das gewünschte Ergebnis zu erzielen.
 
Zurück
Oben