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 Mitarbeiterengagement bewerten. Wenn Sie jedoch schon einmal versucht haben, mit Umfragedaten zu arbeiten, wissen Sie wahrscheinlich, dass das Standardformat nur schwer zu bearbeiten ist. In diesem Leitfaden erstellen wir eine automatisierte Pipeline, die Google Forms-Ergebnisse erfasst, die Daten mit Cloud Dataprep für die Analyse vorbereitet, sie in BigQuery lädt und es Ihrem Team ermöglicht, visuelle Analysen mit Tools wie Looker oder Data Studio durchzuführen.

Umfang

In diesem Codelab verwenden Sie Dataprep, um Antworten aus unserer Beispielumfrage in Google Forms in ein Format umzuwandeln, das für die Datenanalyse geeignet ist. Sie schieben die transformierten Daten in BigQuery, wo Sie mit SQL detailliertere Fragen stellen und sie mit anderen Datensätzen zusammenführen können, um leistungsstärkere Analysen durchzuführen. Am Ende können Sie vordefinierte Dashboards ansehen oder Ihr eigenes Business-Intelligence-Tool mit BigQuery verbinden, um neue Berichte zu erstellen.

Aufgaben in diesem Lab

• Umfragedaten mit Dataprep transformieren
• Umfragedaten in BigQuery pushen
• Mehr Erkenntnisse aus Umfragedaten gewinnen

Voraussetzungen

• Ein Google Cloud-Projekt mit aktivierter Abrechnung, BigQuery und Dataprep
• Grundkenntnisse in Dataprep sind hilfreich, aber nicht erforderlich.
• Grundkenntnisse in BigQuery und SQL sind hilfreich, aber nicht erforderlich.

2. Google-Formularantworten verwalten

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

Umfrageergebnisse können auf dem Tab „Antworten“ exportiert werden. Klicken Sie dazu auf das Google Tabellen-Symbol und erstellen Sie eine neue Tabelle oder laden Sie die Ergebnisse in eine vorhandene Tabelle. Über Google Formulare werden der Tabelle so lange Antworten hinzugefügt, wie Teilnehmer ihre Antworten einreichen, bis Sie die Auswahl der Schaltfläche „Antworten akzeptieren“ aufheben.

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

3. Umfrageantworten transformieren

Umfragefragen können in vier Familien gruppiert werden, die jeweils ein bestimmtes Exportformat haben. Je nach Art der Frage müssen Sie die Daten auf eine bestimmte Weise neu strukturieren. 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-Liste, lineare Skala usw.

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

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

• Fragename: Spaltenname
• Antwort: Liste von Werten mit Semikolon als Trennzeichen (z.B. „Antwort 1; Antwort 4; Antwort 6“)
• Anforderungen an die Transformation: Die Liste der Werte muss extrahiert und pivotiert werden, damit jede Antwort eine neue Zeile wird.

Multiple-Choice-Fragen mit Raster

Hier ist ein Beispiel für eine Multiple-Choice-Frage. Sie müssen für jede Zeile einen einzelnen Wert auswählen.

• Fragenname: Jede einzelne Frage wird zu einem Spaltennamen mit dem Format „Frage [Option]“.
• Antwort: Jede einzelne Antwort im Raster wird zu einer Spalte mit einem eindeutigen Wert.
• Anforderungen an die Transformation: Jede Frage/Antwort muss zu einer neuen Zeile in der Tabelle werden und in zwei Spalten unterteilt werden. Eine Spalte mit der Option der Frage und die andere Spalte mit der Antwort.

Multiple-Choice-Fragen mit Kästchenraster

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

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

Zuerst muss die Werteliste jeder Antwort extrahiert und pivotiert werden, damit jede Antwort eine neue Zeile für die jeweilige Frage bildet.

Zweitens: Jede einzelne Antwort muss eine neue Zeile in der Tabelle werden und in zwei Spalten unterteilt werden. Eine Spalte mit der Frageoption und die andere Spalte mit der Antwort.

Als Nächstes sehen wir uns an, wie diese Transformationen mit Cloud Dataprep verarbeitet werden.

4. Cloud Dataprep-Workflow erstellen

„Google Forms Analytics Design Pattern“ in Cloud Dataprep importieren

Laden Sie das Flusspaket für das Analytics-Designmuster für Google Formulare herunter (ohne es zu entpacken). Klicken Sie in der Cloud Dataprep-Anwendung in der linken Navigationsleiste auf das Symbol „Abläufe“. Wählen Sie dann auf der Seite „Abläufe“ im Kontextmenü die Option „Importieren“ aus.

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

Tabelle mit Umfrageergebnissen in Google Tabellen verknüpfen

Auf der linken Seite des Ablaufs muss die Datenquelle wieder mit einer Google Tabellen-Datei verbunden werden, die die Google-Formularergebnisse enthält. Klicken Sie mit der rechten Maustaste auf das Google-Tabellen-Dataset-Objekt und wählen Sie „Ersetzen“ aus.

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

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

Klicken Sie auf „Go“ (Los) und dann rechts unten auf „Import & Add to Flow“ (Importieren und zu Ablauf hinzufügen). Klicken Sie im Modalfenster rechts unten auf die Schaltfläche „Ersetzen“.

BigQuery-Tabellen verbinden

Auf der rechten Seite des Flussdiagramms müssen Sie die Ausgaben mit Ihrer eigenen BigQuery-Instanz verknüpfen. Klicken Sie bei jeder Ausgabe auf das Symbol und bearbeiten Sie die Eigenschaften wie unten beschrieben.

Bearbeiten Sie zuerst die „Manuellen Ziele“.

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

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

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

