Trois exercices bonus sur Olist

Pour qui : tu as fini le TP principal et il te reste du temps. Ces trois exercices utilisent le même jeu de données, donc rien de nouveau à installer ni à explorer. Ils ne font pas avancer le programme, ils creusent. Côté référentiel, c'est le BC02 : analyser et interpréter des tendances.

Ouvre Choisir entre WITH, FILTER et OVER quand un des mécanismes suivants te bloque. Les exercices mobilisent les 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., c'est-à-dire des résultats nommés avec WITH et réutilisés dans la requête principale, les agrégatsVocabulaire · agrégatUn calcul qui résume plusieurs lignes en une seule valeur : COUNT, SUM, AVG, MIN, MAX. conditionnels (COUNT(*) FILTER (WHERE ...)) et 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. (un calcul sur un groupe de lignes avec OVER(), sans réduire le nombre de lignes).

Ordre conseillé : 1 → 3 → 2. Le n°2, les 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., demande davantage de mise en place avant de produire un résultat.

Le bac à sable affiche au maximum 200 lignes. Le résultat de la 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. dépasse cette limite : les lignes visibles sont un extrait. Pour contrôler l'effectif complet, remplace temporairement le SELECT final par un comptage.

Noms de colonnes : vérifie-les au moment d'utiliser une table, avec \d nom_de_table dans psqlVocabulaire · psqlL'outil en ligne de commande de PostgreSQL, par opposition à pgAdmin qui est l'interface graphique. ou dans l'arborescence de pgAdminVocabulaire · pgAdminL'interface graphique pour administrer une base PostgreSQL.. Les corrigés supposent orders(order_id, order_customer_id, …), order_payments(payment_order_id, …), customers(customer_id, customer_unique_id, …). Si ton schémaVocabulaire · schémaLa description des tables d'une base : leurs noms, leurs colonnes, et le type de chaque colonne. diffère, adapte, c'est l'exercice.

Le réflexe qui vaut pour les trois : un client s'identifie par customer_unique_id. La colonne customer_id change à chaque commande.

Exercice 1, segmentation RFM

La question métier : « Sur qui faut-il concentrer le budget marketing ? »

La segmentation RFM classe chaque client sur trois axes :

• Récence : depuis combien de jours n'a-t-il pas commandé ?
• Fréquence : combien de commandes a-t-il passées ?
• Montant : combien a-t-il dépensé au total ?

Chaque axe est noté de 1 à 5. NTILE(5) est la window function qui découpe la population en cinq groupes de taille égale, du plus petit au plus grand. La combinaison des trois scores donne des segments, c'est-à-dire des groupes de clients sur lesquels une action marketing peut porter.

Ce que tu rends : une requête qui sort une ligne par client avec ses trois valeurs brutes, ses trois scores, et un libellé de segment. Plus 3 à 5 lignes d'analyse : quel segment cibler en priorité, et pourquoi.

Indices

1. La « date d'aujourd'hui » n'existe pas : le jeu de données s'arrête en 2018. Prends MAX(order_purchase_timestamp) comme date de référence.
2. Construis d'abord une CTE « une ligne par commande avec son montant », puis une CTE « une ligne par client ». Ne saute pas l'étape.
3. Attention au sens du tri dans NTILE. Pour la récence, petit = bon, donc il faut inverser par rapport à la fréquence et au montant.

Le piège : tu vas trouver une fréquence de 1 pour la plupart des clients. Sur l'ensemble des commandes, 96,88 % des customer_unique_id n'en ont qu'une. Ce n'est pas un bug. 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., une plateforme qui met en relation des vendeurs et des acheteurs, et ses clients reviennent peu. Dis-le dans ton analyse au lieu de le cacher. Constater qu'un axe RFM est inexploitable sur un jeu de données est déjà un résultat d'analyste.

