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

1. Introduction

Vous pouvez lancer des enquêtes pour de nombreuses raisons: évaluer la satisfaction des clients, mener 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 des données. Vous allez transférer les données transformées vers 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 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 les données d'une enquête à l'aide de Dataprep
• Transférer des données d'enquête vers 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
• Connaître les bases de 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 des 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".

Voyons maintenant chaque type de réponse et sa traduction dans le fichier Google Sheets.

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

Les questions d'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 examinons ici chacun des groupes et les types de transformations que nous devons appliquer.

Questions à sélection unique: réponse courte, paragraphe, liste déroulante, é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, "Réponse 1; Réponse 4; Réponse 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 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 contenant l'option de la question et l'autre contenant la réponse.

Questions de type "Grille à choix multiples"

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

• 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 contenant 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 "Tableau à choix multiples". Ils doivent être traités dans cet ordre.

Tout d'abord, la liste de 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 doit devenir une nouvelle ligne du tableau et être divisée en deux colonnes. Une colonne contenant l'option de la question et l'autre contenant 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 d'analyse Google Forms" dans Cloud Dataprep

Téléchargez le package de flux du modèle de conception Analytics pour Google Forms (sans le décompresser). Dans l'application Cloud Dataprep, cliquez sur l'icône Flux dans la barre de navigation de gauche. 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 une feuille de calcul Google Sheets contenant les résultats d'une 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 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 de la fenêtre modale. Cliquez sur l'icône 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" (Accéder), puis sur "Import & Add to Flow" (Importer et ajouter au flux) en bas à droite. Lorsque vous êtes de retour dans la fenêtre modale, 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 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 "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 tâche de sortie individuellement et à la demande. Cela permet de tester 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 afin de mettre à jour vos rapports avec les dernières réponses.

Itérez sur 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 Google Forms Analytics" consiste à effectuer les transformations sur les réponses aux enquêtes 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 des raisons de simplicité)

Nous vous suggérons d'explorer chacune des recettes une par une, en commençant par "Clean Headers" (En-têtes nettoyés), puis "SingleChoiceSELECT-Questions" (Questions SingleChoiceSELECT), puis toutes les autres recettes en dessous.

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 chargement des tables BigQuery devrait 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 voir les détails de chacune des nouvelles tables.

Grâce aux données des enquêtes dans BigQuery, vous pouvez facilement poser des questions plus complètes pour mieux comprendre les réponses des enquêtes. Par exemple, imaginons que vous essayiez de comprendre quel langage de programmation est le plus couramment utilisé par des personnes de 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 aux enquêtes 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 sur l'assistance client ou le ciblage des utilisateurs pour de nouveaux lancements.

Nous allons vous montrer comment joindre les données de l'enquête à un tableau de compte en fonction du domaine du répondant et du site Web du compte. Vous pouvez désormais voir la répartition des réponses par type de compte, ce qui vous aide à comprendre combien de personnes répondent à partir de 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 table de comptes Salesforce et l'ajouter à BigQuery en suivant ces instructions.

8. Effectuer des analyses visuelles

Maintenant que vos données d'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 LookML dans ce dossier pour commencer à analyser l'échantillon d'enquête et les données CRM pour ce modèle. Il vous suffit de créer un projet Looker, d'ajouter le LookML, puis de remplacer les noms de la connexion et de la table dans le fichier pour qu'ils correspondent à votre configuration BigQuery. Si vous ne disposez pas d'instance Looker, mais que vous souhaitez en savoir plus, vous pouvez planifier une démonstration ici.

Data Studio

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

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 Cloud Console, accédez à 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.