Expliquer les ventes d'une boutique en ligne

Ce que vous saurez faire

Transformez un export d’un million de lignes de facture d’une boutique en ligne réelle en une petite base de données fiable. Vous repérez les pièges de l’export, créez quatre tables avec des clés et rapprochez trois totaux de revenus différents. Vous répondez ensuite à trois questions de planification avec SQL. Quand la saison de Noël commence-t-elle? Qu’est-ce qui explique les pics de retours? Les nouveaux clients reviennent-ils? À la fin, vous livrez une table vérifiée pour les rapports mensuels, avec une note de provenance.

Étude de casPièce de portfolio

Jeu de données

Online Retail II, Royaume-Uni, 2009–2011 (UCI)

Licence

CC BY 4.0

Durée

Environ 2,5 heures

S’exécute dans

Colab, Kaggle ou VS Code

Compétences utilisées

• Profiler un export
• Clés et vérification des lignes orphelines
• Rapprocher les totaux
• Fonctions de fenêtre (LAG, ROW_NUMBER, RANK)
• Cohortes avec des CTE
• Validation et provenance

Ouvrir dans ColabOuvrir dans KaggleVoir sur GitHub

Les carnets sont en anglais. Colab et Kaggle ouvrent votre propre copie. Pour utiliser VS Code, téléchargez le carnet depuis GitHub. Vous êtes bloqué ? Consultez le carnet de solution.

Le carnet (notebook) Jupyter contient toutes les requêtes de cette page, dans le même ordre, avec trois tâches et leurs vérifications. Ses cellules de préparation installent pandas et téléchargent les données (15 Mo). Aucune préparation locale n’est donc nécessaire. Il s’exécute en environ une minute sur un ordinateur portable. La base de données est trop grande pour les zones SQL du navigateur. Cette étude de cas s’exécute donc uniquement dans le carnet. Lisez les explications ici et exécutez le code dans le carnet.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 150 minutes

Ce que vous devez savoir

• La granularité, les clés et les lignes orphelines, dans Les éléments d’un jeu de données et Les concepts relationnels.
• Pourquoi une jointure peut ajouter des lignes, dans Joindre des tables : la jointure ajoute des lignes.
• CASE dans une agrégation, dans Agréger correctement.
• Les CTE, dans Des requêtes lisibles, puis les dates, LAG, ROW_NUMBER et RANK, dans Le temps et le classement.
• Les règles sous forme de requêtes qui ne doivent renvoyer aucune ligne, dans Les règles de validation, et la provenance d’un jeu de données, dans Suivre la provenance des données.

Ce qu’il vous faut sur votre ordinateur (uniquement pour l’exécution locale; Colab et Kaggle n’exigent rien de cela)

• Python 3.12 ou une version ultérieure, avec pandas 3.0.6. Le .venv de votre projet de Transformer avec Python le contient. La première cellule du carnet l’installe s’il manque.
• Un nouveau dossier vide pour cette étude de cas, par exemple ~/projects/case-studies. Ouvrez le carnet dans ce dossier. Sa cellule de préparation crée un dossier explain-an-online-shops-sales et y télécharge les données. Le carnet crée ensuite shop.db (environ 100 Mo) dans ce dossier. Pour recommencer, supprimez le dossier et exécutez le carnet depuis le début.

Testé sur macOS avec Python 3.14.6 (SQLite 3.50.4) et pandas 3.0.6, dans Jupyter. Les fonctions de fenêtre exigent SQLite 3.25 ou une version ultérieure. Windows, Colab et Kaggle n’ont pas été testés.

La situation

Une boutique en ligne du Royaume-Uni vend des cadeaux et des articles pour la maison. Beaucoup de ses clients sont de petites boutiques qui achètent pour revendre. Nous sommes en décembre 2011. Deux personnes cherchent des réponses dans les données de vente.

Ines, responsable du marchandisage, prépare le prochain Noël. La boutique achète son stock de Noël auprès de fournisseurs plusieurs mois à l’avance. Si elle commande trop peu, les étagères sont vides pendant les meilleures semaines de l’année. Si elle commande trop, la boutique paie pour du stock que personne n’achète. Elle demande : « Quand notre saison de Noël commence-t-elle vraiment? Quelle est son importance, et est-elle en croissance? Quels produits se vendent le mieux dans chaque pays? »

Kwame, analyste financier, a trois problèmes. Trois rapports donnent trois totaux de revenus différents pour les deux mêmes années. Il ne peut pas dire lequel est juste. Le conseil d’administration a vu un taux de retour de 13,6 % en janvier 2011 et veut une politique de retour plus stricte. Et un tableau de bord indique que les nouveaux clients reviennent de moins en moins chaque mois. Il demande : « À quels chiffres puis-je me fier, et que disent-ils vraiment? »

Ines, Kwame et leurs questions sont inventés pour cette étude de cas. Les données sont réelles : ce sont les données de facturation de la boutique, publiées pour la recherche.

Ce que vous allez créer

Vous allez créer une base de données pour les rapports. Elle contient les ventes de la boutique dans quatre tables avec des clés. Elle comprend aussi les vérifications de leur exhaustivité et une vue des lignes de vente. À partir de cette base, vous livrez monthly_sales, une table avec une ligne par mois que l’équipe des finances peut utiliser. Vous l’accompagnez de six règles de validation et d’une note de provenance. En chemin, vous répondez à chaque question d’Ines et de Kwame avec une requête et son résultat réel.

L’étude de cas utilise les compétences suivantes du cours :

• la granularité d’une table, les clés et les lignes orphelines (Les concepts relationnels);
• les jointures qui ajoutent des lignes sans erreur (Joindre des tables);
• les CTE qui divisent une longue requête en étapes (Des requêtes lisibles);
• les dates, LAG, ROW_NUMBER et RANK (Le temps et le classement);
• un profil avant toute modification (Profiler avant de modifier);
• les règles de validation et la provenance (Les règles de validation, Suivre la provenance des données).

L’étude de cas Segmenter les clients d’une boutique en ligne, dans Apprentissage automatique : du problème à un modèle fiable, utilise les mêmes données pour regrouper les clients par apprentissage automatique. Dans un projet réel, la présente étude de cas vient en premier : elle rend les chiffres fiables.

Les données

Les données proviennent d’Online Retail II, du UCI Machine Learning Repository. Elles contiennent chaque ligne de facture de la boutique du 1 décembre 2009 au 9 décembre 2011. Daqing Chen les a publiées sous la licence Creative Commons Attribution 4.0 (CC BY 4.0). Cette licence permet leur réutilisation avec mention de la source.

Chen, D. (2012). Online Retail II [Jeu de données]. UCI Machine Learning Repository. https://doi.org/10.24432/C5CG6D. CC BY 4.0.

Le cours conserve une copie fixe dans son dépôt d’exercices, avec une somme de contrôle SHA-256. La source est un fichier Excel avec deux feuilles, une par année. Le cours les a réunies, dans l’ordre, en un fichier CSV compressé, sans rien changer d’autre. La fiche du jeu de données décrit cette opération, comme l’exige la licence.

Une ligne du fichier correspond à une ligne de facture : un produit sur une facture. Voici les colonnes, avec les noms que cette étude de cas leur donne dans SQLite :

Colonne	Signification
invoice	Le numéro de facture. Un numéro qui commence par C est un retour (une annulation). Un numéro qui commence par A est un ajustement comptable.
stock_code	Le code du produit, généralement cinq chiffres, parfois avec une lettre (85123A).
description	Le nom du produit.
quantity	Le nombre d’unités sur la ligne. Négatif pour un retour.
invoice_date	La date et l’heure de la facture, sous forme de texte YYYY-MM-DD HH:MM:SS (année-mois-jour, heures:minutes:secondes).
price	Le prix d’une unité, en livres (£).
customer_id	Le numéro du client. Vide pour certaines lignes.
country	Le pays du client.

Charger l’export dans SQLite

L’export est une seule grande table. Avant de modifier quoi que ce soit, vous le chargez tel quel. Vous pourrez ainsi retracer chaque chiffre calculé par la suite. pandas lit le fichier, et sa méthode to_sql écrit la table dans une nouvelle base de données SQLite. Le code ajoute une colonne, line_no : la position de la ligne dans le fichier. Le fichier n’a pas d’identifiant de ligne; line_no devient cet identifiant.

import sqlite3
from pathlib import Path

import pandas as pd

raw = pd.read_csv("data/online_retail_ii.csv.gz",
                  dtype={"Invoice": str, "StockCode": str, "Customer ID": "Int64"})
raw.columns = ["invoice", "stock_code", "description", "quantity",
               "invoice_date", "price", "customer_id", "country"]
raw.insert(0, "line_no", range(1, len(raw) + 1))  # the line's position in the file

Path("shop.db").unlink(missing_ok=True)  # start from an empty database
con = sqlite3.connect("shop.db")
raw.to_sql("raw_lines", con, index=False)
print(f"raw_lines: {len(raw):,} lines, {raw.shape[1]} columns")

raw_lines: 1,067,371 lines, 9 columns

Ce que cela nous apprend. La table raw_lines contient les 1 067 371 lignes du fichier, sans modification. Le code lit invoice et stock_code comme du texte, car certaines valeurs contiennent des lettres. Int64 conserve le numéro du client comme un entier, avec NULL pour les valeurs vides. Désormais, chaque requête de cette étude de cas est en SQL. Dans le carnet, sql(query) exécute une requête et affiche le résultat, comme q.py dans le cours.

Examinez maintenant la taille de l’export :

SELECT COUNT(*)                   AS lines,
       COUNT(DISTINCT invoice)    AS invoices,
       COUNT(DISTINCT stock_code) AS stock_codes,
       COUNT(DISTINCT customer_id) AS customers,
       MIN(invoice_date)          AS first_line,
       MAX(invoice_date)          AS last_line
FROM raw_lines

lines    invoices  stock_codes  customers  first_line           last_line
-------  --------  -----------  ---------  -------------------  -------------------
1067371  53628     5305         5942       2009-12-01 07:45:00  2011-12-09 12:50:00
1 row

Puis les quatre premières lignes :

SELECT line_no, invoice, stock_code, description, quantity,
       invoice_date, price, customer_id, country
FROM raw_lines
WHERE line_no <= 4

line_no  invoice  stock_code  description                          quantity  invoice_date         price  customer_id  country
-------  -------  ----------  -----------------------------------  --------  -------------------  -----  -----------  --------------
1        489434   85048       15CM CHRISTMAS GLASS BALL 20 LIGHTS  12        2009-12-01 07:45:00  6.95   13085        United Kingdom
2        489434   79323P      PINK CHERRY LIGHTS                   12        2009-12-01 07:45:00  6.75   13085        United Kingdom
3        489434   79323W       WHITE CHERRY LIGHTS                 12        2009-12-01 07:45:00  6.75   13085        United Kingdom
4        489434   22041       RECORD FRAME 7" SINGLE SIZE          48        2009-12-01 07:45:00  2.1    13085        United Kingdom
4 rows

Ce que cela nous apprend. L’export couvre deux ans et neuf jours : 53 628 factures, 5 305 codes de produit et 5 942 clients. Les quatre premières lignes appartiennent à une même facture, 489434. Le client 13 085 a acheté quatre produits à 7 h 45 le 1 décembre 2009. La granularité de l’export est donc une ligne de facture. Les faits concernant la facture, comme l’heure, le client et le pays, se répètent sur chaque ligne. La ligne 3 montre déjà une petite surprise : sa description commence par une espace.

