Einer der häufigsten KPIs, die unsere Kunden gerne sehen, sind die Seitenaufrufe ihrer Website. Vor diesem Hintergrund stellen wir immer die folgenden Kennzahlen zur Verfügung, nämlich Seitenaufrufe, eindeutige Seitenaufrufe, Zielseite, Seitenpfad und Ausgangsseite. Auf diese Weise haben unsere Kunden auf der Website einen Überblick über ihre Kunden.
Bist du bereit, dir mit ein bisschen Code die Hände schmutzig zu machen? Schauen wir uns dieses Stück SQL genauer an und sehen, was es kann!
https://gist.github.com/sigmaError/bc8d4d5728480fe7195b690ebf8d58f1
Vorbereitung der Daten
Zuerst haben wir unsere prep_page_view Unterabfrage, dieser Block der Abfrage sagt einfach: „Hey, bevor wir mit dem Rest des Codes fortfahren, lassen Sie uns zuerst einige Vorbereitungen treffen.“
In diesem Vorbereitungscodeblock extrahieren wir nur die erforderlichen Spalten, zum Beispiel event_timestamp und Benutzer_Pseudo_ID. Das session_id, page_title, page_location und is_landing_page sind unterschiedlich und müssen extrahiert werden mit UNNEST Funktion. Warum fragst du? Ich habe bereits einen Blogbeitrag darüber verfasst hier.
Um es noch einmal zusammenzufassen: Informationen wie page_view, page_title und andere werden in speziellen „wiederholten Feldern“ gespeichert, die als event_params. Um die Werte aus diesen wiederholten Feldern zu erhalten, müssen wir die UNNEST-Funktion verwenden. Wenn wir Daten für die Seitenansicht abrufen wollen, filtern wir sie nach event_name = 'Seitenansicht. Damit erhalten wir die folgenden Informationen für jedes Seitenaufrufereignis:
- event_timestamp: Die Uhrzeit, zu der das Ereignis eingetreten ist
- user_pseudo_id: Eine pseudonyme Kennung für den Benutzer, der das Ereignis ausgelöst hat
- session_id: Die Google Analytics-Sitzungs-ID für das Seitenaufrufereignis
- page_title: Der Titel der Seite, die angesehen wurde
- page_location: Die URL oder der Standort der Seite, die angesehen wurde
- is_landing_page: Ein boolescher Wert, der angibt, ob das Seitenaufrufereignis die erste Seite in der Sitzung des Benutzers war
Landing- und Exitpage definieren
Mit dieser Unterabfrage können wir bereits Kennzahlen wie den Gesamtseitenaufruf und auch den eindeutigen Seitenaufruf berechnen, aber wir wollen noch einen Schritt weiter gehen und den Pfad der Landing- und Exit-Page sehen. Um die Landing- und Exit-Pages zu konfigurieren, müssen wir unsere Tabelle noch weiter anpassen. Für die Landingpage überprüfen wir zunächst, ob Seiten_Standort Die Spalte ist TRUE, wenn sie einen TRUE-Wert hat, geben wir die Seiten-URL an.
Um zu bekommen Seite verlassen wir verwenden die Fensterfunktion und partitionieren sie durch Benutzer_Pseudo_ID und Sitzungs-ID und ordne die Daten nach event_timestamp aufsteigend und wenn wir dann den letzten Wert von page_location nehmen, erhalten wir den Seite verlassen.
Die vorherige und die nächste Seite können mit einem abgerufen werden VERZÖGERUNG und BLEI Funktion. Dies ist eine weitere Fensterfunktion, die im Grunde Daten betrachtet und jeweils einen Schritt davor (oder danach) vorgeht. In unserem Fall erhalten wir die URL der vorherigen (oder nächsten) Seiten. Mit dieser Art von Datenstruktur können wir uns die Reise eines Kunden während der Sitzung ansehen.
Wenn Sie die Abfrage ausführen, sollten Sie eine Tabelle haben, die ungefähr so aussieht wie die folgende:
Jetzt können Sie bereits mit den Daten herumspielen und intuitivere Tabellen und Grafiken erstellen. Im Folgenden habe ich Looker Studio verwendet, um mir Ansichten und einzigartige Ansichten für verschiedene Seiten anzusehen. Ich habe auch ein Diagramm für die Anzahl der Landingpages für verschiedene Seiten erstellt.
Wenn Sie dies auf eine andere Ebene bringen möchten, können Sie sogar ein Trichterdiagramm daraus machen. Das Trichterdiagramm bietet mehr Einblick in das Kundenverhalten. Zum Beispiel
- wie viele Kunden waren auf der Startseite
- wie viele von ihnen haben sich die verschiedenen Produkte angesehen
- wie viele haben die Produkte in den Einkaufswagen gelegt
- wie viele bezahlte Details hinzugefügt
- wie viele haben die Produkte gekauft
Wir werden vielleicht in Zukunft eine weitere Serie darüber erstellen, wie man ein Trichterdiagramm erstellt, aber wenn Sie es dringend benötigen, zögern Sie nicht, uns zu kontaktieren.
Unterschiede zu GA4
Warum ist es jetzt besser, Ihre Analysen in BigQuery und nicht in GA4 durchzuführen?
Alles in allem haben Sie in BigQuery mehr Flexibilität, um Ihre Seitenaufruf-Daten zu filtern und zu transformieren. Beispielsweise ist in GA4 eine Landing-Page-Dimension verfügbar, aber (bisher) keine Exit-Page. Um eine Vorstellung von deinem Austrittsverhalten in GA4 zu bekommen, musst du die Anzahl der Exits zu deiner Exploration hinzufügen, aber trotzdem fehlt dann die Rate der Exits.
Darüber hinaus können Sie mit den LEAD- und LAG-Funktionen ein besseres trichterähnliches Diagramm erstellen und mehr Tiefenanalysen als in GA4 durchführen.
Fazit
Insgesamt bietet die Abfrage einen detaillierten Überblick über das Nutzerverhalten auf einer Website, einschließlich der Reihenfolge der besuchten Seiten und ob bestimmte Seiten häufig als Landing Pages oder Exitpages verwendet werden. Jetzt können Sie sich mit ein bisschen Code die Hände schmutzig machen und herausfinden, wie beliebt Ihre Website ist.
Weitere Links
Folge uns auf LinkedIn für Einblicke in unsere tägliche Arbeit und wichtige Updates zu BigQuery, Data Studio und Marketing Analytics.
Abonniere unseren YouTube-Kanal für Diskussionen zu DWH, BigQuery, Looker Studio und Google Tag Manager.
Wenn Sie BigQuery von Grund auf lernen möchten, erhalten Sie Zugriff auf unsere kostenloser BigQuery-Kurs
Verbessern Sie Ihre Fähigkeiten mit Google Data Studio und BigQuery, indem Sie sich für unser Udemy-Kurs.
Benötigen Sie Hilfe bei der Einrichtung eines modernen, kostengünstigen Data Warehouse oder eines analytischen Dashboards? Senden Sie uns eine E-Mail an hello@datadice.io um einen Anruf zu vereinbaren. Call.
