Analyse de ventes réelles avec l’aide-mémoire de l’analyste

Avez-vous déjà utilisé l'antisèche de l'analyste de données pour analyser un ensemble de données commerciales ? Dans cet article, vous pourrez voir les commandes de l'antisèche en action.

Je vais explorer un ensemble de données de ventes réelles pour répondre à 10 questions commerciales clés à l'aide de SQL. En suivant les conseils de l'aide-mémoire SQL pour l'analyse de données, je démontrerai comment les requêtes SQL peuvent aider à analyser les tendances, à mesurer les performances et à extraire des informations significatives des données de vente.

Pour ce faire, nous utiliserons un jeu de données ouvert de Tableau qui contient des données sur les ventes d'articles ménagers tels que des bureaux et des articles de papeterie. Cet ensemble de données offre un moyen pratique d'explorer les questions de reporting liées à l'entreprise et de voir comment SQL peut être utilisé pour découvrir des tendances utiles.

Plutôt qu'une étude de cas formelle, cet article se concentre sur la mise en correspondance des questions commerciales avec des requêtes SQL générales. Vous verrez comment les techniques SQL s'alignent sur les défis d'analyse de données du monde réel et comment différents modèles métriques émergent dans les rapports.

À la fin de l'article, vous serez en mesure de reconnaître ces modèles et de les appliquer à votre propre travail de reporting SQL. Plongeons dans le vif du sujet !

Vue d'ensemble

Cette analyse est structurée en deux parties principales. Tout d'abord, j'examine les tendances générales, telles que le chiffre d'affaires total, le bénéfice et la quantité vendue. Ensuite, je me concentre sur les tendances des ventes, en identifiant les schémas dans le temps, par catégorie et dans les différents secteurs d'activité.

Pour répondre à ces questions, j'utilise des techniques SQL telles que les agrégations, les ratios, les fonctions de fenêtre et les sous-requêtes. Les schémas métriques courants, tels que le regroupement des données, le suivi des tendances, le calcul des moyennes mobiles et le classement des résultats, aident à structurer l'analyse. L'aide-mémoire SQL pour l'analyse des données sert de référence pour les fonctions clés telles que le GROUP BY, le classement, les totaux courants et les extractions de dates. J'ai ajouté des balises dans les exemples ci-dessous pour les classer : des parenthèses pour les techniques SQL [SQL], des parenthèses pour les modèles métriques (METRIC) et des accolades pour les références à l'antisèche {SHEET}. Ces balises permettent d'organiser l'analyse de chaque exemple ci-dessous.

Les exemples sont organisés par niveau de difficulté, en commençant par des requêtes simples et en incorporant progressivement des techniques plus avancées. Il est ainsi facile de suivre et d'appliquer des approches similaires à d'autres ensembles de données d'entreprise.

Comment préparer les données

Pour commencer l'analyse, nous devons préparer le jeu de données. J'utilise un jeu de données ouvert de Tableau Public, qui contient des données sur les ventes d'articles ménagers tels que des bureaux et des articles de papeterie. Comme il est largement utilisé dans les rapports d'entreprise, c'est une excellente ressource pour s'entraîner aux requêtes SQL. Vous trouverez ci-dessous des instructions pas à pas pour télécharger le jeu de données, l'importer dans un environnement SQL et procéder à de petits ajustements pour nettoyer les données.

Téléchargez le jeu de données à partir de Tableau Public.

• Naviguez vers Learn → Sample Data → Business → Superstore Sales.
• Téléchargez le jeu de données Superstore Sales.

Ouvrez l'ensemble de données dans Excel.

1. Les données pertinentes se trouvent dans l'onglet "Commandes".
2. Ouvrir le fichier .xls
3. Accédez à l'onglet "Commandes" de votre feuille de calcul.

Téléchargement de SQL IDE - optionnel pour ceux qui veulent suivre et recréer les métriques. J'utilise MySQL Workbench et MySQL Community Server. Si vous souhaitez utiliser les mêmes, les téléchargements sont disponibles ci-dessous. Je montrerai les étapes pour importer le jeu de données dans MySQL Workbench.

Liens de téléchargement :

• MySQL Workbench : https://dev.mysql.com/downloads/workbench/
• MySQL Community Server : https://dev.mysql.com/downloads/mysql/

Je préfère utiliser MySQL Workbench avec le serveur communautaire, les étapes suivantes sont donc destinées à le configurer :

1. Tout d'abord, je confirme que la connexion à l'instance locale est établie.

2. Ensuite, je crée un nouveau schéma en suivant ces instructions :

