Transformer et charger les réponses à une enquête Google Forms dans BigQuery

1. Introduction

Il existe de nombreuses raisons de réaliser des enquêtes : évaluer la satisfaction client, réaliser des études de marché, améliorer un produit ou un service, ou évaluer l'engagement des employés. Toutefois, si vous avez déjà essayé de travailler avec des données d'enquête, vous savez probablement que le format standard est difficile à utiliser. Dans ce guide, nous allons créer un pipeline automatisé qui capture les résultats de Google Forms, prépare les données pour l'analyse avec Cloud Dataprep, les charge dans BigQuery et permet à votre équipe d'effectuer des analyses visuelles à l'aide d'outils tels que Looker ou Data Studio.

Objectifs de l'atelier

Dans cet atelier de programmation, vous allez utiliser Dataprep pour transformer les réponses de notre exemple d'enquête Google Forms dans un format utile pour l'analyse de données. Vous allez transférer les données transformées dans BigQuery, où vous pourrez poser des questions plus approfondies avec SQL et les joindre à d'autres ensembles de données pour des analyses plus poussées. À la fin, vous pourrez explorer des tableaux de bord prédéfinis ou connecter votre propre outil d'informatique décisionnelle à BigQuery pour créer des rapports.

Points abordés

  • Transformer des données d'enquête à l'aide de Dataprep
  • Transférer des données d'enquête dans BigQuery
  • Comment obtenir plus d'insights à partir des données d'enquête

Prérequis

2. Gérer les réponses Google Forms

Nous allons commencer par examiner de plus près les réponses Google Forms à notre exemple d'enquête.

f3d25efd2cc923f5.png

Vous pouvez exporter les résultats de l'enquête depuis l'onglet "Réponses" en cliquant sur l'icône Google Sheets, puis en créant une feuille de calcul ou en chargeant les résultats dans une feuille de calcul existante. Google Forms continuera d'ajouter les réponses à la feuille de calcul au fur et à mesure que les personnes interrogées les enverront, jusqu'à ce que vous désactiviez le bouton "Accepter les réponses".

d499e5a4dccdf5fd.png4939332a5d8f9f19.png

Examinons maintenant chaque type de réponse et la façon dont il est traduit dans le fichier Google Sheets.

3. Réponses à l'enquête dans le groupe de transformation

Les questions de l'enquête peuvent être regroupées en quatre familles qui auront un format d'exportation particulier. En fonction du type de question, vous devrez restructurer les données d'une certaine manière. Nous allons examiner chacun des groupes et les types de transformations que nous devons appliquer.

Questions à choix unique : réponse courte, paragraphe, menu déroulant, échelle linéaire, etc.

  • Nom de la question : nom de la colonne
  • Réponse : valeur de la cellule
  • Exigences de transformation : aucune transformation n'est nécessaire. La réponse est chargée telle quelle.

3eeedc50b0fd54fd.png

Questions à choix multiples : choix multiples, case à cocher

  • Nom de la question : nom de la colonne
  • Réponse : liste de valeurs séparées par un point-virgule (par exemple, "Réponse 1 ; Réponse 4 ; Réponse 6")
  • Exigences de transformation : la liste des valeurs doit être extraite et pivotée, de sorte que chaque réponse devienne une nouvelle ligne.

cab8a38a96a13ce4.png

Questions de type "Grille à choix multiples"

Voici un exemple de question à choix multiples. Vous devez sélectionner une seule valeur pour chaque ligne.

c6ea3d47d4dd5e78.png

  • Nom de la question : chaque question devient un nom de colonne au format "Question [Option]".
  • Réponse : chaque réponse individuelle de la grille devient une colonne avec une valeur unique.
  • Exigences de transformation : chaque question/réponse doit devenir une nouvelle ligne du tableau et être divisée en deux colonnes. Une colonne mentionnant l'option de la question et l'autre colonne avec la réponse.

9223d0271516c58d.png

Questions de type "Grille de cases à cocher"

Voici un exemple de grille de cases à cocher. Vous pouvez sélectionner aucune ou plusieurs valeurs pour chaque ligne.

