Skip to content

Motorcycle Parts Sales Analysis

This project analyzes sales data for a company that sells motorcycle parts. The goal is to understand wholesale revenue by product line and how it varies month-to-month across different warehouses.

Objectives

  1. Calculate net revenue for each product line.
  2. Group results by month and warehouse.
  3. Filter results to include only "Wholesale" orders.

Dataset Description

The dataset includes the following columns:

  • order_number: Unique order number.
  • date: Date of the order (June to August 2021).
  • warehouse: Warehouse from which the order was made (North, Central, West).
  • client_type: Order type (Retail, Wholesale).
  • product_line: Type of product ordered.
  • quantity: Number of products ordered.
  • unit_price: Price per product (dollars).
  • total: Total price of the order (dollars).
  • payment: Payment method (Credit card, Transfer, Cash).
  • payment_fee: Percentage of total charged as a result of the payment method.

Analysis Steps

  1. Data Preparation: Load and explore the data.
  2. Data Transformation: Create necessary calculations and transformations.
  3. Revenue Calculation: Calculate net revenue for each product line.
  4. Grouping and Filtering: Group results by month and warehouse, and filter for "Wholesale" orders.
  5. Visualization: Visualize the results for better understanding.
Spinner
DataFrameas
df1
variable
SELECT
	*
FROM sales
Spinner
DataFrameas
revenue_by_product_line
variable
-- Start coding here
SELECT product_line,
    CASE WHEN EXTRACT('month' from date) = 6 THEN 'June'
        WHEN EXTRACT('month' from date) = 7 THEN 'July'
        WHEN EXTRACT('month' from date) = 8 THEN 'August'
    END as month,
    warehouse,
	SUM(total) - SUM(payment_fee) AS net_revenue
FROM sales
WHERE client_type = 'Wholesale'
GROUP BY product_line, warehouse, month
ORDER BY product_line, month, net_revenue DESC
Spinner
DataFrameas
df
variable
WITH month_CTE AS (
    SELECT 
        to_char((SUM(total) - SUM(payment_fee)), 'FM999,999,999,999') AS Revenue,
        SUM(total) - SUM(payment_fee) AS Revenue_unformatted,
        order_number,
        EXTRACT(MONTH FROM date) AS month_number,
        CASE
            WHEN EXTRACT(MONTH FROM date) = 6 THEN 'June'
            WHEN EXTRACT(MONTH FROM date) = 7 THEN 'July'
            WHEN EXTRACT(MONTH FROM date) = 8 THEN 'August'
            ELSE 'Other'
        END AS month
    FROM sales
    GROUP BY EXTRACT(MONTH FROM date), order_number
)

SELECT 
    s.product_line, 
    month_CTE.month,
    s.warehouse,
    to_char((SUM(s.total) - SUM(s.payment_fee)), 'FM999,999,999,999') AS Warehouse_net_Revenue,
    to_char(SUM(SUM(s.total) - SUM(s.payment_fee)) OVER (PARTITION BY month_CTE.month), 'FM999,999,999,999') AS Monthly_net_Revenue
FROM sales s
JOIN month_CTE ON EXTRACT(MONTH FROM s.date) = month_CTE.month_number
WHERE s.client_type = 'Wholesale'
GROUP BY s.product_line, month_CTE.month, s.warehouse, month_CTE.month_number
ORDER BY s.product_line, month_CTE.month_number, Warehouse_net_Revenue;

The following code adds a little more than what was asked of me. We have the net reveune for each warehouse grouped by product, and month from least to greatest by warehouse revenue. Then I added how much revenue each warehouse made per month for that specific product. So there should the same number repeating three times for that specific month and that product.

Summary and Insights

Based on the analysis, the following insights were obtained:

  1. Monthly Trends: Net revenue shows distinct trends across different months.
  2. Warehouse Performance: Certain warehouses perform better in specific product lines.
  3. Payment Method Impact: The choice of payment method affects net revenue due to varying fees.

These insights can help the company optimize inventory and marketing strategies for different warehouses and product lines.