GSP407

Aperçu

BigQuery est la base de données d'analyse NoOps, économique et entièrement gérée de Google. Avec BigQuery, vous pouvez interroger plusieurs téraoctets de données sans avoir à gérer d'infrastructure ni faire appel à un administrateur de base de données. Basé sur le langage SQL et le modèle de paiement à l'usage, BigQuery vous permet de vous concentrer sur l'analyse des données pour en dégager des informations pertinentes.

Nous mettons à disposition un nouvel ensemble de données d'e-commerce comptant des millions d'enregistrements Google Analytics pour le Google Merchandise Store, chargé dans une table dans BigQuery. Dans cet atelier, vous allez utiliser une copie de cet ensemble de données. Des exemples de scénarios sont fournis. Vous allez observer les données proposées, apprendre comment supprimer les informations en double, puis analyser les données plus en détail.

Pour continuer et tester les requêtes BigQuery fournies pour l'analyse des données, consultez la documentation de référence sur la syntaxe des requêtes disponible sur la page BigQuery.

Points abordés

Dans cet atelier, vous allez utiliser BigQuery pour :

• accéder à un ensemble de données d'e-commerce ;
• examiner les métadonnées de l'ensemble de données ;
• supprimer les entrées en double ;
• écrire et exécuter des requêtes.

Préparation

Avant de cliquer sur le bouton "Démarrer l'atelier"

Lisez ces instructions. Les ateliers sont minutés, et vous ne pouvez pas les mettre en pause. Le minuteur, qui démarre lorsque vous cliquez sur Démarrer l'atelier, indique combien de temps les ressources Google Cloud resteront accessibles.

Cet atelier pratique vous permet de suivre vous-même les activités dans un véritable environnement cloud, et non dans un environnement de simulation ou de démonstration. Nous vous fournissons des identifiants temporaires pour vous connecter à Google Cloud le temps de l'atelier.

Pour réaliser cet atelier :

