SQL Project pour le portefeuille : Magasin Northwind

Les projets SQL pour un portefeuille sont une partie importante de la formation d'un analyste de données. Comment démarrez-vous un projet, et où allez-vous avec les informations que vous découvrez ? Nous utiliserons la base de données du magasin Northwind pour répondre à ces questions.

La réalisation d'un projet SQL solide est un élément essentiel du processus d'apprentissage de tout analyste de données. Un projet SQL est un excellent outil d'apprentissage, car il vous oblige à utiliser SQL sur un ensemble de données réelles. C'est particulièrement important si vous êtes un débutant et que vous n'avez pas l'occasion de travailler avec des ensembles de données réels. En travaillant avec plusieurs ensembles de données réels, vous apprenez les problèmes que vous pouvez rencontrer dans le monde réel. En outre, la réalisation de plusieurs projets SQL dans le cadre de votre portefeuille est toujours une bonne chose à voir dans un CV.

La question est de savoir comment préparer un portefeuille de projets SQL. Une fois que vous avez trouvé un jeu de données en ligne gratuit que vous souhaitez analyser, que faites-vous ensuite ? Nous allons répondre à ces questions en utilisant la base de données du magasin Northwind.

Vous trouverez cet ensemble de données dans le cours Bases de données SQL pour la pratique. Il s'agit de l'un des six ensembles de données qui comprennent des données provenant d'une université, du trafic d'un blog, de résultats sportifs, d'un magasin de musique et du Museum of Modern Art (MoMA). Ce cours fait partie de la pisteLa pratique du SQL , où vous pouvez pratiquer l'agrégation, JOINs, les sous-requêtes, les CTE, CASE WHEN, et d'autres sujets SQL importants. Si vous avez besoin de rafraîchir vos connaissances sur certains de ces domaines importants pour la création de rapports, essayez notre cours de Création de rapports basiques en SQL cours.

Prenons maintenant l'ensemble de données du magasin Northwind et utilisons-le pour un projet SQL destiné à un portefeuille d'analystes de données. Je vais réaliser ce projet avec PostgreSQL, mais tout ce que je fais est transférable à d'autres bases de données avec des modifications mineures de la syntaxe.

N'hésitez pas à consulter notre aide-mémoire SQL pour l'analyse de données.

Le jeu de données du magasin Northwind

La seule information que je vous donnerai sur cette base de données est qu'elle se compose de six tables :

1. categories - Une liste de catégories de produits.
2. channels - Une liste des sources par lesquelles le magasin acquiert des clients.
3. customers - Une liste des clients du magasin.
4. order_items - Une liste des produits inclus dans chaque commande.
5. orders - La liste des commandes passées par les clients.
6. products - la liste des produits proposés par le magasin.

Nous recueillerons le reste des informations en réalisant notre projet. En fait, cette inspection de la base de données devrait constituer l'étape initiale de tout projet, celle qui précède le début de l'analyse.

1. Inspection de la base de données

Cette étape de la préparation d'un projet SQL consiste à connaître vos données, telles que les noms des tables et des colonnes, les clés primaires et étrangères, les relations entre les tables et les types de données de chaque table.

Inspection des noms de tables

Une première façon de connaître les tables de l'ensemble de données est de les trouver dans le navigateur du SGBDR dans lequel vous avez importé l'ensemble de données, par exemple PostgreSQL, SQL Server ou MySQL.

Dans PostgreSQL, vous pouvez écrire cette requête pour obtenir une liste de toutes les tables d'une base de données :

SELECT table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog') AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

Elle recherche les données dans la table information_schemaCette requête recherche les données dans la base de données "tables", où vous pouvez trouver les métadonnées de la base de données. Comme je souhaite obtenir le nom de chaque table, j'utilise la vue tables après avoir référencé le schéma et placé table_name dans SELECT.

La première condition dans WHERE filtre les schémas système et ne laisse que les tables définies par l'utilisateur. La deuxième condition garantit que seules les tables de base sont répertoriées, sans les vues et les autres tables.

Voici la liste des tables de la base de données Northwind :

table_name
categories
channels
customers
order_items
orders
products

Inspection des informations sur les colonnes

Nous voulons maintenant mieux comprendre les détails de chaque table. Connaître leurs colonnes est un bon début.

