Umfrageantworten aus Google Formulare umwandeln und in BigQuery laden

1. Einführung

Es gibt viele Gründe für das Durchführen von Umfragen: die Kundenzufriedenheit bewerten, Marktforschung betreiben, ein Produkt oder eine Dienstleistung verbessern oder die Bindung von Mitarbeitenden bewerten. Wenn Sie jedoch bereits versucht haben, mit Umfragedaten zu arbeiten, wissen Sie wahrscheinlich, dass das Standardformat schwierig zu arbeiten ist. In diesem Leitfaden wird eine automatisierte Pipeline erstellt, die Ergebnisse aus Google Formulare erfasst, die Daten für die Analyse mit Cloud Dataprep vorbereitet, in BigQuery lädt und Ihrem Team ermöglicht, visuelle Analysen mit Tools wie Looker oder Data Studio durchzuführen.

Inhalt

In diesem Codelab wandeln Sie die Antworten aus unserer Beispielumfrage in Google Formulare mithilfe von Dataprep in ein Format um, das für Datenanalysen geeignet ist. Sie übertragen die transformierten Daten in BigQuery, wo Sie mit SQL tiefergehende Fragen stellen und sie mit anderen Datasets verknüpfen können, um leistungsstärkere Analysen zu erhalten. Am Ende können Sie sich 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
• Wie Sie Umfragedaten in BigQuery übertragen
• Wie Sie aus Umfragedaten mehr Informationen 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

Sehen wir uns zuerst die Antworten aus Google Formulare in unserer Beispielumfrage genauer an.

Umfrageergebnisse können aus den „Antworten“ exportiert werden indem Sie auf das Symbol für Google Tabellen klicken und eine neue Tabelle erstellen oder die Ergebnisse in eine vorhandene Tabelle laden. Google Formulare fügt der Tabelle weiterhin Antworten hinzu, bis Sie die Option "Antworten möglich" deaktivieren, während die Teilnehmer ihre Antworten senden. Schaltfläche.

Sehen wir uns nun die einzelnen Antworttypen und deren Übersetzung in der Google Tabellen-Datei an.

3. Umfrageantworten transformieren

Die Fragen können in vier Gruppen mit einem bestimmten Exportformat unterteilt werden. 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 anzuwendenden Transformationen an.

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

• Fragename: Spaltenname
• Antwort: Zellenwert
• Transformationsanforderungen: Keine Transformation erforderlich. wird die Antwort unverändert geladen.

Multiple-Choice-Fragen: Mehrfachauswahl, Kästchen

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

Fragen für das Multiple-Choice-Raster

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

• Name der Frage: 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 sein. In einer Spalte wird die option der Frage erwähnt, in der anderen mit der Antwort.

Kästchenraster-Fragen für Multiple-Choice-Frage

Hier ist ein Beispiel für ein Kästchenraster. Aus jeder Zeile kann keiner oder mehrere Werte ausgewählt werden.

• Name der Frage: Jede einzelne Frage wird zu einem Spaltennamen im Format „Frage [Option]“.
• Antwort: Jede einzelne Antwort im Raster wird zu einer Spalte, in der eine Liste von Werten durch Semikolons getrennt ist.
• Transformationsanforderungen: Bei diesen Fragetypen wird das Kästchen „Kästchen“ kombiniert. und das Multiple-Choice-Raster und müssen in dieser Reihenfolge geklärt werden.

Zunächst muss die Werteliste jeder Antwort extrahiert und neu ausgerichtet werden, damit 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. In einer Spalte wird die option der Frage erwähnt, in der anderen die Antwort.

Als Nächstes erfahren Sie, wie diese Transformationen mit Cloud Dataprep gehandhabt werden.

4. Cloud Dataprep-Ablauf erstellen

Das „Google Formulare Analytics Design Pattern“ importieren in Cloud Dataprep

Laden Sie das Flusspaket für Google Formulare: Analytics-Designmuster herunter, ohne es zu entpacken. Klicken Sie in der Cloud Dataprep-Anwendung in der linken Navigationsleiste auf das Ablaufsymbol. 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 dann so aussehen:

Tabelle mit Umfrageergebnissen bei Google Tabellen verbinden

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

Klicken Sie dann auf „Import Datasets“ (Datasets importieren) unten im Dialogfenster. Klicken Sie auf „Pfad bearbeiten“ Stift.

Ersetzen Sie dort den aktuellen Wert durch diesen Link, der auf eine Google-Tabelle mit einigen Google-Formularergebnissen 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 geht's!". und dann auf „Importieren und Zu Ablauf hinzufügen“ . Wenn Sie sich wieder im Dialogfenster befinden, klicken Sie auf „Ersetzen“. auf die Schaltfläche.

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.

Beginnen Sie mit der Bearbeitung der manuellen Ziele

Im Abschnitt „Veröffentlichungseinstellungen“ klicken Sie auf die Schaltfläche „Bearbeiten“,