4e3189b8cc2d4a8b.png

  • Nom de la question : chaque question devient un nom de colonne au format "Question [Option]".
  • Réponse : chaque réponse individuelle de la grille devient une colonne avec une liste de valeurs séparées par un point-virgule.
  • Exigences de transformation : ces types de questions combinent les catégories "Case à cocher" et "Grille à choix multiples", et doivent être résolus dans cet ordre.

Tout d'abord, la liste des valeurs de chaque réponse doit être extraite et pivotée, de sorte que chaque réponse devienne une nouvelle ligne pour la question concernée.

Deuxièmement, chaque réponse individuelle doit devenir une nouvelle ligne dans le tableau et être divisée en deux colonnes. Une colonne mentionnant l'option de la question et l'autre colonne avec la réponse.

3c3c2bd098e03003.png

Nous allons maintenant voir comment ces transformations sont gérées avec Cloud Dataprep.

4. Créer le flux Cloud Dataprep

Importer le modèle de conception "Google Forms Analytics" dans Cloud Dataprep

Téléchargez le package de flux Google Forms Analytics Design Pattern (sans le décompresser). Dans l'application Cloud Dataprep, cliquez sur l'icône "Flux" dans la barre de navigation de gauche. Ensuite, sur la page "Flux", sélectionnez "Importer" dans le menu contextuel.

ba7c0cb0eec398df.png

Une fois le flux importé, sélectionnez-le pour le modifier. Votre écran devrait se présenter comme suit :

44978861eb34ec71.png

Associer la feuille de calcul Google Sheets contenant les résultats de l'enquête

Sur le côté gauche du flux, la source de données doit être reconnectée à une feuille de calcul Google Sheets contenant les résultats de Google Forms. Effectuez un clic droit sur l'objet de l'ensemble de données Google Sheets, puis sélectionnez "Remplacer".

55c16f0c04366f0c.png

Cliquez ensuite sur le lien "Import Datasets" (Importer des ensembles de données) en bas de la fenêtre modale. Cliquez sur l'icône en forme de crayon "Modifier le chemin d'accès".

8afeef260c96277f.png

Ensuite, remplacez la valeur actuelle par ce lien qui pointe vers une feuille de calcul Google Sheets contenant des résultats Google Forms. Vous pouvez utiliser notre exemple ou votre propre copie : https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing

Cliquez sur "Go" (OK), puis sur "Import & Add to Flow" (Importer et ajouter au flux) en bas à droite. Lorsque vous êtes de retour dans la boîte de dialogue, cliquez sur le bouton "Remplacer" en bas à droite.

Connecter des tables BigQuery

Sur le côté droit du flux, vous devez connecter les sorties à votre propre instance BigQuery. Pour chaque sortie, cliquez sur l'icône, puis modifiez ses propriétés comme suit.

Commencez par modifier les "destinations manuelles".

a3fc2cb80153ec25.png

Sur l'écran "Paramètres de publication" qui s'affiche, cliquez sur le bouton de modification.

85791e6162a370de.png

Lorsque l'écran "Action de publication" s'affiche, vous devez modifier les paramètres de connexion en cliquant sur la connexion BigQuery et en modifiant ses propriétés.

1f3e4887baaeaffd.png

Sélectionnez l'ensemble de données BigQuery dans lequel vous souhaitez charger les résultats Google Forms. Vous pouvez sélectionner "default" si vous n'avez pas encore créé d'ensemble de données BigQuery.

f4eaa05ecf9de162.png

Après avoir modifié les "Destinations manuelles", procédez de la même manière pour la sortie "Destinations planifiées".

46edea1b8ca63270.png

Répétez l'opération pour chaque sortie en suivant les mêmes étapes. Vous devez modifier huit destinations au total.

5. Explication du flux Cloud Dataprep

L'idée de base du flux "Modèle de conception d'analyse Google Forms" est d'effectuer les transformations sur les réponses à l'enquête comme décrit précédemment, en décomposant chaque catégorie de questions en une recette de transformation de données Cloud Dataprep spécifique.

Ce flux divise les questions en quatre tables (correspondant aux quatre catégories de questions, pour plus de simplicité).

afa421849b1bd398.png