Nous pouvons à nouveau interroger information_schema pour obtenir des informations importantes sur les colonnes :

SELECT table_name,
       column_name,
       data_type,
       is_nullable,
       column_default
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_name, ordinal_position;

Nous pouvons trouver les noms des tables, les noms des colonnes et le type de données de chaque colonne en listant table_name, column_name, et data_type dans SELECT.

La colonne is_nullable nous indiquera si la colonne accepte les valeurs NULL.

table_name	column_name	data_type	is_nullable
categories	category_id	integer	NO
categories	category_name	character varying	NO
categories	description	text	YES
channels	id	integer	NO
channels	channel_name	character varying	NO
customers	customer_id	integer	NO
customers	email	character varying	NO
customers	full_name	character varying	NO
customers	address	character varying	YES
customers	city	character varying	YES
customers	region	character varying	YES
customers	postal_code	character varying	YES
customers	country	character varying	YES
customers	phone	character varying	YES
customers	registration_date	timestamp without time zone	NO
customers	channel_id	integer	NO
customers	first_order_id	integer	YES
customers	first_order_date	timestamp without time zone	YES
customers	last_order_id	integer	YES
customers	last_order_date	timestamp without time zone	YES
order_items	order_id	integer	NO
order_items	product_id	integer	NO
order_items	unit_price	numeric	NO
order_items	quantity	smallint	NO
order_items	discount	numeric	NO
orders	order_id	integer	NO
orders	customer_id	integer	NO
orders	order_date	timestamp without time zone	YES
orders	total_amount	numeric	NO
orders	ship_name	character varying	YES
orders	ship_address	character varying	YES
orders	ship_city	character varying	YES
orders	ship_region	character varying	YES
orders	ship_postalcode	character varying	YES
orders	ship_country	character varying	YES
orders	shipped_date	timestamp without time zone	YES
products	product_id	integer	NO
products	product_name	character varying	NO
products	category_id	integer	NO
products	unit_price	numeric	YES
products	discontinued	boolean	NO

De cette manière, nous disposons de toutes les informations en un seul endroit, ce qui facilite les recherches.

Tout d'abord, nous pouvons comprendre quelles sont les données de chaque tableau en voyant les noms des colonnes. Les types de données semblent tous logiques. Par exemple, aucun ID n'est défini comme character varying. Il n'est donc pas nécessaire de convertir les données dans un format approprié à ce stade.

En ce qui concerne les NULL, nous obtenons des informations précieuses sur les tables :

• Le tableau categories permet à la description de la catégorie d'être NULL.
• Le tableau channels n'autorise pas du tout NULLs.
• Le tableau customers autorise les NULLdans de nombreuses colonnes, y compris le pays. Si nous voulons créer une segmentation géographique des clients, ces NULLpourraient nous poser des problèmes.
• La sortie ci-dessus montre qu'aucune colonne de la table order_items n'est nullable.
• Pour la table products il est intéressant de noter que le prix unitaire peut être NULL.
• Il semble un peu étrange que la table orders ait autant de colonnes nullables. En effet, il est possible d'avoir un numéro d'identification de l'ordre et aucune autre information sur l'ordre. Nous devons garder cela à l'esprit lors de l'analyse de cette table.

Recherche des clés primaires et étrangères

L'étape suivante de l'inspection des données consiste à comprendre comment les tables fonctionnent ensemble. Pour ce faire, nous devons d'abord trouver les clés primaires (PK) et les clés étrangères (FK).

Les clés primaires nous indiquent quelle(s) colonne(s) une table utilise(nt) pour identifier les données de manière unique. Ce faisant, vous comprendrez mieux la structure de la table.

Les clés primaires nous indiquent quelle colonne est liée à une clé primaire d'une autre table. C'est la base pour comprendre les relations entre les tables.

Vous pouvez dresser la liste des PK et des FK à l'aide de la requête ci-dessous :

SELECT kcu.table_name,
       kcu.column_name,
       tc.constraint_type
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
ON kcu.constraint_name = tc.constraint_name
AND kcu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY kcu.table_name ASC, tc.constraint_type DESC;

Cette fois, nous devons utiliser deux vues de la table information_schemaà savoir table_constraints et key_column_usage.

La vue table_constraints contient des informations sur les contraintes de chaque table. La vue key_column_usage permet d'obtenir des informations sur les colonnes ayant des contraintes de clé.