Définir les questions

Chaque question exige une unité claire et une définition claire. Sinon, deux personnes calculeront deux réponses différentes.

• Revenus. Les rapports de Kwame doivent compter la même chose. Ici, les revenus correspondent aux ventes nettes de marchandises. C’est la valeur des marchandises vendues, moins celle des marchandises retournées, en livres. Les frais de livraison, les frais divers et les ajustements comptables représentent de l’argent réel. Mais ce ne sont pas des ventes de produits. Vous prenez cette décision avant d’écrire la requête.
• L’unité de chaque réponse. Les ventes et les retours mensuels utilisent une ligne par mois. La question de fidélisation utilise une ligne par client, et seulement les clients avec un identifiant. Une ligne sans client reste une vente. Elle compte donc dans les revenus, mais pas dans le nombre de clients.
• Périodes complètes. Les données s’arrêtent au 9 décembre 2011. Ne comparez pas un mois de neuf jours avec un mois complet. La même règle s’applique au début : la boutique vendait avant décembre 2009, mais les données ne le montrent pas.
• Identifiants des clients. Ce sont des numéros attribués par la boutique. Il n’y a ni noms ni adresses. N’essayez pas de découvrir l’identité d’un client.

Premières vérifications : repérer les pièges

Un profil compte les problèmes avant toute modification, comme dans Profiler avant de modifier. Chaque requête ci-dessous pose une question.

SELECT COUNT(*)                                          AS lines,
       SUM(customer_id IS NULL)                          AS no_customer,
       ROUND(100.0 * SUM(customer_id IS NULL) / COUNT(*), 1) AS pct_no_customer,
       SUM(invoice LIKE 'C%')                            AS return_lines,
       SUM(invoice LIKE 'A%')                            AS adjustment_lines,
       SUM(price <= 0)                                   AS price_0_or_below
FROM raw_lines

lines    no_customer  pct_no_customer  return_lines  adjustment_lines  price_0_or_below
-------  -----------  ---------------  ------------  ----------------  ----------------
1067371  243007       22.8             19494         6                 6207
1 row

Ce que cela nous apprend. 22,8 % des lignes n’ont pas d’identifiant de client. Il s’agit probablement de visiteurs qui ne se sont pas inscrits, et leurs achats sont de vraies ventes. Il y a 19 494 lignes de retour et 6 lignes d’ajustement. Il y a aussi 6 207 lignes dont le prix est de 0 ou moins. Rien de tout cela n’est encore une erreur; chaque cas exige une décision.

Les lignes répétées

Cherchez ensuite les lignes identiques dans toutes les colonnes :

WITH repeated AS (
    SELECT invoice_date, COUNT(*) AS copies
    FROM raw_lines
    GROUP BY invoice, stock_code, description, quantity,
             invoice_date, price, customer_id, country
    HAVING COUNT(*) > 1
)
SELECT COUNT(*)        AS groups,
       SUM(copies - 1) AS extra_lines,
       SUM(CASE WHEN invoice_date >= '2010-12-01' AND invoice_date < '2010-12-10'
                THEN copies - 1 ELSE 0 END) AS extra_lines_1_to_9_dec_2010
FROM repeated

groups  extra_lines  extra_lines_1_to_9_dec_2010
------  -----------  ---------------------------
32907   34335        22844
1 row

Ce que cela nous apprend. 32 907 lignes apparaissent plus d’une fois, ce qui donne 34 335 lignes supplémentaires. Les deux tiers des lignes supplémentaires (22 844) proviennent de neuf jours, du 1 au 9 décembre 2010. Des erreurs ne se concentrent pas sur neuf jours par hasard. Rappelez-vous comment le cours a créé le fichier : une feuille Excel par année, réunies dans l’ordre. Si les deux feuilles contiennent ces neuf jours, le fichier les contient deux fois.

Vous pouvez tester cette idée avec une fonction de fenêtre. Les lignes sont en ordre chronologique dans chaque feuille. Le temps devrait donc reculer une seule fois dans le fichier : au début de la deuxième feuille. LAG(invoice_date) OVER (ORDER BY line_no) donne à chaque ligne l’heure de la ligne précédente, dans l’ordre du fichier :

WITH in_file_order AS (
    SELECT line_no, invoice, invoice_date,
           LAG(invoice_date) OVER (ORDER BY line_no) AS previous_date
    FROM raw_lines
)
SELECT line_no, invoice, previous_date, invoice_date
FROM in_file_order
WHERE invoice_date < previous_date

line_no  invoice  previous_date        invoice_date
-------  -------  -------------------  -------------------
525462   536365   2010-12-09 20:01:00  2010-12-01 08:26:00
1 row

Ce que cela nous apprend. Sur plus d’un million de lignes, le temps recule exactement une fois : à la ligne 525 462. Il passe du 9 décembre 2010 au 1 décembre 2010. C’est là que commence la deuxième feuille. Comparez maintenant les neuf jours des deux feuilles, ligne par ligne :

WITH nine_days AS (
    SELECT *, CASE WHEN line_no < 525462 THEN 1 ELSE 2 END AS sheet
    FROM raw_lines
    WHERE invoice_date >= '2010-12-01' AND invoice_date < '2010-12-10'
),
per_line AS (
    SELECT SUM(sheet = 1) AS in_sheet_1, SUM(sheet = 2) AS in_sheet_2
    FROM nine_days
    GROUP BY invoice, stock_code, description, quantity,
             invoice_date, price, customer_id, country
)
SELECT (SELECT COUNT(*) FROM nine_days WHERE sheet = 1) AS lines_sheet_1,
       (SELECT COUNT(*) FROM nine_days WHERE sheet = 2) AS lines_sheet_2,
       COUNT(*)                                     AS different_lines,
       (SELECT ROUND(SUM(quantity * price)) FROM nine_days WHERE sheet = 2) AS amount_sheet_2
FROM per_line
WHERE in_sheet_1 <> in_sheet_2

lines_sheet_1  lines_sheet_2  different_lines  amount_sheet_2
-------------  -------------  ---------------  --------------
22523          22523          0                377488.0
1 row

Ce que cela nous apprend. Chaque feuille contient 22 523 lignes pour ces neuf jours, et aucune ligne ne diffère. Même une ligne présente deux fois dans la première feuille apparaît deux fois dans la deuxième. Les neuf jours de la deuxième feuille sont donc une copie exacte, d’une valeur de 377 488 £. Tout rapport qui additionne les lignes brutes les compte deux fois. Vous supprimez ces 22 523 lignes.

Qu’en est-il des autres répétitions? Examinez ce qui reste après avoir supprimé la copie :

WITH one_copy AS (
    SELECT * FROM raw_lines
    WHERE NOT (line_no >= 525462 AND invoice_date < '2010-12-10')
),
repeated AS (
    SELECT COUNT(*) AS copies, quantity * price AS amount
    FROM one_copy
    GROUP BY invoice, stock_code, description, quantity,
             invoice_date, price, customer_id, country
    HAVING COUNT(*) > 1
)
SELECT COUNT(*) AS groups, SUM(copies - 1) AS extra_lines,
       ROUND(SUM((copies - 1) * amount)) AS extra_amount,
       (SELECT ROUND(SUM(quantity * price)) FROM one_copy) AS all_lines_amount
FROM repeated

groups  extra_lines  extra_amount  all_lines_amount
------  -----------  ------------  ----------------
11001   11812        54228.0       18909762.0
1 row

Ce que cela nous apprend. 11 812 répétitions se trouvent dans une même feuille. Il peut s’agir du même produit saisi deux fois sur une facture. Le système d’une boutique peut l’enregistrer ainsi volontairement. Personne ne peut prouver que ce sont des copies. Ensemble, elles représentent 54 228 £, soit 0,3 % du montant. Vous les conservez donc et documentez ce choix comme une limite. Voilà la différence entre une copie prouvée et une répétition suspecte. SELECT DISTINCT * supprimerait les deux sans vous le dire.

Factures et produits

Pour diviser l’export en tables, chaque fait doit appartenir à une seule table. Une facture devrait avoir un seul client, un seul pays et une seule heure. Vérifiez-le :

WITH per_invoice AS (
    SELECT invoice,
           COUNT(DISTINCT customer_id)  AS customer_ids,
           COUNT(DISTINCT country)      AS countries,
           COUNT(DISTINCT invoice_date) AS times,
           MIN(invoice_date)            AS first_time,
           MAX(invoice_date)            AS last_time
    FROM raw_lines
    GROUP BY invoice
)
SELECT COUNT(*)                AS invoices,
       SUM(customer_ids > 1)   AS several_customers,
       SUM(countries > 1)      AS several_countries,
       SUM(times > 1)          AS several_times,
       MAX(ROUND((julianday(last_time) - julianday(first_time)) * 24 * 60)) AS max_minutes_apart,
       SUM(substr(first_time, 1, 7) <> substr(last_time, 1, 7)) AS cross_a_month
FROM per_invoice

invoices  several_customers  several_countries  several_times  max_minutes_apart  cross_a_month
--------  -----------------  -----------------  -------------  -----------------  -------------
53628     0                  0                  83             9.0                0
1 row

Ce que cela nous apprend. Chaque facture a un seul client et un seul pays. Ces faits peuvent donc passer dans une table invoices. Il y a 83 factures avec deux heures, séparées d’au plus 9 minutes. Elles ont probablement été saisies sur quelques minutes. Aucune ne passe à un autre mois. Le choix de l’heure ne peut donc pas déplacer des ventes entre les mois. Vous conservez la première heure.

Un produit devrait avoir un seul code et un seul nom :

SELECT COUNT(DISTINCT stock_code)        AS codes,
       COUNT(DISTINCT UPPER(stock_code)) AS codes_ignoring_case,
       (SELECT COUNT(*) FROM (
            SELECT stock_code FROM raw_lines
            GROUP BY stock_code
            HAVING COUNT(DISTINCT description) > 1)) AS codes_with_several_descriptions
FROM raw_lines

codes  codes_ignoring_case  codes_with_several_descriptions
-----  -------------------  -------------------------------
5305   5132                 1232
1 row

Ce que cela nous apprend. Il y a 5 305 codes, mais seulement 5 132 si l’on ignore les majuscules et les minuscules. Le même produit apparaît donc sous les codes 15056BL et 15056bl, par exemple. Et 1 232 codes ont plus d’une description. Examinez l’un d’eux, le présentoir à gâteaux à trois étages :

SELECT description, COUNT(*) AS lines,
       SUM(price > 0)           AS lines_with_a_price,
       SUM(customer_id IS NULL) AS lines_without_customer
FROM raw_lines
WHERE stock_code = '22423'
GROUP BY description
ORDER BY lines DESC

description               lines  lines_with_a_price  lines_without_customer
------------------------  -----  ------------------  ----------------------
REGENCY CAKESTAND 3 TIER  4412   4408                635
damaged                   3      0                   3
smashed                   2      0                   2
faulty                    2      0                   2
damages                   2      0                   2
wonky bottom/broken       1      0                   1
broken, uneven bottom     1      0                   1
NULL                      1      0                   1
8 rows