• vous devez avoir accès à un navigateur Internet standard (nous vous recommandons d'utiliser Chrome) ;

Remarque : Ouvrez une fenêtre de navigateur en mode incognito/navigation privée pour effectuer cet atelier. Vous éviterez ainsi les conflits entre votre compte personnel et le temporaire étudiant, qui pourraient entraîner des frais supplémentaires facturés sur votre compte personnel.

• vous disposez d'un temps limité ; une fois l'atelier commencé, vous ne pouvez pas le mettre en pause.

Remarque : Si vous possédez déjà votre propre compte ou projet Google Cloud, veillez à ne pas l'utiliser pour réaliser cet atelier afin d'éviter que des frais supplémentaires ne vous soient facturés.

Démarrer l'atelier et se connecter à la console Google Cloud

1. Cliquez sur le bouton Démarrer l'atelier. Si l'atelier est payant, un pop-up s'affiche pour vous permettre de sélectionner un mode de paiement. Sur la gauche, vous trouverez le panneau Détails concernant l'atelier, qui contient les éléments suivants :

   • Le bouton Ouvrir la console Google
   • Le temps restant
   • Les identifiants temporaires que vous devez utiliser pour cet atelier
   • Des informations complémentaires vous permettant d'effectuer l'atelier

2. Cliquez sur Ouvrir la console Google. L'atelier lance les ressources, puis ouvre la page Se connecter dans un nouvel onglet.

   Conseil : Réorganisez les onglets dans des fenêtres distinctes, placées côte à côte.

   Remarque : Si la boîte de dialogue Sélectionner un compte s'affiche, cliquez sur Utiliser un autre compte.

3. Si nécessaire, copiez le nom d'utilisateur inclus dans le panneau Détails concernant l'atelier et collez-le dans la boîte de dialogue Se connecter. Cliquez sur Suivant.

4. Copiez le mot de passe inclus dans le panneau Détails concernant l'atelier et collez-le dans la boîte de dialogue de bienvenue. Cliquez sur Suivant.

   Important : Vous devez utiliser les identifiants fournis dans le panneau de gauche. Ne saisissez pas vos identifiants Google Cloud Skills Boost. Remarque : Si vous utilisez votre propre compte Google Cloud pour cet atelier, des frais supplémentaires peuvent vous être facturés.

5. Accédez aux pages suivantes :

   • Acceptez les conditions d'utilisation.
   • N'ajoutez pas d'options de récupération ni d'authentification à deux facteurs (ce compte est temporaire).
   • Ne vous inscrivez pas aux essais offerts.

Après quelques instants, la console Cloud s'ouvre dans cet onglet.

Remarque : Vous pouvez afficher le menu qui contient la liste des produits et services Google Cloud en cliquant sur le menu de navigation en haut à gauche.

Tâche 1 : Épingler le projet de l'atelier dans BigQuery

Dans cette section, vous allez ajouter le projet data-to-insights à vos ressources d'environnement.

1. Accédez au menu de navigation > BigQuery.

Le message "Bienvenue sur BigQuery dans la console Cloud" s'affiche.

Remarque : Il contient un lien vers le guide de démarrage rapide et les nouveautés de l'interface utilisateur.

2. Cliquez sur OK.
3. Les ensembles de données publics BigQuery ne sont pas affichés par défaut dans l'interface utilisateur Web de BigQuery. Pour ouvrir le projet d'ensembles de données publics, copiez data-to-insights.
4. Cliquez sur + Ajouter > Ajouter un projet aux favoris en saisissant son nom, puis définissez le nom sur data-to-insights. Cliquez sur AJOUTER AUX FAVORIS.

Le projet data-to-insights apparaît désormais dans la section "Explorateur".

Tâche 2 : Explorer les données d'e-commerce et identifier les enregistrements en double

Scénario : votre équipe d'analystes de données a exporté les journaux Google Analytics d'un site Web d'e-commerce dans BigQuery et a créé une table regroupant toutes les données de session visiteur brutes du site d'e-commerce.

Examinez les données de la table all_sessions_raw :

1. Cliquez sur l'icône Développer le nœud en regard de data-to-insights pour développer le projet.
2. Développez e-commerce.
3. Cliquez sur all_sessions_raw.

Dans le volet de droite, une section s'ouvre. Elle contient trois vues des données de la table :

• Onglet "Schema" (Schéma) : nom du champ, type, mode et description ; les contraintes logiques utilisées pour organiser les données
• Onglet "Details" (Détails) : métadonnées de la table
• Onglet "Preview" (Aperçu) : aperçu de la table

4. Cliquez sur l'onglet Details (Détails) pour afficher les métadonnées de la table.

Questions :

Identifier les lignes en double

Voir un échantillon de données peut vous donner une idée plus précise des éléments inclus dans l'ensemble de données.

1. Pour voir un échantillon des lignes de la table sans utiliser SQL, cliquez sur l'onglet Preview (Aperçu).

2. Analysez et faites défiler les lignes. Aucun champ en particulier ne permet d'identifier une ligne de manière unique. Vous devez donc utiliser une logique avancée pour identifier les lignes en double.

3. La requête que vous utiliserez (ci-dessous) applique la fonction SQL GROUP BY à chaque champ et comptabilise (COUNT) le nombre de fois où des lignes possèdent les mêmes valeurs dans chaque champ :

• Si chaque champ est unique, la fonction COUNT renvoie 1, car il n'existe aucun autre groupe de lignes possédant exactement la même valeur pour tous les champs.
• Si plusieurs lignes présentent les mêmes valeurs pour tous les champs, elles seront groupées et la valeur COUNT sera supérieure à 1.

La dernière partie de la requête est un filtre d'agrégation qui utilise la fonction HAVING pour afficher uniquement les résultats ayant un nombre (COUNT) de doublons supérieur à 1. Ainsi, le nombre d'enregistrements présentant des doublons sera identique au nombre de lignes dans la table obtenue.

4. Copiez la requête suivante et collez-la dans l'Éditeur de requête, puisEXÉCUTEZ la requête pour rechercher les enregistrements en double dans toutes les colonnes.

#standardSQL SELECT COUNT(*) as num_duplicate_rows, * FROM `data-to-insights.ecommerce.all_sessions_raw` GROUP BY fullVisitorId, channelGrouping, time, country, city, totalTransactionRevenue, transactions, timeOnSite, pageviews, sessionQualityDim, date, visitId, type, productRefundAmount, productQuantity, productPrice, productRevenue, productSKU, v2ProductName, v2ProductCategory, productVariant, currencyCode, itemQuantity, itemRevenue, transactionRevenue, transactionId, pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type, eCommerceAction_step, eCommerceAction_option HAVING num_duplicate_rows > 1;

Remarque : Dans vos propres ensembles de données, même si vous disposez d'une clé unique, il est toujours intéressant de confirmer l'unicité des lignes à l'aide des fonctions COUNT, GROUP BY et HAVING avant de commencer votre analyse.

Cliquez sur Vérifier ma progression pour valider l'objectif. Identifier les lignes en double

Analyser la nouvelle table all_sessions

Dans cette section, vous allez utiliser une table dédupliquée appelée all_sessions.

Scénario : votre équipe d'analystes de données vous a fourni cette requête, et vos experts en schémas ont identifié les champs clés qui doivent être uniques pour chaque enregistrement selon votre schéma.

• Exécutez la requête pour confirmer l'absence de doublons, cette fois dans la table all_sessions :

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # the unique visitor ID visitId, # a visitor can have multiple visits date, # session date stored as string YYYYMMDD time, # time of the individual site hit (can be 0 to many per visitor session) v2ProductName, # not unique since a product can have variants like Color productSKU, # unique for each product type, # a visitor can visit Pages and/or can trigger Events (even at the same time) eCommerceAction_type, # maps to ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # revenue of the order transactionId, # unique identifier for revenue bearing transaction COUNT(*) as row_count FROM `data-to-insights.ecommerce.all_sessions` GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12 HAVING row_count > 1 # find duplicates

La requête ne renvoie aucun enregistrement.

Remarque : Dans SQL, vous pouvez appliquer la fonction GROUP BY ou ORDER BY à l'index de la colonne. Exemple : GROUP BY 1 au lieu de GROUP BY fullVisitorId.

Tâche 3 : Écrire une requête SQL de base sur les données d'e-commerce

Dans cette section, vous allez exécuter une requête pour obtenir des insights sur l'ensemble de données d'e-commerce.

Écrire une requête indiquant le nombre total de visiteurs uniques

Votre requête détermine le nombre total de vues en comptant product_views et le nombre de visiteurs uniques en comptant fullVisitorID.

1. Cliquez sur l'icône + (Saisir une nouvelle requête).
2. Écrivez cette requête dans l'éditeur :

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;

3. Pour vous assurer que votre syntaxe est correcte, vérifiez que l'outil de validation des requêtes en temps réel affiche bien une icône représentant une coche verte.
4. Cliquez sur Exécuter. Lisez les résultats pour connaître le nombre de visiteurs uniques.

Résultats :

5. À présent, écrivez une requête indiquant le nombre total de visiteurs uniques (fullVisitorID) par site référent (channelGrouping) :

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;

Résultats :

6. Écrivez une requête pour lister tous les noms de produits uniques (v2ProductName) par ordre alphabétique :

#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName

Conseil : Dans SQL, la valeur par défaut de la clause ORDER BY est "Ascending (ASC) A to Z" (Croissant, de A à Z). Pour inverser le sens, appliquez la commande ORDER BY field_name DESC

Résultats :

Cette requête renvoie un total de 633 produits (lignes).

7. Écrivez une requête pour lister les cinq produits comptant le plus de vues (product_views) de la part de tous les visiteurs (y compris les personnes ayant consulté plusieurs fois le même produit). Votre requête compte le nombre de fois où un produit (v2ProductName) a été vu (product_views), dresse la liste par ordre décroissant, et affiche les cinq premières entrées :

Conseil : Dans Google Analytics, un visiteur peut "visualiser" un produit lors des types d'interactions suivants : 'page', 'screenview', 'event', 'transaction', 'item', 'social', 'exception', 'timing'. Pour les besoins de cet atelier, nous allons appliquer le filtre uniquement au type = 'PAGE'.

#standardSQL SELECT COUNT(*) AS product_views, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Résultats :

8. Bonus : Vous allez maintenant affiner la requête pour ne plus comptabiliser les vues multiples d'un même produit par un même visiteur. La vue d'un produit distinct ne doit être comptée qu'une seule fois pour un même utilisateur :

WITH unique_product_views_by_person AS ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

Conseil : Vous pouvez utiliser la clause SQL WITH pour décomposer les requêtes complexes en plusieurs étapes. Ici, nous créons d'abord une requête qui recherche tous les produits uniques d'un même visiteur et les décompte une seule fois. La seconde requête effectue ensuite l'agrégation de tous les visiteurs et de tous les produits.

Résultats :

9. Développez ensuite votre requête précédente de manière à inclure le nombre total de produits distincts commandés et le nombre total d'unités commandées (productQuantity) :

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Résultats :

Questions :

10. Développez la requête pour inclure le nombre moyen de produits par commande (nombre total d'unités commandées/nombre total de commandes, ou SUM(productQuantity)/COUNT(productQuantity)) :

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Résultats

Question :

La gourde avec infuseur 22 onces YouTube a le plus grand nombre moyen par commande (avg_per_order), avec 9,38 unités par commande.

Cliquez sur Vérifier ma progression pour valider l'objectif. Écrire une requête SQL de base sur les données d'e-commerce

Félicitations !

Vous avez utilisé BigQuery pour consulter et interroger les données, afin d'obtenir des insights pertinents sur différents aspects du marketing produit.

Étapes suivantes et informations supplémentaires

• Explorer les ensembles de données publics BigQuery
• Vous possédez un compte Google Analytics et souhaitez interroger vos propres ensembles de données BigQuery ? Suivez ce guide d'exportation.
• Découvrez 15 atouts de BigQuery dont vous n'aviez probablement pas idée.
• Découvrez d'autres ateliers pour en savoir plus sur BigQuery :
  • Corriger des erreurs SQL courantes avec BigQuery
  • Données météorologiques dans BigQuery

Formations et certifications Google Cloud

Les formations et certifications Google Cloud vous aident à tirer pleinement parti des technologies Google Cloud. Nos cours portent sur les compétences techniques et les bonnes pratiques à suivre pour être rapidement opérationnel et poursuivre votre apprentissage. Nous proposons des formations pour tous les niveaux, à la demande, en salle et à distance, pour nous adapter aux emplois du temps de chacun. Les certifications vous permettent de valider et de démontrer vos compétences et votre expérience en matière de technologies Google Cloud.

Dernière mise à jour du manuel : 26 janvier 2024

Dernier test de l'atelier : 24 août 2023

Copyright 2024 Google LLC Tous droits réservés. Google et le logo Google sont des marques de Google LLC. Tous les autres noms d'entreprises et de produits peuvent être des marques des entreprises auxquelles ils sont associés.