Ces deux vues sont reliées par deux colonnes communes : constraint_name (le nom de la contrainte) et constraint_schema (le nom du schéma contenant la contrainte).

J'utilise la clause WHERE pour ne sortir que les contraintes de clé primaire et de clé étrangère.

Pour finir, j'ordonne la sortie par ordre alphabétique du nom de la table, puis par ordre alphabétique inverse du type de contrainte.

Le code produit cette table :

table_name	column_name	constraint_type
categories	category_id	PRIMARY KEY
channels	id	PRIMARY KEY
customers	customer_id	PRIMARY KEY
customers	last_order_id	FOREIGN KEY
customers	first_order_id	FOREIGN KEY
customers	channel_id	FOREIGN KEY
order_items	order_id	PRIMARY KEY
order_items	product_id	PRIMARY KEY
order_items	order_id	FOREIGN KEY
order_items	product_id	FOREIGN KEY
orders	order_id	PRIMARY KEY
orders	customer_id	FOREIGN KEY
products	product_id	PRIMARY KEY
products	category_id	FOREIGN KEY

Les tables categories et channels sont les seules tables qui ont une PK mais pas de FK.

Toutes les autres tables ont une PK et au moins une FK. La seule exception est la table order_itemsorder_id product_idEn d'autres termes, la table possède une clé primaire composite, ce qui signifie que l'identifiant unique des données est la combinaison unique de deux colonnes. Dans ce cas, il s'agit d'afficher un seul numéro de produit par commande individuelle.

Un examen plus approfondi révèle que les mêmes colonnes sont également une clé étrangère pour la même table. Cela signifie que les clés primaires sont également étrangères, puisqu'elles proviennent d'autres tables.

Comprendre les relations entre les tables

L'énumération des clés primaires et étrangères nous donne déjà une idée des relations entre les tables. Cependant, nous pouvons mieux les comprendre en montrant les tables et colonnes mères et filles des clés étrangères.

Pourquoi faire cela ? Une clé étrangère est toujours la clé primaire d'une autre table. L'énumération de toutes les tables et colonnes mères et filles constitue une amélioration par rapport à la requête précédente, car elle permet de voir facilement les colonnes partagées entre les tables.

Ces informations sont utiles pour comprendre les relations entre les tables, savoir quelles colonnes vous pouvez utiliser pour joindre les tables et si elles peuvent être jointes directement ou par l'intermédiaire d'une autre table.

Voici le code :

SELECT ccu.table_name AS parent_table,
       ccu.column_name AS parent_column,
       kcu.table_name AS child_table,
       kcu.column_name AS child_column      
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY kcu.table_name;

Dans la requête, nous utilisons des informations provenant des tables table_constraints, key_column_usage, et constraint_column_usage dans la requête.

La vue constraint_column_usage montre les colonnes utilisées par une contrainte. Nous l'utiliserons pour montrer la table et la colonne parentes du FK, c'est-à-dire la table et la colonne où ce FK est en fait un PK.

Les données de key_column_usage affichent la table et la colonne d'un FK.

parent_table	parent_column	child_table	child_column
orders	order_id	customers	last_order_id
orders	order_id	customers	first_order_id
channels	id	customers	channel_id
orders	order_id	order_items	order_id
products	product_id	order_items	product_id
customers	customer_id	orders	customer_id
categories	category_id	products	category_id

D'après la sortie ci-dessus, nous voyons que la table orders est directement connectée à la table customers via les colonnes order_id (PK) et customer_id (FK). Il est connecté à la table order_items via order_id (PK).

La table channels n'est directement connectée qu'à la table customers.

Nous avons déjà établi que les clés primaires de la table order_items sont également des clés étrangères. D'après le tableau ci-dessus, nous pouvons voir qu'elles proviennent des tables orders et products.

La table categories n'est reliée qu'à la table products.

2. Exploration des données

Lorsque vous commencez à travailler avec une nouvelle base de données, la première étape consiste à explorer vos données afin d'obtenir des informations de base sur chaque table de la base de données. C'est une bonne pratique que d'examiner les éléments suivants :

• Compter les lignes.
• Trouver les valeurs minimales et maximales (en particulier pour les dates).
• Dresser la liste des catégories distinctes.
• Compter les valeurs distinctes dans chaque catégorie.
• compter les lignes par valeur de catégorie
• Sommes et moyennes pour les valeurs numériques clés.