Ce que cela nous apprend. Le vrai nom apparaît sur 4 412 lignes. Les autres « noms » sont des notes sur le stock : damaged (endommagé), smashed (cassé), faulty (défectueux). Ces lignes n’ont ni prix ni client. L’entrepôt utilisait donc le système de vente pour enregistrer les marchandises abîmées. Ces lignes ne sont pas des ventes. Une table products conserve le nom utilisé le plus souvent.

Enfin, examinez les codes qui ne commencent pas par cinq chiffres. GLOB compare du texte à un motif, comme LIKE. Mais il distingue les majuscules des minuscules et utilise d’autres caractères génériques : [0-9] représente un chiffre et *, n’importe quel texte. Ainsi, '[0-9][0-9][0-9][0-9][0-9]*' signifie « cinq chiffres, puis n’importe quoi » :

SELECT stock_code, COUNT(*) AS lines, MIN(description) AS example,
       ROUND(SUM(quantity * price)) AS amount
FROM raw_lines
WHERE stock_code NOT GLOB '[0-9][0-9][0-9][0-9][0-9]*'
GROUP BY stock_code
ORDER BY lines DESC

stock_code    lines  example                              amount
------------  -----  -----------------------------------  ---------
POST          2122   POSTAGE                              112341.0
DOT           1446   DOTCOM POSTAGE                       322647.0
M             1421   Manual                               -82796.0
C2            282    CARRIAGE                             13386.0
D             177    Discount                             -13485.0
S             104    SAMPLES                              -6066.0
BANK CHARGES  102     Bank Charges                        -35563.0
ADJUST        67     Adjustment by Peter on 24/05/2010 1  6835.0
AMAZONFEE     43     AMAZON FEE                           -260764.0
DCGS0058      31     MISO PRETTY  GUM                     35.0
gift_0001_30  29     Dotcomgiftshop Gift Voucher £30.00   610.0
gift_0001_20  29     Dotcomgiftshop Gift Voucher £20.00   471.0
DCGSSGIRL     25     GIRLS PARTY BAG                      296.0
DCGSSBOY      23     BOYS PARTY BAG                       251.0
PADS          19     PADS TO MATCH ALL CUSHIONS           -37.0
gift_0001_10  16     Dotcomgiftshop Gift Voucher £10.00   126.0
CRUK          16     CRUK Commission                      -7933.0
TEST001       15     This is a test product.              203.0
DCGS0076      15     SUNJAR LED NIGHT NIGHT LIGHT         292.0
DCGS0003      14     BOXED GLASS ASHTRAY                  33.0
62 rows (showing 20; use limit=None to see all)

Ce que cela nous apprend. La plupart de ces codes ne sont pas des marchandises. Ce sont des frais de livraison (POST, DOT, C2), des corrections manuelles (M), des rabais et des échantillons. Il y a aussi des frais bancaires, des frais de plateforme de vente, une commission caritative (CRUK), des lignes de test et des chèques-cadeaux. Les frais Amazon représentent à eux seuls −260 764 £. Ils appartiennent aux comptes, mais pas aux ventes de produits. DCGS0058 (gomme à mâcher) et PADS sont de vrais produits avec des codes inhabituels. Une règle comme « cinq chiffres seulement » serait donc incorrecte. Vous dressez plutôt la liste des codes qui ne désignent pas des marchandises, par nom. La liste complète se trouve dans la prochaine section.

Construire quatre tables avec des clés

Chaque vérification ci-dessus a mené à une décision. Kwame les approuve, car chacune découle de sa définition du revenu :

Problème trouvé	Décision	Raison
La deuxième feuille répète les données du 1 au 9 décembre 2010	Supprimer les 22 523 lignes copiées	Ce sont des copies confirmées.
11 812 répétitions dans une même feuille	Les garder et les noter comme une limite	Elles peuvent être réelles; au plus 54 228 £.
Codes en majuscules et en minuscules	Utiliser UPPER(stock_code)	15056bl et 15056BL sont un seul produit.
Plusieurs descriptions pour un code	Garder le nom le plus fréquent	Les notes de stock ne sont pas des noms.
Deux heures sur une facture	Garder la première heure	Au plus 9 minutes; aucun changement de mois.
Frais de port, frais, ajustements, bons	Les garder dans les tables, avec is_goods = 0	De l’argent réel, mais pas des ventes de marchandises.
Aucun identifiant de client	Les garder, avec customer_id à NULL	L’achat d’un invité est une vente.

Les tables gardent tout ce qui s’est passé, sauf la copie. Les règles d’affaires (marchandises seulement, prix supérieur à 0) iront dans une vue plus tard. Ainsi, une nouvelle règle ne demande jamais un nouveau chargement.

Le diagramme montre les quatre tables. Contrairement à l’exportation de Larkfield dans Les concepts relationnels, vous construisez ces tables vous-même. Vous pouvez donc leur donner de vraies contraintes.

La base de données de rapports : customers 1 à plusieurs invoices, invoices 1 à plusieurs lignes, products 1 à plusieurs lignes. Chaque rectangle montre sa granularité et ses clés.

Description textuelle du diagramme

Le diagramme contient quatre rectangles. customers (une ligne représente un client) contient customer_id comme clé primaire (PK) et le pays. invoices (une ligne représente une facture) contient invoice (PK), invoiced_at, customer_id comme clé étrangère (FK) avec la note « NULL pour un invité », le pays et kind, avec les valeurs sale, return ou adjustment. invoice_lines (une ligne représente une ligne de facture) contient line_no (PK, la position dans le fichier), invoice (FK), stock_code (FK), quantity et unit_price. products (une ligne représente un produit) contient stock_code (PK), description et is_goods, avec la note « 0 pour les frais de port et les frais ». Une ligne portant l’étiquette « passe » relie customers à invoices : une facture a zéro ou un client, et un client a zéro ou plusieurs factures. Une ligne portant l’étiquette « contient 1 ou plusieurs » relie invoices à invoice_lines. Une ligne portant l’étiquette « figure sur » relie products à invoice_lines : un produit figure sur zéro ou plusieurs lignes.

La première étape crée une vue des lignes sans la copie, ainsi que les quatre tables vides. Une vue est une requête enregistrée que vous pouvez utiliser comme une table. source_lines ne stocke aucune ligne, et chaque requête qui la lit exécute de nouveau son SELECT. UPPER() écrit un texte en majuscules. Dans les tables, CHECK (...) est une contrainte comme PRIMARY KEY : SQLite refuse une ligne si la condition est fausse.

-- One copy of each line: sheet 2 repeats 1 to 9 December 2010 of sheet 1.
-- Codes are upper-cased: '15056bl' and '15056BL' are one product.
CREATE VIEW source_lines AS
SELECT line_no, invoice, UPPER(stock_code) AS stock_code, description,
       quantity, invoice_date, price, customer_id, country
FROM raw_lines
WHERE NOT (line_no >= 525462 AND invoice_date < '2010-12-10');

CREATE TABLE products (
    stock_code  TEXT PRIMARY KEY,
    description TEXT,
    is_goods    INTEGER NOT NULL CHECK (is_goods IN (0, 1))
);
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    country     TEXT NOT NULL
);
CREATE TABLE invoices (
    invoice     TEXT PRIMARY KEY,
    invoiced_at TEXT NOT NULL,
    customer_id INTEGER REFERENCES customers (customer_id),
    country     TEXT NOT NULL,
    kind        TEXT NOT NULL CHECK (kind IN ('sale', 'return', 'adjustment'))
);
CREATE TABLE invoice_lines (
    line_no    INTEGER PRIMARY KEY,
    invoice    TEXT NOT NULL REFERENCES invoices (invoice),
    stock_code TEXT NOT NULL REFERENCES products (stock_code),
    quantity   INTEGER NOT NULL,
    unit_price REAL NOT NULL
);

La deuxième étape les remplit. Une clé primaire doit être unique, donc chaque INSERT crée exactement une ligne par clé. GROUP BY invoice donne une ligne par facture. ROW_NUMBER() ... WHERE pick = 1 garde une ligne par produit et par client, comme dans Le temps et le classement. Si une étape créait deux lignes pour une clé, SQLite s’arrêterait avec une erreur. C’est le rôle de la contrainte : une erreur silencieuse devient visible.

-- products: the name used most often; 0 for postage, fees and adjustments
INSERT INTO products
SELECT stock_code, description,
       CASE WHEN stock_code IN ('POST', 'DOT', 'C2', 'C3', 'M', 'D', 'S', 'B', 'BANK CHARGES', 'ADJUST', 'ADJUST2', 'AMAZONFEE', 'CRUK', 'TEST001', 'TEST002')
              OR stock_code LIKE 'GIFT%' THEN 0 ELSE 1 END
FROM (
    SELECT stock_code, description,
           ROW_NUMBER() OVER (PARTITION BY stock_code
                              ORDER BY description IS NULL, COUNT(*) DESC, description) AS pick
    FROM source_lines
    GROUP BY stock_code, description
)
WHERE pick = 1;

-- customers: the country of the customer's latest invoice
INSERT INTO customers
SELECT customer_id, country
FROM (
    SELECT customer_id, country,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY invoice_date DESC, line_no DESC) AS pick
    FROM source_lines
    WHERE customer_id IS NOT NULL
)
WHERE pick = 1;

-- invoices: one row each; the first time if the lines have two
INSERT INTO invoices
SELECT invoice, MIN(invoice_date), MAX(customer_id), MAX(country),
       CASE WHEN invoice LIKE 'C%' THEN 'return'
            WHEN invoice LIKE 'A%' THEN 'adjustment'
            ELSE 'sale' END
FROM source_lines
GROUP BY invoice;

INSERT INTO invoice_lines
SELECT line_no, invoice, stock_code, quantity, price
FROM source_lines;

CREATE INDEX lines_by_invoice ON invoice_lines (invoice);
CREATE INDEX lines_by_product ON invoice_lines (stock_code);
CREATE INDEX invoices_by_customer ON invoices (customer_id);

Le carnet (notebook) Jupyter exécute les deux étapes avec con.executescript(...), puis compte les lignes de chaque table. Les index rendent les jointures suivantes rapides.

raw_lines      1,067,371 rows
products           5,132 rows
customers          5,942 rows
invoices          53,628 rows
invoice_lines  1,044,848 rows

Ce que cela nous apprend. Chaque compte correspond à un nombre du profil. Il y a 5 132 produits (les codes sans tenir compte de la casse), 5 942 clients et 53 628 factures. Le nombre de lignes est de 1 067 371 − 22 523 = 1 044 848. 13 clients ont acheté depuis plusieurs pays; la table garde le pays de leur dernière facture.

Vérifier les clés

SQLite vérifie les clés primaires pendant l’insertion. Il vérifie les clés étrangères seulement si vous activez cette option pour chaque connexion. Vérifiez-les donc vous-même avec les requêtes de recherche d’orphelins des Les concepts relationnels. La quatrième vérification utilise NOT EXISTS (subquery), qui est vrai quand la sous-requête ne retourne aucune ligne. Ici, c’est le cas quand aucune ligne ne porte le numéro de la facture. Chaque ligne compte les problèmes, donc 0 est un bon résultat :

