GSP407

Überblick

BigQuery ist eine vollständig verwaltete, automatisierte und kostengünstige Analysedatenbank von Google. Mit diesem Tool können Sie mehrere Terabyte an Daten abfragen und müssen dabei weder eine Infrastruktur verwalten, noch benötigen Sie einen Datenbankadministrator. BigQuery basiert auf SQL und kann als „Pay as you go“-Modell genutzt werden. Mithilfe von BigQuery können Sie sich ganz auf die Datenanalyse konzentrieren, um wichtige Informationen zu erhalten.

Für dieses Lab steht ein neues E‑Commerce-Dataset mit Millionen Google Analytics-Datensätzen des Google Merchandise Store als BigQuery-Tabelle zur Verfügung. In diesem Lab verwenden Sie eine Kopie des Datasets. Sie untersuchen die Daten in verschiedenen Beispielszenarien und lernen, wie Sie Duplikate aus dem Dataset entfernen können. Außerdem führen Sie weitere Analysen der Daten durch.

Ausführliche Erklärungen zu den BigQuery-Abfragen, mit denen die Daten in diesem Lab analysiert werden, finden Sie in der BigQuery-Referenz zur Abfragesyntax.

Lernziele

In diesem Lab nutzen Sie BigQuery für Folgendes:

• Auf ein E-Commerce-Dataset zugreifen
• Die Metadaten im Dataset ansehen
• Doppelte Einträge entfernen
• Abfragen schreiben und ausführen

Einrichtung und Anforderungen

Vor dem Klick auf „Start Lab“ (Lab starten)

Lesen Sie diese Anleitung. Labs sind zeitlich begrenzt und können nicht pausiert werden. Der Timer beginnt zu laufen, wenn Sie auf Lab starten klicken, und zeigt Ihnen, wie lange Google Cloud-Ressourcen für das Lab verfügbar sind.

In diesem praxisorientierten Lab können Sie die Lab-Aktivitäten in einer echten Cloud-Umgebung durchführen – nicht in einer Simulations- oder Demo-Umgebung. Dazu erhalten Sie neue, temporäre Anmeldedaten, mit denen Sie für die Dauer des Labs auf Google Cloud zugreifen können.

Für dieses Lab benötigen Sie Folgendes:

• Einen Standardbrowser (empfohlen wird Chrome)

Hinweis: Nutzen Sie den privaten oder Inkognitomodus (empfohlen), um dieses Lab durchzuführen. So wird verhindert, dass es zu Konflikten zwischen Ihrem persönlichen Konto und dem Teilnehmerkonto kommt und zusätzliche Gebühren für Ihr persönliches Konto erhoben werden.

• Zeit für die Durchführung des Labs – denken Sie daran, dass Sie ein begonnenes Lab nicht unterbrechen können.

Hinweis: Verwenden Sie für dieses Lab nur das Teilnehmerkonto. Wenn Sie ein anderes Google Cloud-Konto verwenden, fallen dafür möglicherweise Kosten an.

Lab starten und bei der Google Cloud Console anmelden

1. Klicken Sie auf Lab starten. Wenn Sie für das Lab bezahlen müssen, wird ein Dialogfeld geöffnet, in dem Sie Ihre Zahlungsmethode auswählen können. Auf der linken Seite befindet sich der Bereich „Details zum Lab“ mit diesen Informationen:

   • Schaltfläche „Google Cloud Console öffnen“
   • Restzeit
   • Temporäre Anmeldedaten für das Lab
   • Ggf. weitere Informationen für dieses Lab

2. Klicken Sie auf Google Cloud Console öffnen (oder klicken Sie mit der rechten Maustaste und wählen Sie Link in Inkognitofenster öffnen aus, wenn Sie Chrome verwenden).

   Im Lab werden Ressourcen aktiviert. Anschließend wird ein weiterer Tab mit der Seite „Anmelden“ geöffnet.

   Tipp: Ordnen Sie die Tabs nebeneinander in separaten Fenstern an.

   Hinweis: Wird das Dialogfeld Konto auswählen angezeigt, klicken Sie auf Anderes Konto verwenden.

3. Kopieren Sie bei Bedarf den folgenden Nutzernamen und fügen Sie ihn in das Dialogfeld Anmelden ein.

   {{{user_0.username | "Username"}}}

   Sie finden den Nutzernamen auch im Bereich „Details zum Lab“.

4. Klicken Sie auf Weiter.

5. Kopieren Sie das folgende Passwort und fügen Sie es in das Dialogfeld Willkommen ein.

   {{{user_0.password | "Password"}}}

   Sie finden das Passwort auch im Bereich „Details zum Lab“.