Cette liste n'est pas exhaustive et peut changer en fonction du tableau que vous explorez. Il s'agit toutefois d'un minimum qui devrait fonctionner pour la plupart des tableaux.

En général, cette étape se résume à l'agrégation et au regroupement des données. Je vais vous montrer comment cela fonctionne sur une table de notre base de données, puis vous pourrez explorer d'autres tables de la même manière.

Exploration du tableau des commandes

Nombre de lignes

Comme prévu, nous utilisons ici la fonction d'agrégation COUNT():

SELECT COUNT(*) AS number_of_rows
FROM orders;

J'utilise la fonction COUNT() avec un astérisque ; cela comptera toutes les lignes, y compris les NULL.

number_of_rows
11,618

La table orders compte 11 618 lignes. En d'autres termes, il y a 11 618 commandes.

Valeurs minimales et maximales

Voyons quelles colonnes peuvent être utilisées avec les fonctions MIN() et MAX(). Parfait - nous pouvons trouver la date la plus ancienne et la plus récente à laquelle une commande a été passée et expédiée :

SELECT MIN(order_date) AS oldest_order_date, 
	 MAX(order_date) AS latest_order_date,
	 MIN(shipped_date) AS oldest_shipped_date,
	 MAX(shipped_date) AS latest_shipped_date
FROM orders;

Le résultat montre que la première commande a été passée à l'adresse 2017-01-01 et la dernière à l'adresse 2024-06-24. En ce qui concerne les dates d'expédition, la première date est 2017-01-01 et la plus récente est 2024-06-23.

oldest_order_date	latest_order_date	oldest_shipped_date	latest_shipped_date
2017-01-01 0:00:00	2024-06-24 0:00:00	2017-01-06 0:00:00	2024-06-23 0:00:00

Lister les catégories

Nous n'avons pas besoin de fonctions d'agrégation pour ce faire :

SELECT DISTINCT ship_country
FROM orders
ORDER BY ship_country;

Dans la requête, nous utilisons la clause DISTINCT pour n'afficher chaque pays qu'une seule fois.

Voici la liste des pays figurant dans ce tableau :

ship_country
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland
Italy
Mexico
Norway
Poland
Portugal
Spain
Sweden
Switzerland
UK
USA
Venezuela

Il sera également utile de voir une liste de villes :

SELECT DISTINCT ship_city
FROM orders
ORDER BY ship_city;

Comme il y a beaucoup de villes, voici une liste partielle :

ship_city
Aachen
Abilene
Achille
Adelphi
Adrian
Akron
Albany
Alberton
Albuquerque
…
Yucca Valley

Comptage des valeurs distinctes dans chaque catégorie

Voyons maintenant le nombre de pays dans lesquels Northwind a effectué des livraisons :

SELECT COUNT(DISTINCT ship_country) AS number_of_countries
FROM orders;

Nous utilisons à nouveau COUNT(), mais cette fois nous ajoutons DISTINCT et nous référençons la colonne que nous voulons compter.

Le résultat montre qu'il y a 21 pays différents où l'entreprise livre :

number_of_countries
21

Nous pourrions faire la même chose pour les villes :

SELECT COUNT(DISTINCT ship_city) AS number_of_cities
FROM orders;

Il y a 494 villes uniques :

number_of_cities
494

Même si nous ne les avons pas répertoriées dans une catégorie distincte, il sera utile de savoir combien de clients ont passé des commandes :

SELECT COUNT(DISTINCT customer_id) AS number_of_customers_with_orders
FROM orders;

Nous utilisons COUNT() pour compter les identifiants des clients. Bien entendu, nous avons besoin de DISTINCT, car on peut s'attendre à ce que certains clients apparaissent plusieurs fois, c'est-à-dire chaque fois qu'ils passent une commande.

Il y a 761 clients uniques qui ont commandé au moins une fois à l'entreprise.

number_of_customers_with_orders
761

Comptage des lignes par valeur de catégorie