• Cliquez sur la connexion de l'instance locale.
• Choisissez Schemas dans le menu supérieur.
• Cliquez avec le bouton droit de la souris et sélectionnez Créer un schéma.
• Nommez le schéma Tableau Superstore.
• Importez le fichier .xls en tant que .csv sous Tables à l'aide de l'assistant d'importation de tables de données (convertissez d'abord .xls en .csv). Une autre méthode consiste à l'importer en tant que JSON à l'aide d'un convertisseur JSON (lien vers les instructions, c'est la méthode que j'ai utilisée).
• Configurez les paramètres d'importation et choisissez le type de champ par défaut pour les différentes colonnes sources. N'incluez pas la dernière ligne vide !

Avant la dernière étape, effectuez des changements manuels dans le site Alter Table: supprimez les espaces des noms de colonnes et remplacez-les par des traits de soulignement (_).

La dernière étape consiste à cliquer avec le bouton droit de la souris et à sélectionner Créer un schéma. Et voilà ! Félicitations, la partie la plus compliquée est derrière vous.

3. Nommez votre schéma : Tableau Superstore devrait convenir. Utilisez l'assistant d'importation de données de tableau pour ajouter le fichier, comme indiqué dans la capture d'écran.

4. Téléchargez le fichier .csv.

Ou, si vous convertissez en JSON, téléchargez le fichier JSON à la place. Pour ce faire, vous devez utiliser un convertisseur .csv vers .json, comme indiqué dans la capture d'écran.

Gardez tous les champs sélectionnés, mais excluez le champ "vide" et définissez tous les autres comme des champs "texte".

Après cela, il ne reste plus qu'à cliquer avec le bouton droit de la souris sur la orders et de sélectionner Alter Table.

La préparation des données est maintenant terminée et nous sommes prêts à passer à l'analyse. Bonus agréable : vous pouvez toujours trouver toutes les requêtes et les morceaux de code de l'article sur la page GitHub dédiée.

Trouver les tendances générales

Échauffons-nous au niveau le plus simple! Pour commencer l'analyse, nous allons utiliser quelques indicateurs clés de performance (KPI) afin d'obtenir une vue d'ensemble des ventes, des bénéfices et de la quantité vendue. Ces requêtes permettent de résumer l'ensemble des données et de donner un aperçu rapide des performances globales de l'entreprise.

Paramètres de l'ICP : Ventes totales, bénéfices et quantités vendues.

Question : Quel est le total des ventes, des bénéfices et des quantités vendues ?