SELECT 'lines without their invoice' AS check_name, COUNT(*) AS problems
FROM invoice_lines AS l LEFT JOIN invoices AS i ON i.invoice = l.invoice
WHERE i.invoice IS NULL
UNION ALL
SELECT 'lines without their product', COUNT(*)
FROM invoice_lines AS l LEFT JOIN products AS p ON p.stock_code = l.stock_code
WHERE p.stock_code IS NULL
UNION ALL
SELECT 'invoices with an unknown customer', COUNT(*)
FROM invoices AS i LEFT JOIN customers AS c ON c.customer_id = i.customer_id
WHERE i.customer_id IS NOT NULL AND c.customer_id IS NULL
UNION ALL
SELECT 'invoices without lines', COUNT(*)
FROM invoices AS i
WHERE NOT EXISTS (SELECT 1 FROM invoice_lines AS l WHERE l.invoice = i.invoice)
UNION ALL
SELECT 'raw lines not accounted for',
       (SELECT COUNT(*) FROM raw_lines) - (SELECT COUNT(*) FROM invoice_lines) - 22523

check_name                         problems
---------------------------------  --------
lines without their invoice        0
lines without their product        0
invoices with an unknown customer  0
invoices without lines             0
raw lines not accounted for        0
5 rows

Ce que cela nous apprend. Aucun orphelin dans aucune direction. Chaque ligne brute se trouve dans invoice_lines ou parmi les 22 523 copies. Les tables sont complètes. Voyez maintenant quels types de lignes elles contiennent :

SELECT i.kind, p.is_goods, COUNT(*) AS lines,
       COUNT(DISTINCT i.invoice) AS invoices,
       SUM(i.customer_id IS NULL) AS lines_without_customer
FROM invoice_lines AS l
JOIN invoices AS i ON i.invoice = l.invoice
JOIN products AS p ON p.stock_code = l.stock_code
GROUP BY i.kind, p.is_goods
ORDER BY i.kind, p.is_goods DESC

kind        is_goods  lines    invoices  lines_without_customer
----------  --------  -------  --------  ----------------------
adjustment  0         6        6         6
return      1         17974    7406      332
return      0         1191     1024      387
sale        1         1021061  44749     232794
sale        0         4616     4437      1768
5 rows

Ce que cela nous apprend. 1 021 061 lignes sont des ventes de marchandises, et 17 974 sont des retours de marchandises. La plupart des lignes sans client sont des ventes : les invités achètent, mais retournent rarement des marchandises. Dans le carnet, votre première tâche compte les factures de vente des invités.

Rapprocher les revenus

Voici maintenant le premier problème de Kwame : trois rapports, trois totaux. Commencez par une définition claire à un seul endroit. La vue goods_lines contient seulement les lignes qui comptent comme ventes de marchandises, avec les données de leur facture sur chaque ligne :

-- The lines that count as sales of goods, with the facts of their invoice.
CREATE VIEW goods_lines AS
SELECT l.line_no, l.invoice, i.kind, i.invoiced_at,
       strftime('%Y-%m', i.invoiced_at) AS month,
       i.customer_id, i.country, l.stock_code, l.quantity, l.unit_price,
       l.quantity * l.unit_price AS amount
FROM invoice_lines AS l
JOIN invoices AS i ON i.invoice = l.invoice
JOIN products AS p ON p.stock_code = l.stock_code
WHERE p.is_goods = 1
  AND l.unit_price > 0
  AND i.kind IN ('sale', 'return');

Comme source_lines, c’est une vue. Chaque requête suivante lit goods_lines, donc la définition du revenu se trouve à un seul endroit. Si les finances changent la règle, vous modifiez une seule vue.

Répartissez maintenant chaque livre sterling de invoice_lines dans l’une de cinq catégories :

SELECT CASE WHEN i.kind = 'adjustment' THEN '5 bad-debt adjustment'
            WHEN p.is_goods = 0        THEN '4 postage, fees, manual'
            WHEN l.unit_price <= 0     THEN '3 goods at price 0'
            WHEN i.kind = 'return'     THEN '2 goods returned'
            ELSE                            '1 goods sold' END AS part,
       COUNT(*) AS lines,
       ROUND(SUM(l.quantity * l.unit_price), 2) AS amount
FROM invoice_lines AS l
JOIN invoices AS i ON i.invoice = l.invoice
JOIN products AS p ON p.stock_code = l.stock_code
GROUP BY part
ORDER BY part

part                     lines    amount
-----------------------  -------  -----------
1 goods sold             1015088  19700954.46
2 goods returned         17974    -719692.94
3 goods at price 0       5973     0.0
4 postage, fees, manual  5807     76114.68
5 bad-debt adjustment    6        -147614.08
5 rows

Et voici les trois totaux utilisés dans les rapports de Kwame :

SELECT 'every raw line' AS step, COUNT(*) AS lines,
       ROUND(SUM(quantity * price), 2) AS amount
FROM raw_lines
UNION ALL
SELECT 'one copy of each line', COUNT(*), ROUND(SUM(quantity * unit_price), 2)
FROM invoice_lines
UNION ALL
SELECT 'net sales of goods', COUNT(*), ROUND(SUM(amount), 2)
FROM goods_lines

step                   lines    amount
---------------------  -------  -----------
every raw line         1067371  19287250.57
one copy of each line  1044848  18909762.12
net sales of goods     1033062  18981261.52
3 rows

Ce que cela nous apprend. Chaque rapport avait raison, mais mesurait une chose différente :

• 19 287 250,57 £ est la somme de toutes les lignes brutes. Elle compte deux fois les neuf jours copiés : 19 287 250,57 £ − 18 909 762,12 £ = 377 488,45 £, la copie que vous avez trouvée.
• 18 909 762,12 £ est la somme de chaque ligne comptée une seule fois. Mais elle mélange les marchandises avec les frais de port et les frais (+76 114,68 £ au total) et les ajustements pour créances irrécouvrables (−147 614,08 £).
• 18 981 261,52 £ représente les ventes nettes de marchandises : 19 700 954,46 £ vendues moins 719 692,94 £ retournées.

La somme des cinq catégories donne le deuxième total, au centime près. Aucune livre sterling n’est donc perdue. C’est un rapprochement : vous expliquez la différence entre deux totaux, catégorie par catégorie. Kwame déclare maintenant 18 981 261,52 £, avec cette table comme explication. Les invités ont généré 2 569 367 £ de ce montant, soit 13,5 %. Un rapport « clients seulement » manquerait ces ventes.

Erreur courante

Un total qui augmente après une jointure

Symptôme : Kwame veut les ventes nettes et le nombre de lignes par pays. Il a une petite table de totaux par facture et la joint aux lignes pour les compter. La requête s’exécute sans erreur :

WITH invoice_totals AS (
    SELECT invoice, country, SUM(amount) AS total
    FROM goods_lines
    GROUP BY invoice, country
)
SELECT t.country,
       ROUND(SUM(t.total))  AS net_sales,
       COUNT(g.line_no)     AS lines
FROM invoice_totals AS t
JOIN goods_lines AS g ON g.invoice = t.invoice
GROUP BY t.country
ORDER BY net_sales DESC
LIMIT 3

country         net_sales     lines
--------------  ------------  ------
United Kingdom  1018481201.0  950526
Netherlands     30517437.0    5017
EIRE            26863665.0    17404
3 rows

Le Royaume-Uni à lui seul affiche plus de 1 milliard de livres sterling. Les ventes nettes de toute la boutique sont de 19 millions de livres sterling.

Cause : les totaux des factures ont une ligne par facture, et les lignes de facture en ont plusieurs. La jointure répète le total de chaque facture une fois pour chacune de ses lignes. Une facture le montre :

WITH invoice_totals AS (
    SELECT invoice, ROUND(SUM(amount), 2) AS total
    FROM goods_lines
    WHERE invoice = '489434'
    GROUP BY invoice
)
SELECT t.invoice, t.total, g.line_no, g.stock_code, ROUND(g.amount, 2) AS amount
FROM invoice_totals AS t
JOIN goods_lines AS g ON g.invoice = t.invoice

invoice  total  line_no  stock_code  amount
-------  -----  -------  ----------  ------
489434   505.3  1        85048       83.4
489434   505.3  2        79323P      81.0
489434   505.3  3        79323W      81.0
489434   505.3  4        22041       100.8
489434   505.3  5        21232       30.0
489434   505.3  6        22064       39.6
489434   505.3  7        21871       30.0
489434   505.3  8        21523       59.5
8 rows

Le total de la facture est de 505,30 £. Mais SUM(total) sur ces 8 lignes donne 8 × 505,30 £ = 4 042,40 £. Une facture de 50 lignes compte son total 50 fois. Le total du Royaume-Uni est donc environ 63 fois trop élevé. C’est la multiplication des lignes décrite dans Joindre des tables, avec de l’argent.

Correction : faites la somme à la granularité où se trouve le nombre. Les montants se trouvent sur les lignes, donc additionnez les lignes :

SELECT country,
       ROUND(SUM(amount)) AS net_sales,
       COUNT(*)           AS lines
FROM goods_lines
GROUP BY country
ORDER BY net_sales DESC
LIMIT 3

country         net_sales   lines
--------------  ----------  ------
United Kingdom  16221081.0  950526
EIRE            603407.0    17404
Netherlands     546097.0    5017
3 rows

Faites ensuite le rapprochement : la somme de tous les pays doit être égale à 18 981 261,52 £. Remarquez que l’ordre des pays a aussi changé. Avec les mauvais totaux, les Pays-Bas semblaient dépasser l’Irlande (EIRE).

Conséquence en situation réelle : un nombre 63 fois trop élevé est facile à repérer. Une jointure qui répète seulement certaines lignes, par exemple les factures ayant une deuxième ligne de livraison, est moins évidente. Elle donne un total 3 % trop élevé, et personne ne le remarque. Un modèle entraîné sur de telles caractéristiques apprend à partir de valeurs erronées. La protection reste la même : comptez les lignes avant et après une jointure, puis rapprochez chaque total d’un total vérifié.

Les ventes mensuelles et le sommet de Noël

Voici maintenant la question d’Ines : quand la saison commence-t-elle, et est-elle en croissance? D’abord, les ventes de chaque mois. CASE dans SUM sépare les ventes et les retours en un seul passage :

SELECT month,
       COUNT(DISTINCT CASE WHEN kind = 'sale' THEN invoice END) AS orders,
       ROUND(SUM(CASE WHEN kind = 'sale'   THEN amount ELSE 0 END)) AS sales,
       ROUND(-SUM(CASE WHEN kind = 'return' THEN amount ELSE 0 END)) AS returns,
       ROUND(SUM(amount)) AS net_sales
FROM goods_lines
GROUP BY month
ORDER BY month

month    orders  sales      returns   net_sales
-------  ------  ---------  --------  ---------
2009-12  1666    801103.0   19561.0   781542.0
2010-01  1049    613851.0   7466.0    606385.0
2010-02  1189    539745.0   12584.0   527160.0
2010-03  1647    764334.0   9657.0    754677.0
2010-04  1435    648994.0   10025.0   638969.0
2010-05  1484    645645.0   37294.0   608351.0
2010-06  1612    699316.0   26381.0   672935.0
2010-07  1507    634897.0   17133.0   617764.0
2010-08  1402    676061.0   12419.0   663642.0
2010-09  1789    871878.0   27778.0   844100.0
2010-10  2244    1098018.0  25550.0   1072468.0
2010-11  2719    1435680.0  35121.0   1400559.0
2010-12  1550    777932.0   17608.0   760324.0
2011-01  1081    671933.0   91526.0   580407.0
2011-02  1093    508886.0   8376.0    500510.0
2011-03  1440    691258.0   10675.0   680582.0
2011-04  1235    516218.0   33317.0   482901.0
2011-05  1668    741192.0   8948.0    732244.0
2011-06  1525    738752.0   13761.0   724991.0
2011-07  1452    689364.0   11409.0   677955.0
2011-08  1339    725514.0   22930.0   702584.0
2011-09  1818    1030475.0  17045.0   1013431.0
2011-10  2005    1106670.0  43992.0   1062678.0
2011-11  2751    1457746.0  25011.0   1432735.0
2011-12  816     615493.0   174127.0  441367.0
25 rows

