Explore Motorcycle Parts Sales

This project explores sales data from a motorcycle parts reseller to reveal insights into revenue patterns and sales performance.

1 Business Problem

AliMoto Supplies sells motorcycle parts, and they’ve have asked for some help in analyzing their sales data.

They operate three warehouses, and sell products to both individual customers (retail) and businesses (wholesale). They accept credit cards, cash and bank transfer as payment methods. However, each payment type comes with a different fee.

The company’s leadership team wants :

1. A better understanding of how much revenue is generated from wholesale sales.
2. An analysis of how this revenue changes from month to month and across different warehouses.

2 Dataset Overview

To explore these questions, AliMoto provided access their database, specifically a table called sales¹, which contains 1,000 records and 10 fields detailing various aspects of each transaction (date, price, client type), between June and August 2021.

I checked the data for common issues like incorrect values, missing information, or duplicate entries. No data quality issues or duplicates were identified. The dataset is clean and ready for analysis (see Table 1).

-- Get 10 random rows from the sales table
SELECT *
  FROM sales
 ORDER BY RANDOM()
 LIMIT 10;

Table 1: Example rows (randomly selected) from the sales data

Click to see the column details

The following describes each field in the sales table, including its data type and a brief description of its meaning.

Column	Data type	Description
order_number	TEXT	Unique order number.
date	TEXT	Date of the order, from June to August 2021.
warehouse	TEXT	The warehouse that the order was made from: North, Central, or West.
client_type	TEXT	Whether the order was Retail or Wholesale.
product_line	TEXT	Type of product ordered.
quantity	REAL	Number of products ordered.
unit_price	REAL	Price per product (US dollars).
total	REAL	Total price of the order (US dollars).
payment	TEXT	Payment method: Credit card, Transfer, or Cash.
payment_fee	REAL	Percentage of total charged as a result of the payment method.

3 Wholesale Net Revenue

How much revenue comes from business (wholesale) sales? To answer this first question, I wrote an SQL query using a GROUP BY clause, an aggregate function, and a subquery.

The results (see Table 2) show that between June and August 2021, approximately 56% of AliMoto Supplies’ revenue came from sales to business customers, while the remaining 44% came from retail customers.

-- Calculate net revenue and revenue share for each client type
SELECT client_type, 
1       SUM(total - ROUND(total * payment_fee, 2)) AS net_revenue,
2       SUM(total - ROUND(total * payment_fee, 2)) / (
          SELECT SUM(total - ROUND(total * payment_fee, 2))
            FROM sales
          ) AS revenue_share
  FROM sales 
 GROUP BY client_type;

1

Total revenue earned from each client type, after subtracting payment fees from each order.

2

The percentage of total net revenue that comes from each client type.

Table 2: Net revenue and revenue share for each client type

This 12 % difference in revenue share, equivalent to $31,883, is likely due to the larger order sizes placed by business customers (see Figure 1). Because they work on multiple vehicles, businesses tend to purchase motorcycle parts in greater quantities, resulting in higher total order values compared to individual retail customers.

Figure 1: Average quantity ordered by client type.
Business customers order, on average, five times more parts than retail customers.

Having determined that 56% of net revenue comes from business customers, the next step is to explore how this wholesale revenue evolves month-to-month and across different warehouse locations.

4 Monthly Revenue Growth Across Warhouses

To analyze the evolution of wholesale revenue, I began by writing an SQL query to prepare the data. This involved filtering for wholesale transactions, extracting the month from the transaction date, and calculating the net total per transaction. I then aggregated this dataset to calculate the monthly net revenue by warehouse, as shown in Table 3.

-- Step 1: Create a temporary table of wholesale sales
-- Adds month information and calculates net total (after payment fee)
WITH sales_wholesale_revenue AS(
  SELECT warehouse,
         date,
1         CAST(strftime('%m', date) AS INT) AS month_int,
2         CASE strftime('%m', date)
                WHEN '06' THEN 'June'
                WHEN '07' THEN 'July'
                WHEN '08' THEN 'August'
         END AS month_chr,
         total,
         payment_fee,
3         total - ROUND(total * payment_fee, 2) AS net_total
    FROM sales
   WHERE client_type = 'Wholesale'
)

-- Step 2: Summarize net revenue by warehouse and month
SELECT warehouse,
       month_chr,
       SUM(net_total) AS net_revenue
  FROM sales_wholesale_revenue
 GROUP BY warehouse, month_int, month_chr
 ORDER BY warehouse, month_int;

1

Extract the month number from the transaction date for chronological sorting.

2

Derive the full month name from the month number for readability.

3

Calculate each transaction’s total after deducting payment fees.

Table 3: Monthly net revenue by warehouse

To visualize trends more clearly, I created a line plot of monthly revenue by warehouse (see Figure 2). This revealed three key insights:

1. Higher Average Revenue in Central and North Warehouses
   From June to August, the Central and North warehouses generated significantly higher average monthly revenue ($26,000 and $19,200, respectively) compared to the West warehouse ($7,500).
2. Strong Growth in Central and North Warehouses
   Between July and August, net revenue in Central and North warehouses increased by an average of 33%.
3. Decline in West Warehouse Performance
   In contrast, the West warehouse experienced a decline in net revenue, with an average decrease of 13% per month since June.

These findings indicate that overall net revenue growth from business customers is being held back by the underperformance of the West warehouse. Improving operations or marketing efforts in this location may help boost total wholesale revenue moving forward.

Figure 2: Net Revenue Trends Across Warehouses.
Central and North warehouses show strong growth, while West declines steadily.

5 Key Findings and Recommendations

This analysis provided a better understanding of AliMoto Supplies’ revenue, particularly from transactions with business customers.

Key findings include:

• Wholesale sales account for 56% of total revenue, making them a major source of income for the company.
• Revenue in the Central and North warehouses increased significantly, by an average of 33%, between July and August.
• In contrast, the West warehouse has experienced a steady decline in revenue, by an average of 13% per month since June.

To support improvements in performance, I recommend the following:

1. Track monthly net revenue for each warehouse to monitor trends and quickly respond to any changes.
2. Investigate the sales strategies used in the Central and North warehouses to understand what’s driving their success.
3. Apply those insights to the West warehouse, where changes may be needed to improve results.

These steps will help the company make more informed decisions and drive consistent growth across all locations.

Footnotes

1. The dataset comes from a DataCamp project: Analyzing Motorcycle Part Sales.