[AGRÉGATION] (GLOBAL) {FONCTIONS D'AGRÉGATION}

SELECT 
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(SUM(Profit), 0) AS profit_total,
  ROUND(SUM(Quantity), 0) AS quantity_total
FROM `Tableau Superstore`.orders;

Résultat : Cette requête calcule le total des ventes, des bénéfices et des quantités vendues pour l'ensemble des données. Elle permet d'obtenir une vue d'ensemble des performances globales de l'entreprise.

Indicateurs clés de performance : Tendances mensuelles et annuelles

Question : Quels ont été les ventes, les bénéfices et les quantités vendues mensuellement/annuellement ?

[AGRÉGATION] (TENDANCE) {FONCTIONS D'AGRÉGATION | EXTRACTION DE PARTIES DE DATE}

SELECT 
EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Year,
EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Month,
  ROUND(SUM(Sales), 0) AS sales_month,
  ROUND(SUM(Profit), 0) AS profit_month,
  ROUND(SUM(Quantity), 0) AS quantity_month
FROM `Tableau Superstore`.orders
GROUP BY 
  1, 2
ORDER BY 
  1, 2;

Résultat : Cette requête décompose les ventes, les bénéfices et les quantités par année et par mois, ce qui permet d'identifier les tendances au fil du temps. Elle est utile pour repérer les tendances saisonnières ou la croissance d'une année sur l'autre.

Paramètres de l'indicateur clé de performance : Ventes moyennes, bénéfices et quantités vendues

Question : Quels ont été les ventes, les bénéfices et les quantités vendues en moyenne ?

[AGRÉGATION] (GLOBALE) {FONCTIONS D'AGRÉGATION}

SELECT 
  ROUND(AVG(Sales), 0) AS sales_average,
  ROUND(AVG(Profit), 0) AS profit_average,
  ROUND(AVG(Quantity), 0) AS quantity_average
FROM `Tableau Superstore`.orders;

Résultat : Au lieu des valeurs totales, cette requête calcule le chiffre d'affaires, le bénéfice et la quantité vendue moyens par commande. Elle est utile pour comprendre la taille typique d'une transaction et la comparer à différentes périodes ou à différents secteurs d'activité.

J'espère que vous avez réussi à franchir le premier niveau d'analyse ! Passons maintenant à l'étape suivante avec des requêtes plus avancées. Le niveau moyen commence par le calcul d'une moyenne mobile, qui permet de lisser les fluctuations à court terme et d'identifier les tendances des ventes au fil du temps. Cette approche nous permet de prévoir les ventes futures en analysant les tendances des mois précédents.

Question : Comment prévoir les ventes par mois ?
[WINDOW FUNCTION] (TREND | MOVING AVERAGE) {AGGREGATE FUNCTIONS | EXTRACTING PARTS OF DATE | MOVING AVERAGE}

SELECT 
  EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Year,
  EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Month,
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(AVG(SUM(sales)) OVER(ORDER BY EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS moving_average
FROM `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2;

Résultat : Cette requête calcule les totaux mensuels des ventes et applique une moyenne mobile sur les trois derniers mois (y compris le mois en cours). Cela permet d'identifier les tendances des ventes au fil du temps en lissant les fluctuations à court terme, ce qui facilite la prévision des performances futures.

J'espère que vous suivez le mouvement ! Maintenant, attaquons-nous à une requête plus avancée. Nous passons au niveau difficile, où nous calculons une somme cumulée des ventes pour 2017. Ce total courant permet de suivre la progression des ventes mois par mois, ce qui donne une image claire des performances globales tout au long de l'année.

Question : Quelle est la somme cumulée des ventes pour 2017 ? Afficher par mois.
[WINDOW FUNCTION | CTE] (TREND | CUMULATIVE) {AGGREGATE FUNCTIONS | EXTRACTING PARTS OF DATE | RUNNING TOTAL | CTE}

WITH monthly_report AS (
  SELECT 
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Year,
    EXTRACT(MONTH FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Month,
    ROUND(SUM(Sales), 0) AS monthly_sales_total
  FROM `Tableau Superstore`.orders
  WHERE EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) = 2017
  GROUP BY 1, 2
)
SELECT
  order_month,
  yearly_sales_total,
  SUM(yearly_sales_total) OVER(ORDER BY order_Year) AS running_sales_total
FROM monthly_report
ORDER BY 1, 3 DESC;

Résultat : Cette requête calcule d'abord les totaux mensuels des ventes pour 2017 à l'aide d'une expression de table commune (CTE). Elle applique ensuite un total courant à l'aide d'une fonction de fenêtre pour additionner les ventes progressivement au fil des mois. Cela permet d'analyser la croissance cumulative et de détecter les tendances des ventes au cours de l'année.

Détermination des tendances des ventes

Commençons par le niveau le plus simple, mais en nous concentrant cette fois sur les informations régionales et catégorielles. Ces requêtes permettent de décomposer les indicateurs clés en fonction des différents segments de l'entreprise et d'obtenir une image plus claire que si l'on se contente de regarder les totaux globaux.

Question : Quels sont les ventes totales, les bénéfices et les quantités pour chaque région ?

[AGRÉGATION] (GROUPE) {FONCTIONS D'AGRÉGATION}

SELECT 
  Region,
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(SUM(Profit), 0) AS profit_total,
  ROUND(SUM(Quantity), 0) AS quantity_total
FROM `Tableau Superstore`.orders
GROUP BY 1
ORDER BY 2 DESC;

Résultat : Cette requête regroupe les ventes, les bénéfices et les quantités par région, ce qui nous permet de comparer les performances des différentes zones géographiques. Elle permet d'identifier les régions les plus performantes et celles qui nécessitent une analyse plus approfondie.

Question : Quelle est la marge bénéficiaire pour chaque catégorie de produits ?

(GROUPE) {FONCTIONS D'AGRÉGATION | DIVISION ENTIÈRE}

SELECT
  Category,
  ROUND(SUM(Sales), 0) AS sales_total,
  ROUND(SUM(Profit), 0) AS profit_total,
  ROUND(SUM(Profit)/SUM(Sales), 2) AS profit_margin
FROM `Tableau Superstore`.orders
GROUP BY 1
ORDER BY 4 DESC;

Résultat : Cette requête calcule les ventes, le bénéfice et la marge bénéficiaire pour chaque catégorie de produits. En divisant les bénéfices par les ventes, on obtient un ratio de rentabilité qui permet de déterminer les catégories qui génèrent les meilleurs rendements.

Passons maintenant au niveau moyen, où nous analyserons l'évolution des ventes d'une année sur l'autre et calculerons le pourcentage des ventes totales pour chaque catégorie.

Question : Créez un rapport indiquant les ventes, les ventes moyennes et le changement d'une année sur l'autre (delta) par année.
[WINDOW FUNCTION] (TREND | DELTA) {AGGREGATE FUNCTIONS | EXTRACTING PARTS OF DATE | DIFFERENCE BETWEEN TWO ROWS (DELTA)}

SELECT DISTINCT
    EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y'))) AS order_Year,
    ROUND(SUM(sales), 0) AS sales_total,
    ROUND(LAG(SUM(SALES)) OVER(ORDER BY EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y')))), 0) AS sales_prev_year,
    (ROUND(SUM(sales), 0)) - (ROUND(LAG(SUM(SALES)) OVER(ORDER BY EXTRACT(YEAR FROM DATE(STR_TO_DATE(order_date, '%m/%d/%Y')))), 0)) AS sales_yoy_difference
FROM `Tableau Superstore`.orders
GROUP BY 1
ORDER BY 1;

Résultat : Cette requête calcule les ventes totales par année et les compare à celles de l'année précédente à l'aide de la fonction de fenêtre LAG(). Le résultat est une différence en glissement annuel, ce qui permet de suivre d'un coup d'œil la croissance ou la baisse des ventes annuelles.

La question est la suivante : quel est le pourcentage des ventes totales pour un produit donné ? Quel est le pourcentage des ventes totales pour chaque catégorie ?
[SUBQUERY] (GROUP | RATIO) {FONCTIONS D'AGRÉGATION | CALCUL DU POURCENTAGE DU TOTAL AU SEIN D'UN GROUPE}

SELECT DISTINCT
    category,
    ROUND(SUM(sales), 0) AS sales_total,
    ROUND((SUM(sales) / (SELECT SUM(sales) FROM `Tableau Superstore`.orders)) * 100, 0) AS percent_oftotal
FROM `Tableau Superstore`.orders
GROUP BY 1;

Résultat : Cette requête calcule les ventes de chaque catégorie en pourcentage des ventes totales. La sous-requête garantit que le dénominateur reste fixe, ce qui permet une comparaison précise des performances des catégories par rapport à l'ensemble des données.

Nous avons atteint la dernière partie de l'analyse, le niveau difficile, et nous allons donc relever un dernier défi. Cette fois, nous allons identifier les deux sous-catégories les plus vendues dans chaque catégorie de produits pour voir quels articles génèrent le plus de revenus.

Question : Quelles sont les deux sous-catégories les plus vendues dans chaque groupe ?
[WINDOW FUNCTION | CTE] (RANK) {AGGREGATE FUNCTIONS | RANK | CTE}

WITH category_ranking AS (
  SELECT
    Category,
    Sub_Category,
    ROUND(SUM(Sales), 0) AS sales_total,
    DENSE_RANK() OVER(PARTITION BY Category ORDER BY SUM(SALES) DESC) AS sub_category_rank
  FROM `Tableau Superstore`.orders
  GROUP BY 1, 2
)
SELECT
  Category,
  Sub_Category,
  sales_total
FROM category_ranking
WHERE sub_category_rank <= 2
ORDER BY 1, 3 DESC;

Résultat : Cette requête classe les sous-catégories de chaque catégorie en fonction des ventes totales à l'aide de la fonction de fenêtre DENSE_RANK(). Comme il s'agit d'un regroupement par catégorie, il n'est pas possible d'utiliser une simple fonction d'agrégation pour le classement. Au lieu de cela, l'expression de table commune (CTE) calcule d'abord les classements, et la sélection finale filtre uniquement les deux premières sous-catégories par catégorie.

Récapitulation

Dans cet article, nous avons exploré 10 requêtes SQL clés qui permettent de répondre à des questions commerciales liées à la création de rapports. Nous avons commencé par les indicateurs de performance clés de base, puis nous sommes passés à l'analyse des tendances et aux prévisions, avant de terminer par des classements plus avancés et des calculs cumulatifs.

En travaillant sur ces requêtes, vous avez découvert des modèles SQL courants utilisés dans les rapports d'entreprise, notamment les agrégations, les fonctions de fenêtre et les sous-requêtes. Que vous ayez suivi pas à pas ou simplement examiné les exemples, ces techniques peuvent servir de référence pratique pour l'analyse des données de vente en SQL.

J'espère que cette analyse vous a aidé à clarifier la façon d'appliquer les techniques d'analyse des données. Aide-mémoire de l'analyste de données en pratique. Si quelque chose n'est pas clair, si vous repérez des erreurs, ou si vous avez des questions sur l'article ou sur un morceau de code, discutons-en et améliorons-le ensemble ! Je serais heureux de me connecter sur LinkedInet vous pouvez trouver plus d'exemples et d'idées sur mon profil Tableau Public ou sur mon blog. J'attends avec impatience vos commentaires !