Apprendre SQL avec de vraies données GA4 : Un guide pratique pour les débutants

Vous êtes curieux de savoir comment les données réelles de commerce électronique se présentent en SQL ? Dans cet article, vous analyserez les événements Google Analytics 4 (GA4) du Google Merchandise Store à l'aide de BigQuery, tout en acquérant des compétences SQL pratiques.

Vous voulez améliorer vos compétences en SQL avec de vraies données de commerce électronique ? Dans cet article, vous explorerez les données de Google Analytics4 (GA4) à l'aide de SQL dans BigQuery, l'entrepôt de données de Google basé sur le cloud. GA4 est la plateforme d'analyse web de Google utilisée par des millions de sites web et d'applications pour suivre l'interaction des utilisateurs avec leur contenu. Elle est largement adoptée dans le domaine de l'analyse numérique, ce qui rend les compétences en GA4 très utiles sur le marché du travail et dans de nombreuses entreprises. Si vous connectez votre propriété GA4 à BigQuery, vous pouvez stocker ces données d'événements brutes et les interroger à l'aide de SQL. Cela vous permet d'aller au-delà des rapports GA4 intégrés et d'analyser le comportement des utilisateurs de manière beaucoup plus détaillée.

Google propose un ensemble de données GA4 gratuit dans BigQuery : ga4_obfuscated_sample_ecommerce.events_20210113. Il contient des données d'événements réels provenant de la boutique Google Merchandise Store - la boutique en ligne officielle de Google pour les produits de marque tels que les t-shirts, les tasses et les accessoires - capturées le 13 janvier 2021. Vous y trouverez des vues de produits, des ajouts au panier, des achats et des informations sur les utilisateurs telles que la localisation ou le type d'appareil. Il s'agit d'un ensemble de données léger et réel, parfait pour apprendre le langage SQL.

Besoin d'une remise à niveau rapide en SQL ? Essayez notre piste interactive SQL pour l'analyse de données. C'est le moyen le plus rapide d'apprendre les bases que vous utiliserez tout au long de ce guide.

Connaître l'ensemble de données

Dans ce guide, nous utiliserons un jeu de données public gratuit dans BigQuery :
bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113 .

GA4 utilise un modèle basé sur les événements, où chaque interaction de l'utilisateur - comme la visualisation d'un produit, son ajout au panier ou la finalisation d'un achat - est suivie comme un événement distinct. Chaque ligne de l'ensemble de données représente l'une de ces interactions et contient à la fois des champs standard (comme le nom de l'événement et l'horodatage) et des structures imbriquées (comme les détails du produit ou les paramètres de l'événement).

Voici quelques colonnes clés avec lesquelles vous travaillerez :

• event_name: le type d'action de l'utilisateur, tel que page_view, view_item, add_to_cart, begin_checkout, ou purchase
• user_pseudo_idl'identifiant anonyme de l'utilisateur
• event_timestampdate de l'événement : date de l'événement
• event_paramsles détails relatifs à l'événement (stockés sous forme de paires clé-valeur)
• itemsDonnées sur les produits : données sur les produits imbriquées pour des événements tels que view_item ou l'achat.

Quels sont les événements les plus courants ?

Commençons par voir ce que font les utilisateurs sur le site. Cette requête montre les types d'événements les plus courants et le nombre d'utilisateurs qui les ont déclenchés :

SELECT
  event_name,
  COUNT(DISTINCT user_pseudo_id) AS unique_users,
  COUNT(*) AS total_events
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
GROUP BY event_name
ORDER BY total_events DESC;

Cette requête montre les actions les plus courantes des utilisateurs. Comme on pouvait s'y attendre, page_view arrive en tête avec plus de 15 000 événements. Seuls 34 utilisateurs ont effectué un achat, tandis que beaucoup d'autres ont consulté des produits ou les ont ajoutés au panier - une chute nette typique des entonnoirs de commerce électronique.

Suivi de l'entonnoir d'achat

Ensuite, construisons un entonnoir de base pour comprendre combien d'utilisateurs passent de la navigation à l'achat. Nous allons compter combien d'utilisateurs uniques ont déclenché chacun des événements clés du commerce électronique :

SELECT
  event_name,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
WHERE event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
GROUP BY event_name
ORDER BY users DESC;

Cela vous montre comment les utilisateurs abandonnent à chaque étape du parcours d'achat. Vous remarquerez probablement qu'il y a plus d'utilisateurs qui consultent les articles que d'utilisateurs qui achètent - comme dans la plupart des boutiques en ligne.

