Retour à la liste des articles Articles
12 minutes de lecture

Analyse de la croissance des ventes avec l'aide de l'analyste de données : Partie 2

Author's photo
Scott Davis

Table des matières

Bienvenue dans la deuxième partie de notre voyage sur l'analyse des données de vente. À l'aide de l'aide-mémoire SQL pour l'analyse des données, nous allons approfondir les mesures clés et appliquer des requêtes SQL pour découvrir des informations sur les consommateurs et les produits. Affûtons nos compétences en SQL !

Dans le prolongement de mon article précédent, Analyse de ventes réelles avec l’aide-mémoire de l’analyste, cet article explore d'autres mesures de ventes à l'aide du même ensemble de données. Nous nous concentrerons sur différents cas d'utilisation impliquant des consommateurs et des produits tout en continuant à suivre les conseils de l'aide-mémoire SQL pour l'analyse de données de LearnSQL.fr.

Dans cet article, nous nous pencherons sur trois éléments clés : les sujets SQL de base, l'identification des schémas métriques et les sections pertinentes de l'aide-mémoire SQL pour l'analyse des données pour une référence rapide. À l'aide d'un jeu de données Tableau présentant des données de vente d'articles ménagers, nous répondrons à des questions commerciales relatives aux clients et aux produits.

L'objectif est de s'entraîner à reconnaître les modèles de rapports et à appliquer des requêtes SQL pour résoudre ces questions. Bien que le jeu de données puisse évoluer, l'accent doit être mis sur le processus et les étapes logiques de l'élaboration des requêtes.

Ce que vous apprendrez :

  • Reconnaître les modèles dans les requêtes SQL, l'analyse métrique et les références aux antisèches
  • Appliquer ces modèles à votre travail quotidien de reporting et de SQL

Vue d'ensemble

Cet article est divisé en deux sections : l'analyse des clients et l'analyse des produits, couvrant les KPI, les tendances et les différentes méthodes de segmentation.

Pour répondre aux questions, j'applique les techniques SQL de l'article précédent, notamment les agrégations, les ratios et les expressions de table communes (CTE). L'analyse se concentre sur des mesures telles que les indicateurs clés de performance, le regroupement, le suivi des tendances, le classement et la segmentation. Les références à l'aide-mémoire SQL pour l'analyse des données comprennent le GROUPE PAR, le classement, les extractions de parties de date, les ratios et la division entière. Dans chaque exemple, j'indique clairement les techniques SQL pertinentes, les modèles métriques et les références à l'antisèche.

Les exemples sont organisés par difficulté, en commençant par des requêtes simples et en progressant vers des techniques plus avancées, ce qui facilite l'application de ces méthodes à d'autres ensembles de données d'entreprise.

Comment préparer les données

Comme dans l'article précédent, 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. Ce jeu de données est largement utilisé dans les rapports d'entreprise et constitue une excellente ressource pour s'entraîner aux requêtes SQL. Voici les étapes à suivre pour préparer 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 l'ensemble de données Superstore Sales.
Analyse de la croissance des ventes avec l'aide de l'analyste de données : Partie 2
  1. Les données pertinentes se trouvent dans l'onglet "Commandes".
  2. Ouvrir le fichier .xls
  3. Ouvrez l'onglet "Commandes" du fichier .xls et exportez-le vers un fichier CSV.

Pour un IDE SQL, je recommande d'utiliser MySQL Workbench et MySQL Community Server. Téléchargez le fichier CSV dans votre base de données MySQL pour commencer à faire des requêtes.

Comme dans l'article précédent, vous pouvez télécharger le code utilisé dans cet article depuis mon compte Github.

Analyse des clients

Commençons par le niveau le plus simple , le sous-domaine des clients. Nous allons résumer les différentes façons d'analyser les clients pour les cas d'utilisation financiers, de commande et de segmentation.