6. Klicken Sie auf Weiter.

   Wichtig: Sie müssen die für das Lab bereitgestellten Anmeldedaten verwenden. Nutzen Sie nicht die Anmeldedaten Ihres Google Cloud-Kontos. Hinweis: Wenn Sie Ihr eigenes Google Cloud-Konto für dieses Lab nutzen, können zusätzliche Kosten anfallen.

7. Klicken Sie sich durch die nachfolgenden Seiten:

   • Akzeptieren Sie die Nutzungsbedingungen.
   • Fügen Sie keine Wiederherstellungsoptionen oder Zwei-Faktor-Authentifizierung hinzu (da dies nur ein temporäres Konto ist).
   • Melden Sie sich nicht für kostenlose Testversionen an.

Nach wenigen Augenblicken wird die Google Cloud Console in diesem Tab geöffnet.

Hinweis: Wenn Sie auf Google Cloud-Produkte und ‑Dienste zugreifen möchten, klicken Sie auf das Navigationsmenü oder geben Sie den Namen des Produkts oder Dienstes in das Feld Suchen ein.

Aufgabe 1: Lab-Projekt in BigQuery anpinnen

In diesem Abschnitt fügen Sie Ihren Umgebungsressourcen das Projekt data-to-insights hinzu.

1. Klicken Sie auf Navigationsmenü > BigQuery.

Das Fenster Willkommen bei BigQuery in der Cloud Console wird geöffnet.

Hinweis: Dieses Fenster enthält einen Link zur Kurzanleitung und Informationen zu Aktualisierungen der Benutzeroberfläche.

2. Klicken Sie auf Fertig.

Öffentliche BigQuery-Datasets werden nicht standardmäßig in der BigQuery-Webbenutzeroberfläche angezeigt. Zum Öffnen des Projekts für öffentliche Datasets kopieren Sie data-to-insights.

3. Klicken Sie auf + Hinzufügen > Projekt nach Name markieren. Fügen Sie dann den Namen in data-to-insights ein. Klicken Sie auf MARKIEREN.

Daraufhin wird im Abschnitt „Explorer“ das Projekt „data-to-insights“ angezeigt.

Aufgabe 2: E-Commerce-Daten überprüfen und doppelte Datensätze erkennen

Szenario: Ihr Analyseteam hat die Google Analytics-Protokolle für eine E-Commerce-Website nach BigQuery exportiert und eine neue Tabelle mit allen Rohdaten der E-Commerce-Besuchersitzungen erstellt.

So können Sie sich die Daten in der Tabelle all_sessions_raw ansehen:

1. Klicken Sie auf das Symbol Knoten maximieren neben data-to-insights, um das Projekt zu maximieren.
2. Maximieren Sie ecommerce.
3. Klicken Sie auf all_sessions_raw.

Im rechten Bereich wird ein Abschnitt mit drei Ansichten der Tabellendaten geöffnet:

• Tab „Schema“: Feldname, Typ, Modus und Beschreibung (die logischen Einschränkungen, mit denen die Daten organisiert sind)
• Tab „Details“: Metadaten der Tabelle
• Tab „Vorschau“: Vorschau der Tabelle

4. Klicken Sie auf den Tab Details, um die Metadaten der Tabelle aufzurufen.

Fragen:

Doppelte Zeilen erkennen

Anhand einer Beispieldatenmenge lässt sich leichter verstehen, was im Dataset enthalten ist.

1. Wenn Sie sich, ohne SQL zu verwenden, Beispielzeilen aus der Tabelle als Vorschau anzeigen lassen möchten, klicken Sie auf den Tab Vorschau.

2. Scrollen Sie durch die Zeilen und sehen Sie sich sie Daten an. Es gibt kein Feld, das eine Zeile eindeutig kennzeichnet. Zum Erkennen doppelter Zeilen brauchen Sie deshalb eine erweiterte Logik.

3. In dieser Abfrage (unten) wird für jedes Feld die SQL‑Funktion GROUP BY verwendet und mit der Funktion COUNT werden Zeilen gezählt, deren Felder jeweils denselben Wert haben.

• Wenn jedes Feld eindeutig ist, gibt COUNT den Wert 1 zurück, da keine anderen Zeilengruppierungen mit demselben Wert für alle Felder vorhanden sind.
• Wenn alle Felder mehrerer Zeilen die gleichen Werte haben, werden diese Zeilen zu einer Gruppe zusammengefasst. COUNT ist dann größer als 1.

