Umfrageantworten aus Google Formulare umwandeln und in BigQuery laden

1. Einführung

Es gibt viele Gründe, Umfragen durchzuführen: Kundenzufriedenheit bewerten, Marktforschung betreiben, ein Produkt oder eine Dienstleistung verbessern oder das Engagement der Mitarbeiter beurteilen. Wenn Sie jedoch schon einmal mit Umfragedaten gearbeitet haben, wissen Sie wahrscheinlich, dass das Standardformat schwer zu bearbeiten ist. In diesem Leitfaden erstellen wir eine automatisierte Pipeline, die Google Formulare-Ergebnisse erfasst, die Daten für die Analyse mit Cloud Dataprep vorbereitet, sie in BigQuery lädt und Ihrem Team ermöglicht, visuelle Analysen mit Tools wie Looker oder Data Studio durchzuführen.

Aufgaben

In diesem Codelab verwenden Sie Dataprep, um Antworten aus unserer Google Formulare-Beispielumfrage in ein Format zu transformieren, das für die Datenanalyse nützlich ist. Sie übertragen die transformierten Daten nach BigQuery, wo Sie mit SQL tiefergehende Fragen stellen und die Daten mit anderen Datasets verknüpfen können, um leistungsfähigere Analysen durchzuführen. Am Ende können Sie vordefinierte Dashboards erkunden oder Ihr eigenes Business-Intelligence-Tool mit BigQuery verbinden, um neue Berichte zu erstellen.

Lerninhalte

  • Umfragedaten mit Dataprep transformieren
  • Umfragedaten in BigQuery übertragen
  • So gewinnen Sie mehr Erkenntnisse aus Umfragedaten

Voraussetzungen

2. Google Formulare-Antworten verwalten

Sehen wir uns zuerst die Google Formulare-Antworten auf unsere Beispielumfrage genauer an.

f3d25efd2cc923f5.png

Die Umfrageergebnisse können auf dem Tab „Antworten“ exportiert werden. Klicken Sie dazu auf das Google Sheets-Symbol und erstellen Sie eine neue Tabelle oder laden Sie die Ergebnisse in eine vorhandene Tabelle. In Google Formulare werden der Tabelle weiterhin Antworten hinzugefügt, bis Sie die Option „Antworten akzeptieren“ deaktivieren.

d499e5a4dccdf5fd.png4939332a5d8f9f19.png

Sehen wir uns nun die einzelnen Antworttypen und ihre Darstellung in der Google Sheets-Datei an.

3. Umfrageantworten transformieren

Umfragefragen können in vier Familien gruppiert werden, die ein bestimmtes Exportformat haben. Je nach Art der Frage müssen Sie die Daten auf eine bestimmte Weise umstrukturieren. Hier sehen wir uns die einzelnen Gruppen und die Arten von Transformationen an, die wir anwenden müssen.

Fragen mit Einzelauswahl: Kurzantwort, Absatz, Drop-down, lineare Skala usw.

  • Fragename: Spaltenname
  • Antwort: Zellenwert
  • Transformationsanforderungen: Es ist keine Transformation erforderlich. Die Antwort wird unverändert geladen.

3eeedc50b0fd54fd.png

Multiple-Choice-Fragen: Multiple-Choice, Kästchen

  • Fragename: Spaltenname
  • Antwort: Liste von Werten, die durch Semikolons getrennt sind (z.B. „Resp 1; Resp 4; Resp 6“)
  • Transformationsanforderungen: Die Liste der Werte muss extrahiert und pivotiert werden, sodass jede Antwort zu einer neuen Zeile wird.

cab8a38a96a13ce4.png

Multiple-Choice-Rasterfragen

Hier ist ein Beispiel für eine Multiple-Choice-Frage. In jeder Zeile muss ein einzelner Wert ausgewählt werden.

c6ea3d47d4dd5e78.png

  • Fragename: Jede einzelne Frage wird zu einem Spaltennamen im Format „Frage [Option]“.
  • Antwort: Jede einzelne Antwort im Raster wird zu einer Spalte mit einem eindeutigen Wert.
  • Transformationsanforderungen: Jede Frage/Antwort muss eine neue Zeile in der Tabelle werden und in zwei Spalten aufgeteilt werden. Eine Spalte mit der Fragenoption und eine Spalte mit der Antwort.

9223d0271516c58d.png

Multiple-Choice-Fragen mit Kästchenraster

Hier ist ein Beispiel für ein Kästchenraster. Sie können in jeder Zeile keinen oder mehrere Werte auswählen.

4e3189b8cc2d4a8b.png

  • Fragename: Jede einzelne Frage wird zu einem Spaltennamen im Format „Frage [Option]“.
  • Antwort: Jede einzelne Antwort im Raster wird zu einer Spalte mit einer Liste von Werten, die durch Semikolons getrennt sind.
  • Transformationsanforderungen: Diese Fragetypen kombinieren die Kategorien „Kästchen“ und „Multiple-Choice-Raster“ und müssen in dieser Reihenfolge aufgelöst werden.

