Lab - SQL analytique sur theLook

Ouvrir dans Google Colab

Lab BigQuery - SQL analytique sur theLook

Vous êtes analyste chez theLook, une boutique de vêtements en ligne. Le directeur financier veut comprendre les ventes 2024, et savoir ce que coûtent vos requêtes. Vous travaillez sur les tables publiques de BigQuery, depuis ce notebook, sans rien installer ni payer.

Modèle de données

theLook eCommerce est un jeu de données public et synthétique fourni par Google : une boutique de vêtements fictive, avec clients, commandes, articles et catalogue. Il sert d’exemple d’analytique e-commerce dans BigQuery, sans données personnelles réelles.

Dataset : bigquery-public-data.thelook_ecommerce.

Les quatre tables sont utilisées dans le lab.

  • users - Clients inscrits sur la boutique : identité, lieu et canal d’acquisition.
  • orders - En-tête de commande : une ligne = une commande (qui a commandé, statut global, dates). Le détail des articles n’est pas ici.
  • order_items - Lignes de la commande : une ligne = un article dans une commande. Si une commande contient 3 produits, elle a 1 ligne dans orders et 3 dans order_items.
  • products - Catalogue produits : catégorie, marque, prix et entrepôt associé.
users id INT64 PK first_name STRING last_name STRING email STRING age INT64 gender STRING state STRING street_address STRING postal_code STRING city STRING country STRING latitude FLOAT64 longitude FLOAT64 traffic_source STRING created_at TIMESTAMP orders order_id INT64 PK user_id INT64 FK status STRING gender STRING created_at TIMESTAMP returned_at TIMESTAMP shipped_at TIMESTAMP delivered_at TIMESTAMP num_of_item INT64 order_items id INT64 PK order_id INT64 FK user_id INT64 FK product_id INT64 FK inventory_item_id INT64 FK status STRING created_at TIMESTAMP shipped_at TIMESTAMP delivered_at TIMESTAMP returned_at TIMESTAMP sale_price FLOAT64 products id INT64 PK cost FLOAT64 category STRING name STRING brand STRING retail_price FLOAT64 department STRING sku STRING distribution_center_id INT64 FK 1 N 1 N 1 N Grain : 1 ligne de order_items = 1 article vendu. Types BigQuery.

Avant de commencer

  1. Ouvrir la console BigQuery avec votre compte Google et accepter les conditions. Si la console le demande, cliquer sur Créer un projet. Le projet fonctionne en Sandbox : gratuit, sans carte bancaire. N’activez pas la facturation.
  2. Copier l’ID du projet, visible dans le sélecteur de projet en haut de la console (par exemple tp-bigquery-123456). C’est l’ID qu’il faut, pas le nom.
  3. Dans Colab : Fichier → Enregistrer une copie dans Drive, puis travailler dans cette copie.
  4. Coller l’ID dans la cellule Configuration, l’exécuter, puis autoriser l’accès à votre compte Google.
  5. Exécuter les cellules dans l’ordre. Avant chaque requête, lisez l’estimation affichée : c’est le réflexe du lab.

Le Sandbox donne 1 Tio de requêtes par mois et 10 Gio de stockage ; les tables créées expirent après 60 jours. C’est largement suffisant pour ce lab.

Si vous voyez… Alors…
Access Denied ou bigquery.jobs.create L’ID du projet est faux : recopiez l’ID, pas le nom.
Not found: Dataset … in location EU Le dataset tp_lab doit être en US : relancez la configuration.
Syntax error Vérifiez les ... et les TODO restés dans la requête.

Configuration

%pip install -q google-cloud-bigquery db-dtypes
PROJECT_ID = "votre-id-de-projet"  # À modifier
DATASET = "tp_lab"
LOCATION = "US"  # theLook est stocké aux États-Unis

from google.colab import auth
auth.authenticate_user()

from google.cloud import bigquery

assert PROJECT_ID != "votre-id-de-projet", "Renseignez l'ID de votre projet."
client = bigquery.Client(project=PROJECT_ID, location=LOCATION)
MAXIMUM_BYTES_BILLED = 1 * 1024**3  # 1 Gio par requête exécutée
client.default_query_job_config = bigquery.QueryJobConfig(
    maximum_bytes_billed=MAXIMUM_BYTES_BILLED
)
dataset = bigquery.Dataset(f"{PROJECT_ID}.{DATASET}")
dataset.location = LOCATION
client.create_dataset(dataset, exists_ok=True)
print("Prêt : projet", PROJECT_ID, "- dataset", DATASET)