WITH commandes AS (
    SELECT c.customer_unique_id,
           o.order_id,
           o.order_purchase_timestamp,
           SUM(oi.price + oi.freight_value) AS montant
    FROM orders o
    JOIN customers c    ON c.customer_id = o.order_customer_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY c.customer_unique_id, o.order_id, o.order_purchase_timestamp
),
reference AS (
    SELECT MAX(order_purchase_timestamp)::date AS date_ref FROM commandes
),
rfm AS (
    SELECT cm.customer_unique_id,
           (SELECT date_ref FROM reference) - MAX(cm.order_purchase_timestamp)::date
                            AS recence_jours,
           COUNT(*)         AS frequence,
           SUM(cm.montant)  AS montant_total
    FROM commandes cm
    GROUP BY cm.customer_unique_id
),
scores AS (
    SELECT rfm.*,
           NTILE(5) OVER (ORDER BY recence_jours DESC) AS score_r,
           NTILE(5) OVER (ORDER BY frequence ASC)      AS score_f,
           NTILE(5) OVER (ORDER BY montant_total ASC)  AS score_m
    FROM rfm
)
SELECT customer_unique_id,
       recence_jours,
       frequence,
       ROUND(montant_total, 2) AS montant_total,
       score_r, score_f, score_m,
       CASE
           WHEN score_r >= 4 AND score_m >= 4 THEN 'Champions'
           WHEN score_r >= 4 AND score_m <= 2 THEN 'Nouveaux / petits paniers'
           WHEN score_r <= 2 AND score_m >= 4 THEN 'Gros clients à risque'
           WHEN score_r <= 2 AND score_m <= 2 THEN 'Perdus'
           ELSE 'À cultiver'
       END AS segment
FROM scores
ORDER BY score_r DESC, score_m DESC;

Exercice 2, cohortes de rétention

La question métier : « Les clients de janvier reviennent-ils plus que ceux de marsVocabulaire · MARMissing At Random : hypothèse où, une fois prises en compte les données observées, la probabilité qu'une valeur manque ne dépend plus des valeurs absentes ; elle peut dépendre de ce qui est observé. Les seules données observées ne permettent pas de confirmer cette hypothèse. ? »

Une analyse de cohortes range chaque client dans le mois de sa première commande, sa cohorte. Elle mesure ensuite combien de ces clients sont encore actifs un mois plus tard, deux mois plus tard, et ainsi de suite. C'est le tableau de référence pour juger si un produit fidélise.

Ce que tu rends : un tableau à une ligne par cohorte, avec l'effectif initial (M0) et le nombre de clients actifs à M+1, M+2, M+3, plus le taux de rétentionVocabulaire · rétentionLa part des clients qui reviennent commander après leur premier achat. à M+1 en pourcentage. Et 3 à 5 lignes d'analyse.

Indices

1. Trois CTE : la cohorte de chaque client (MIN du mois de commande), les mois où chaque client est actif (DISTINCT), puis la jointureVocabulaire · jointureRapprocher deux tables sur une colonne commune, pour lire ensemble des informations qui sont stockées séparément. des deux.
2. date_trunc('month', …) ramène une date au premier jour de son mois.
3. L'écart en mois entre deux dates ne s'obtient pas par soustraction directe : (année_a - année_b) * 12 + (mois_a - mois_b).
4. Le tableau final se construit avec des agrégats conditionnels, une colonne FILTER (WHERE mois_ecart = 1) par période.

Le piège : les dernières cohortes n'ont pas eu le temps d'avoir un M+3. Une case vide ne veut pas dire « 0 % de rétention », elle veut dire « on ne sait pas encore ». Ne présente pas les deux de la même façon.

WITH premieres AS (
    SELECT c.customer_unique_id,
           MIN(date_trunc('month', o.order_purchase_timestamp)) AS cohorte
    FROM orders o
    JOIN customers c ON c.customer_id = o.order_customer_id
    WHERE o.order_status = 'delivered'
    GROUP BY c.customer_unique_id
),
activite AS (
    SELECT DISTINCT
           c.customer_unique_id,
           date_trunc('month', o.order_purchase_timestamp) AS mois
    FROM orders o
    JOIN customers c ON c.customer_id = o.order_customer_id
    WHERE o.order_status = 'delivered'
),
horizon AS (
    SELECT MAX(mois) AS dernier_mois_observe
    FROM activite
),
jointure AS (
    SELECT p.cohorte,
           a.customer_unique_id,
           (EXTRACT(YEAR  FROM a.mois) - EXTRACT(YEAR  FROM p.cohorte)) * 12
         + (EXTRACT(MONTH FROM a.mois) - EXTRACT(MONTH FROM p.cohorte)) AS mois_ecart
    FROM premieres p
    JOIN activite a ON a.customer_unique_id = p.customer_unique_id
)
SELECT cohorte::date AS cohorte,
       COUNT(DISTINCT customer_unique_id) FILTER (WHERE mois_ecart = 0) AS m0,
       CASE WHEN cohorte + INTERVAL '1 month' <= h.dernier_mois_observe
            THEN COUNT(DISTINCT customer_unique_id) FILTER (WHERE mois_ecart = 1)
       END AS m1,
       CASE WHEN cohorte + INTERVAL '2 months' <= h.dernier_mois_observe
            THEN COUNT(DISTINCT customer_unique_id) FILTER (WHERE mois_ecart = 2)
       END AS m2,
       CASE WHEN cohorte + INTERVAL '3 months' <= h.dernier_mois_observe
            THEN COUNT(DISTINCT customer_unique_id) FILTER (WHERE mois_ecart = 3)
       END AS m3,
       CASE WHEN cohorte + INTERVAL '1 month' <= h.dernier_mois_observe
            THEN ROUND(100.0
                 * COUNT(DISTINCT customer_unique_id) FILTER (WHERE mois_ecart = 1)
                 / NULLIF(COUNT(DISTINCT customer_unique_id)
                          FILTER (WHERE mois_ecart = 0), 0), 2)
       END AS retention_m1_pct