Le graphique superpose les deux années commerciales. Une année commerciale va ici de décembre à novembre. Chaque année se termine donc par sa saison de Noël et compte douze mois complets.

• Décembre 2009 à novembre 2010
• Décembre 2010 à novembre 2011

Ventes nettes de marchandises par mois, de décembre à novembre, pour les deux années commerciales. Les deux années restent stables de décembre à août, puis augmentent en septembre, octobre et novembre.

Description textuelle du graphique

Un graphique à deux courbes, une par année commerciale, de décembre à novembre. Les deux courbes restent entre environ 480 000 £ et 780 000 £ de décembre à août. Puis les deux augmentent fortement. La première année atteint 844 000 £ en septembre, 1 072 000 £ en octobre et 1 401 000 £ en novembre. La deuxième atteint 1 013 000 £ en septembre, 1 063 000 £ en octobre et 1 433 000 £ en novembre. La deuxième année est plus basse de janvier à avril et plus haute de mai à septembre.

Ce que cela nous apprend. Le sommet est en novembre, pas en décembre. Les clients sont des boutiques : elles achètent de septembre à novembre pour vendre à leurs propres clients en décembre. Décembre 2011 semble montrer un effondrement, mais sa ligne ne contient que neuf jours.

Pour comparer un mois au même mois un an plus tôt, utilisez LAG(net_sales, 12) : la valeur 12 lignes avant. Mais LAG compte les lignes, pas les mois. S’il manquait un mois, 12 lignes avant correspondrait à 13 mois avant, sans erreur. La requête montre donc aussi LAG(month, 12). Vous pouvez ainsi voir que chaque comparaison porte sur le bon mois :

WITH monthly AS (
    SELECT month, SUM(amount) AS net_sales
    FROM goods_lines
    GROUP BY month
),
with_last_year AS (
    SELECT month, net_sales,
           LAG(month, 12)     OVER (ORDER BY month) AS month_12_rows_back,
           LAG(net_sales, 12) OVER (ORDER BY month) AS net_sales_last_year
    FROM monthly
)
SELECT month, ROUND(net_sales) AS net_sales, month_12_rows_back,
       ROUND(net_sales_last_year) AS net_sales_last_year,
       ROUND(100.0 * (net_sales - net_sales_last_year) / net_sales_last_year, 1) AS change_pct
FROM with_last_year
WHERE month >= '2010-12'

month    net_sales  month_12_rows_back  net_sales_last_year  change_pct
-------  ---------  ------------------  -------------------  ----------
2010-12  760324.0   2009-12             781542.0             -2.7
2011-01  580407.0   2010-01             606385.0             -4.3
2011-02  500510.0   2010-02             527160.0             -5.1
2011-03  680582.0   2010-03             754677.0             -9.8
2011-04  482901.0   2010-04             638969.0             -24.4
2011-05  732244.0   2010-05             608351.0             20.4
2011-06  724991.0   2010-06             672935.0             7.7
2011-07  677955.0   2010-07             617764.0             9.7
2011-08  702584.0   2010-08             663642.0             5.9
2011-09  1013431.0  2010-09             844100.0             20.1
2011-10  1062678.0  2010-10             1072468.0            -0.9
2011-11  1432735.0  2010-11             1400559.0            2.3
2011-12  441367.0   2010-12             760324.0             -42.0
13 rows

Ce que cela nous apprend. Chaque ligne compare le même mois de deux années, donc LAG est sûr ici. L’année a commencé faiblement (avril −24,4 %) et s’est redressée à partir de mai. Septembre 2011 dépassait septembre 2010 de 20,1 %, mais octobre et novembre étaient presque stables. Sans le retrait de la copie, décembre 2010 compterait une deuxième fois 377 488 £ de lignes copiées. Chaque comparaison avec ce mois serait fausse.

Le −42,0 % de décembre 2011 est une fausse alerte. Comparez plutôt les mêmes neuf jours de chaque année :

SELECT substr(invoiced_at, 1, 4) AS year,
       ROUND(SUM(amount)) AS net_sales_1_to_9_dec,
       COUNT(DISTINCT CASE WHEN kind = 'sale' THEN invoice END) AS orders
FROM goods_lines
WHERE substr(invoiced_at, 6, 5) BETWEEN '12-01' AND '12-09'
GROUP BY year

year  net_sales_1_to_9_dec  orders
----  --------------------  ------
2009  379913.0              841
2010  402364.0              830
2011  441367.0              816
3 rows

Ce que cela nous apprend. Sur les mêmes neuf jours, décembre 2011 dépassait 2010 de 9,7 %. Ce n’était pas un effondrement. Signalez toute période partielle dans un rapport et comparez-la seulement à la même partie d’une autre période.

Enfin, quelle est l’importance de la saison? Comparez septembre à novembre avec l’année commerciale entière :

SELECT CASE WHEN month <= '2010-11' THEN 'Dec 2009 to Nov 2010'
            ELSE 'Dec 2010 to Nov 2011' END AS trading_year,
       ROUND(SUM(amount)) AS net_sales,
       ROUND(SUM(CASE WHEN substr(month, 6, 2) IN ('09', '10', '11') THEN amount END)) AS sep_to_nov,
       ROUND(100.0 * SUM(CASE WHEN substr(month, 6, 2) IN ('09', '10', '11') THEN amount END)
             / SUM(amount), 1) AS sep_to_nov_pct
FROM goods_lines
WHERE month <= '2011-11'
GROUP BY trading_year

trading_year          net_sales  sep_to_nov  sep_to_nov_pct
--------------------  ---------  ----------  --------------
Dec 2009 to Nov 2010  9188553.0  3317127.0   36.1
Dec 2010 to Nov 2011  9351342.0  3508843.0   37.5
2 rows

Ce que cela nous apprend. Trois mois apportent plus du tiers des ventes nettes annuelles : 36,1 %, puis 37,5 %. L’année commerciale a augmenté de 1,8 %, et la saison de 5,8 % (3 508 843 £ contre 3 317 127 £). Pour Ines, le stock de Noël doit être dans l’entrepôt avant la fin d’août. La saison augmente aussi un peu plus vite que le reste de l’année.

Les retours : un taux et les commandes derrière les pointes

Deuxième problème de Kwame : le conseil d’administration a vu un taux de retours de 13,6 % en janvier 2011. Le taux de retours est la valeur des marchandises retournées divisée par celle des marchandises vendues dans le même mois. À côté, la requête montre la plus grande ligne de retour de chaque mois, trouvée avec ROW_NUMBER() :

WITH return_lines AS (
    SELECT month, line_no, invoice, customer_id, stock_code, quantity,
           -amount AS value,
           ROW_NUMBER() OVER (PARTITION BY month ORDER BY -amount DESC, line_no) AS place
    FROM goods_lines
    WHERE kind = 'return'
),
monthly AS (
    SELECT month,
           SUM(CASE WHEN kind = 'sale' THEN amount ELSE 0 END) AS sales,
           -SUM(CASE WHEN kind = 'return' THEN amount ELSE 0 END) AS returns
    FROM goods_lines
    GROUP BY month
)
SELECT m.month, ROUND(m.returns) AS returns,
       ROUND(100.0 * m.returns / m.sales, 1) AS return_rate_pct,
       ROUND(r.value) AS largest_line,
       ROUND(100.0 * r.value / m.returns) AS largest_line_pct
FROM monthly AS m
JOIN return_lines AS r ON r.month = m.month AND r.place = 1
ORDER BY m.month

month    returns   return_rate_pct  largest_line  largest_line_pct
-------  --------  ---------------  ------------  ----------------
2009-12  19561.0   2.4              932.0         5.0
2010-01  7466.0    1.2              424.0         6.0
2010-02  12584.0   2.3              3631.0        29.0
2010-03  9657.0    1.3              210.0         2.0
2010-04  10025.0   1.5              640.0         6.0
2010-05  37294.0   5.8              1700.0        5.0
2010-06  26381.0   3.8              1332.0        5.0
2010-07  17133.0   2.7              2971.0        17.0
2010-08  12419.0   1.8              850.0         7.0
2010-09  27778.0   3.2              1653.0        6.0
2010-10  25550.0   2.3              3168.0        12.0
2010-11  35121.0   2.4              1621.0        5.0
2010-12  17608.0   2.3              1275.0        7.0
2011-01  91526.0   13.6             77184.0       84.0
2011-02  8376.0    1.6              1224.0        15.0
2011-03  10675.0   1.5              526.0         5.0
2011-04  33317.0   6.5              6539.0        20.0
2011-05  8948.0    1.2              442.0         5.0
2011-06  13761.0   1.9              1155.0        8.0
2011-07  11409.0   1.7              990.0         9.0
2011-08  22930.0   3.2              2433.0        11.0
2011-09  17045.0   1.7              3825.0        22.0
2011-10  43992.0   4.0              1836.0        4.0
2011-11  25011.0   1.7              716.0         3.0
2011-12  174127.0  28.3             168470.0      97.0
25 rows

Le taux de retours mensuel, de décembre 2009 à décembre 2011. Deux pointes ressortent; dans chacune, une ligne de retour représente la majorité des retours du mois.

Description textuelle du graphique

Un graphique à courbe du taux de retours pour chacun des 25 mois. La plupart des mois, le taux se situe entre 1,2 % et 4,0 %. Deux mois sont un peu plus élevés : mai 2010 à 5,8 % et avril 2011 à 6,5 %. Deux mois sont beaucoup plus élevés : janvier 2011 à 13,6 %, où une ligne représente 84 % des retours du mois, et décembre 2011 à 28,3 %, où une ligne représente 97 %.

Ce que cela nous apprend. Dans la moitié des 24 mois complets, le taux est de 2,3 % ou moins. Deux mois affichent une hausse brusque. Dans chacun, une seule ligne de retour explique presque tout : 77 184 £ en janvier 2011 et 168 470 £ en décembre 2011. Trouvez la commande derrière chacune de ces deux lignes. La jointure cherche une vente du même produit au même client, dans la même quantité. La vente doit précéder le retour d’au plus une heure :

SELECT r.invoice AS return_invoice, s.invoice AS sale_invoice, r.customer_id,
       r.stock_code, s.quantity, s.invoiced_at AS ordered_at, r.invoiced_at AS returned_at,
       ROUND(s.amount) AS value
FROM goods_lines AS r
JOIN goods_lines AS s
  ON  s.customer_id = r.customer_id
  AND s.stock_code  = r.stock_code
  AND s.quantity    = -r.quantity
  AND s.kind = 'sale'
  AND s.invoiced_at <= r.invoiced_at
  AND r.invoiced_at <= datetime(s.invoiced_at, '+1 hour')
WHERE r.kind = 'return' AND -r.amount > 10000