0. Exemple guidé

Tout le lab passe par l’objet client, de trois façons. Exécutez les trois cellules sur une petite requête, et observez.

Estimer sans exécuter. Avec dry_run=True, BigQuery calcule combien d’octets la requête lirait. Rien n’est exécuté, rien n’est compté. La configuration DRY_RUN resservira dans tout le lab.

Chaque requête exécutée dans ce notebook reçoit aussi un plafond de 1 Gio (maximum_bytes_billed). Si l’estimation le dépasse, BigQuery refuse le job : relisez le SQL ou augmentez exceptionnellement la limite après l’avoir justifié.

DRY_RUN = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)

sql = """
SELECT
  department,
  COUNT(*) AS nb_produits
FROM `bigquery-public-data.thelook_ecommerce.products`
GROUP BY department
"""
job = client.query(sql, job_config=DRY_RUN)
print(f"Estimation : {job.total_bytes_processed / 1024**2:.2f} Mio")

Exécuter et récupérer un tableau. client.query(sql) lance la requête ; .to_dataframe() renvoie le résultat sous forme de tableau pandas.

client.query(sql).to_dataframe()

Créer puis supprimer une table. CREATE et DROP ne renvoient pas de lignes : .result() attend simplement que BigQuery ait fini. Une table de votre dataset s’écrit tp_lab.nom_de_table.

client.query("""
CREATE OR REPLACE TABLE tp_lab.exemple AS
SELECT
  id,
  category
FROM `bigquery-public-data.thelook_ecommerce.products`
""").result()
print(client.get_table("tp_lab.exemple").num_rows, "lignes dans tp_lab.exemple")

client.query("DROP TABLE tp_lab.exemple").result()
print("tp_lab.exemple supprimée")

À retenir pour la suite :

Besoin Code
Estimer client.query(sql, job_config=DRY_RUN).total_bytes_processed
Lire un résultat client.query(sql).to_dataframe() (plafond de 1 Gio appliqué)
Créer ou supprimer client.query(sql).result() (plafond de 1 Gio appliqué)

1. Premier regard sur mars 2024

comparer deux estimations avant d’exécuter.

À faire : deux requêtes calculent le même SUM(sale_price). sql_filtree limite mars 2024. sql_non_filtree ne filtre pas. Estimez les deux, exécutez la version filtrée, puis comparez les octets

# TODO : écrivez sql_filtree (mars 2024) et sql_non_filtree (tout l'historique)
sql_filtree = """
TODO
"""

sql_non_filtree = """
TODO
"""

job = client.query(sql_filtree, job_config=DRY_RUN)
print(f"Estimation filtrée     {job.total_bytes_processed / 1024**2:.2f} Mio")

job = client.query(sql_non_filtree, job_config=DRY_RUN)
print(f"Estimation non filtrée {job.total_bytes_processed / 1024**2:.2f} Mio")

client.query(sql_filtree).to_dataframe()

Questions :

  1. une phrase : le filtre de date réduit-il les octets lus ? Pourquoi ?

VOTRE RÉPONSE ICI

  1. une piste : sinon, comment réduire les octets lus sur cette table ?

VOTRE RÉPONSE ICI

2. Votre table partitionnée

Observer concrètement l’effet du partitionnement sur le volume d’octets lus.

À faire : 1. Créez votre propre table tp_lab.items à partir de order_items, partitionnée par jour. 2. Écrivez la même requête filtrée sur mars 2024 qu’à la question 1, mais sur votre table tp_lab.items. 3. Estimez son coût en DRY_RUN et comparez le résultat avec l’estimation de la question 1.

# TODO : créer la table tp_lab.items partitionnée par jour sur DATE(created_at)
client.query("""
TODO
""").result()

# TODO : même requête filtrée sur mars 2024 que la question 1, sur tp_lab.items
sql_opt = """
TODO
"""

job = client.query(sql_opt, job_config=DRY_RUN)
print(f"Table optimisée : {job.total_bytes_processed / 1024**2:.2f} Mio")

Questions 1. Comparez avec la question 1, pourquoi les octets scannés ont-ils diminués ?

VOTRE RÉPONSE ICI

3. Le piège du SELECT *

Observer l’impact du choix des colonnes sur le volume de données lues.