Zuerst muss die Werteliste jeder Antwort extrahiert und pivotiert werden, sodass jede Antwort zu einer neuen Zeile für die jeweilige Frage wird.

Zweitens: Jede einzelne Antwort muss eine neue Zeile in der Tabelle werden und in zwei Spalten aufgeteilt werden. Eine Spalte mit der Option der Frage und eine Spalte mit der Antwort.

3c3c2bd098e03003.png

Als Nächstes zeigen wir, wie diese Transformationen mit Cloud Dataprep verarbeitet werden.

4. Cloud Dataprep-Ablauf erstellen

„Google Forms Analytics Design Pattern“ in Cloud Dataprep importieren

Laden Sie das Ablaufpaket Google Forms Analytics Design Pattern herunter, ohne es zu entpacken. Klicken Sie in der Cloud Dataprep-Anwendung in der linken Navigationsleiste auf das Symbol „Flows“. Wählen Sie dann auf der Seite „Abläufe“ im Kontextmenü die Option „Importieren“ aus.

ba7c0cb0eec398df.png

Nachdem Sie den Ablauf importiert haben, wählen Sie ihn aus, um ihn zu bearbeiten. Ihr Bildschirm sollte so aussehen:

44978861eb34ec71.png

Google Sheets-Tabelle mit Umfrageergebnissen verknüpfen

Auf der linken Seite des Ablaufs muss die Datenquelle wieder mit einer Google-Tabelle verbunden werden, die die Google Formulare-Ergebnisse enthält. Klicken Sie mit der rechten Maustaste auf das Google Sheets-Datasetsobjekt und wählen Sie „Ersetzen“ aus.

55c16f0c04366f0c.png

Klicken Sie dann unten im modalen Fenster auf den Link „Datasets importieren“. Klicken Sie auf das Stiftsymbol „Pfad bearbeiten“.

8afeef260c96277f.png

Ersetzen Sie den aktuellen Wert durch diesen Link, der auf eine Google-Tabelle mit einigen Google Formulare-Ergebnissen verweist. Sie können unser Beispiel oder Ihre eigene Kopie verwenden: https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing

Klicken Sie auf „Los“ und dann rechts unten auf „Importieren und zu Ablauf hinzufügen“. Klicken Sie dann im Dialogfeld unten rechts auf „Ersetzen“.

BigQuery-Tabellen verbinden

Auf der rechten Seite des Ablaufs müssen Sie die Ausgaben mit Ihrer eigenen BigQuery-Instanz verbinden. Klicken Sie für jede Ausgabe auf das Symbol und bearbeiten Sie die Attribute wie folgt.

Bearbeiten Sie zuerst die „Manuellen Ziele“.

a3fc2cb80153ec25.png

Klicken Sie auf dem Bildschirm „Veröffentlichungseinstellungen“ auf die Schaltfläche „Bearbeiten“.

85791e6162a370de.png

Wenn der Bildschirm „Publishing Action“ (Veröffentlichungsaktion) angezeigt wird, müssen Sie die Verbindungseinstellungen ändern. Klicken Sie dazu auf die BigQuery-Verbindung und bearbeiten Sie die Eigenschaften.

1f3e4887baaeaffd.png

Wählen Sie das BigQuery-Dataset aus, in das die Google Formulare-Ergebnisse geladen werden sollen. Sie können „default“ auswählen, wenn Sie noch kein BigQuery-Dataset erstellt haben.

f4eaa05ecf9de162.png

Nachdem Sie die „Manuellen Ziele“ bearbeitet haben, gehen Sie für die Ausgabe „Geplante Ziele“ genauso vor.

46edea1b8ca63270.png

Wiederholen Sie die Schritte für jede Ausgabe. Insgesamt müssen Sie acht Ziele bearbeiten.

5. Cloud Dataprep-Ablauf erklärt

Die grundlegende Idee des Ablaufs „Google Formulare Analytics Design Pattern“ besteht darin, die Transformationen der Umfrageantworten wie zuvor beschrieben durchzuführen. Dazu wird jede Fragekategorie in ein bestimmtes Cloud Dataprep-Datentransformationsschema aufgeschlüsselt.

In diesem Ablauf werden die Fragen in vier Tabellen unterteilt (der Einfachheit halber entsprechend den vier Fragekategorien).

afa421849b1bd398.png

Wir empfehlen, die Rezepte nacheinander zu erkunden, beginnend mit „Clean Headers“, dann „SingleChoiceSELECT-Questions“ und dann die anderen Rezepte darunter.

Alle Rezepte sind kommentiert, um die verschiedenen Transformationsschritte zu erklären. In einem Rezept können Sie einen Schritt bearbeiten und sich den Vorher-/Nachher-Zustand einer bestimmten Spalte ansehen.

449da06d96cd520e.png4ac6e14f578d0707.png

6. Cloud Dataprep-Ablauf ausführen

Nachdem Quelle und Ziel richtig konfiguriert sind, können Sie den Ablauf ausführen, um die Antworten zu transformieren und in BigQuery zu laden. Wählen Sie die einzelnen Ausgaben aus und klicken Sie auf „Ausführen“. Wenn die angegebene BigQuery-Tabelle vorhanden ist, werden neue Zeilen von Dataprep angehängt. Andernfalls wird eine neue Tabelle erstellt.

