Présentation

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 le document Syntaxe des requêtes SQL standards.

Objectifs de l'atelier

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

Pour chaque atelier, nous vous attribuons un nouveau projet Google Cloud et un nouvel ensemble de ressources pour une durée déterminée, sans frais.

1. Connectez-vous à Qwiklabs dans une fenêtre de navigation privée.

2. Vérifiez le temps imparti pour l'atelier (par exemple : 01:15:00) : vous devez pouvoir le terminer dans ce délai.
   Une fois l'atelier lancé, vous ne pouvez pas le mettre en pause. Si nécessaire, vous pourrez le redémarrer, mais vous devrez tout reprendre depuis le début.

3. Lorsque vous êtes prêt, cliquez sur Démarrer l'atelier.

4. Notez vos identifiants pour l'atelier (Nom d'utilisateur et Mot de passe). Ils vous serviront à vous connecter à Google Cloud Console.

5. Cliquez sur Ouvrir la console Google.

6. Cliquez sur Utiliser un autre compte, puis copiez-collez les identifiants de cet atelier lorsque vous y êtes invité.
   Si vous utilisez d'autres identifiants, des messages d'erreur s'afficheront ou des frais seront appliqués.

7. Acceptez les conditions d'utilisation et ignorez la page concernant les ressources de récupération des données.

Tâche 1 : Ajouter le projet de l'atelier aux favoris dans BigQuery

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

Ouvrir la console BigQuery

1. Dans Google Cloud Console, sélectionnez le menu de navigation > BigQuery. Le message Welcome to BigQuery in the Cloud Console (Bienvenue sur BigQuery dans Cloud Console) s'affiche. Il contient un lien vers le guide de démarrage rapide et répertorie les mises à jour de l'interface utilisateur.
2. Cliquez sur Done (OK).

Les ensembles de données publics BigQuery ne sont pas affichés par défaut dans l'UI Web de BigQuery. Vous devez donc ouvrir le projet dans lequel ils se trouvent.

4. Cliquez sur + Ajouter des données.

5. Sélectionnez Ajouter un projet aux favoris en saisissant son nom.

6. Dans le champ Nom du projet, saisissez data-to-insights.

7. Cliquez sur Ajouter aux favoris.

8. Dans le volet "Explorateur", le projet data-to-insights apparaîtra dans les favoris.

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.

Revenez à la section Explorateur classique, puis examinez les données de la table all_sessions_raw :

1. Développez le projet data-to-insights.
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 "Schéma" : nom du champ, type, mode et description ; les contraintes logiques utilisées pour organiser les données
• Onglet "Détails" : métadonnées de la table
• Onglet "Aperçu" : aperçu de la table

4. Cliquez sur l'onglet 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. Pour voir un échantillon des lignes de la table sans utiliser SQL, cliquez sur l'onglet Aperçu.

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

Votre requête utilise la fonction SQL GROUP BY sur chaque champ et compte (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 une ligne possède 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.

• Copiez et collez la requête suivante dans l'ÉDITEUR DE REQUÊTE, puis cliquez sur EXÉCUTER pour rechercher des enregistrements en double dans toutes les colonnes. Si l'onglet ÉDITEUR n'est pas visible, cliquez sur + Requête SQL.

#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.

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, conformément à 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 renvoie zéro 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 + Requête SQL.
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 vérifier que votre syntaxe est correcte, cliquez sur l'icône de l'outil de validation des requêtes en temps réel.
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 référence au site (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 A à Z). Pour inverser le sens, appliquez la commande ORDER BY field_name DESC

Résultats

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

8. É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

9. Bonus : Vous allez 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 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

10. 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 :

11. 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.

Tâche 4 : Entraînement avec SQL

Prêt à mettre en pratique vos compétences SQL ? Testez-vous avec les questions suivantes !

Défi n° 1 : Calculer un taux de conversion

1. Écrivez une requête de taux de conversion pour des produits répondant aux critères suivants :

• Plus de 1 000 unités ont été ajoutées au panier ou commandées
• ET n'étaient pas des frisbees

2. Répondez aux questions suivantes :

• Dans combien de commandes (finalisées ou non) trouvait-on le produit ?
• Au total, combien d'unités du produit trouvait-on dans les commandes (finalisées ou non) ?
• Quel produit présentait le taux de conversion le plus élevé ?

3. Effectuez la requête partielle suivante :

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE v2ProductName NOT LIKE 'frisbee' GROUP BY v2ProductName HAVING quantity_product_added > ORDER BY conversion_rate LIMIT 10;

Solution possible :

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, (COUNT(productQuantity) / COUNT(*)) AS conversion_rate, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE LOWER(v2ProductName) NOT LIKE '%frisbee%' GROUP BY v2ProductName HAVING quantity_product_added > 1000 ORDER BY conversion_rate DESC LIMIT 10;

Défi n° 2 : Suivre la progression du visiteur dans le processus de règlement

• Écrivez une requête affichant le eCommerceAction_type et le compte séparé de fullVisitorId correspondant à chaque type.

Solution possible :

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

Bonus

Cette mise en correspondance vous est fournie pour le type d'action : Unknown (Inconnu) = 0 Click through of product lists (Clic dans les listes de produits) = 1 Product detail views (Vues de détail des produits) = 2 Add product(s) to cart (Ajouter le ou les produits au panier) = 3 Remove product(s) from cart (Retirer le ou les produits du panier) = 4 Check out (Payer) = 5 Completed purchase (Achat terminé) = 6 Refund of purchase (Remboursement de l'achat) = 7 Checkout options (Options de livraison et de paiement) = 8

