Umfrageantworten aus Google Formulare umwandeln und in BigQuery laden

1. Einführung

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

Was Sie erstellen

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

Lerninhalte

• Umfragedaten mit Dataprep transformieren
• Umfragedaten in BigQuery übertragen
• 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 Formulare-Antworten verwalten

Zuerst sehen wir uns die Antworten auf unsere Beispielumfrage in Google Formulare genauer an.

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. Google Formulare fügt der Tabelle weiterhin Antworten hinzu, sobald Teilnehmer ihre Antworten senden, bis Sie die Schaltfläche „Antworten annehmen“ deaktivieren.

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

3. Umfrageantworten transformieren

Umfragefragen können in vier Gruppen unterteilt werden, die ein bestimmtes Exportformat haben. Je nach Fragetyp müssen Sie die Daten auf bestimmte Weise umstrukturieren. Hier sehen wir uns die einzelnen Gruppen und die Transformationen an, die wir anwenden müssen.

Fragen mit einer einzigen Antwortmöglichkeit: Kurzantwort, Absatz, Drop-down, lineare Skala usw.

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

Fragen mit mehreren Antwortmöglichkeiten: Multiple-Choice-Frage, Kästchen

• Fragenname: Spaltenname
• Antwort: Liste der Werte mit Semikolon als Trennzeichen (z.B. "Antwort 1; Antwort 4; Antwort 6")
• Transformationsanforderungen: Die Liste der Werte muss extrahiert und gedreht werden, sodass jede Antwort zu einer neuen Zeile wird.

Fragen mit Multiple-Choice-Raster

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

• Fragenname: 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 zu einer neuen Zeile in der Tabelle werden und in zwei Spalten aufgeteilt werden. Eine Spalte enthält die Option der Frage und die andere Spalte die Antwort.

Fragen mit Multiple-Choice-Raster und Kästchen

Hier ist ein Beispiel für ein Kästchenraster. In jeder Zeile können keine bis mehrere Werte ausgewählt werden.

• Fragenname: 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 verarbeitet werden.

Zuerst muss die Liste der Werte für jede Antwort extrahiert und gedreht werden, sodass jede Antwort zu einer neuen Zeile für die jeweilige Frage wird.

Zweitens muss jede einzelne Antwort zu einer neuen Zeile in der Tabelle werden und in zwei Spalten aufgeteilt werden. Eine Spalte enthält die Option der Frage und die andere Spalte die Antwort.

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

4. Cloud Dataprep-Ablauf erstellen

Designmuster für Google Formulare-Analysen in Cloud Dataprep importieren

Laden Sie das Ablaufpaket für das Designmuster für Google Formulare-Analysen 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. Ihr Bildschirm sollte so aussehen:

Google Sheets-Tabelle mit Umfrageergebnissen verbinden

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

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

Ersetzen Sie den aktuellen Wert durch diesen Link, der auf eine Google Sheets-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 rechts unten auf „Weiter“ und dann auf „Importieren und zu Ablauf hinzufügen“. Wenn Sie wieder im Modal sind, klicken Sie rechts unten auf die Schaltfläche „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 dann die Eigenschaften wie folgt.

Bearbeiten Sie zuerst die „Manuelle Ziele“.

Klicken Sie im 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 zugehörigen Eigenschaften.

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

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

Mit manuellen Zielen können Sie jeden einzelnen Ausgabefjob unabhängig und bei Bedarf ausführen. Das ist praktisch, um ein Rezept zur Datenvorbereitung zu testen und ein Ergebnis während des Entwurfs Ihres Ablaufs zu validieren.

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

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

5. Cloud Dataprep-Ablauf

Die Grundidee des Ablaufs „Designmuster für Google Formulare-Analysen“ besteht darin, die Transformationen für die Umfrageantworten wie zuvor beschrieben durchzuführen. Dazu wird jede Fragenkategorie in ein bestimmtes Cloud Dataprep-Rezept für die Datentransformation aufgeteilt.

In diesem Ablauf werden die Fragen in vier Tabellen aufgeteilt (entsprechend den vier Fragenkategorien, um es einfach zu halten).

Wir empfehlen, die einzelnen Rezepte nacheinander zu verwenden, beginnend mit „Clean Headers“ und dann „SingleChoiceSELECT-Questions“, gefolgt von den anderen Rezepten.

Alle Rezepte sind kommentiert, um die verschiedenen Transformationsschritte zu erklären. In einem Rezept können Sie einen Schritt bearbeiten und eine Vorschau des Zustands einer bestimmten Spalte vor und nach der Transformation ansehen.

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 jede Ausgabe aus und klicken Sie auf die Schaltfläche „Ausführen“. Wenn die angegebene BigQuery-Tabelle vorhanden ist, fügt Dataprep neue Zeilen hinzu. Andernfalls wird eine neue Tabelle erstellt.

Klicken Sie links auf das Symbol „Jobverlauf“, um die Jobs zu beobachten. Es dauert einige Minuten, bis die BigQuery-Tabellen verarbeitet und geladen werden.

Wenn alle Jobs abgeschlossen sind, werden die Umfrageergebnisse in einem sauberen, strukturierten und normalisierten Format in BigQuery 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 besser zu verstehen. Angenommen, Sie möchten herausfinden, welche Programmiersprache am häufigsten von Personen mit unterschiedlichen Berufsbezeichnungen 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 gestalten, können Sie die Umfrageantworten mit CRM-Daten verknüpfen, um zu sehen, ob Teilnehmer Konten zugeordnet sind, die bereits in Ihrem Data Warehouse enthalten sind. So können Sie fundiertere Entscheidungen zum Kundensupport treffen oder Nutzer für neue Produkte ansprechen.

Hier zeigen wir, wie Sie die Umfragedaten anhand der Domain des Antwortenden und der Website des Kontos mit einer Kontotabelle verknüpfen können. Jetzt können Sie die Verteilung der Antworten nach Kontotyp sehen. So erfahren Sie, wie viele Antwortende 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 Beispiel-Salesforce-Kontotabelle verwenden und sie mit dieser Anleitung in BigQuery hinzufügen.

8. Visuelle Analysen durchführen

Nachdem Ihre Umfragedaten in einem Data Warehouse zentralisiert wurden, 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 mit dem LookML in diesem Ordner die Beispielumfrage- und CRM-Daten für dieses Muster analysieren. Erstellen Sie einfach ein neues Looker-Projekt, fügen Sie das 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.

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 der Anleitung in Data Studio. Weitere Informationen finden Sie in der Kurzanleitung und in der Einführung in die Hauptfunktionen von Data Studio hier. 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 haben Sie die Möglichkeit, die einzelnen Ressourcen zu 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. Wechseln Sie in der Cloud Console zu „Ressourcen verwalten“.
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.