FROM jointure
CROSS JOIN horizon h
GROUP BY cohorte, h.dernier_mois_observe
ORDER BY cohorte;

Exercice 3, le poids du transport dans le prix

La question métier : « Sur quelles catégories les frais de port pèsent-ils le plus lourd par rapport au prix du produit, et est-ce que ça devrait changer notre politique de livraison gratuite ? »

Le TP ne pose pas cette question. Elle se traite avec les mêmes outils que la partie 2.

Ce que tu rends : (a) les catégories classées par part du transport dans le prix, avec un seuil de volume que tu choisis et que tu justifies ; (b) le rang de chaque catégorie dans sa tranche de prix, pour comparer le poids relatif des frais de port entre catégories de prix proches. Et 3 à 5 lignes d'analyse.

Indices

1. La part se calcule sur des moyennes : AVG(freight_value) rapporté à AVG(price). La moyenne des rapports donnerait un autre chiffre, et l'écart entre les deux est une bonne question à se poser.
2. Un HAVING COUNT(*) >= n écarte les catégories à faible volume. Le n est ton choix : écris pourquoi.
3. NTILE(4) OVER (ORDER BY AVG(price)) découpe les catégories en quatre tranches de prix, et RANK() OVER (PARTITION BY ...) classe à l'intérieur de chaque tranche.
4. Les catégories sans nom (product_category_name IS NULL) existent : décide de les garder ou non, et dis-le.

Le piège : une catégorie à deux articles peut afficher n'importe quelle part. Sans seuil de volume, ton classement remonte du bruit, et une décision commerciale prise dessus repose sur deux articles.

SELECT p.product_category_name AS categorie,
       COUNT(*) AS articles,
       ROUND(AVG(oi.price), 2) AS prix_moyen,
       ROUND(AVG(oi.freight_value), 2) AS transport_moyen,
       ROUND(AVG(oi.freight_value) * 100 / AVG(oi.price), 1) AS pct_du_prix
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
WHERE p.product_category_name IS NOT NULL
GROUP BY p.product_category_name
HAVING COUNT(*) >= 500
ORDER BY pct_du_prix DESC;

WITH par_categorie AS (
    SELECT p.product_category_name AS categorie,
           COUNT(*) AS articles,
           AVG(oi.price) AS prix_moyen,
           AVG(oi.freight_value) * 100 / AVG(oi.price) AS pct_du_prix
    FROM order_items oi
    JOIN products p ON p.product_id = oi.product_id
    WHERE p.product_category_name IS NOT NULL
    GROUP BY p.product_category_name
    HAVING COUNT(*) >= 500
),
tranches AS (
    SELECT categorie, articles, prix_moyen, pct_du_prix,
           NTILE(4) OVER (ORDER BY prix_moyen) AS tranche_prix
    FROM par_categorie
)
SELECT tranche_prix, categorie,
       ROUND(prix_moyen, 2) AS prix_moyen,
       ROUND(pct_du_prix, 1) AS pct_du_prix,
       RANK() OVER (PARTITION BY tranche_prix ORDER BY pct_du_prix DESC) AS rang
FROM tranches
ORDER BY tranche_prix, rang;

Comment ces exercices sont regardés

Il n'y a pas de note. Ce qu'on regarde, dans l'ordre :

1. La requête est juste : bonne 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., bons filtres, pas de double comptage silencieux.
2. Elle est lisible : des CTE nommées, pas un bloc de 40 lignes imbriquées.
3. L'analyse existe : 3 à 5 lignes qui disent ce que le résultat signifie pour le métier. Une requête sans interprétation reste un travail de développeur. L'analyste ajoute ce que le résultat signifie et ce qu'il permet de décider.

Bloqué plus de 20 minutes sur un point ? Sélectionne le passage concerné, puis utilise « Une question sur ce passage ? ». Décris la requête essayée et le résultat obtenu.