Pour ce faire, nous devons énumérer chaque valeur de catégorie (nous l'avons fait précédemment) et compter le nombre de lignes pour chaque valeur.

Par exemple, nous obtiendrons le nombre de commandes par pays :

SELECT ship_country,
	 COUNT(*) AS number_of_orders
FROM orders
GROUP BY ship_country
ORDER BY number_of_orders DESC;

Je sélectionne la colonne ship_country et j'utilise ensuite COUNT(*) pour compter toutes les lignes. Pour que le comptage se fasse par pays, je dois introduire la clause GROUP BY ship_country.

Nous obtenons ainsi le nombre de commandes par pays. Le résultat montre que les États-Unis sont le marché le plus important, avec 9 789 commandes :

ship_country	number_of_orders
USA	9,789
France	236
Brazil	233
Germany	230
UK	144
Spain	123
Mexico	118
Venezuela	103
Argentina	87
Canada	82
Italy	64
Austria	58
Portugal	58
Belgium	56
Denmark	56
Finland	47
Norway	30
Sweden	28
Poland	27
Ireland	26
Switzerland	23

Somme et valeurs moyennes

Si vos données sont appropriées, vous devez trouver des moyennes et des totaux de valeurs numériques. Nous pouvons le faire pour la colonne total_amount, qui représente la valeur d'une commande :

SELECT SUM(total_amount) AS orders_total_value,
       AVG(total_amount) AS average_order_value
FROM orders;

Pour afficher ces deux calculs, utilisez les fonctions d'agrégation SUM() et AVG().

Nous pouvons voir que le chiffre d'affaires total du magasin est d'un peu plus de 19 millions. La valeur moyenne d'une commande est de 1 636,15 :

orders_total_value	average_order_value
19,008,819.69	1,636.15

Exploration des données en prime

Comme je l'ai mentionné, les agrégations précédentes devraient être le minimum que vous fassiez pour chaque table de la base de données.

Cette exploration des données va un peu plus loin (mais pas trop loin) que les simples agrégations et GROUP BY. Bien qu'il s'agisse toujours de la base, vous pouvez également utiliser d'autres concepts tels que le filtrage des données (à l'aide de WHERE et/ou HAVING), l'extraction de parties de dates ou de l'heure, l'utilisation de CASE WHEN pour étiqueter les données, et ainsi de suite.

Examinons quelques exemples.

Nombre de produits

Nous pouvons utiliser la fonction COUNT() pour trouver le nombre total de produits vendus par le magasin.

SELECT COUNT(*) AS number_of_active_products
FROM products
WHERE discontinued IS FALSE; 

La clause WHERE contient une condition pour n'afficher que les produits qui ne sont pas en fin de série, c'est-à-dire les produits que le magasin vend actuellement.

D'après la section précédente, nous savons que la colonne discontinued est de type booléen. Nous devons donc utiliser l'opérateur IS FALSE pour n'inclure dans le décompte que les produits qui ne sont pas en rupture de stock.

Le résultat est 69 :

number_of_active_products
69

Recettes par année

Un rapport simple indiquant les recettes par année peut être créé à l'aide de SUM():

SELECT EXTRACT(YEAR FROM order_date) AS revenue_year,
       SUM(total_amount) AS revenue  
FROM orders
GROUP BY revenue_year;

Nous utilisons la fonction EXTRACT() pour obtenir uniquement les années à partir des dates de commande. Ensuite, nous additionnons les montants totaux de toutes les commandes et nous les regroupons par année pour afficher les valeurs de chaque année séparément.

Les résultats montrent que l'année 2018 a été l'année de pointe pour l'entreprise. L'année où les recettes sont les plus faibles est 2024, mais cela peut s'expliquer par le fait que l'année n'est pas terminée (au moment de l'analyse).

Un autre point intéressant est qu'il n'y a pas de données sur les revenus pour les années 2019-2022. Il convient de vérifier si les données sont manquantes pour une raison précise ou s'il s'agit d'une erreur.

revenue_year	revenue
2017	3,088,759.84
2018	9,368,330.91
2023	4,646,048.11
2024	1,905,680.83

Segmentation des clients par pays

Nous disposons de données sur les pays des clients, de sorte qu'un aperçu du nombre de clients dans chaque pays serait instructif.

Nous ne sommes pas non plus intéressés par les clients qui n'ont pas passé de commande, car cela peut gonfler artificiellement le nombre de clients. Non, nous ne voulons que les clients qui commandent chez nous. Voici la requête :

SELECT country,
	 COUNT(*) AS number_of_customers