Analysez ce que les internautes ont acheté

Vous voulez savoir quels sont les produits que les utilisateurs achètent le plus ? De nombreux événements de commerce électronique contiennent des détails sur les produits dans un champ imbriqué appelé "articles". Pour y accéder, nous utiliserons la fonction UNNEST().

Voici comment trouver les 10 produits les plus achetés :

SELECT
  item.item_name,
  COUNT(*) AS purchases
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`,
  UNNEST(items) AS item
WHERE event_name = 'purchase'
GROUP BY item.item_name
ORDER BY purchases DESC
LIMIT 10;

Et voici le résultat :

Vous pouvez également analyser les prix des produits et calculer le revenu total ou moyen.

Par exemple, cette requête renvoie le revenu total par produit :

SELECT
  item.item_name,
  ROUND(SUM(item.price * item.quantity), 2) AS total_revenue
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`,
  UNNEST(items) AS item
WHERE event_name = 'purchase'
GROUP BY item.item_name
ORDER BY total_revenue DESC
LIMIT 10;

C'est un excellent moyen de trouver les produits les plus vendus de votre boutique.

Segmenter les utilisateurs par pays

Les données GA4 comprennent également des informations sur l'emplacement et l'appareil de chaque utilisateur. Vous pouvez segmenter le trafic par geo.country.

Voici une requête qui montre d'où les utilisateurs achètent :

SELECT
  geo.country,
  COUNT(DISTINCT user_pseudo_id) AS unique_buyers
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210113`
WHERE event_name = 'purchase'
GROUP BY geo.country
ORDER BY unique_buyers DESC;

Pratiquez davantage par vous-même

Maintenant que vous avez travaillé sur les exemples guidés, il est temps de vous entraîner seul. La meilleure façon de développer vos compétences en SQL - et de penser comme un analyste de données - est d'explorer les données en posant vos propres questions.

• Ventiler les achats par source de trafic (traffic_source.source)
• Comparez la taille moyenne du panier entre les utilisateurs de téléphones portables et d'ordinateurs de bureau
• Analysez les consultations de produits qui n'ont pas abouti à un achat.
• Construire un parcours au niveau de la session pour un seul utilisateur

Voici quelques défis pratiques à relever :

• Décomposer les achats par source de trafic. Voyez quelles sources (comme Google, directes ou de référence) génèrent le plus d'achats à l'aide de traffic_source.source.
• Comparez la taille moyenne du panier entre les utilisateurs de téléphones portables et d'ordinateurs de bureau. Utilisez les données relatives à l'appareil pour déterminer si le comportement de l'utilisateur change en fonction de la plateforme.
• Analysez les consultations de produits qui n'ont pas abouti à un achat. Identifiez les points de chute dans l'entonnoir en comparant les événements view_item et purchase.
• Construisez un parcours au niveau de la session pour un seul utilisateur. Choisissez un user_pseudo_id et reconstituez son parcours étape par étape à l'aide de event_timestamp.

Plus vous expérimenterez des requêtes de ce type, mieux vous comprendrez les données et plus vous aurez confiance dans l'application du langage SQL à des problèmes professionnels concrets.

Réflexions finales

L'apprentissage du langage SQL est beaucoup plus intéressant - et pratique - lorsque vous travaillez avec des données réelles. En particulier lorsque ces données reflètent le comportement d'utilisateurs réels dans une boutique en ligne, comme la boutique Google Merchandise. Il ne s'agit pas d'une simple théorie ; c'est ainsi que les analystes de données, les spécialistes du marketing et les équipes de produits travaillent tous les jours.

Dans cet article, vous avez appris à

• Rédiger des requêtes à l'aide des données réelles de GA4 sur le commerce électronique
• Travailler avec des champs imbriqués à l'aide de UNNEST()
• Explorer les événements, les produits, les segments d'utilisateurs et les entonnoirs

En travaillant directement avec les données GA4 dans BigQuery, vous avez fait un grand pas en avant pour penser comme un analyste de données - en posant des questions, en rédigeant des requêtes pertinentes et en trouvant des informations qui comptent.

Vous voulez aller plus loin ? Consultez notre piste SQL de A à Z pour construire une base solide, ou plongez dans des sujets plus avancés avec nos cours SQL interactifs.

Continuez à pratiquer, restez curieux - et bonnes requêtes !