Question 1 : Combien y a-t-il de clients par période de temps ?

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,
    COUNT(DISTINCT(Customer_ID)) AS active_customers
FROM `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2;

Résultat : La requête calcule le nombre total de clients par période sur l'ensemble de la base de données. Elle fournit une tendance du nombre de clients ayant effectué des achats par année et par mois. L'utilisateur peut ainsi voir comment la clientèle a évolué au cours de l'histoire de l'entreprise.

Pour apprendre à rédiger des rapports SQL comme celui-ci, je vous recommande le cours Création de rapports basiques en SQL pour apprendre à rédiger des rapports SQL comme celui-ci. Il contient près de 100 exercices qui vous apprendront à rédiger des rapports complexes en SQL.

Question 2 : Créer des indicateurs financiers basés sur la clientèle par catégorie et sous-catégorie de produits pour le revenu moyen par utilisateur (ARPU) et le bénéfice moyen par utilisateur (APPU) par emplacement.

SELECT
      Region,
      State,
      City,
      ROUND(SUM(Sales) / COUNT(DISTINCT `Customer_ID`), 1) AS ARPU,
      ROUND(SUM(Profit) / COUNT(DISTINCT `Customer_ID`), 1) AS APPU
FROM `Tableau Superstore`.orders
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Résultat : Cette requête calcule l'ARPU et l'APPU pour une combinaison région, état, ville et classe les villes par ARPU dans l'ordre décroissant. Les résultats montrent les villes qui génèrent le plus d'ARPU ainsi que leur APPU. Cette requête peut être utilisée pour identifier les villes à forte valeur ajoutée qui génèrent le plus de revenus par client dans le cadre d'une stratégie marketing et/ou commerciale.

Question 3 : Quel est le nombre moyen de commandes par client ? Quelle est la valeur moyenne des commandes par client ?

SELECT
    ROUND(SUM(Quantity) / COUNT(DISTINCT `Customer_ID`), 1) AS AQPU,
    ROUND(SUM(Sales)/COUNT(order_id), 2) as average_order_value
FROM `Tableau Superstore`.orders

Résultat : Cette requête calcule deux indicateurs clés de performance liés aux commandes (par rapport aux indicateurs financiers de la dernière requête) : Average Quantity Per User (AQPU), qui mesure la quantité moyenne de produits achetés par client unique, et Average Order Value (AOV), qui mesure le montant moyen des ventes par commande. Les commandes permettent aux parties prenantes de mieux comprendre le comportement d'achat des clients.

Voici quelques questions difficiles, toutes deux relatives à des segmentations de clients de type classement. La première examine les clients à l'aide de quatre mesures différentes, tandis que la dernière combine plusieurs mesures pour trouver le bon segment.

Question 4 : Quels sont les trois principaux comptes/clients en termes de marge bénéficiaire, de bénéfice total, de chiffre d'affaires total et de fréquence des ventes ?

WITH customer_metrics AS (
    SELECT 
         customer_name, 
         ROUND(SUM(Sales), 1) AS sales_total,
         ROUND(SUM(Profit), 1) AS profit_total,
         ROUND(SUM(Profit)/SUM(Sales), 1) as profit_margin,
         COUNT(order_id) AS sales_frequency
    FROM  `Tableau Superstore`.orders
    GROUP BY 1
),
ranked_customers AS (
    SELECT 
        customer_name, 
        'Profit Margin' AS metric, 
        profit_margin AS amount,
        DENSE_RANK() OVER (ORDER BY profit_margin DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Total Profit' AS metric, 
        profit_total AS amount,
        DENSE_RANK() OVER (ORDER BY profit_total DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Total Sales' AS metric, 
        sales_total AS amount,
        DENSE_RANK() OVER (ORDER BY sales_total DESC) AS customer_rank
    FROM customer_metrics

    UNION ALL

    SELECT 
        customer_name, 
        'Sales Frequency' AS metric, 
        sales_frequency AS amount,
        DENSE_RANK() OVER (ORDER BY sales_frequency DESC) AS customer_rank
    FROM customer_metrics
)
SELECT 
    customer_name, 
    metric, 
    amount,
    customer_rank
FROM ranked_customers
WHERE customer_rank <= 3
ORDER BY 2, 4

Résultat : Il s'agit plutôt d'un rapport de type ascendant qui présente plusieurs indicateurs (ventes totales, bénéfice total, marge bénéficiaire et fréquence des ventes) à un niveau granulaire - clients individuels. Les résultats montrent les 3 premiers clients dans chacune des 4 catégories. Cette requête peut être utilisée pour donner la priorité aux clients de grande valeur dans le cadre de stratégies de marketing et de vente ciblées.

Avertissement - Cette méthode permet de segmenter les clients, mais vous devez décider quel indicateur doit être filtré en premier (chiffre d'affaires, bénéfice ou marge). L'ordre affecte vos résultats et n'est pas toujours évident. Nous verrons plus loin une requête qui classe les clients en utilisant toutes les mesures ensemble.

Pour en savoir plus sur l'utilisation de WITH dans les requêtes SQL, consultez le cours Requêtes récursives.

Question 5 : Identifiez les clients prioritaires sur la base d'indicateurs tels que le bénéfice, la marge bénéficiaire, la fréquence d'achat et la quantité vendue. Segmentez les clients en groupes de priorité faible, moyenne et élevée, la priorité élevée représentant ceux qui réalisent le plus de bénéfices et dont la fréquence d'achat est la plus élevée.

WITH customer_metrics AS (
    SELECT 
        customer_name, 
        ROUND(SUM(Sales), 1) AS sales_total,
        ROUND(SUM(Profit), 1) AS profit_total,
        ROUND(SUM(Profit)/SUM(Sales), 1) as profit_margin,
        COUNT(order_id) AS sales_frequency
    FROM `Tableau Superstore`.orders
    GROUP BY 1
),
ranked_customers AS (
    SELECT 
        customer_name, 
        sales_total, 
        profit_total, 
        profit_margin,
        sales_frequency, 
        PERCENT_RANK() OVER (ORDER BY profit_margin DESC) AS profit_rank,
        PERCENT_RANK() OVER (ORDER BY sales_frequency DESC) AS frequency_rank
    FROM customer_metrics
),
segmented_customers AS (
 SELECT 
    customer_name, 
    sales_total, 
    profit_total, 
    profit_margin,
    sales_frequency,
    CASE 
        WHEN profit_rank <= 0.1 AND frequency_rank <= 0.1 THEN 'High Profit & High Frequency'
        WHEN profit_rank <= 0.1 THEN 'High Profit'
        WHEN frequency_rank <= 0.1 THEN 'High Frequency'
        ELSE 'Low Profit & Low Frequency'
    END AS customer_segment
 FROM ranked_customers
)
SELECT 
    customer_name, 
    sales_total, 
    profit_total, 
    profit_margin,
    sales_frequency,
    customer_segment
FROM segmented_customers
WHERE customer_segment = 'High Profit & High Frequency'
ORDER BY 3 DESC;

Résultat : Cette requête segmente les clients en fonction de la marge bénéficiaire et de la fréquence des ventes, en identifiant les 10 % les plus importants dans les deux catégories. Les résultats montrent que les clients les plus précieux sont ceux qui ont une marge bénéficiaire et une fréquence de vente élevées. Cela contraste avec la dernière requête qui séparait les 4 catégories et pour laquelle il était difficile de combiner les résultats pour montrer les clients les plus précieux. Cette requête peut être utilisée pour classer les clients par ordre de priorité et les cibler pour des campagnes de marketing ou de vente.

Pour en savoir plus sur les fonctions de fenêtre en SQL, je vous recommande notre cours interactif Fonctions de fenêtrage.

Analyse des produits

Voici d'autres questions de niveau facile, cette fois-ci axées sur les produits. Les deux premières questions sont linéaires par rapport à certaines questions des clients et nécessitent une analyse des tendances, mais la dernière est un autre type de problème de segmentation.

Question 6 : Combien de produits différents sont commandés à chaque période ?

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,
    COUNT(DISTINCT product_id) AS unique_items
FROM 
    `Tableau Superstore`.orders
GROUP BY 1, 2
ORDER BY 1, 2 DESC

Résultat : Cette requête montre une tendance du côté des produits pour les articles uniques vendus. Cette mesure peut être utilisée comme premier contrôle du type d'inventaire des produits vendus.

Question 7 : Créez un rapport sur les ventes, les bénéfices et la marge bénéficiaire pour chaque produit.

SELECT 
    product_id,
    category,
    sub_category,
    ROUND(SUM(sales), 1) AS sales_total,
    ROUND(SUM(profit), 1) AS profit_total,
    ROUND(SUM(profit)/SUM(sales), 1) as profit_margin
FROM `Tableau Superstore`.orders
GROUP BY 1, 2, 3
ORDER BY 4 DESC

Résultat :

Cette requête calcule les ventes totales, le bénéfice total et la marge bénéficiaire pour chaque produit et indique l'ID du produit, la catégorie et la sous-catégorie pour chacun d'entre eux. Les résultats montrent les performances au niveau des produits, en mettant en évidence les produits les plus vendus et les marges bénéficiaires pour chacun d'entre eux. La requête peut être utilisée pour trouver les produits les plus vendus et montrer leur rentabilité - il s'agit d'un rapport général qui peut être utilisé pour la tarification et l'inventaire.

Il s'agit d'un autre type de question de niveau " Hard ", qui porte sur la segmentation, mais qui est différente des deux premières questions portant sur les clients. Il s'agit de segmenter un résultat groupé - une mesure par catégorie.

Question 8 : Quels sont les trois articles dont la marge bénéficiaire est la plus faible pour chaque site, en tenant compte des combinaisons d'articles et de sites. S'il y a des produits avec des liens, indiquez-les également.

WITH product_metrics AS (
    SELECT 
        product_id, 
        state,
        city,
        ROUND(SUM(Sales), 1) AS sales_total,
        ROUND(SUM(Profit), 1) AS profit_total,
        ROUND(SUM(Profit)/SUM(Sales), 2) as profit_margin
    FROM `Tableau Superstore`.orders
    GROUP BY 1, 2, 3
),
ranked_products AS (
    SELECT 
        product_id,
        state,
        city, 
        sales_total, 
        profit_total,
        profit_margin,
        DENSE_RANK() OVER (ORDER BY profit_margin ASC) AS profit_rank
    FROM product_metrics
   WHERE profit_margin IS NOT NULL
)
SELECT 
    product_id,
    state,
    city,
    sales_total, 
    profit_margin,
    profit_rank
FROM ranked_products
WHERE profit_rank <= 3
ORDER BY 5 ASC;

Résultat : Cette requête a calculé les indicateurs de performance des produits par combinaison ville-état-produit et montre les marges bénéficiaires les plus faibles. Les résultats montrent les combinaisons les moins rentables et peuvent être utilisés pour mettre en évidence les zones les moins performantes - soit pour améliorer, soit pour supprimer certains produits dans ces zones.

Récapitulation

Les 8 exemples de cet article poursuivent la discussion de l'article précédent sur la réponse aux questions de reporting dans un contexte commercial. Nous avons commencé par les indicateurs de performance clés de base, puis nous sommes passés à l'analyse des tendances et avons terminé par des calculs de classement et de segmentation plus avancés.

En travaillant sur ces requêtes, vous avez vu les modèles SQL courants utilisés dans les rapports d'entreprise, y compris 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 !

Tags:

Vous aimerez aussi