FROM customers
WHERE first_order_id IS NOT NULL
GROUP BY country
ORDER BY number_of_customers DESC;

Nous sélectionnons et regroupons par pays et utilisons la fonction d'agrégation COUNT() pour trouver le nombre de clients. Si les données de la colonne first_order ne sont pas nulles, alors ce client a passé au moins une commande ; c'est la condition que nous devons utiliser dans WHERE.

Les données sont classées du plus grand au plus petit nombre de clients.

Le résultat montre que le marché le plus important de Northwind en termes de clients est celui des États-Unis. On peut également en conclure qu'il s'agit du marché le plus important en termes de chiffre d'affaires.

country	number_of_customers
USA	697
Germany	8
France	8
Brazil	8
UK	5
Venezuela	4
Spain	4
Mexico	4
Argentina	3
Canada	3
Belgium	2
Denmark	2
Portugal	2
Finland	2
Italy	2
Austria	2
Sweden	1
Poland	1
Ireland	1
Switzerland	1
Norway	1

3. Analyse avancée des données et informations exploitables

Ce que nous avons fait jusqu'à présent est un bon début. Cependant, l'analyse des données dans un projet SQL pour un portefeuille ne doit pas s'arrêter à une simple agrégation et à une exploration pour chaque table.

Nous allons maintenant aller plus loin et écrire des requêtes plus complexes qui nous permettront de prendre des mesures et d'améliorer les activités de Northwind. Par exemple, nous pourrions vouloir voir comment les ventes changent en fonction du canal. Voyons ce que nous obtenons et décidons ensuite des prochaines étapes.

Ventes par canal

Pour chaque canal, nous voulons afficher les ventes totales, la valeur moyenne des commandes, le nombre total de commandes et le nombre de clients uniques. Nous voulons également classer les canaux en fonction des ventes totales. Voici la requête :

SELECT ch.channel_name,
       SUM(o.total_amount) AS total_sales,
       AVG(o.total_amount) AS average_order_value,
       COUNT(o.order_id) AS total_orders,
       COUNT(DISTINCT o.customer_id) AS unique_customers,
       RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS sales_rank
FROM channels ch
LEFT JOIN customers c
ON ch.id = c.channel_id
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY ch.channel_name
ORDER BY sales_rank;

Cette requête utilise plusieurs agrégations : SUM() pour calculer les ventes totales, AVG() pour la valeur moyenne des commandes, et COUNT() pour le nombre total de commandes et (avec DISTINCT) pour les clients uniques.

Ensuite, nous utilisons la fonction de fenêtre RANK() pour classer les chaînes en fonction de leurs ventes totales. Étant donné que les fonctions de fenêtre sont exécutées avant l'agrégation, nous ne pouvons pas simplement utiliser la colonne total_sales dans la fonction de fenêtre. Au lieu de cela, je dois reproduire l'ensemble du calcul - une tâche simple qui s'effectue par copier-coller.

Nous utilisons les données des tableaux channels et orders. Cependant, je ne peux pas joindre directement ces deux tables car elles n'ont pas de colonne commune. Au lieu de cela, nous devons les joindre par l'intermédiaire de la table customers.

Voici le résultat :

channel_name	total_sales	average_order_value	total_orders	unique_customers	sales_rank
Organic Search	14,003,046.95	1,603.10	8,735	565	1
Direct	2,436,649.06	1,799.59	1,354	91	2
Referral	1,034,734.45	1,645.05	629	46	3
Social	837,378.77	1,824.35	459	32	4
Paid Search	483,824.24	1,645.66	294	19	5
Email	213,186.22	1,450.25	147	8	6

Nous constatons que la plupart des ventes de Northwind proviennent de la recherche organique. Il n'y a pas de contradiction dans les données : le classement par chiffre d'affaires total reflète également le nombre de commandes et de clients uniques.

Il est intéressant de noter que le canal de recherche organique n'a pas la valeur moyenne de commande la plus élevée. Cela mérite d'être amélioré !

Envisageons une stratégie d'augmentation du chiffre d'affaires qui se concentre sur l'augmentation de la valeur moyenne des commandes de tous les canaux plutôt que sur de nouvelles acquisitions.

Nous ne pourrons probablement pas adopter une approche uniforme basée uniquement sur le canal de commercialisation. Dans chaque segment, il peut y avoir des clients dont les habitudes de consommation sont diamétralement opposées. Nous pouvons supposer que cela s'applique particulièrement à la recherche organique, qui est un canal très important.