return_invoice  sale_invoice  customer_id  stock_code  quantity  ordered_at           returned_at          value
--------------  ------------  -----------  ----------  --------  -------------------  -------------------  --------
C541433         541431        12346        23166       74215     2011-01-18 10:01:00  2011-01-18 10:17:00  77184.0
C581484         581483        16446        23843       80995     2011-12-09 09:15:00  2011-12-09 09:27:00  168470.0
2 rows

Ce que cela nous apprend. Le client 12346 a commandé 74 215 bocaux de rangement et a annulé la commande 16 minutes plus tard. Le client 16446 a commandé 80 995 trousses de bricolage en papier et a annulé 12 minutes plus tard. Ce sont des commandes erronées : probablement une mauvaise quantité, corrigée immédiatement, avant que des marchandises quittent l’entrepôt. Retirez les deux côtés de chaque commande et recalculez le taux :

WITH monthly AS (
    SELECT month,
           SUM(CASE WHEN kind = 'sale' THEN amount ELSE 0 END) AS sales,
           -SUM(CASE WHEN kind = 'return' THEN amount ELSE 0 END) AS returns,
           SUM(CASE WHEN invoice IN ('541431', '581483', 'C541433', 'C581484')
                    THEN ABS(amount) ELSE 0 END) / 2 AS mistaken
    FROM goods_lines
    GROUP BY month
)
SELECT month,
       ROUND(100.0 * returns / sales, 1) AS return_rate_pct,
       ROUND(100.0 * (returns - mistaken) / (sales - mistaken), 1) AS without_mistaken_orders_pct
FROM monthly
WHERE month IN ('2011-01', '2011-12')

month    return_rate_pct  without_mistaken_orders_pct
-------  ---------------  ---------------------------
2011-01  13.6             2.4
2011-12  28.3             1.3
2 rows

Ce que cela nous apprend. Sans les deux commandes erronées, janvier 2011 avait 2,4 % de retours et décembre 2011, 1,3 % : des mois normaux. Une politique de retours plus stricte n’aurait empêché aucune pointe, car aucun client n’a renvoyé de marchandises. La vraie correction est une vérification dans le système de commandes : un avertissement pour une très grande quantité. Les commandes erronées ne changent pas les ventes nettes, car chaque commande et son retour s’annulent. Elles changent seulement les montants bruts et le taux.

Les nouveaux clients reviennent-ils?

Troisième problème de Kwame : le tableau de bord indique que les nouveaux clients reviennent de moins en moins. Une cohorte est un groupe de clients qui ont commencé le même mois. La question pour chaque cohorte : quelle proportion a acheté de nouveau dans les trois mois suivants?

La requête comporte trois étapes avec des CTE. Vous pouvez exécuter chacune séparément, comme dans Des requêtes lisibles :

WITH active AS (          -- one row per customer and month with a purchase
    SELECT DISTINCT customer_id, month
    FROM goods_lines
    WHERE kind = 'sale' AND customer_id IS NOT NULL
),
first_month AS (          -- the month each customer first appears
    SELECT customer_id, MIN(month) AS cohort
    FROM active
    GROUP BY customer_id
),
came_back AS (            -- did they buy again in the next three months?
    SELECT f.cohort, f.customer_id,
           MAX(a.month >  f.cohort AND
               a.month <= strftime('%Y-%m', f.cohort || '-01', '+3 months')) AS back
    FROM first_month AS f
    JOIN active AS a ON a.customer_id = f.customer_id
    GROUP BY f.cohort, f.customer_id
)
SELECT cohort, COUNT(*) AS customers,
       ROUND(100.0 * AVG(back), 1) AS back_within_3_months_pct
FROM came_back
WHERE cohort <= '2011-08'
GROUP BY cohort
ORDER BY cohort

cohort   customers  back_within_3_months_pct
-------  ---------  ------------------------
2009-12  951        63.0
2010-01  368        55.4
2010-02  375        52.3
2010-03  441        44.2
2010-04  294        38.1
2010-05  255        35.3
2010-06  267        38.2
2010-07  185        44.3
2010-08  163        50.3
2010-09  239        42.3
2010-10  375        37.1
2010-11  326        25.2
2010-12  76         18.4
2011-01  72         40.3
2011-02  125        34.4
2011-03  179        43.6
2011-04  106        39.6
2011-05  111        42.3
2011-06  108        47.2
2011-07  101        50.5
2011-08  107        52.3
21 rows

La jointure dans came_back répète chaque client une fois par mois actif. Puis GROUP BY f.cohort, f.customer_id ramène le résultat à une ligne par client. MAX(...) vaut 1 si au moins un de ces mois se trouve dans la période. La dernière cohorte est celle d’août 2011. Les données ne couvrent pas trois mois complets après les cohortes suivantes.

La proportion de chaque cohorte qui a acheté de nouveau dans les trois mois, de décembre 2009 à août 2011. La première cohorte a le taux le plus élevé et compte de loin le plus de clients.

Description textuelle du graphique

Un graphique en courbe de 21 cohortes. La première cohorte, décembre 2009, atteint le taux le plus élevé, soit 63,0 %, avec 951 clients. La courbe descend à 55,4 % et à 52,3 % en janvier et février 2010. Elle varie ensuite entre 35 % et 50 % pour la plupart des cohortes. Elle tombe à 25,2 % pour novembre 2010, puis à son minimum de 18,4 % pour décembre 2010, avec seulement 76 clients. En 2011, les cohortes comptent chacune de 72 à 179 clients. La courbe monte de 34,4 % à 52,3 % en août 2011.

Ce que cela nous apprend. À première vue, le tableau de bord a raison. Les premières cohortes revenaient beaucoup plus souvent que les suivantes. Mais deux éléments sont suspects. La première cohorte est de loin la meilleure, et aussi de loin la plus grande. Elle compte 951 clients, contre environ 100 par mois en 2011.

Réfléchissez au sens de « premier mois » ici. C’est le premier mois dans les données, pas celui de la toute première commande. La boutique vendait déjà avant décembre 2009. Un client fidèle qui achète chaque mois depuis des années apparaît pour la première fois dans les données en décembre 2009. La requête considère ce client comme « nouveau ». Les premières cohortes contiennent donc beaucoup de clients fidèles de longue date. Vous pouvez vérifier cette idée. Si les clients achetaient avant le début des données, certains retours concernent des marchandises jamais achetées dans les données. EXISTS (subquery) vaut 1 quand la sous-requête trouve au moins une ligne, et 0 quand elle n’en trouve aucune. Ainsi, sale_in_data indique si le client a acheté ce produit avant le retour :

WITH return_lines AS (
    SELECT r.month, r.line_no,
           EXISTS (SELECT 1 FROM goods_lines AS s
                   WHERE s.customer_id = r.customer_id
                     AND s.stock_code = r.stock_code
                     AND s.kind = 'sale'
                     AND s.invoiced_at <= r.invoiced_at) AS sale_in_data
    FROM goods_lines AS r
    WHERE r.kind = 'return' AND r.customer_id IS NOT NULL
)
SELECT CASE WHEN month <= '2010-02' THEN '1: Dec 2009 to Feb 2010'
            WHEN month <= '2010-11' THEN '2: Mar to Nov 2010'
            ELSE                         '3: Dec 2010 to Dec 2011' END AS period,
       COUNT(*) AS return_lines,
       ROUND(100.0 * SUM(NOT sale_in_data) / COUNT(*), 1) AS sold_before_the_data_pct
FROM return_lines
GROUP BY period
ORDER BY period

period                   return_lines  sold_before_the_data_pct
-----------------------  ------------  ------------------------
1: Dec 2009 to Feb 2010  2096          42.8
2: Mar to Nov 2010       7007          7.0
3: Dec 2010 to Dec 2011  8539          1.7
3 rows

Ce que cela nous apprend. Dans les trois premiers mois, 42,8 % des marchandises retournées avaient été achetées avant le début des données. Un an plus tard, cette proportion n’était que de 1,7 %. L’historique avant décembre 2009 est donc bien réel et important. Il remplit les premières cohortes de clients de longue date. Comparez directement deux cohortes de décembre :

WITH active AS (
    SELECT DISTINCT customer_id, month
    FROM goods_lines
    WHERE kind = 'sale' AND customer_id IS NOT NULL
),
first_month AS (
    SELECT customer_id, MIN(month) AS cohort FROM active GROUP BY customer_id
),
came_back AS (
    SELECT f.cohort, f.customer_id,
           MAX(a.month >  f.cohort AND
               a.month <= strftime('%Y-%m', f.cohort || '-01', '+3 months')) AS back
    FROM first_month AS f
    JOIN active AS a ON a.customer_id = f.customer_id
    GROUP BY f.cohort, f.customer_id
)
SELECT CASE WHEN cohort = '2009-12' THEN 'Dec 2009: first month of the data'
            WHEN cohort = '2010-12' THEN 'Dec 2010: 12 months of history before' END AS cohort,
       COUNT(*) AS customers,
       ROUND(100.0 * AVG(back), 1) AS back_within_3_months_pct
FROM came_back
WHERE cohort IN ('2009-12', '2010-12')
GROUP BY cohort

cohort                                 customers  back_within_3_months_pct
-------------------------------------  ---------  ------------------------
Dec 2009: first month of the data      951        63.0
Dec 2010: 12 months of history before  76         18.4
2 rows

Ce que cela nous apprend. Pour le même mois du calendrier, on compte 951 « nouveaux » clients, dont 63,0 % reviennent, puis 76, dont 18,4 % reviennent. Le deuxième résultat est proche de la réalité. Un client ayant acheté dans les 12 mois avant décembre 2010 ne fait pas partie de cette cohorte. Le premier résultat décrit surtout des clients de longue date. La « baisse de fidélisation » du tableau de bord reflète donc surtout le début des données, pas un changement dans la boutique.

Que montre un portrait fidèle? De janvier à août 2011, entre 34,4 % et 52,3 % des nouveaux clients ont acheté de nouveau dans les trois mois. Les cohortes de novembre 2010 (25,2 %) et de décembre 2010 (18,4 %) ont des taux faibles. Les clients qui achètent pour la première fois pendant la période de Noël reviennent moins souvent dans les trois mois suivants. Ces mois sont aussi les plus calmes de l’année. Pour Ines et Kwame, il faut donner aux clients de Noël une raison de revenir au printemps.

Dans le carnet (notebook) Jupyter, vous changez un seul élément : la période passe de trois à six mois.

Le produit le plus populaire dans chaque pays, avec les égalités

Ines veut aussi connaître, pour chaque pays, le produit présent dans le plus de commandes pendant la dernière saison. Cette saison va de septembre à novembre 2011. ROW_NUMBER() donne une ligne par pays. Mais que se passe-t-il quand deux produits figurent dans le même nombre de commandes? Examinez deux pays, avec ROW_NUMBER() et RANK() côte à côte :

WITH orders_per_product AS (
    SELECT country, stock_code, COUNT(DISTINCT invoice) AS orders
    FROM goods_lines
    WHERE kind = 'sale' AND month BETWEEN '2011-09' AND '2011-11'
    GROUP BY country, stock_code
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY country ORDER BY orders DESC) AS row_number,
           RANK()       OVER (PARTITION BY country ORDER BY orders DESC) AS rank
    FROM orders_per_product
)
SELECT country, stock_code, orders, row_number, rank
FROM ranked
WHERE country IN ('Spain', 'Switzerland') AND rank = 1
ORDER BY country, row_number

