TP : analyse de la marketplace Olist

Module SQL. Le déroulé jour par jour est dans le planning du module. Côté référentiel, ce TP travaille le BC01 (extraction et nettoyage), le BC02 (analyse exploratoire) et, en partie 4, une approche du BC06 (restitution visuelle).

Olist est une marketplaceVocabulaire · marketplaceUne place de marché en ligne : le site ne vend rien lui-même, il met en relation des vendeurs et des acheteurs. brésilienne. Le jeu de données rassemble des commandes, des clients, des vendeurs, des produits, des paiements, des avis et des données de géolocalisation. La première partie te fait mesurer toi-même son volume et sa période.

Ton travail : partir de ces tables brutes et produire une analyse dont un responsable métier pourrait se servir.

Une question a été reformulée depuis la première publication

Cette page fait foi. Si tu as recopié les questions dans les commentaires de tes fichiers, ta copie ne bouge pas quand l'énoncé bouge, et c'est celle que tu lis en travaillant.

La question 3 de la partie 2 a été corrigée deux fois, pour deux défauts différents, signalés par des apprenants.

Elle disait : « Panier moyenVocabulaire · panier moyenLe montant moyen d'une commande, pas le nombre d'articles qu'elle contient. On additionne ce qu'une commande a coûté, puis on fait la moyenne sur l'ensemble des commandes. par commande, et sa distributionVocabulaire · distributionLa façon dont les valeurs d'une colonne se répartissent : combien sont petites, combien sont grandes, et où elles s'entassent. La moyenne et la médiane sont deux résumés d'une distribution, elles ne la décrivent pas. (médianeVocabulaire · médianeLa valeur qui coupe la population en deux parts égales : la moitié des lignes sont en dessous, l'autre moitié au-dessus. Contrairement à la moyenne, une valeur extrême ne la déplace presque pas., min, max). La moyenne est-elle représentative ici ? »

• « Panier moyen » est du vocabulaire de commerce, et il n'était défini nulle part. Il l'est maintenant dans la question.
• « Sa distribution » ne disait pas de quelle population. Une distribution des montants par commande et une distribution par article sont deux choses différentes, et la phrase autorisait la seconde lecture.

Aucune question n'a été ajoutée ni retirée. Les parties 1, 3 et 4 n'ont pas bougé. Si tu as déjà répondu à l'ancienne formulation, relis ta requête avec cette question en tête : tes quatre valeurs sont-elles calculées sur les commandes ou sur les articles ? Les deux réponses diffèrent.

La règle du jeu

Toute la préparation, la transformation et le calcul se font en SQL. Python n'intervient qu'à la fin, pour tracer les graphiques.

Cette contrainte simule le cas réel où les données sont trop volumineuses pour tenir en mémoire. Concrètement, chaque requête qui alimente un graphique doit renvoyer au plus quelques dizaines de lignes. Si tu charges 100 000 lignes dans un DataFrameVocabulaire · DataFrameLa table en mémoire du package Python pandas, avec des lignes et des colonnes, comme une feuille de tableur mais manipulable en code. (le tableau en mémoire de la bibliothèque pandas) pour faire ensuite un groupby en pandas, l'exercice est raté, même si le graphique est joli.

La fiche Choisir entre WITH, FILTER et OVER est un aide-mémoire. Tu n'en as pas besoin en entier tout de suite :

Ouvre-la quand tu bloques. Si tu passes plus de vingt minutes sur une notion sans l'appliquer, arrête de lire et va écrire du SQL. Tu y reviendras avec une question précise.

Récupérer ton dépôt, et git

Ton travail est rendu sur un dépôt GitHub personnel, créé depuis ton espace personnel avec le bouton « Récupérer mon dépôt ». Il contient déjà la structure de rendu et les scripts de mise en place.

Git fait partie du titre, et ce TP est l'occasion de le pratiquer. Il n'est pas évalué dans ce module : ce qui compte est que ton travail soit poussé. Trois commandes suffisent. Si l'une d'elles renvoie une erreur, copie-la dans le formulaire de question de cette fiche, avec la commande exécutée. Tu recevras une aide à partir de ces deux éléments.

git clone <l'adresse de ton dépôt>   # une fois, au début
git add -A && git commit -m "partie 1"   # à chaque fin de demi-journée
git push                                  # juste après le commit

Le message de commit n'a pas besoin d'être élaboré : « partie 1 », « début partie 2 » suffisent.

Si Git te bloque plus de dix minutes, demande en classe ou pose une question depuis cette fiche. Donne la commande exécutée et son message exact.

Si Git ne passe pas, tu rends quand même. Préviens ton formateur ou prof404 et joins tes fichiers dans l'échange Discord. Ce qui est évalué est ton SQL, pas le dépannage de Git.

Mise en place

Ce que contient ton dépôt. Le bouton « Récupérer mon dépôt » de ton espace personnel te crée tp-sql-olist-<ton login> avec cette arborescence :

setup/01_creation_tables.sql      le schéma, à exécuter en premier
setup/02_chargement_donnees.sql   le chargement des CSV
sql/01_controle_qualite.sql       partie 1, à remplir
sql/02_analyse_commerciale.sql    partie 2, à remplir
sql/03_livraison_satisfaction.sql partie 3, à remplir
notebook/visualisations.ipynb     partie 4
SYNTHESE.md                       la synthèse d'une page

Les CSV ne sont pas dans le dépôt : ils pèsent trop lourd pour git. Tu les récupères auprès de l'équipe pédagogique, et tu les déposes dans un dossier data/ à la racine de ton dépôt. Le .gitignore est déjà réglé pour ne pas les versionner.

1. Créer les tables : exécute setup/01_creation_tables.sql.

2. Charger les données : le chargement utilise \copy, une commande de psql exécutée côté client. À ne pas confondre avec COPY, exécutée par le serveur, qui exige que les fichiers soient sur la machine du serveur.

   Ouvre setup/02_chargement_donnees.sql et remplace chaque chemin par le chemin réel vers ton dossier data/. Sous pgAdminVocabulaire · pgAdminL'interface graphique pour administrer une base PostgreSQL. : clic droit sur ta base, « PSQLVocabulaire · psqlL'outil en ligne de commande de PostgreSQL, par opposition à pgAdmin qui est l'interface graphique. Tool », puis :

   \i 'chemin/vers/setup/02_chargement_donnees.sql'

   Sous macOS ou Linux, psql -U <user> -d <base> -f setup/02_chargement_donnees.sql fait la même chose. Les chemins s'écrivent alors avec des /.

   ⚠️ \d et \copy sont des commandes de psql, pas du SQL. Elles marchent dans le terminal psql et dans l'outil « PSQL Tool » de pgAdmin. Elles ne marchent ni dans une fenêtre de requête ordinaire, ni dans le bac à sable. L'équivalent SQL de \d est une requête sur information_schema.columns.

3. Vérifier le chargement. Tu dois retrouver exactement ces volumes. Si un compte ne tombe pas juste, corrige maintenant : tout le reste du TP en dépend.

   Table	Lignes attendues
   geolocation	1 000 163
   order_items	112 650
   order_payments	103 886
   customers	99 441
   orders	99 441
   order_reviews	99 224
   products	32 951
   sellers	3 095

Un fichier product_category_name_translation.csv est fourni en plus. Il traduit les catégories de produits du portugais vers l'anglais. À toi de voir si et comment tu t'en sers : il n'a volontairement pas de table dans le script de création.

Ces données concernent des personnes

customers contient un identifiant client, une ville et un préfixe de code postal. geolocation contient des coordonnées. Ce sont des données à caractère personnel, même sans nom ni adresse : un identifiant qui suit quelqu'un dans le temps en est une, et un point géographique précis aussi.

Trois conséquences pour ce TP. Elles sont au barème du titre.

1. Ne travaille qu'au niveau agrégé dès que tu publies un résultat. Une ligne par client dans ta synthèse n'apporte rien qu'un total n'apporte pas.
2. Ne recopie pas d'identifiant client dans la synthèse ni dans un graphique.
3. Dis-le dans ta synthèse, en une phrase : quelles colonnes personnelles tu as manipulées, et ce que tu en as fait. Un jury pose cette question.

Ce jeu de données est public et anonymisé par son éditeur. Le risque est faible ici. L'habitude de se poser la question, elle, se prend maintenant.

Quatre contrôles à faire au moment où tu en as besoin

Chacun coûte du temps quand on le découvre au milieu de la partie 3.

1. Le schémaVocabulaire · schémaLa description des tables d'une base : leurs noms, leurs colonnes, et le type de chaque colonne. réel fait foi. La description des tables plus bas vient de la documentation d'origine, et elle ne correspond pas toujours au schéma réel. Deux écarts connus :

Prends le réflexe maintenant : avant d'écrire une requête sur une table, regarde ce qu'elle contient vraiment.

\d order_payments

Ce décalage entre documentation et schéma est courant en entreprise. Le réflexe de vérifier fait partie de ce qu'on évalue.

2. Un client s'identifie par customer_unique_id. Dans ce jeu de données, customer_id est propre à une commande : le même individu qui commande trois fois apparaît avec trois customer_id différents et un seul customer_unique_id. Confondre les deux ne provoque aucune erreur et donne des résultats faux.

3. Attention à la granularitéVocabulaire · granularitéLe niveau de détail d'une ligne de table : une ligne par commande, ou une ligne par article de la commande, ce n'est pas la même chose et ça change tous les comptes., c'est-à-dire à ce qu'une ligne représente. Une commande de trois articles occupe trois lignes dans order_items. Après une jointureVocabulaire · jointureRapprocher deux tables sur une colonne commune, pour lire ensemble des informations qui sont stockées séparément., compter les lignes surcompte les commandes. Demande-toi à chaque requête : une ligne de mon résultat, ça représente quoi ?

4. Les montants sont en réaux brésiliens. Olist est une marketplace brésilienne : price, freight_value (les frais de port) et payment_value sont en BRL, pas en euros. Aucune colonne ne le dit, et le jeu de données ne porte nulle part de symbole de devise.

Un chiffre d'affaires annoncé en euros alors qu'il est en réaux ne se corrige pas en relisant la requête. La requête est juste. L'erreur est dans la phrase qui l'accompagne, et le lecteur n'a aucun moyen de la repérer. Écris l'unité à côté de chaque montant que tu communiques. Un nombre sans son unité n'est pas un résultat.

Partie 1 : contrôle qualité des données

Avant d'analyser, il faut savoir ce qu'on manipule. Cette partie rend crédible tout ce qui suit.

Si tu ne sais pas par où entrer, la section « Le chemin minimum » de la fiche Du SQL applicatif au SQL d'analyse donne huit formes de requête, avec la question à laquelle chacune sert. Deux choses leur manquent : le pourcentage de la question 2, qui a besoin du total que ton GROUP BY ne donne pas, et le décompte des questions 5 et 6, qui demande d'envelopper un résultat. Les sections « Donner un nom au résultat avec WITH » et « Utiliser un total dans un pourcentage » de Écrire une requête avec WITH montrent ces deux gestes. Les window functionsVocabulaire · window functionUne fonction qui calcule un agrégat en gardant toutes les lignes du résultat, au lieu de les écraser comme le fait un GROUP BY. ne servent qu'à partir de la partie 2.

1. Compte les lignes de chaque table et vérifie la période réellement couverte par les commandes (première et dernière date d'achat).
2. Donne la répartition des commandes par order_status, en valeur absolue et en pourcentage.
3. Recense les valeurs manquantes sur les dates de orders : order_approved_at, order_delivered_carrier_date, order_delivered_customer_date. Combien pour chacune ?
4. Cherche les incohérences. Existe-t-il des commandes au statut delivered sans date de livraison client ? Combien ? Une date de livraison antérieure à la date d'achat ? Une commande approuvée avant d'être passée ?
5. order_reviews : le champ review_id est-il réellement unique ? Et order_id ? Quantifie.
6. order_payments : combien de commandes ont plusieurs lignes de paiement, et pourquoi (regarde payment_sequential et payment_type) ?

Livrable de la partie 1 : une note de nettoyage. Pour chaque anomalie trouvée, écris ce que tu décides (exclure, conserver, corriger) et pourquoi. Cette note est lue en priorité, avant la liste des anomalies.

Il n'y a pas une seule bonne réponse. Exclure les commandes non livrées d'un calcul de chiffre d'affaires est défendable. Les garder en le disant l'est aussi. Ne pas savoir lesquelles sont dans ton calcul ne l'est pas.

Partie 2 : analyse commerciale

Sauf mention contraire, justifie dans un commentaire SQL le périmètre que tu retiens, c'est-à-dire quels statuts de commande tu comptes, et pourquoi.

1. Chiffre d'affaires total. Précise ce que tu inclus : price seul, ou price + freight_value ?

2. Chiffre d'affaires par mois, avec l'évolution en pourcentage par rapport au mois précédent. Que remarques-tu sur le premier et le dernier mois de la période ?

3. Le montant total de chaque commande, ce qu'on appelle son panierVocabulaire · panierCe qu'une commande contient et ce qu'elle coûte. Parler du « panier » d'une commande désigne presque toujours son montant total.. Puis, sur l'ensemble des commandes : le panier moyen, la médiane, le minimum et le maximum. La moyenne est-elle représentative ici ?

   La question se fait en deux temps : une ligne par commande avec son total, puis les quatre valeurs calculées sur cette population de commandes, pas sur les articles.

4. Top 10 des catégories de produits par chiffre d'affaires. Ajoute le nombre de commandes et le panier moyen de chaque catégorie.

5. Répartition du chiffre d'affaires par État client. Combien d'États représentent la moitié du chiffre d'affaires total ?

6. Moyens de paiement : répartition du nombre de transactions et du montant par payment_type. Le paiement en plusieurs fois est-il corrélé à un panier plus élevé ?

Partie 3 : livraison et satisfaction

Cette partie produit le résultat métier du TP. Prends le temps.

Pour les questions 1 et 2, ouvre Choisir entre une durée et des jours calendaires au moment où tu dois choisir entre une comparaison de dates et une comparaison d'horodatages. Les deux calculs répondent à des définitions différentes.

Pour la question 3, tu auras besoin de fabriquer une colonne de tranches à partir d'un nombre, puis de grouper dessus. La forme est un CASE à plusieurs branches, comme celui de la section Construire des tranches avec CASE, placé dans une CTEVocabulaire · CTECommon Table Expression : un résultat intermédiaire auquel on donne un nom avec WITH, pour s'en servir ensuite comme d'une table. et utilisé ensuite comme clé de GROUP BY.

1. Délai de livraison réel, en jours, entre l'achat et la livraison client : moyenne, médiane, et distribution.
2. Compare la date de livraison à la date estimée annoncée au client. Quelle proportion des commandes arrive en retard ? Et en avance ?
3. Croise le retard et la note de satisfaction. Note moyenne selon que la commande est arrivée en avance, le jour promis, ou en retard. Découpe en tranches, par exemple ces cinq : plus de 7 jours d'avance, de 1 à 7 jours d'avance, le jour promis, de 1 à 7 jours de retard, plus de 7 jours de retard. Le découpage est à toi, dis lequel tu retiens. C'est le résultat que la synthèse doit porter.
4. Refais l'analyse du retard par État vendeur. Écarte les États à faible volume avant de classer, et explique le seuil que tu as choisi.
5. Identifie les livraisons anormalement lentes par rapport à la moyenne de leur État. À toi de définir « anormalement » et de justifier ton critère.

Partie 4 : visualisations en Python

Pour chaque graphique, la requête SQL fait tout le calcul et le DataFrame reçoit un résultat déjà agrégé.

Obligatoires :

1. L'évolution mensuelle du chiffre d'affaires.
2. La relation entre retard de livraison et note moyenne (le résultat de la question 3.3).

Si tu as le temps :

3. Le top 10 des catégories.
4. La distribution des délais de livraison.

Deux graphiques justes, lisibles et légendés valent mieux que quatre bâclés.

Le choix du graphique fait partie de l'exercice. Pour chacun, écris en commentaire une phrase qui dit pourquoi cette forme-là :

• Une évolution dans le temps appelle une courbe, qui montre la continuité. Un histogrammeVocabulaire · histogrammeUn graphique qui découpe une série de nombres en tranches et montre combien de valeurs tombent dans chacune. C'est la forme la plus directe pour lire une distribution. sur les mêmes données fait chercher la tendance.
• Une comparaison entre catégories appelle des barres, triées par valeur et pas par ordre alphabétique. Un camembert au-delà de cinq parts devient illisible.
• Une distribution appelle un histogramme ou une boîte à moustachesVocabulaire · boîte à moustachesUn graphique qui montre, sur une même série de nombres, la médiane et l'étalement des valeurs autour d'elle, y compris les valeurs extrêmes, sans passer par un histogramme.. Ici l'information est dans l'étalement des valeurs, que la moyenne ne montre pas.

Trois règles d'accessibilité. Elles sont au barème du titre.

1. Ne fais pas porter l'information par la seule couleur. Environ un homme sur douze et une femme sur deux cents distinguent mal le rouge du vert. Ajoute une étiquette, un motif, ou un ordre qui se lit sans les couleurs.
2. Les axes portent un nom et une unité. « R$ », « jours », « nombre de commandes ». Un axe nu oblige à deviner.
3. Le titre énonce le constat. « Dix États concentrent la majorité des vendeurs » apporte une information que « Vendeurs par État » ne donne pas. Vérifie toujours le constat avant de l'écrire dans un titre : un titre est une affirmation, et c'est celle qu'on retient du graphique.

Pour chaque graphique, indique en commentaire le nombre de lignes renvoyées par ta requête. Si l'un dépasse quelques dizaines de lignes, le calcul n'est pas au bon endroit.

Le notebook notebook/visualisations.ipynb de ton dépôt montre comment exécuter du SQL depuis Python. Tu n'as pas à partir de zéro, le motif est toujours le même :

from sqlalchemy import create_engine
import pandas as pd

# Reprends les paramètres de connexion du notebook fourni.
engine = create_engine(
    "postgresql+psycopg://ton_user:ton_mdp@localhost:5432/olist"
)

# Exemple voisin du TP : compter les vendeurs par État.
# Pour ton graphique évalué, remplace cette requête par la tienne.
requete = """
    SELECT seller_state AS etat,
           COUNT(*) AS vendeurs
    FROM sellers
    GROUP BY seller_state
    ORDER BY vendeurs DESC
    LIMIT 10
"""

df = pd.read_sql(requete, engine)
print(len(df), "lignes")

ax = df.plot.bar(x="etat", y="vendeurs", legend=False)
ax.set_title("Les dix États qui comptent le plus de vendeurs")
ax.set_ylabel("Nombre de vendeurs")
ax.set_xlabel("État du vendeur")

ton_user et ton_mdp sont à remplacer par les tiens. Ce sont des caractères comme les autres, rien ne les remplace tout seul. Si tu oublies, PostgreSQL répond FATAL: authentification par mot de passe échouée pour l'utilisateur « ton_user », et il te donne ainsi le nom exact de ce qu'il a reçu. Quand une erreur de connexion cite une valeur, demande-toi d'où elle vient avant de toucher au serveur.

Les deux paquets à installer sont sqlalchemy et psycopg (version 3, sans le « 2 ») : pip install sqlalchemy "psycopg[binary]" pandas matplotlib. Les guillemets sont nécessaires sous zsh, le shell par défaut de macOS, qui interprète les crochets. Le notebook de ton dépôt utilise exactement cette combinaison. Ne mélange pas avec psycopg2, qui est l'ancienne bibliothèque et se connecte autrement.

Remplace ensuite la requête d'exemple par une requête que tu as écrite pour la partie 2 ou 3, puis adapte les noms passés à x et y. Si print(len(df)) affiche des milliers de lignes, retourne dans le SQL : le DataFrame doit recevoir le résultat agrégé, pas les lignes brutes.

Livrables

1. Les trois fichiers sql/01, sql/02 et sql/03, commentés, chacun s'exécutant du début à la fin sans erreur. Une requête sans commentaire expliquant ce qu'elle cherche est une requête à moitié faite.
2. Le notebook notebook/visualisations.ipynb avec au moins les deux graphiques obligatoires.
3. Une synthèse d'une page destinée à un responsable métier : les trois ou quatre conclusions que tu retiens, chacune appuyée sur un chiffre. Pas de SQL dans ce document.

Tout est rendu sur ton dépôt GitHub du TP. Si git te bloque, dépose les fichiers en pièce jointe dans ton échange Discord avec prof404 ou ton formateur : c'est la voie de secours, pour que le problème Git ne bloque pas ton rendu.

Comment c'est évalué

Le critère d'interprétation distingue un travail d'analyste d'un travail de développeur. Une requête juste sans interprétation vaut la moitié des points.

Le parcours minimum

Le temps est compté. Si tu vois que tu ne traiteras pas tout, ne réponds pas à moitié à quinze questions : traite complètement celles qui portent le sens du TP. C'est un choix légitime. Un travail cohérent et incomplet est mieux noté qu'un travail complet et faux.

Le minimum qui constitue un rendu défendable :

Ce que tu peux laisser de côté sans que ça pénalise ton rendu : les questions 2.3, 2.5, 2.6, 3.4, 3.5, et les graphiques 3 et 4.

Dis-le dans ta synthèse. Une phrase du type « je n'ai pas traité la répartition géographique faute de temps » est une information honnête. C'est ce qu'on attend d'un analyste qui rend un travail en cours. Un manque passé sous silence se voit.

Si tu suis ce parcours et qu'il te reste du temps, reviens sur les questions laissées de côté plutôt que d'attaquer les exercices bonus.

Si tu finis en avance

Ne prends pas d'avance sur le programme. Creuse le même jeu de données avec les exercices bonus : segmentation RFMVocabulaire · RFMRécence, Fréquence, Montant : une façon classique de classer les clients selon la date de leur dernier achat, leur nombre d'achats et ce qu'ils dépensent., cohortesVocabulaire · cohorteUn groupe de clients rassemblés par leur date d'entrée, par exemple tous ceux qui ont commandé pour la première fois en mars, qu'on suit ensuite dans le temps. de rétentionVocabulaire · rétentionLa part des clients qui reviennent commander après leur premier achat., poids des frais de port.

Bloqué ?

Plus de vingt minutes sur le même point : sélectionne le passage concerné puis utilise « Une question sur ce passage ? ». Pour un problème général, écris dans le formulaire de Mon espace. L'équipe pédagogique retrouve la question et sa réponse au même endroit.

Précise toujours ce que tu as déjà essayé et le message d'erreur exact. Une question précise obtient une réponse précise.