Nous devons donc en savoir plus sur la répartition de la valeur des commandes pour chaque canal.

Distribution de la valeur des commandes

Calculons la valeur moyenne des commandes, la médiane, le quartile supérieur, le quartile inférieur et l'écart-type pour chaque canal :

SELECT ch.channel_name,
	 AVG(total_amount) AS average_order_value,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_order_value,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS upper_quartile_order_value,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS lower_quartile_order_value,
       STDDEV(total_amount) AS order_value_stddev
FROM channels ch
LEFT JOIN customers c
ON ch.id = c.channel_id
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY ch.channel_name;

La valeur moyenne des commandes est calculée par AVG().

La médiane (ou 50e centile), le quartile supérieur et le quartile inférieur sont calculés à l'aide de la fonction d'agrégation d'ensembles ordonnés PERCENTILE_CONT(), le centile étant spécifié entre parenthèses.

Pour le calcul du percentile, les données doivent être triées par ordre croissant, ce que nous faisons à l'aide de la clause WITHIN GROUP.

Après le calcul du percentile, STDDEV() est utilisé pour calculer l'écart type.

Voici le résultat :

channel_name	average_order_value	median_order_value	upper_quartile_order_value	lower_quartile_order_value	order_value_stddev
Direct	1,799.59	1,005.90	2,166.80	517.40	2,323.63
Email	1,450.25	960.00	1,970.41	492.50	1,655.97
Organic Search	1,603.10	1,007.70	2,018.20	480.00	1,916.39
Paid Search	1,645.66	1,083.00	2,104.40	486.55	1,813.22
Referral	1,645.05	1,064.00	2,034.50	482.90	1,969.01
Social	1,824.35	1,122.80	2,123.20	559.90	2,319.10

Analysons les mesures pour le premier canal.

L'écart-type du canal Direct est de 2 323,63, ce qui est supérieur à la moyenne. Cela indique une grande variabilité, c'est-à-dire qu'il y a probablement des valeurs aberrantes ou un large éventail de valeurs de commande.

Les quartiles inférieur et supérieur montrent que 50 % des commandes se situent entre 517,40 et 2 166,80. Toutefois, un écart-type élevé signifie que de nombreux ordres se situent en dehors de cette fourchette.

La médiane est nettement inférieure à la moyenne, ce qui indique que la distribution est asymétrique, c'est-à-dire qu'un faible nombre de valeurs élevées augmente la moyenne.

Nous pouvons analyser chaque canal de la même manière.

Cela nous donne une autre idée. Pour créer des stratégies personnalisées d'augmentation du chiffre d'affaires, nous pouvons segmenter davantage les clients en fonction du quartile supérieur et du quartile inférieur. Nous considérerons tous les clients situés au-dessus du quartile supérieur comme des clients très dépensiers ; les clients situés en dessous du quartile inférieur seront des clients peu dépensiers.

Nous pourrons ainsi adapter notre stratégie à chaque canal et aux habitudes de consommation de chaque groupe.

Clients à forte et à faible valeur ajoutée par canal

Je vais effectuer ce calcul pour un seul canal de commercialisation. Vous pouvez le faire pour tous les autres, car le code reste le même ; vous changez seulement le canal dans WHERE.

Les clients situés dans le quartile supérieur sont des clients à forte valeur ajoutée, et nous voulons qu'ils figurent sur une liste distincte :

WITH customer_order_totals AS (
    SELECT c.customer_id,
           c.full_name,
           SUM(o.total_amount) AS total_order_value,
           ch.channel_name
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN channels ch ON c.channel_id = ch.id
    WHERE ch.channel_name = 'Direct'
    GROUP BY c.customer_id, c.full_name, ch.channel_name
),

upper_quartile_value AS (
    SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_order_value) AS upper_quartile
    FROM customer_order_totals
)

SELECT cot.customer_id,
       cot.full_name,
       cot.total_order_value,
       cot.channel_name
FROM customer_order_totals cot
JOIN upper_quartile_value uqv 
ON cot.total_order_value >= uqv.upper_quartile
ORDER BY cot.total_order_value DESC;