Nachdem Sie die manuellen Ziele bearbeitet haben, gehen Sie genauso vor, um die Ausgabe für die geplanten Ziele zu bearbeiten.

Mit manuellen Zielen können Sie jeden einzelnen Ausgabejob unabhängig und bei Bedarf ausführen. So können Sie ein Rezept für die Datenvorbereitung testen und ein Ergebnis während der Erstellung des Ablaufs validieren.

Geplante Ziele werden verwendet, wenn Sie Ihren Ablauf regelmäßig ausführen möchten. So können Sie beispielsweise die Google Forms-Ergebnisse jeden Tag in BigQuery neu laden, damit Ihre Berichte mit den neuesten Antworten aktualisiert werden.

Wiederholen Sie die Schritte für jede Ausgabe. Sie müssen insgesamt 8 Ziele bearbeiten.

5. Cloud Dataprep-Workflow

Die Grundidee des „Google Forms Analytics Design Pattern“-Workflows besteht darin, die Transformationen wie oben beschrieben auf die Umfrageantworten anzuwenden. Dazu wird jede Fragekategorie in ein bestimmtes Cloud Dataprep-Transformationsrezept unterteilt.

Bei diesem Ablauf werden die Fragen in vier Tabellen unterteilt, die den vier Fragekategorien entsprechen (der Einfachheit halber).

Wir empfehlen, die einzelnen Rezepte nacheinander zu durchgehen, 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 eine Vorschau des Vorher/Nachher-Zustands einer bestimmten Spalte anzeigen.

6. Cloud Dataprep-Ablauf ausführen

Nachdem Sie die Quelle und die Ziele richtig konfiguriert haben, 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 die Schaltfläche „Ausführen“. Wenn die angegebene BigQuery-Tabelle vorhanden ist, werden in Dataprep neue Zeilen angehängt. Andernfalls wird eine neue Tabelle erstellt.

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

Wenn alle Jobs abgeschlossen sind, werden die Umfrageergebnisse in BigQuery in einem bereinigten, strukturierten und normalisierten Format geladen, das für die Analyse bereit ist.

7. Umfragedaten in BigQuery analysieren

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

Mit den Umfragedaten in BigQuery können Sie ganz einfach umfassendere Fragen stellen, um die Umfrageantworten genauer zu analysieren. Angenommen, Sie möchten wissen, welche Programmiersprache von Personen mit verschiedenen Berufsbezeichnungen am häufigsten verwendet wird. Sie könnten dann eine Suchanfrage wie diese stellen:

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 leistungsfähiger zu machen, können Sie die Umfrageantworten mit CRM-Daten zusammenführen, um zu sehen, ob Teilnehmer zu Konten gehören, die bereits in Ihrem Data Warehouse enthalten sind. So können Sie fundiertere Entscheidungen zum Kundensupport oder zur Ausrichtung auf Nutzer für neue Produkte treffen.

Hier zeigen wir, wie Sie die Umfragedaten basierend auf der Domain des Teilnehmers und der Website des Kontos mit einer Kontotabelle zusammenführen. Sie können jetzt die Verteilung der Antworten nach Kontotyp sehen, um zu erfahren, wie viele Teilnehmer zu bestehenden Kundenkonten gehören.

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

Hinweis:Wenn Sie diese Abfrage selbst ausführen möchten, können Sie unsere Beispieltabelle für Salesforce-Konten verwenden und sie anhand dieser Anleitung in BigQuery einfügen.

8. Visuelle Analysen durchführen

Da Ihre Umfragedaten jetzt 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 Looker erstellt.

Looker

Wenn Sie bereits eine Looker-Instanz haben, können Sie die LookML in diesem Ordner verwenden, um die Umfragedaten und CRM-Daten der Stichprobe auf dieses Muster zu analysieren. Erstellen Sie einfach ein neues Looker-Projekt, fügen Sie die LookML hinzu und ersetzen Sie die Verbindungs- und Tabellennamen in der Datei so, dass sie Ihrer BigQuery-Konfiguration entsprechen. Wenn Sie keine Looker-Instanz haben, aber mehr darüber erfahren möchten, können Sie hier eine Demo vereinbaren.

Data Studio

Alternativ können Sie einen Bericht in Data Studio erstellen, indem Sie auf den Frame mit dem Google-Kreuz „Leerer Bericht“ klicken und eine Verbindung zu BigQuery herstellen. Folgen Sie der Anleitung in Data Studio. Weitere Informationen finden Sie in dieser Kurzanleitung und in der Einführung in die wichtigsten Funktionen von Data Studio. Hier finden Sie auch unsere vorgefertigten Data Studio-Dashboards.

9. Bereinigen

Am einfachsten können Sie die Abrechnung deaktivieren, wenn Sie das Cloud-Projekt löschen, das Sie für die Anleitung erstellt haben. Alternativ können Sie die einzelnen Ressourcen löschen.

Achtung: Das Löschen von Projekten hat folgende Auswirkungen:

• Alle Inhalte des Projekts werden gelöscht. Wenn Sie für diese Anleitung ein bereits bestehendes Projekt verwendet haben, werden auch alle anderen im Rahmen des Projekts erstellten Daten gelöscht.
• Benutzerdefinierte Projekt-IDs gehen verloren. Beim Erstellen dieses Projekts haben Sie möglicherweise eine benutzerdefinierte Projekt-ID erstellt, die Sie weiterhin verwenden möchten. Damit die URLs, die die Projekt-ID verwenden, z. B. eine appspot.com-URL, erhalten bleiben, sollten Sie ausgewählte Ressourcen innerhalb des Projekts löschen, statt das gesamte Projekt.

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.