• Utilisez une instruction CASE pour ajouter une colonne à votre requête précédente et afficher l'étiquette eCommerceAction_type (par exemple "Completed purchase" [Achat terminé]).

Solution possible :

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS number_of_unique_visitors, eCommerceAction_type, CASE eCommerceAction_type WHEN '0' THEN 'Unknown' WHEN '1' THEN 'Click through of product lists' WHEN '2' THEN 'Product detail views' WHEN '3' THEN 'Add product(s) to cart' WHEN '4' THEN 'Remove product(s) from cart' WHEN '5' THEN 'Check out' WHEN '6' THEN 'Completed purchase' WHEN '7' THEN 'Refund of purchase' WHEN '8' THEN 'Checkout options' ELSE 'ERROR' END AS eCommerceAction_type_label FROM `data-to-insights.ecommerce.all_sessions` GROUP BY eCommerceAction_type ORDER BY eCommerceAction_type;

Quel est le pourcentage de visiteurs ayant ajouté un élément à leur panier qui ont finalisé leur achat ?

Réponse : 19988 / 56010 = 0,3568 ou 35,68 %

Défi n° 3 : Suivre les paniers abandonnés des sessions haute qualité

• À l'aide des fonctions d'agrégation, écrivez une requête affichant les ID de sessions uniques des visiteurs ayant ajouté un produit à leur panier mais n'ayant pas finalisé le règlement (abandon du panier d'achat).

Solution possible :

#standardSQL # high quality abandoned carts SELECT #unique_session_id CONCAT(fullVisitorId,CAST(visitId AS STRING)) AS unique_session_id, sessionQualityDim, SUM(productRevenue) AS transaction_revenue, MAX(eCommerceAction_type) AS checkout_progress FROM `data-to-insights.ecommerce.all_sessions` WHERE sessionQualityDim > 60 # high quality session GROUP BY unique_session_id, sessionQualityDim HAVING checkout_progress = '3' # 3 = added to cart AND (transaction_revenue = 0 OR transaction_revenue IS NULL)

Terminer l'atelier

Une fois l'atelier terminé, cliquez sur End Lab (Terminer l'atelier). Qwiklabs supprime les ressources que vous avez utilisées, puis efface le compte.

Si vous le souhaitez, vous pouvez noter l'atelier. Sélectionnez le nombre d'étoiles correspondant à votre note, saisissez un commentaire, puis cliquez sur Submit (Envoyer).

Le nombre d'étoiles que vous pouvez attribuer à un atelier correspond à votre degré de satisfaction :

• 1 étoile = très mécontent(e)
• 2 étoiles = insatisfait(e)
• 3 étoiles = ni insatisfait(e), ni satisfait(e)
• 4 étoiles = satisfait(e)
• 5 étoiles = très satisfait(e)

Si vous ne souhaitez pas donner votre avis, vous pouvez fermer la boîte de dialogue.

Pour soumettre des commentaires, suggestions ou corrections, veuillez utiliser l'onglet Support (Assistance).

Copyright 2020 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.