11. Intermediate Sales Analysis

Intermediate Sales Analysis

Task #1: Categorize customers by profit marginality (low, medium, high)

In this analysis, we focus on categorizing customers based on their profit margin, a crucial metric for understanding customer profitability. Customers are divided into three groups: low, medium, and high marginality. This classification provides valuable insights for targeted marketing and customer relationship strategies.

• Low Marginality: This category includes customers whose profit margin is at or below 10%, as indicated in the ProfitMarginPercentage column.
• Medium Marginality: Customers fall into this category if their profit margin is above 10% but does not exceed 30%.
• High Marginality: This group consists of customers with a profit margin over 30%.

Objective: Categorize customers into three groups — low, medium, and high marginality — based on their profit margin percentages. Profit margin calculates as Profit / Sales.

Expected Result: Customers will be categorized into 'Low', 'Medium', or 'High' marginality groups, with an additional 'Not Classified' category for any outliers.

Data Source: dataacademykz.superstore.sales_analytics

Click To Show / Hide Solution

-- CTE for calculating profit margin percentage for each customer

WITH
  CustomerProfitMargin AS (
  SELECT
    Customer_Name, -- Name of the customer
    SUM(Profit) AS TotalProfit, -- Total profit for each custome
    SUM(Sales) AS TotalSales, -- Total sales for each customer
    (SUM(Profit) / NULLIF(SUM(Sales), 0)) * 100 AS ProfitMarginPercentage, -- Profit margin percentag
  FROM
    `dataacademykz.superstore.sales_analytics` 
    GROUP BY Customer_Name -- Grouping by customer name
  )

SELECT
  Customer_Name, -- Name of the customer
  TotalProfit, -- Total profit
  TotalSales, -- Total sales
  ProfitMarginPercentage, -- Profit margin percentage
  CASE
    WHEN ProfitMarginPercentage <= 10 THEN 'Low' -- Classification as 'Low
    WHEN ProfitMarginPercentage > 10 
    AND ProfitMarginPercentage <= 30 THEN 'Medium' -- Classification as 'Medium
    WHEN ProfitMarginPercentage > 30 THEN 'High' -- Classification as 'High
  ELSE 'Not Classified' -- Classification as 'Not Classified'
  END
    AS MarginalityClassification -- Profit margin category
FROM
  CustomerProfitMargin -- Referencing the CTE

Explanation:

Imagine you're running a store and want to group your customers based on how profitable they are. This query is like going through your sales records and:

• First, figuring out how much profit each customer brought in and what percentage of their total purchases that profit represents.
• Then, based on this percentage, we put each customer in a group - 'Low', 'Medium', or 'High'.

It's like sorting your customers into buckets based on how beneficial they are to your business.

Technical Breakdown:

This SQL query categorizes customers based on their profit margin, calculated as a percentage.

• First Step (CTE - Common Table Expression):

  • The CustomerProfitMargin CTE computes the total profit, total sales, and profit margin percentage for each customer.
  • SUM(Profit) and SUM(Sales) calculate the total profit and sales, respectively.
  • ProfitMarginPercentage is computed as (SUM(Profit) / NULLIF(SUM(Sales), 0)) * 100, dividing total profit by total sales (while handling potential division by zero errors) and converting it to a percentage.

• Second Step (Main Query):

  • The main query selects the customer name, their total profit, total sales, and profit margin percentage from the CTE.
  • A CASE statement classifies each customer into 'Low', 'Medium', or 'High' marginality based on their ProfitMarginPercentage.

• Result: The output provides a list of customers with their respective profit figures and categorizes them based on their profit margin percentage into low, medium, or high marginality groups.

Task #2: How many low, medium, and high profit margin customers we have?

Building upon our earlier categorization of customers by profit marginality, this task aims to quantify the number of customers in each category. By understanding the distribution of customers across low, medium, and high profit margin groups, businesses can tailor their strategies to target each segment more effectively.

Objective: Count the number of customers in each profit margin category (low, medium, high) based on their calculated profit margin percentages.

Expected Result: A summary of the number of customers categorized as 'Low', 'Medium', or 'High' based on their profit margins.

Data Source: dataacademykz.superstore.sales_analytics

Click To Show / Hide Solution

-- CTEs for calculating customer profit margin and categorizing customers