country      stock_code  orders  row_number  rank
-----------  ----------  ------  ----------  ----
Spain        22077       6       1           1
Spain        22423       6       2           1
Switzerland  22326       6       1           1
Switzerland  22551       6       2           1
Switzerland  22554       6       3           1
5 rows

Ce que cela nous apprend. En Espagne, deux produits figuraient chacun dans 6 commandes. En Suisse, trois produits étaient dans ce cas. RANK() leur attribue à tous le rang 1. ROW_NUMBER() attribue le rang 1 à un seul produit. Mais le ORDER BY ne précise pas lequel, alors SQLite choisit. Sur un autre ordinateur ou avec une autre version, WHERE row_number = 1 peut renvoyer un autre produit, sans avertissement. Est-ce fréquent?

WITH orders_per_product AS (
    SELECT country, stock_code, COUNT(DISTINCT invoice) AS orders
    FROM goods_lines
    WHERE kind = 'sale' AND month BETWEEN '2011-09' AND '2011-11'
    GROUP BY country, stock_code
),
ranked AS (
    SELECT *, RANK() OVER (PARTITION BY country ORDER BY orders DESC) AS rank
    FROM orders_per_product
)
SELECT COUNT(DISTINCT country) AS countries,
       COUNT(DISTINCT CASE WHEN tied > 1 THEN country END) AS countries_with_a_tie_at_the_top
FROM (SELECT country, COUNT(*) AS tied FROM ranked WHERE rank = 1 GROUP BY country)

countries  countries_with_a_tie_at_the_top
---------  -------------------------------
30         16
1 row

Ce que cela nous apprend. Dans 16 des 30 pays, plusieurs produits partagent la première place. La plupart sont de petits marchés avec peu de commandes. Départagez donc les égalités avec une règle que vous pouvez expliquer. Classez d’abord par nombre de commandes, puis par ventes nettes, puis par code. Indiquez aussi combien de produits étaient à égalité. COUNT(*) OVER (PARTITION BY country, orders) est une fonction de fenêtre sans ORDER BY. Elle compte les lignes du même pays ayant le même nombre de commandes, et conserve chaque ligne. La requête affiche seulement les pays dont le premier produit figure dans au moins 6 commandes :

WITH orders_per_product AS (
    SELECT country, stock_code, COUNT(DISTINCT invoice) AS orders,
           ROUND(SUM(amount)) AS net_sales
    FROM goods_lines
    WHERE kind = 'sale' AND month BETWEEN '2011-09' AND '2011-11'
    GROUP BY country, stock_code
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY country
                              ORDER BY orders DESC, net_sales DESC, stock_code) AS place,
           COUNT(*) OVER (PARTITION BY country, orders) AS same_orders
    FROM orders_per_product
)
SELECT r.country, r.stock_code, p.description, r.orders, r.net_sales,
       r.same_orders AS products_with_as_many_orders
FROM ranked AS r
JOIN products AS p ON p.stock_code = r.stock_code
WHERE r.place = 1 AND r.orders >= 6
ORDER BY r.orders DESC

country         stock_code  description                          orders  net_sales  products_with_as_many_orders
--------------  ----------  -----------------------------------  ------  ---------  ----------------------------
United Kingdom  22086       PAPER CHAIN KIT 50'S CHRISTMAS       758     43602.0    1
France          23084       RABBIT NIGHT LIGHT                   40      4554.0     1
Germany         22326       ROUND SNACK BOXES SET OF4 WOODLAND   32      1061.0     1
EIRE            22423       REGENCY CAKESTAND 3 TIER             24      1947.0     1
Belgium         22326       ROUND SNACK BOXES SET OF4 WOODLAND   16      739.0      1
Norway          22720       SET OF 3 CAKE TINS PANTRY DESIGN     9       314.0      1
Netherlands     22629       SPACEBOY LUNCH BOX                   8       1525.0     1
Italy           22960       JAM MAKING SET WITH JARS             7       218.0      1
Portugal        22557       PLASTERS IN TIN VINTAGE PAISLEY      7       91.0       1
Finland         84997D      PINK 3 PIECE MINI DOTS CUTLERY SET   6       1009.0     1
Spain           22423       REGENCY CAKESTAND 3 TIER             6       666.0      2
Switzerland     22326       ROUND SNACK BOXES SET OF4 WOODLAND   6       351.0      3
12 rows

Ce que cela nous apprend. Le résultat est maintenant identique sur tous les ordinateurs. La dernière colonne indique où le choix était serré. Au Royaume-Uni, un ensemble de guirlandes de Noël en papier figurait dans 758 commandes. C’est un gagnant évident pour le plan d’achat d’Ines. Dans les autres pays, « le premier produit » figure dans 6 à 40 commandes. Dans les 18 pays que la requête n’affiche pas, il figure dans 1 à 4 commandes. C’est trop peu pour planifier les stocks par pays. Les mêmes quelques produits, comme les boîtes à collations et les présentoirs à gâteaux, arrivent premiers dans plusieurs pays. Ines devrait donc planifier les petits marchés ensemble.

Livrer une table mensuelle vérifiée

Kwame a besoin d’une table que ses rapports peuvent lire chaque mois. Il doit pouvoir s’y fier sans refaire ce travail. La table monthly_sales contient une ligne par mois : jours d’activité, commandes, clients inscrits, ventes, retours, ventes nettes, taux de retour. Elle contient aussi la variation par rapport au même mois de l’année précédente et un indicateur de mois incomplet.

DROP TABLE IF EXISTS monthly_sales;
CREATE TABLE monthly_sales AS
WITH monthly AS (
    SELECT month,
           COUNT(DISTINCT substr(invoiced_at, 1, 10)) AS trading_days,
           COUNT(DISTINCT CASE WHEN kind = 'sale' THEN invoice END) AS orders,
           COUNT(DISTINCT CASE WHEN kind = 'sale' THEN customer_id END) AS customers,
           ROUND(SUM(CASE WHEN kind = 'sale' THEN amount ELSE 0 END), 2) AS sales,
           ROUND(-SUM(CASE WHEN kind = 'return' THEN amount ELSE 0 END), 2) AS returns,
           ROUND(SUM(amount), 2) AS net_sales
    FROM goods_lines
    GROUP BY month
)
SELECT month, trading_days, orders, customers, sales, returns, net_sales,
       ROUND(returns / sales, 4) AS return_rate,
       LAG(net_sales, 12) OVER (ORDER BY month) AS net_sales_last_year,
       ROUND(net_sales / LAG(net_sales, 12) OVER (ORDER BY month) - 1, 4) AS change_vs_last_year,
       month < '2011-12' AS complete_month
FROM monthly
ORDER BY month;

Les quatre derniers mois de la table :

month    trading_days  orders  customers  sales       returns    net_sales   return_rate  net_sales_last_year  change_vs_last_year  complete_month
-------  ------------  ------  ---------  ----------  ---------  ----------  -----------  -------------------  -------------------  --------------
2011-09  26            1818    1259       1030475.36  17044.68   1013430.68  0.0165       844100.37            0.2006               1
2011-10  26            2005    1361       1106669.82  43992.16   1062677.66  0.0398       1072467.65           -0.0091              1
2011-11  26            2751    1660       1457745.53  25010.54   1432734.99  0.0172       1400559.24           0.023                1
2011-12  8             816     614        615493.27   174126.76  441366.51   0.2829       760323.91            -0.4195              0
4 rows

Ce que cela nous apprend. Décembre 2011 compte 8 jours d’activité et complete_month = 0. Un rapport peut donc l’exclure des comparaisons ou l’afficher avec un avertissement. customers compte seulement les clients inscrits. Les commandes des clients invités figurent dans orders et dans les colonnes de montants.

Les règles de validation

Voici les règles que la table doit toujours respecter, comme dans Les règles de validation. Chaque règle est une requête qui renvoie les lignes qui ne la respectent pas. Aucune ligne signifie que la règle est respectée :

rules = {
    "one row per month":
        "SELECT month FROM monthly_sales GROUP BY month HAVING COUNT(*) > 1",
    "no missing month":
        """SELECT month FROM (SELECT month, LAG(month) OVER (ORDER BY month) AS previous
                              FROM monthly_sales)
           WHERE previous IS NOT NULL
             AND month <> strftime('%Y-%m', previous || '-01', '+1 month')""",
    "net sales = sales - returns":
        "SELECT month FROM monthly_sales WHERE ABS(sales - returns - net_sales) > 0.02",
    "total equals goods_lines":
        """SELECT 1 FROM (SELECT SUM(net_sales) AS t FROM monthly_sales)
           WHERE ABS(t - (SELECT SUM(amount) FROM goods_lines)) > 0.5""",
    "return rate between 0 and 0.3":
        "SELECT month FROM monthly_sales WHERE return_rate < 0 OR return_rate > 0.3",
    "only the last month is incomplete":
        """SELECT month FROM monthly_sales
           WHERE complete_month = (month = (SELECT MAX(month) FROM monthly_sales))""",
}
failed = 0
for name, rule in rules.items():
    problems = con.execute(rule).fetchall()
    failed += bool(problems)
    print(f"{'FAIL' if problems else 'pass'}  {name}" + (f"  {problems[:3]}" if problems else ""))
print(f"{len(rules) - failed} of {len(rules)} rules pass")

pass  one row per month
pass  no missing month
pass  net sales = sales - returns
pass  total equals goods_lines
pass  return rate between 0 and 0.3
pass  only the last month is incomplete
6 of 6 rules pass

Ce que cela nous apprend. Les six règles sont respectées. Deux d’entre elles protègent les conclusions précédentes. « Aucun mois manquant » rend LAG(net_sales, 12) sûr. Avec un trou, reculer de 12 lignes ne reviendrait plus à reculer de 12 mois. « Le total égale goods_lines » transforme le rapprochement en règle. Ainsi, une multiplication des lignes ou un mois perdu déclenchera une erreur claire la prochaine fois. La règle du taux de retour accepte volontairement une plage large, de 0 à 0,3. La commande erronée de décembre 2011 donne 0,2829. Si une telle commande se reproduit et que le taux dépasse 0,3, la règle bloque le rapport. Une personne doit alors examiner le problème.

La note de provenance

Enfin, le carnet écrit monthly_sales.csv et une courte note de provenance, LINEAGE.md. Elle indique d’où viennent les chiffres et ce que chaque étape a changé, comme dans Suivre la provenance des données. Le code se trouve dans le carnet; voici ce qu’il écrit :

# monthly_sales.csv: lineage

- Source: Online Retail II, Daqing Chen, UCI Machine Learning Repository,
  https://doi.org/10.24432/C5CG6D, CC BY 4.0. File online_retail_ii.csv.gz
  (SHA-256 c5ab45f73700a1411440c2cd6b7d85611d22cdc72b3594c2d29d37261e977e4f), the two Excel sheets joined in order.
- raw_lines: 1,067,371 lines, loaded unchanged, with line_no = position in the file.
- invoice_lines: 1,044,848 lines. Removed the 22,523 lines of sheet 2 dated
  1-9 Dec 2010 (line_no >= 525462): an exact copy of sheet 1. Kept 11,812 repeats inside one sheet.
- goods_lines: 1,033,062 lines. Goods only (no postage, fees, manual lines,
  vouchers or bad-debt adjustments), unit price above 0, sales and returns.