Wenn Sie die Veröffentlichungsaktion müssen Sie die Verbindungseinstellungen ändern, indem Sie auf die BigQuery-Verbindung klicken und ihre Eigenschaften bearbeiten.

Wählen Sie das BigQuery-Dataset aus, in das die Google Formulare-Ergebnisse geladen werden sollen. Du kannst „Standard“ auswählen wenn Sie noch kein BigQuery-Dataset erstellt haben.

Nachdem Sie „Manuelle Ziele“ bearbeitet haben, fahren Sie unter „Geplante Ziele“ genauso fort. .

Mit manuellen Zielen können Sie jeden einzelnen Ausgabejob unabhängig und bei Bedarf ausführen. Dies ist praktisch, um ein Datenvorbereitungsschema zu testen und ein Ergebnis während der Gestaltung des Ablaufs zu validieren.

Geplante Ziele werden verwendet, wenn Sie Ihren Ablauf routinemäßig ausführen möchten. Beispielsweise können Sie die Ergebnisse aus Google Formulare täglich in BigQuery laden, um Ihre Berichte mit den neuesten Antworten zu aktualisieren.

Iterieren Sie jede Ausgabe mit denselben Schritten. Sie müssen insgesamt 8 Ziele bearbeiten.

5. Ablauf in Cloud Dataprep

Die Grundidee des "Google Formulare Analytics-Designmusters" die Transformationen der Umfrageantworten wie zuvor beschrieben durchzuführen, indem jede Fragekategorie in ein bestimmtes Cloud Dataprep-Datentransformationsschema unterteilt wird.

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

Wir empfehlen, jedes der Rezepte nacheinander durchzugehen, beginnend mit „Clean Headers“. und dann auf „SingleChoiceSELECT-Questions“. gefolgt von jeweils anderen Rezepten.

Alle Schemas sind kommentiert, um die verschiedenen Transformationsschritte zu erklären. In einem Schema können Sie einen Schritt bearbeiten und eine Vorschau des Vorher-Nachher-Zustands einer bestimmten Spalte anzeigen.

6. Cloud Dataprep-Ablauf ausführen

Da die Quelle und die Ziele nun ordnungsgemäß konfiguriert sind, 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 „Ausführen“. Schaltfläche. Wenn die angegebene BigQuery-Tabelle vorhanden ist, hängt Dataprep neue Zeilen an. Andernfalls wird eine neue Tabelle erstellt.

Klicken Sie auf den Jobverlauf klicken, um die Jobs zu überwachen. Es sollte einige Minuten dauern, bis die BigQuery-Tabellen geladen werden.

Wenn alle Jobs abgeschlossen sind, werden die Umfrageergebnisse in BigQuery in einem sauberen, 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 zu jeder der neuen Tabellen sehen können.

Mit den Umfragedaten in BigQuery können Sie ganz einfach umfassendere Fragen stellen, um die Umfrageantworten auf einer tieferen Ebene nachzuvollziehen. Angenommen, Sie möchten herausfinden, welche Programmiersprache am häufigsten von Personen mit unterschiedlichen Berufsbezeichnungen verwendet wird. Sie können eine Abfrage wie folgt 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 leistungsfähiger zu machen, können Sie die Umfrageantworten mit CRM-Daten zusammenführen, um festzustellen, ob die Teilnehmenden Konten zugeordnet sind, die bereits in Ihrem Data Warehouse enthalten sind. So kann Ihr Unternehmen fundiertere Entscheidungen in Bezug auf den Kundensupport oder die Ausrichtung auf Nutzer für Neuveröffentlichungen treffen.

Hier sehen Sie, wie Sie die Umfragedaten in einer Kontotabelle zusammenführen können. Diese basiert auf der Domain des Teilnehmers und der Kontowebsite. Jetzt sehen Sie die Verteilung der Antworten nach Kontotyp. So können Sie besser nachvollziehen, wie viele Teilnehmer einem bestehenden Kundenkonto angehö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 für sich selbst ausführen möchten, können Sie unsere Salesforce-Beispieltabelle verwenden und sie mithilfe dieser Anleitung in BigQuery hinzufügen.

8. Visuelle Analysen durchführen

Da Ihre Umfragedaten jetzt zentral in einem Data Warehouse gespeichert sind, können Sie die Daten 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 den LookML-Code 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 erfahren möchten, können Sie hier einen Termin für eine Demo vereinbaren.

Data Studio

Wenn Sie einen Bericht in Data Studio erstellen möchten, klicken Sie auf den Frame mit dem Google-Kreuz „Leerer Bericht“. und eine Verbindung zu BigQuery herstellen. Folgen Sie der Anleitung von Data Studio. Hier finden Sie eine Kurzanleitung und eine Einführung in die wichtigsten Funktionen von Data Studio. Hier finden Sie auch unsere vordefinierten Data Studio-Dashboards.

9. Bereinigen

Am einfachsten vermeiden Sie weitere Kosten, 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, anstatt das gesamte Projekt 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.