Le premier CTE calcule la valeur totale de la commande de chaque client en utilisant SUM() et en joignant les tables customers et les commandes. Dans WHERE, nous filtrons tous les canaux à l'exception de Direct.

Le deuxième CTE calcule le quartile supérieur de la manière habituelle.

Le troisième SELECT CROSS JOINs les deux CTE pour trouver tous les clients dont la valeur de la commande est supérieure au quartile supérieur.

Voici les dix premières lignes de la sortie :

customer_id	full_name	total_order_value	channel_name
134	Barry Michael	79,371.50	Direct
152	Carolann Williams	64,365.21	Direct
7	Frédérique Citeaux	61,865.74	Direct
17	Sven Ottlieb	57,251.14	Direct
64	Sergio Gutiérrez	55,140.75	Direct
490	Alice Blevins	54,736.24	Direct
8	Martín Sommer	54,499.55	Direct
303	Gregory Mack	52,554.20	Direct
316	Jeff Heard	51,976.31	Direct
129	Stephan Bufford	50,868.70	Direct

Au total, il y a 23 clients dans le résultat. Nous pouvons utiliser ces informations pour cibler ces clients de grande valeur. Par exemple, nous pouvons créer des programmes de fidélisation dans le cadre desquels ces clients peuvent bénéficier de remises spéciales, d'un service personnalisé, de points échangeables contre des achats, de programmes d'adhésion VIP, etc.

De même, nous pouvons dresser la liste des clients dont les commandes sont inférieures au quartile inférieur :

WITH customer_order_totals AS (
    SELECT c.customer_id,
           c.full_name,
           SUM(o.total_amount) AS total_order_value,
	       ch.channel_name
    FROM customers c
    JOIN orders o 
    ON c.customer_id = o.customer_id
    JOIN channels ch
    ON c.channel_id = ch.id
	WHERE ch.channel_name = 'Direct'
    GROUP BY c.customer_id, c.full_name, ch.channel_name
),

lower_quartile_value AS (
    SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_order_value) AS lower_quartile
    FROM customer_order_totals
)

SELECT cot.customer_id,
       cot.full_name,
       cot.total_order_value,
       cot.channel_name
FROM customer_order_totals cot, lower_quartile_value lqv
WHERE cot.total_order_value <= lqv.lower_quartile
ORDER BY cot.total_order_value ASC;

Cette requête est presque identique à la précédente, sauf que nous calculons cette fois le quartile inférieur. Nous recherchons également les clients dont les commandes sont inférieures à ce quartile.

Une fois de plus, la requête renvoie 191 clients. Voici les dix premières lignes :

customer_id	full_name	total_order_value	channel_name
939	Shannon Aguilar	98.40	Direct
997	Barbra Armstrong	251.50	Direct
687	Andrew Scott	452.90	Direct
787	Dennis Myer	912.00	Direct
917	Les Allen	991.40	Direct
921	Shelby Turgeon	1,162.25	Direct
560	Nancy Wiggins	1,425.80	Direct
678	Tracey Thomas	2,555.20	Direct
756	Dora Rowlands	2,713.50	Direct
715	George Scott	2,906.50	Direct

Ces clients doivent être abordés différemment, car ils doivent être incités à dépenser davantage. Les stratégies visant à augmenter les recettes provenant de ces clients peuvent inclure des offres groupées de produits personnalisées, la livraison gratuite à partir d'un certain montant, l'augmentation des remises pour des seuils de commande plus élevés ou l'offre d'un cadeau gratuit à partir d'un certain montant de commande.

Maintenant, faites la même analyse pour tous les autres canaux et réfléchissez aux tactiques que vous pourriez utiliser pour augmenter les valeurs de commande de ce canal.

Prêt pour votre propre projet de portefeuille SQL ?

Dans cet article, nous avons montré comment préparer un projet de portefeuille SQL en utilisant la base de données du magasin Northwind. Bien entendu, si vous êtes suffisamment créatif, vous pouvez également créer votre propre ensemble de données.

La réalisation d'un projet SQL pour votre portfolio est une partie importante de la préparation au processus d'embauche. Les portfolios sont une excellente vitrine de vos compétences pratiques en matière d'analyse de données en SQL. Le projet ci-dessus n'est qu'un exemple. Il y a beaucoup d'autres idées d'analyse que vous pouvez essayer dans notre cours Bases de données SQL pour la pratique, que nous recommandons vivement !