- monthly_sales: 25 months, Dec 2009 to Dec 2011. Net sales = sales - returns, in GBP.
  Dec 2011 has 9 days only (complete_month = 0). Guest orders (no customer ID) count in sales,
  not in customers.
- Checks: the six validation rules in the notebook pass; the total equals goods_lines.
- Output: monthly_sales.csv, SHA-256 e04eb68728bf33eeaabf0f30074445bc1946c72949e12afabf58301dde4fd03b

Ce que cela nous apprend. Une personne qui lit monthly_sales.csv peut maintenant répondre à « d’où vient ce chiffre? » sans vous le demander. La somme de contrôle de l’entrée prouve quel fichier la génération a lu. Celle de la sortie permet à Kwame de voir si le fichier a changé après les vérifications.

Recommandations

Vous rédigez les recommandations pour Ines et Kwame avec leurs mots. Les chiffres couvrent la période du 1 décembre 2009 au 9 décembre 2011.

Pour Kwame (finances) :

1. Présentez des ventes nettes de marchandises de 18 981 261,52 £ pour les deux années, tirées de monthly_sales. Les deux autres totaux incluaient neuf journées copiées, ou les frais de livraison, les autres frais et les créances irrécouvrables. Conservez la table de rapprochement avec le rapport.
2. Ne resserrez pas la politique de retour à cause de janvier 2011. Les deux pics de retours provenaient chacun d’une commande erronée, annulée en moins de 16 minutes. Dans la moitié des mois complets, les retours représentaient 2,3 % des ventes ou moins. Présentez le taux de retour avec et sans les commandes erronées.
3. Demandez une vérification des quantités dans le système de commande. Une commande de 74 215 ou de 80 995 unités d’un produit devrait exiger une deuxième confirmation.
4. Remplacez le graphique de fidélisation. Ses premières cohortes contiennent surtout des clients qui achetaient avant le début des données. Présentez les cohortes à partir de décembre 2010, qui ont un an d’historique avant elles. En 2011, de 34 % à 52 % des nouveaux clients ont acheté de nouveau dans les trois mois.

Pour Ines (marchandisage) :

5. Placez les stocks de Noël dans l’entrepôt avant la fin d’août. La période de septembre à novembre représente 37,5 % des ventes nettes annuelles. Le sommet arrive en novembre, pas en décembre.
6. Prévoyez une croissance modeste. La saison a progressé de 5,8 % et l’année entière de 1,8 %. Les neuf premiers jours de décembre 2011 dépassaient ceux de 2010 de 9,7 %.
7. Planifiez par produit pour le Royaume-Uni, et par groupe de produits pour les autres marchés. Ailleurs, le premier produit d’un pays figurait dans 40 commandes ou moins. Dans 18 des 30 pays, il figurait dans moins de 5 commandes. Dans 16 pays, plusieurs produits partageaient la première place.

À quoi ces chiffres ne doivent pas servir. Ils décrivent une boutique de 2009 à 2011. Ce ne sont pas des prévisions : ils montrent ce qui s’est passé, pas ce qui se passera. Les nombres de clients comptent seulement les clients inscrits.

Limites et prochaines étapes

Les limites, dans l’ordre où Kwame devrait les connaître :

• Les 11 812 répétitions dans une même feuille sont conservées. Si ce sont des doubles saisies, les ventes nettes sont surestimées d’au plus 54 228 £ (0,3 %).
• L’historique avant décembre 2009 manque. La notion de « nouveau client » n’est pas fiable pendant la première année des données. Utilisez les cohortes à partir de décembre 2010.
• Les clients invités représentent 22,8 % des lignes brutes et 13,5 % des ventes nettes, mais ils n’ont pas d’identifiant. Les questions sur les clients décrivent seulement les clients inscrits.
• Les commandes erronées ont été repérées par leur taille. La règle repère un retour supérieur à 10 000 £ dans l’heure suivant la commande correspondante. Elle trouve les deux grandes commandes erronées. Les petites erreurs restent dans le taux de retour.
• Les codes autres que ceux des marchandises forment une liste. Un nouveau code de frais l’an prochain compterait comme une marchandise jusqu’à son ajout à la liste. La règle du total ne détecte pas cela; un profil des nouveaux codes chaque mois le ferait.

Prochaines étapes : ajoutez une règle qui liste les codes jamais vus auparavant. Exécutez ensuite la génération chaque mois sur la nouvelle exportation. Avec des chiffres mensuels propres, une prévision de la prochaine saison devient possible. Le cours d’apprentissage automatique enseigne les modèles.

En faire un projet de portfolio

Cette étude de cas peut devenir un projet public. Elle montre à un employeur que vous savez transformer une exportation désordonnée en chiffres fiables pour les finances. Elle montre aussi que vous pouvez expliquer chaque différence.

Quoi mettre dans le dépôt :

• README.md, avec cinq courtes parties :
  1. Problème : « Rendre fiables les données de vente d’une boutique en ligne et répondre à trois questions de planification : la saison de Noël, les pics de retours et la fidélisation des clients. »
  2. Données et licence : Online Retail II, UCI, CC BY 4.0, avec la référence et un lien vers la fiche du jeu de données.
  3. Approche : le profil, la feuille copiée repérée avec LAG, quatre tables avec des clés et des vérifications de lignes orphelines, la vue goods_lines et la table de rapprochement.
  4. Résultats : les trois totaux rapprochés à 18 981 261,52 £; la saison (37,5 % de l’année de septembre à novembre); les deux commandes erronées derrière les pics de retours; le piège des cohortes (63,0 % contre 18,4 %).
  5. Limites : la liste ci-dessus, dans vos mots.
• Le carnet, exécuté depuis le début, avec ses sorties.
• monthly_sales.csv et LINEAGE.md. Le carnet écrit les deux dans le dossier explain-an-online-shops-sales.
• Le SQL des quatre tables et de monthly_sales dans des fichiers .sql. Une personne pourra ainsi les exécuter sans le carnet.

Ne mettez pas shop.db ni le fichier de données dans votre dépôt : ils sont volumineux, et la source est publique. Ajoutez des liens vers la page UCI et la copie du cours, et conservez l’attribution.

Comment le décrire en entrevue, en deux phrases : « J’ai chargé dans SQLite l’exportation d’une vraie boutique en ligne, soit un million de lignes de factures, repéré neuf journées copiées avec une fonction de fenêtre et rapproché trois totaux de revenus différents pour obtenir un chiffre vérifié. Avec ce chiffre, j’ai montré que les pics de retours venaient de deux commandes erronées et que la baisse de fidélisation venait du début des données. »

Pour aller plus loin :

• Trouvez toutes les commandes erronées, pas seulement les deux grandes : une ligne de commande annulée entièrement par le même client dans l’heure. Comptez-les et traitez un retour correspondant à deux commandes sans le compter deux fois.
• Créez les mêmes tables dans PostgreSQL, avec de vrais types de dates et des clés étrangères toujours activées. Comparez les requêtes.
• Ajoutez une table hebdomadaire et comparez les semaines avant Noël des deux années.
• Utilisez monthly_sales.csv comme entrée d’une prévision simple de la prochaine saison, après le cours d’apprentissage automatique.

Vérification des connaissances

Vérifiez votre compréhension

Les deux tiers des répétitions exactes dataient du 1er au 9 décembre 2010. Comment avez-vous prouvé qu'elles étaient une copie, et pourquoi avez-vous gardé les 11 812 autres répétitions?

Afficher la réponse

Le fichier réunit deux feuilles Excel, une par année, en ordre chronologique. LAG(invoice_date) OVER (ORDER BY line_no) a montré que le temps recule une seule fois, à la ligne 525 462. C’est le début de la deuxième feuille. Les neuf jours comptaient alors 22 523 lignes dans chaque feuille, sans aucune différence. Vous avez donc retiré la deuxième copie. Les autres répétitions se trouvent dans une même feuille. Il peut s’agir du même produit saisi deux fois sur une facture, ce qui peut être réel. Personne ne peut prouver que ce sont des copies. Elles représentent 54 228 £, soit 0,3 % du montant. Vous les gardez donc et notez ce choix comme une limite. SELECT DISTINCT * aurait retiré les deux types sans laisser de trace.

Vérifiez votre compréhension

Kwame joint une table de totaux de factures aux lignes de facture et obtient plus d'un milliard de livres pour le Royaume-Uni. Que s'est-il passé, et quelles sont les deux habitudes qui permettent de repérer cette erreur?

Afficher la réponse

Les totaux de factures ont une ligne par facture, et les lignes de facture en ont plusieurs par facture. La jointure répète le total de chaque facture une fois pour chacune de ses lignes. Ainsi, SUM(total) additionne le même total plusieurs fois : la facture 489434 avec 8 lignes donne 8 × 505,30 £. La solution consiste à additionner au niveau de détail du montant : SUM(amount) sur les lignes de facture. Les deux habitudes : compter les lignes avant et après chaque jointure, et rapprocher chaque total d’un total vérifié (ici 18 981 261,52 £). La règle « total égal à goods_lines » automatise la deuxième habitude.

Vérifiez votre compréhension

La cohorte de décembre 2009 est revenue à 63,0 %, celle de décembre 2010 à 18,4 %. La boutique fidélise-t-elle moins bien ses nouveaux clients?

Afficher la réponse

En grande partie, non. Les données commencent en décembre 2009, mais la boutique existait déjà. Le « premier mois dans les données » n’est pas le « premier achat ». La cohorte de décembre 2009 compte donc beaucoup de clients fidèles de longue date. La preuve : 42,8 % des retours des trois premiers mois concernaient des produits achetés avant le début des données. Un an plus tard, cette proportion était de 1,7 %. La cohorte de décembre 2010 est précédée d’un an d’historique, elle regroupe donc de nouveaux clients. Comparez les cohortes ayant le même historique, à partir de décembre 2010. En 2011, de 34 % à 52 % des nouveaux clients sont revenus dans les trois mois. Les cohortes de Noël reviennent moins souvent pendant les mois calmes après Noël. C’est un constat réel et utile.

Récapitulatif

Dans cette étude de cas, vous avez transformé 1 067 371 lignes de facture brutes en chiffres fiables pour une équipe financière :

• Un profil avant toute modification a repéré les clients invités (22,8 % des lignes), les retours, les notes de stock, les codes hors marchandises et les lignes répétées. LAG appliqué à l’ordre du fichier a prouvé que 22 523 lignes provenaient d’une feuille copiée.
• Quatre tables avec des clés, contenant une ligne par clé et vérifiées par des requêtes qui cherchent les lignes orphelines. Les règles d’affaires se trouvent dans une seule vue, goods_lines.
• Un rapprochement a expliqué trois totaux et retenu 18 981 261,52 £ de ventes nettes de marchandises. Une jointure des totaux de factures aux lignes a produit un total 63 fois trop élevé, sans erreur.
• Les fonctions de fenêtre et les CTE ont répondu aux questions : la saison va de septembre à novembre (37,5 % de l’année). Les deux pics de retours étaient des commandes erronées, et la meilleure cohorte correspondait seulement au début des données.
• Une livraison vérifiée : monthly_sales avec six règles de validation et une note sur la provenance des données.

Ensuite, un dossier de projet : une table sans fuite qui signale les demandes de réparation d’une ville qui seront en retard.

Leçon suivante : Préparer une table sans fuite pour les demandes de réparation d'une ville