Synthèse puis utilisation cartographique de données clients

Note personnelle : un fichier SQL pour filtrer/mettre à jour/exporter les données est présent en PJ (_PourExporterDonnées.sql).

Nous disposons cette fois de données brutes sur des participants à un salon de professionnels du domaine médical. Pour des raisons analytiques ou prédictives, il est intéressant de connaître la typologie de ces clients (sa cible, target), et leur comportement (nous parlons parfois de personas, les grands profils clients d'une activité).

Les clients s'inscrivent sur place ou à l'avance dans une application. Il peuvent déjà avoir un profil dans l'application et participer à d'autres congrès. Ils peuvent aussi payer plusieurs entrées à un même congrès.

Une base SQL est derrière et pour des raisons applicatives tout-à-fait standards, les données vous sont accessibles uniquement sous la forme de tableaux occurentiels (des tables SQL). Ici la table des profils des clients et la table des commandes effectuées.

Pour les utiliser dans des thématiques cartographiques, il nous faut d'abord les synthétiser (calculer les chiffres d’affaire par pays, départements, spécialités...).

Le fichier soft_orders liste les commandes effectuées aux congrès. Le congrès qui nous intéresse (AMWC 2024), mais aussi d'autres qui ne nous intéressent pas. Certains identifiants de clients sont donc en doublons (plusieurs commandes, plusieurs congrès...) et ces doublons sont souhaitables. Certains montants sont à 0 euros (invités ou autres) et c'est normal.

Le fichier soft_customers stocke les information sur tous les clients, dont certains n’ont pas été facturé pour cet événement. Nous avons aussi des informations de localisation (liées à leurs adresses), possiblement redondantes, et donc non-souhaitables selon l'usage que l'on souhaite en faire.

1) Importer les fichiers Excel dans une base Access

Il faudra poser une clé primaire sur l'id_customer, et donc peut-être dédoublonner les customers.

Clé primaire également sur id_order.

Il faudra gérer les types de champs dans les deux tables en fonction de l'usage que l'on souhaite en faire.

2) Requêtes de regroupement et jointures

Nous allons calculer les chiffres d'affaire (CA). Créez 3 requêtes :

• Requête ca_customers pour lister les clients facturés, sommer leurs montants en euros, compter le nombre de commandes et ramener les informations géographiques. Nommez correctement chaque champ directement dans la requête et organiser l'ordre des champs logiquement.

• Requête ca_country pour lister les pays d’origine des clients facturés, compter les clients par pays, leurs commandes, et sommer les CA par pays. Formater les pays au format title/proper case (première lettre de chaque mot en majuscule, le reste en minuscule).

• Requête nommée ca_france pour lister les départements français des clients facturés (directement dans la requête, avec une extraction de chaîne), compter les clients par département, leurs commandes, et sommer les CA par département.

À chaque requête, renommez vos champs de regroupement en gardant le format requête, et vérifiez vos résultats sur un ou deux clients ayant plus d'une commande. 

3) Cartographie

Exportez en CSV, séparateur point-virgile et délimité par des guillemets double, la requête ca_country. Ouvrez-le sous QGIS.

Liez-le avec le shape country et faites quelques thématiques sur les clients et CA par pays.

Notes : bien qu'il soit possible de lier directement une base Access avec QGIS, nous ne le ferons pas, car cela n'a que peu d'intérêt. Si vous avez un jour ce type de besoin, vos données seront mieux stockées dans une vraie base SQL.

4) Cartographie avec fusion d'entités

Importez dans QGIS le fichier/requête ca_france.

Exportez en CSV, séparateur tabulation et sans délimiteur, la requête ca_country. Ouvrez-le sous QGIS.

À partir du shape des IRIS, créer un second shape des départements français.

Cela nécessitera la création d’un nouveau champ contenant le code département (là aussi extraction de chaîne : utilisez le champ des codes commune), puis dissolution sur ce champ.

Attention : si des départements ont des erreurs de géométrie, pour avoir une couche complète des départements, vous allez devoir valider les géométries, puis dissoudre sur les géométries valides séparément, puis fusionner vos 2 shapes péalablement dissous.

Importez et liez votre requête ca_france à votre nouveau shape.

Faites quelques thématiques sur le nombre de commandes et leur montant en surface de couleurs et en symboles proportionnels.

Attention : certains polygones sont multiparties (les îles), ce qui fausse certaines cartographies en cercles proportionnelle. Vous allez donc devoir extraire les centroïdes des polygones.

5) SQL spatial

Notez qu'il aurait été possible de générer les géométries de nos départements plus efficacement en SQL spatial !

Ouvrez la fenêtre SQL de l'outil Bases de données/Gestionnaire de bases de données/Couches virtuelles/Couches du projet.

Puis testez cette commande :

SELECT SUBSTR(INSEE_COM,1,2) AS dep, ST_UNION(geometry) AS geometry
FROM IRIS
GROUP BY SUBSTR(INSEE_COM,1,2) ;

Et même directement les centroïdes, sans passer par un export des départements polygonaux :

SELECT SUBSTR(INSEE_COM,1,2) AS dep, ST_CENTROID(ST_UNION(geometry)) AS geometry
FROM IRIS
GROUP BY SUBSTR(INSEE_COM,1,2) ;

Et même, allons-y gaiement, avec une jointure sur la table ca_france ! D'une pierre trois coups.

Attention : pour délimiter les objets de bases de données (tables et champs) contenant des caractères spéciaux, il faudra utiliser des guillemets obliques (AltGroup+7+espace). Remplacez-les au bon endroit dans la requête ci-dessous, sinon elle ne marchera pas.

SELECT dep, 'Nombre de commandes', geometry
FROM
(SELECT SUBSTR(INSEE_COM,1,2) AS dep, ST_CENTROID(ST_UNION(geometry)) AS geometry
FROM IRIS
GROUP BY SUBSTR(INSEE_COM,1,2)) AS sous_requete
LEFT JOIN ca_france
ON dep = 'Département' ;

Via une sous-requête car la table ca_france n'a aucune raison de faire l'objet d'une agrégation. Comme quoi on aurait pu importer les tables orders et customers et faire la totale en SQL, sans même passer par la requête ca_customer...

La même requête en partant du principe qu'aucun nom de champ ne contient de caractères spéciaux, et avec une gestion du caractère de séparation des décimaux et des valeurs nulles :

SELECT
dep,
 
COALESCE(
REPLACE(
REPLACE(
nombre_de_commandes
, ",", ".")
, ".00", "")
,0) AS nb_com,
 
geometry
 
FROM
 
(
SELECT SUBSTR(INSEE_COM,1,2) AS dep,
ST_CENTROID(ST_UNION(geometry)) AS geometry
FROM IRIS
GROUP BY SUBSTR(INSEE_COM,1,2)
) AS sous_requete
 
LEFT JOIN ca_france
ON dep = departement ;

Liens ou pièces jointes

Activité des résidents	[Activité des résidents]	0 Ko
BPE	[ ]	0 Ko
country.zip	[Countries]	0 Ko
Couples - Familles - Ménages	[Couples - Familles - Ménages]	0 Ko
IRIS	[ ]	0 Ko
mag_telandcom.zip	[mag_telandcom]	22 Ko
soft_customers	[Clients]	4591 Ko
soft_orders	[Commandes effectuées]	96 Ko
TRONCON_HYDROGRAPHIQUE	[ ]	0 Ko
TRONCON_ROUTE	[ ]	0 Ko
TRONCON_VOIE_FERREE	[ ]	0 Ko
_PourExporterDonnées.sql	[Pour filtrer/mettre à jour/exporter les données]	1 Ko