Im letzten Teil der Abfrage kommt mit der Klausel HAVING ein Aggregationsfilter zum Einsatz, mit dem nur die Ergebnisse angezeigt werden, deren COUNT-Wert für Duplikate größer als 1 ist. Daher ist die Anzahl der Datensätze, die Duplikate enthalten, mit der Anzahl der Zeilen in der resultierenden Tabelle identisch.

4. Kopieren Sie die folgende Abfrage und fügen Sie sie in den Abfrage-Editor ein. Klicken Sie dann auf Abfrage ausführen, um herauszufinden, welche Datensätze in allen Spalten doppelt vorhanden sind.

#standardSQL SELECT COUNT(*) as num_duplicate_rows, * FROM `data-to-insights.ecommerce.all_sessions_raw` GROUP BY fullVisitorId, channelGrouping, time, country, city, totalTransactionRevenue, transactions, timeOnSite, pageviews, sessionQualityDim, date, visitId, type, productRefundAmount, productQuantity, productPrice, productRevenue, productSKU, v2ProductName, v2ProductCategory, productVariant, currencyCode, itemQuantity, itemRevenue, transactionRevenue, transactionId, pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type, eCommerceAction_step, eCommerceAction_option HAVING num_duplicate_rows > 1;

Hinweis: Selbst wenn Sie einen eindeutigen Schlüssel verwenden, ist es auch bei eigenen Datasets sinnvoll, die Eindeutigkeit der Zeilen mit COUNT, GROUP BY und HAVING vor der Analyse zu prüfen.

Klicken Sie auf Fortschritt prüfen. Doppelte Zeilen erkennen

Neue Tabelle „all_sessions“ analysieren

In diesem Abschnitt verwenden Sie eine deduplizierte Tabelle mit dem Namen all_sessions.

Szenario: Ihr Analyseteam hat die folgende Abfrage bereitgestellt. Schemaspezialisten haben die Schlüsselfelder ermittelt, die laut Schema für jeden Datensatz eindeutig sein müssen.

1. Führen Sie die Abfrage aus, um dafür zu sorgen, dass keine Duplikate vorhanden sind. Diesmal für die Tabelle all_sessions:

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # the unique visitor ID visitId, # a visitor can have multiple visits date, # session date stored as string YYYYMMDD time, # time of the individual site hit (can be 0 to many per visitor session) v2ProductName, # not unique since a product can have variants like Color productSKU, # unique for each product type, # a visitor can visit Pages and/or can trigger Events (even at the same time) eCommerceAction_type, # maps to ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # revenue of the order transactionId, # unique identifier for revenue bearing transaction COUNT(*) as row_count FROM `data-to-insights.ecommerce.all_sessions` GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12 HAVING row_count > 1 # find duplicates

Die Abfrage gibt keine Datensätze zurück.

Hinweis: In SQL können Sie GROUP BY (Gruppieren nach) oder ORDER BY (Sortieren nach) auf den Index der Spalte anwenden, wie z. B. GROUP BY 1 statt GROUP BY fullVisitorId.

Aufgabe 3: Einfache SQL-Abfrage für E-Commerce-Daten schreiben

In diesem Abschnitt fragen Sie Informationen aus dem E-Commerce-Dataset ab.

Anzahl einzelner Besucher abfragen

Mit der Abfrage wird die Gesamtzahl der Aufrufe durch Zählen des Wertes product_views sowie die Anzahl einzelner Besucher (unique_visitors) durch Zählen des Wertes fullVisitorID ermittelt.

1. Klicken Sie auf das Symbol + (Neue Abfrage erstellen).
2. Geben Sie die folgende Abfrage in den Editor ein:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;

3. Wenn Sie prüfen möchten, ob die Syntax korrekt ist, setzen Sie das grüne Häkchen für die Abfragevalidierung in Echtzeit.
4. Klicken Sie auf Ausführen. Auf dem Tab „Ergebnisse“ sehen Sie die Anzahl der einzelnen Besucher.

Ergebnisse:

5. Schreiben Sie nun eine Abfrage, mit der die Gesamtzahl einzelner Besucher (fullVisitorID) angezeigt wird, diesmal aber nach der Website gruppiert, von der aus sie zum Store gelangt sind (channelGrouping):

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;

Ergebnisse:

6. Schreiben Sie eine Abfrage, mit der alle eindeutigen Produktnamen (v2ProductName) alphabetisch aufgelistet werden:

#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName

Tipp: In SQL sind die ORDER BY-Klauseln standardmäßig aufsteigend von A bis Z sortiert. Sie können diese Einstellung umkehren und DESC für „ORDER BY field_name“ eingeben.

Ergebnisse:

Diese Abfrage gibt insgesamt 633 Produkte (Zeilen) zurück.

7. Schreiben Sie eine Abfrage, mit der die fünf Produkte aufgelistet werden, die von allen Besuchern (auch Personen, die das gleiche Produkt mehrmals aufgerufen haben) am häufigsten aufgerufen wurden (product_views). Die Abfrage zählt, wie oft ein Produkt (v2ProductName) aufgerufen wurde (product_views), sortiert die Liste in absteigender Reihenfolge und listet die ersten fünf Einträge auf:

Tipp: In Google Analytics kann ein Besucher ein Produkt während der folgenden Interaktionstypen „ansehen“: 'page', 'screenview', 'event', 'transaction', 'item', 'social', 'exception', 'timing'. Für unsere Zwecke filtern Sie nur nach Typ = 'PAGE'.

#standardSQL SELECT COUNT(*) AS product_views, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Ergebnisse:

8. Bonus: Verfeinern Sie die Abfrage nun so, dass Produktansichten für Besucher, die ein Produkt sehr häufig angesehen haben, nicht mehr doppelt gezählt werden. Jede einzelne Produktansicht sollte pro Besucher nur einmal gezählt werden:

WITH unique_product_views_by_person AS ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

Tipp: Mit der SQL‑Klausel WITH können Sie eine komplexe Abfrage in mehrere Schritte aufteilen. Hier erstellen wir zuerst eine Abfrage, die jedes einzelne Produkt pro Besucher findet und einmal zählt. Die zweite Abfrage führt dann die Aggregation für alle Besucher und Produkte aus.

Ergebnisse:

9. Erweitern Sie dann die letzte Abfrage so, dass die Gesamtzahl der verschiedenen bestellten Produkte und die Gesamtzahl der bestellten Einheiten (productQuantity) enthalten sind:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Ergebnisse:

Fragen:

10. Erweitern Sie die Abfrage so, dass die durchschnittliche Produktmenge pro Bestellung angezeigt wird, d. h. die Gesamtzahl der bestellten Einheiten ÷ Gesamtzahl der Bestellungen bzw. SUM(productQuantity)/COUNT(productQuantity):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Ergebnisse

Frage:

Das Produkt mit dem Namen „22 oz YouTube Bottle Infuser“ weist mit 9,38 Stück pro Bestellung die höchste durchschnittliche Bestellmenge (avg_per_order) auf.

Klicken Sie auf Fortschritt prüfen. Einfache SQL-Abfrage für E-Commerce-Daten schreiben

Glückwunsch!

Glückwunsch! In diesem Lab haben Sie BigQuery zum Ansehen und Abfragen von Daten verwendet, um aussagekräftige Informationen zu den verschiedenen Aspekten im Produktmarketing zu erhalten. Sie haben gelernt, wie Sie ein E‑Commerce-Dataset aufrufen, die Dataset-Metadaten anzeigen lassen, doppelte Einträge entfernen sowie Abfragen erstellen und ausführen.

Weitere Informationen

• Öffentliche BigQuery-Datasets
• Sie haben bereits ein Google Analytics-Konto und möchten Ihre eigenen Datasets in BigQuery abfragen? Folgen Sie dieser Anleitung zum Exportieren nach BigQuery.
• Lesen Sie den Blogpost 15 Awesome things you probably didn’t know about BigQuery
• In folgenden Labs erfahren Sie mehr über BigQuery:
  • Häufig auftretende SQL-Fehler mit BigQuery beheben
  • Wetterdaten in BigQuery

Google Cloud-Schulungen und -Zertifizierungen

In unseren Schulungen erfahren Sie alles zum optimalen Einsatz unserer Google Cloud-Technologien und können sich entsprechend zertifizieren lassen. Unsere Kurse vermitteln technische Fähigkeiten und Best Practices, damit Sie möglichst schnell mit Google Cloud loslegen und Ihr Wissen fortlaufend erweitern können. Wir bieten On-Demand-, Präsenz- und virtuelle Schulungen für Anfänger wie Fortgeschrittene an, die Sie individuell in Ihrem eigenen Zeitplan absolvieren können. Mit unseren Zertifizierungen weisen Sie nach, dass Sie Experte im Bereich Google Cloud-Technologien sind.

Anleitung zuletzt am 2. April 2024 aktualisiert

Lab zuletzt am 2. April 2024 getestet

© 2025 Google LLC. Alle Rechte vorbehalten. Google und das Google-Logo sind Marken von Google LLC. Alle anderen Unternehmens- und Produktnamen können Marken der jeweils mit ihnen verbundenen Unternehmen sein.