Nous vous suggérons d'explorer chacune des recettes une par une, en commençant par "Clean Headers", puis "SingleChoiceSELECT-Questions", puis chacune des autres recettes ci-dessous.

Toutes les recettes sont commentées pour expliquer les différentes étapes de transformation. Lorsque vous êtes dans une recette, vous pouvez modifier une étape et prévisualiser l'état avant/après d'une colonne spécifique.

449da06d96cd520e.png4ac6e14f578d0707.png

6. Exécuter le flux Cloud Dataprep

Maintenant que votre source et vos destinations sont correctement configurées, vous pouvez exécuter le flux pour transformer et charger les réponses dans BigQuery. Sélectionnez chaque résultat, puis cliquez sur le bouton "Exécuter". Si la table BigQuery spécifiée existe, Dataprep ajoutera de nouvelles lignes. Sinon, il créera une table.

47cf50f6d17a5b1e.png

Cliquez sur l'icône "Historique des jobs" dans le volet de gauche pour surveiller les jobs. Le chargement des tables BigQuery devrait prendre quelques minutes.

afc79eeb27202fb4.png

Une fois toutes les tâches terminées, les résultats de l'enquête seront chargés dans BigQuery dans un format propre, structuré et normalisé, prêt pour l'analyse.

7. Analyser les données de l'enquête dans BigQuery

Dans la console Google pour BigQuery, vous devriez pouvoir voir les détails de chacune des nouvelles tables.

df370873572511ac.png

Une fois les données de l'enquête dans BigQuery, vous pouvez facilement poser des questions plus complètes pour mieux comprendre les réponses. Par exemple, imaginons que vous essayez de comprendre quel langage de programmation est le plus couramment utilisé par des personnes ayant des intitulés de poste différents. Vous pouvez écrire une requête comme celle-ci :

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

Pour rendre vos analyses encore plus efficaces, vous pouvez joindre les réponses à l'enquête aux données CRM afin de voir si les participants correspondent à des comptes déjà inclus dans votre entrepôt de données. Cela peut aider votre entreprise à prendre des décisions plus éclairées concernant le service client ou le ciblage des utilisateurs pour les nouveaux lancements.

Ici, nous montrons comment joindre les données de l'enquête à une table de comptes en fonction du domaine du répondant et du site Web du compte. Vous pouvez maintenant voir la répartition des réponses par type de compte, ce qui vous aide à comprendre combien de répondants appartiennent à des comptes client existants.

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. Effectuer une analyse visuelle

Maintenant que les données de votre enquête sont centralisées dans un entrepôt de données, vous pouvez facilement les analyser dans un outil d'informatique décisionnelle. Nous avons créé des exemples de rapports dans Data Studio et dans Looker.

Looker

Si vous disposez déjà d'une instance Looker, vous pouvez utiliser le code LookML de ce dossier pour commencer à analyser l'exemple d'enquête et les données CRM pour ce modèle. Il vous suffit de créer un projet Looker, d'ajouter le code LookML et de remplacer les noms de connexion et de table dans le fichier pour qu'ils correspondent à votre configuration BigQuery. Si vous n'avez pas d'instance Looker, mais que vous souhaitez en savoir plus, vous pouvez planifier une démonstration ici.

129db05d6f85f484.png

Data Studio

Vous pouvez également créer un rapport dans Data Studio en cliquant sur le cadre "Rapport vide" avec la croix Google, puis en vous connectant à BigQuery. Suivez toutes les instructions de Data Studio. Pour en savoir plus, consultez ce guide de démarrage rapide et cette présentation des principales fonctionnalités de Data Studio. Vous trouverez également nos tableaux de bord Data Studio prédéfinis ici.

5e744869e3fe3f8f.png

9. Nettoyage

Le moyen le plus simple d'éviter la facturation consiste à supprimer le projet Cloud que vous avez créé pour le tutoriel. Vous pouvez également supprimer les différentes ressources.

  1. Dans la console Cloud, accédez à la page "Gérer les ressources".
  2. Dans la liste des projets, sélectionnez le projet que vous souhaitez supprimer, puis cliquez sur Supprimer.
  3. Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.