47cf50f6d17a5b1e.png

Klicken Sie im linken Bereich auf das Symbol „Jobverlauf“, um die Jobs zu überwachen. Es sollte einige Minuten dauern, bis die BigQuery-Tabellen geladen sind.

afc79eeb27202fb4.png

Wenn alle Jobs abgeschlossen sind, werden die Umfrageergebnisse in einem sauberen, strukturierten und normalisierten Format in BigQuery geladen und können analysiert werden.

7. Umfragedaten in BigQuery analysieren

In der Google Console für BigQuery sollten Sie die Details der einzelnen neuen Tabellen sehen können.

df370873572511ac.png

Mit den Umfragedaten in BigQuery können Sie ganz einfach umfassendere Fragen stellen, um die Umfrageantworten besser zu verstehen. Angenommen, Sie möchten herausfinden, welche Programmiersprache von Personen mit unterschiedlichen Berufsbezeichnungen am häufigsten verwendet wird. Dann können Sie eine Abfrage wie diese schreiben:

SELECT
   programming_answers.Language  AS programming_answers_language,
   project_answers.Title  AS project_answers_title,
   AVG((case when programming_answers.Level='None' then 0 
when programming_answers.Level='beginner' then 1
when programming_answers.Level='competent' then 2 
when programming_answers.Level='proficient' then 3
when programming_answers.Level='expert' then 4 
else null end) ) AS programming_answers_average_level_value
FROM `my-project.DesignPattern.A000111_ProjectAnswers` AS project_answers
INNER JOIN `my-project.A000111_ProgrammingAnswers` AS programming_answers
ON programming_answers.RESPONSE_ID = project_answers.RESPONSE_ID
GROUP BY 1,2
ORDER BY 3 DESC

Um Ihre Analysen noch aussagekräftiger zu machen, können Sie die Umfrageantworten mit CRM-Daten verknüpfen, um zu sehen, ob die Teilnehmer mit Konten übereinstimmen, die bereits in Ihrem Data Warehouse enthalten sind. So können Sie fundiertere Entscheidungen im Hinblick auf den Kundensupport oder die Ausrichtung auf Nutzer bei neuen Produkteinführungen treffen.

Hier zeigen wir, wie Sie die Umfragedaten anhand der Domain des Befragten und der Website des Kontos mit einer Kontotabelle verknüpfen können. Jetzt sehen Sie die Verteilung der Antworten nach Kontotyp. So können Sie nachvollziehen, wie viele Antworten von Bestandskunden stammen.

SELECT
   account.TYPE  AS account_type,
   COUNT(DISTINCT project_answers.Domainname) AS project_answers_count_domains
FROM `my-project.A000111_ProjectAnswers` AS project_answers
LEFT JOIN `my-project.testing.account` AS account 
ON project_answers.Domainname=account.website
GROUP BY 1

8. Visuelle Analysen durchführen

Nachdem Ihre Umfragedaten nun in einem Data Warehouse zentralisiert sind, können Sie sie ganz einfach in einem Business-Intelligence-Tool analysieren. Wir haben einige Beispielberichte in Data Studio und in Looker erstellt.

Looker

Wenn Sie bereits eine Looker-Instanz haben, können Sie die LookML in diesem Ordner verwenden, um mit der Analyse der Beispielumfrage- und CRM-Daten für dieses Muster zu beginnen. Erstellen Sie einfach ein neues Looker-Projekt, fügen Sie die LookML hinzu und ersetzen Sie die Verbindungs- und Tabellennamen in der Datei, damit sie Ihrer BigQuery-Konfiguration entsprechen. Wenn Sie keine Looker-Instanz haben, aber mehr erfahren möchten, können Sie hier eine Demo vereinbaren.

129db05d6f85f484.png

Data Studio

Alternativ können Sie einen Bericht in Data Studio erstellen. Klicken Sie dazu auf den Rahmen mit dem Google-Kreuz „Leerer Bericht“ und stellen Sie eine Verbindung zu BigQuery her. Folgen Sie allen Anweisungen von Data Studio. Hier finden Sie einen Schnellstart und eine Einführung in die wichtigsten Funktionen von Data Studio. Hier finden Sie auch unsere vorgefertigten Data Studio-Dashboards.

5e744869e3fe3f8f.png

9. Bereinigen

Am einfachsten können Sie weitere Kosten vermeiden, wenn Sie das Cloud-Projekt löschen, das Sie für die Anleitung erstellt haben. Alternativ haben Sie die Möglichkeit, die einzelnen Ressourcen zu löschen.

  1. Rufen Sie in der Cloud Console Ressourcen verwalten auf.
  2. Wählen Sie in der Projektliste das Projekt aus, das Sie löschen möchten, und klicken Sie auf Löschen.
  3. Geben Sie im Dialogfeld die Projekt-ID ein und klicken Sie auf Beenden, um das Projekt zu löschen.