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

1. Introduction

Il existe de nombreuses raisons de mener des enquêtes : évaluer la satisfaction des clients, 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 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 des 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 puissantes. À la fin, vous pouvez explorer les 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 les données d'enquête à l'aide de Dataprep
• Transférer des données d'enquête dans BigQuery
• Obtenir plus d'insights à partir des données d'enquête

Prérequis

• Un projet Google Cloud avec la facturation, BigQuery et Dataprep activés
• Avoir des connaissances de base sur Dataprep est utile, mais pas obligatoire
• Des connaissances de base sur BigQuery et SQL sont utiles, mais pas obligatoires.

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.

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 à mesure que les personnes interrogées les enverront, jusqu'à ce que vous désélectionniez le bouton "Accepter les réponses".

Examinons maintenant chaque type de réponse et comment il est traduit dans le fichier Google Sheets.

3. Transformer les réponses à l'enquête

Les questions de l'enquête peuvent être regroupées en quatre familles, qui auront un format d'exportation spécifique. En fonction du type de question, vous devrez restructurer les données d'une certaine manière. Nous allons maintenant 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.

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, "Resp 1; Resp 4; Resp 6")
• Exigences de transformation : la liste des valeurs doit être extraite et pivotée afin que chaque réponse devienne une nouvelle ligne.

Questions de type "Grille à choix multiples"

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

• Nom de la question : chaque question individuelle 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 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.

Questions de type "Grille à choix multiples (cases à cocher)"

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

• Nom de la question : chaque question individuelle 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ème étape : chaque réponse individuelle 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.

Nous allons ensuite vous montrer 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.

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

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

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

Cliquez ensuite sur le lien "Importer des ensembles de données" en bas du pop-up. Cliquez sur l'icône en forme de crayon "Modifier le chemin d'accès".

Remplacez ensuite 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. Une fois de retour dans la fenêtre modale, cliquez sur le bouton "Remplacer" en bas à droite.

Connecter des tables BigQuery

Sur la droite du flux, vous devez connecter les sorties à votre propre instance BigQuery. Pour chacune des sorties, cliquez sur l'icône, puis modifiez ses propriétés comme suit.

Commencez par modifier les "Destinations manuelles".

Dans l'écran "Paramètres de publication" suivant, cliquez sur le bouton de modification.

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.

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

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

Les destinations manuelles vous permettent d'exécuter chaque job de sortie individuellement et à la demande. Cela vous permet de tester facilement une recette de préparation des données et de valider un résultat lors de la conception de votre flux.

Les destinations planifiées sont utilisées lorsque vous souhaitez exécuter votre flux de manière régulière. Par exemple, vous pouvez recharger les résultats Google Forms dans BigQuery tous les jours pour actualiser vos rapports avec les dernières réponses.

Itérez sur chaque résultat 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 "Schéma de conception Google Forms Analytics" 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 tableaux (correspondant aux quatre catégories de questions, pour plus de simplicité).

Nous vous suggérons d'explorer chaque recette une par une, en commençant par "Nettoyer les en-têtes", puis "Questions SingleChoiceSELECT", et ainsi de suite.

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

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 chacune des sorties, puis cliquez sur le bouton "Exécuter". Si la table BigQuery spécifiée existe, Dataprep ajoute de nouvelles lignes. Sinon, il crée une table.

Cliquez sur l'icône "Historique des tâches" dans le panneau de gauche pour surveiller les tâches. Le processus et le chargement des tables BigQuery devraient prendre quelques minutes.

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 à être analysé.

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

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

Une fois les données d'enquête dans BigQuery, vous pouvez facilement poser des questions plus complètes pour mieux comprendre les réponses à l'enquête. Par exemple, imaginons que vous essayiez de comprendre quel langage de programmation est le plus couramment utilisé par les personnes ayant différents titres professionnels. 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 associer 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 l'assistance client ou le ciblage des utilisateurs pour les nouveaux lancements.

Nous allons vous montrer comment joindre les données de l'enquête à un tableau de comptes en fonction du domaine du répondant et du site Web du compte. Vous pouvez désormais consulter la répartition des réponses par type de compte, ce qui vous permet de savoir 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

Remarque : Si vous souhaitez exécuter cette requête vous-même, vous pouvez utiliser notre exemple de tableau de compte Salesforce et l'ajouter à BigQuery en suivant ces instructions.

8. Effectuer des analyses visuelles

Maintenant que les données de vos enquêtes sont centralisées dans un entrepôt de données, vous pouvez facilement les analyser dans un outil de business intelligence. 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 LookML de ce dossier pour commencer à analyser les exemples de données d'enquête et de CRM pour ce modèle. Il vous suffit de créer un projet Looker, d'ajouter le 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.

Data Studio

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

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.

Attention : La suppression d'un projet aura les effets suivants :

• Tout le contenu du projet est supprimé. Si vous avez utilisé un projet existant pour ce tutoriel et que vous le supprimez, vous supprimerez également tout autre travail effectué dans le projet.
• Les ID de projets personnalisés sont perdus. Lorsque vous avez créé ce projet, vous avez peut-être créé un ID de projet personnalisé dont vous souhaitez vous servir par la suite. Pour conserver les URL qui utilisent l'ID de projet, telle qu'une URL appspot.com, supprimez les ressources sélectionnées dans le projet au lieu de supprimer l'ensemble du projet.

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.