WITH
  CustomerProfitMargin AS (
  SELECT
    Customer_ID, -- Customer ID
    Customer_Name, -- Customer's name
    SUM(Profit) AS TotalProfit, -- Total profit
    SUM(Sales) AS TotalSales, -- Total sales
    (SUM(Profit) / NULLIF(SUM(Sales), 0)) * 100 AS ProfitMarginPercentage, -- Profit margin percentag
  FROM
    `dataacademykz.superstore.sales_analytics` 
    GROUP BY Customer_ID, Customer_Name -- Grouping by customer ID and nam
  ),

  CustomerCategory AS(
  SELECT
    Customer_ID,
    Customer_Name,
    TotalProfit,
    TotalSales,
    ProfitMarginPercentage,
    CASE
      WHEN ProfitMarginPercentage <= 10 THEN 'Low' -- Classifying as 'Low
      WHEN ProfitMarginPercentage > 10 AND ProfitMarginPercentage <= 30 THEN 'Medium' -- Classifying as 'Medium
      WHEN ProfitMarginPercentage > 30 THEN 'High' -- Classifying as 'High
    ELSE 'Not Classified' -- Classification as 'Not Classified
    END AS MarginalityClassification -- Profit margin category
    FROM CustomerProfitMargin -- Referencing the first CTE
  )

SELECT
  MarginalityClassification, -- Profit margin classification
  COUNT(Customer_ID) AS NumberOfCustomers -- Count of customers in each categor
FROM
  CustomerCategory -- Referencing the second CTE
GROUP BY
  MarginalityClassification -- Grouping by classification
ORDER BY
  NumberOfCustomers DESC -- Ordering by the count of customers in descending order

Explanation:

Imagine sorting your customers into three groups based on how profitable they are – some bring in a little profit, some a decent amount, and some a lot. This query counts how many customers fall into each of these groups:

• It's like putting customers into 'Low', 'Medium', or 'High' profit buckets.
• Then, we tally up how many customers are in each bucket.

This way, you can see where most of your profitable customers fall and maybe focus more on the groups that are bringing in the most money.

Technical Breakdown:

This SQL query extends the previous analysis to quantify the number of customers in each profit margin category. 

• First Step (CTE - CustomerProfitMargin):

  • The CustomerProfitMargin CTE calculates the total profit, total sales, and profit margin percentage for each customer.
  • SUM(Profit) and SUM(Sales) calculate the total profit and sales, respectively.
  • ProfitMarginPercentage is computed as the ratio of total profit to total sales, multiplied by 100.

• Second Step (CTE - CustomerCategory):

  • The CustomerCategory CTE classifies each customer into 'Low', 'Medium', or 'High' marginality based on the calculated profit margin percentage using a CASE statement.

• Final Query:

  • The main query selects the marginality classification and counts the number of customers (COUNT(Customer_ID)) in each category.
  • The GROUP BY MarginalityClassification clause groups customers based on their profit margin classification.
  • The ORDER BY NumberOfCustomers DESC clause sorts the results by the count of customers in each category, from highest to lowest.

• Result: The output provides a count of customers classified into low, medium, and high marginality categories, offering insights into the distribution of customer profitability.

Task #3: Sub-Category Performance in the Most Recent Year

In this task, we aim to evaluate the performance of different sub-categories within the most recent year available in the database. This analysis is essential for understanding current market trends and the profitability of various product lines.

Objective: Assess the sales and profit performance of different sub-categories in the most recent year recorded in the database.

Expected Result: A summary of total sales and total profit for each sub-category in the latest year, with the results ordered by total profit in descending order.

Data Source: dataacademykz.superstore.sales_analytics

Click To Show / Hide Solution

-- Selecting total sales and profit for each sub-category in the most recent year

SELECT
  Sub_Category, -- Sub-category of the product
  SUM(Sales) AS Total_Sales, -- Total sales amount
  SUM(Profit) AS Total_Profit, -- Total profit amount
FROM
  dataacademykz.superstore.sales_analytics -- Source table
WHERE
  EXTRACT(YEAR 
    FROM Order_Date) = (
    SELECT
      MAX(EXTRACT(YEAR 
        FROM Order_Date))
      FROM
      dataacademykz.superstore.sales_analytics ) -- Filtering for the most recent year
GROUP BY 
  Sub_Category -- Grouping by sub-category
ORDER BY 
  Total_Profit DESC -- Ordering by total profit in descending order

Explanation:

Think of this query as a year-end report card for each type of product (sub-category) in your store, focusing only on the most recent year’s data.

• We’re checking how much money each sub-category brought in and how much profit was made from them, but only for the latest year.
• It’s like lining up all the product types and seeing which ones were the top earners and which ones didn’t do as well, but just for the last year we have data for.

Technical Breakdown:

This SQL query aims to analyze the performance of various sub-categories in the latest year available in the dataset. Here's the technical breakdown:

• Objective: The goal is to evaluate the total sales and profit for each sub-category in the most recent year.

• Subquery for Recent Year: The subquery SELECT MAX(EXTRACT(YEAR FROM Order_Date)) FROM dataacademykz.superstore.sales_analytics calculates the most recent year in the dataset.

• Filtering: The WHERE clause uses this subquery to filter the data, ensuring that only sales and profit from the most recent year are considered in the analysis.

• Aggregation and Grouping: The SUM(Sales) AS Total_Sales and SUM(Profit) AS Total_Profit compute the total sales and profit for each sub-category. The GROUP BY Sub_Category clause groups the data by sub-category.

• Sorting: The ORDER BY Total_Profit DESC clause sorts the results by total profit in descending order, highlighting the most profitable sub-categories first.

• Result: The output provides a summary of total sales and profit for each sub-category in the latest year, offering insights into their recent performance.

Task #4: Identifying High-Performing Products in Sub-Categories

In this task, we aim to identify the products that are outperforming others in terms of profit within their respective sub-categories. Understanding which products are performing above the average profit level in each sub-category can provide valuable insights for inventory management, marketing focus, and strategic planning.

Objective: Determine the products within each sub-category that have profit levels exceeding the average profit for that sub-category.

Expected Result: A list of products with their corresponding sub-categories and profit figures, where each product's profit surpasses the average profit of its respective sub-category.

Data Source: dataacademykz.superstore.sales_analytics

Click To Show / Hide Solution

-- Selecting products with profit greater than the average profit of their sub-category

SELECT
  Sub_Category, -- Sub-category of the product
  Product_Name, -- Name of the product
  Profit, -- Profit amount
FROM
  `dataacademykz.superstore.sales_analytics` AS superstore -- Alias for the main table
WHERE
  Profit > (
    SELECT
      AVG(Profit)
      FROM
      `dataacademykz.superstore.sales_analytics` AS sub -- Subquery for average profit
      WHERE
      superstore.Sub_Category = sub.Sub_Category ) -- Condition linking sub-category
ORDER BY
  Profit DESC -- Ordering by profit in descending order

Explanation:

Imagine you’re trying to find the star performers in each section of your store. This query is like holding a contest within each product category to see which items are earning more than what's typical for that category.

• We first figure out the average earnings for each type of product (like all the chairs, all the laptops, etc.).
• Then, we spotlight the products that are doing better than this average – the ones bringing in more profit than their peers.

It’s a way of picking out the overachievers in each product group.

Technical Explanation:

This query initially seems complex but is quite intuitive. We are essentially seeking products whose Profit exceeds the average Profit within the same Sub_Category. The key to this query is the correlated subquery, which calculates the average profit for each sub-category, ensuring a relevant and fair comparison. Let’s break the query into chunks and explore it.

SELECT
  Sub_Category,
  Product_Name,
  Profit
FROM
  `dataacademykz.superstore.sales_analytics` AS superstore

This is our main query. Nothing complicated. We create the table and call it a superstore.

WHERE
  Profit >

Here, we started our condition. Where Profit is greater than “something”

  SELECT
    AVG(Profit)
  FROM
    `dataacademykz.superstore.sales_analytics` AS sub
  WHERE
    superstore.Sub_Category = sub.Sub_Category

A-ha! This is our sub-query. We are looking for a condition where profit is greater than the average. But the average of what?

  WHERE
    superstore.Sub_Category = sub.Sub_Category

This WHERE statement is the correlation condition (a correlated subquery) that links the outer query to the sub-query. It ensures that the average profit is calculated for the same sub-category as the current row being examined by the outer query. In other words, we want to make sure that we are comparing apples to apples, products of the same sub-category. 

Without the condition superstore.Sub_Category = sub.Sub_Category, the sub-query would calculate the average profit across all entries in the sales_analytics table without regard to sub-categories. This means it would return a single average profit value for the entire dataset, rather than an average profit for each specific sub-category.