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. Cependant, si vous avez déjà essayé de travailler avec des données d'enquête avant de savoir probablement que le format standard est difficile à utiliser. Dans ce guide, nous créons 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 convertir les réponses de notre exemple d'enquête Google Forms dans un format utile pour l'analyse de données. Vous transférerez les données transformées dans BigQuery, où vous pourrez poser des questions plus approfondies avec SQL et les associer à d'autres ensembles de données pour des analyses plus puissantes. À la fin, vous pourrez 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 vers BigQuery
• Comment obtenir plus d'informations à partir des données d'enquête

Prérequis

• Un projet Google Cloud dans lequel la facturation, BigQuery et Dataprep sont activés
• Des connaissances de base sur Dataprep sont utiles, mais pas obligatoires.
• Des connaissances de base sur BigQuery et SQL sont utiles, mais pas obligatoires.

2. Gérer les réponses Google Forms

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

Les résultats de l'enquête peuvent être exportés depuis le dossier "Réponses" en cliquant sur l'icône Google Sheets et en créant une feuille de calcul ou en chargeant les résultats dans une feuille de calcul existante. Google Forms continue d'ajouter des réponses à la feuille de calcul à mesure que les personnes interrogées envoient leurs réponses, jusqu'à ce que vous désélectionniez l'option "Accepte les réponses". .

Passons maintenant en revue chaque type de réponse et la façon dont il se traduit dans le fichier Google Sheets.

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

Les questions d'une enquête peuvent être regroupées en quatre familles qui respecteront un format d'exportation particulier. Selon le type de question, vous devrez restructurer les données d'une certaine manière. Nous allons passer en revue chacun des groupes et les types de transformations à appliquer.

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

• Question name: 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

• Question name: nom de la colonne
• Réponse: liste de valeurs avec un point-virgule comme séparateur (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 sur la 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 dans 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 la question option et l'autre colonne avec la réponse.

Questions sur la grille de cases à cocher à choix multiples

Voici un exemple de grille de cases à cocher. Il est possible de ne sélectionner aucune 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 concernant la transformation: ces types de questions associent une case à cocher. et la grille « Choix multiples » catégories et doivent être résolus 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 devient une nouvelle ligne pour la question particulière.

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

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 (modèle de conception d'analyses Google Forms), sans le décompresser. Dans l'application Cloud Dataprep, cliquez sur l'icône Flows dans la barre de navigation de gauche. Ensuite, sur la page "Flux", sélectionnez "Importer" dans le menu contextuel.

Après avoir importé le flux, sélectionnez-le pour le modifier. Votre écran devrait ressembler à ceci:

Feuille de calcul des résultats de l'enquête Connect Google Sheets

Sur la 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 d'ensembles de données Google Sheets, puis sélectionnez "Remplacer".

Cliquez ensuite sur "Import Datasets" en bas de la fenêtre modale. Cliquez sur le bouton un crayon.

À partir de là, remplacez la valeur actuelle par ce lien qui renvoie vers une feuille de calcul Google Sheets par 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 "OK". puis sur "Importer et Ajouter au flux" en bas à droite. Une fois de retour dans la fenêtre modale, cliquez sur "Remplacer". en bas à droite.

Connecter des tables BigQuery

À droite du flux, vous devez connecter les sorties à votre propre instance BigQuery. Cliquez sur l'icône de chaque sortie, puis modifiez ses propriétés comme suit.

Commencez par modifier la colonne "Destinations manuelles"

Dans les paramètres de publication suivants, à l'écran, cliquez sur le bouton "Modifier"

Lorsque le message "Publishing Action" (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.

Une fois que vous avez modifié les "Destinations manuelles", procédez de la même manière pour les "Destinations planifiées". de sortie.

Les destinations manuelles vous permettent d'exécuter chaque job de sortie individuel de manière indépendante et à la demande. C'est pratique pour tester une recette de préparation de données et valider un résultat lors de la conception de votre flux.

Les destinations planifiées sont utilisées lorsque vous souhaitez exécuter régulièrement votre flux. Par exemple, vous pouvez recharger chaque jour les résultats Google Forms dans BigQuery afin d'actualiser vos rapports avec les dernières réponses.

Itérez chaque sortie en suivant les mêmes étapes. Au total, vous devez modifier huit destinations.

5. Présentation du flux Cloud Dataprep

L'idée de base du "modèle de conception d'analyse des formulaires Google Forms" consiste à transformer les réponses à l'enquête comme décrit précédemment, en décomposant chaque catégorie de question en une recette de transformation de données Cloud Dataprep spécifique.

Ce flux divise les questions en 4 tableaux (correspondant aux 4 catégories de questions, par souci de simplicité)

Nous vous suggérons d’explorer chacune des recettes une par une, en commençant par « Clean Headers » puis « SingleChoiceSELECT-Questions » suivies les unes des 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. Dans le cas contraire, il crée une table.

Cliquez sur l'historique des tâches située dans la zone de gauche pour surveiller les jobs. Le chargement des tables BigQuery devrait prendre quelques minutes.

Une fois tous les jobs terminés, les résultats de l'enquête sont chargés dans BigQuery dans un format propre, structuré et normalisé, prêt à être analysé.

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

Vous devriez pouvoir consulter les détails de chacune des nouvelles tables dans la console Google pour BigQuery.

Grâce aux 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, disons que vous essayez de comprendre quel langage de programmation est le plus souvent utilisé par les 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 performantes, vous pouvez associer les réponses à l'enquête à des 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.

Dans cet article, nous vous expliquons comment associer les données de l'enquête à un tableau de compte en fonction du domaine de la personne interrogée 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 à déterminer combien de personnes interrogées 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 table 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 d'informatique décisionnelle. Nous avons créé quelques exemples de rapports dans Data Studio et dans Looker.

Looker

Si vous disposez déjà d'une instance Looker, vous pouvez utiliser LookML dans 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 code LookML et de remplacer les noms des connexions et des tables dans le fichier pour qu'ils correspondent à votre configuration BigQuery. Si vous ne possédez 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 comportant la croix Google "Rapport vide". et connectez-vous à BigQuery. Suivez toutes les instructions de Data Studio. Pour en savoir plus, consultez un guide de démarrage rapide et une présentation des principales fonctionnalités de Data Studio ici. Vous trouverez également ici nos tableaux de bord Data Studio prédéfinis.

9. Nettoyage

Le moyen le plus simple d'empêcher la facturation est de supprimer le projet Cloud que vous avez créé pour ce 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.