À faire : 1. Écrivez sql_tout qui sélectionne toutes les colonnes (SELECT *) de order_items. 2. Écrivez sql_utile qui ne sélectionne que deux colonnes (id et sale_price). 3. Écrivez sql_limit qui reprend le SELECT * avec une clause LIMIT 10. 4. Observez et comparez les trois estimations d’octets lus.

table = "`bigquery-public-data.thelook_ecommerce.order_items`"

sql_tout = """
TODO
"""

sql_utile = """
TODO
"""

sql_limit = """
TODO
"""

requetes = {
    "SELECT *": sql_tout,
    "Colonnes utiles": sql_utile,
    "Avec LIMIT 10": sql_limit,
}

for nom, sql in requetes.items():
    job = client.query(sql, job_config=DRY_RUN)
    print(f"{nom:<20} {job.total_bytes_processed / 1024**2:.2f} Mio")

Questions : 1. Pourquoi sql_utile lit-elle moins d’octets que sql_tout ? ;

VOTRE RÉPONSE ICI

  1. Pourquoi LIMIT 10 ne réduit-il pas le volume scanné ?

VOTRE RÉPONSE ICI

4. Vérifier la granularité avant de joindre

But : savoir ce que représente une ligne de chaque table, avant de les assembler.

À faire :

  1. Contrôler la granularité de order_item_id
  2. Contrôler la granularité de products
  3. Contrôler les commandes sans produits

Attendu :

# TODO - contrôle 1 : vérifier grain de order_item_id est
client.query("""
TODO
""").to_dataframe()
# TODO - contrôle 2 : grain de products
sql = """
TODO
"""
job = client.query(sql, job_config=DRY_RUN)
print(f"Estimation : {job.total_bytes_processed / 1024**2:.2f} Mio")

client.query(sql).to_dataframe()
# TODO - contrôle 3 : articles sans produit
sql = """
TODO
"""
job = client.query(sql, job_config=DRY_RUN)
print(f"Estimation : {job.total_bytes_processed / 1024**2:.2f} Mio")

client.query(sql).to_dataframe()

Questions

Pourquoi ces contrôles sont importants ?

VOTRE RÉPONSE ICI

5. Calculer le panier moyen par pays pour la catégorie Jeans

sql = """
TODO
"""
job = client.query(sql, job_config=DRY_RUN)
print(f"Estimation : {job.total_bytes_processed / 1024**2:.2f} Mio")

panier_pays = client.query(sql).to_dataframe()
panier_pays.head(10)

Questions 1. Quel pays a le panier moyen le plus élevé pour les Jeans ?

VOTRE RÉPONSE ICI

  1. Pourquoi doit-on diviser par COUNT(DISTINCT order_id) et non par COUNT(*) ?

VOTRE RÉPONSE ICI

6. Segmentation client du premier trimestre 2024

** construire une segmentation client en enchaînant des CTE et une classification par CASE WHEN.**

  1. Écrire une première CTE qui agrège les achats par client sur le premier trimestre 2024 en excluant les statuts Cancelled et Returned en calculant le nombre de commandes distinctes et le montant total dépensé.
  2. Écrire une deuxième CTE qui catégorise chaque client :
    • VIP Fidélisé : montant \(\ge 300\) et au moins 2 commandes ;
    • Panier Élevé : montant \(\ge 150\) ;
    • Client Standard : sinon.
  3. Dans la requête finale, regrouper par segment pour afficher le nombre de clients, le montant total cumulé et la dépense moyenne par client dans chaque segment.
  4. Trier par montant total décroissant.
sql = """
TODO
"""
job = client.query(sql, job_config=DRY_RUN)
print(f"Estimation : {job.total_bytes_processed / 1024**2:.2f} Mio")

segments = client.query(sql).to_dataframe()
segments

Questions 1. Quel segment regroupe le plus de clients, et quel segment génère le plus grand montant d’achats ?

VOTRE RÉPONSE ICI

  1. Quel est l’intérêt d’enchaîner deux CTE plutôt que d’écrire une seule requête avec des sous-requêtes imbriquées ?

VOTRE RÉPONSE ICI

7. Délai de réachat des clients en 2024

Mesurer combien de jours s’écoulent en moyenne entre deux commandes d’un même client.

sql = """
TODO
"""
job = client.query(sql, job_config=DRY_RUN)
print(f"Estimation : {job.total_bytes_processed / 1024**2:.2f} Mio")

reachat = client.query(sql).to_dataframe()
reachat