Skip to content
Analyzing Motorcycle Part Sales
You're working for a company that sells motorcycle parts, and they've asked for some help in analyzing their sales data!
They operate three warehouses in the area, selling both retail and wholesale. They offer a variety of parts and accept credit cards, cash, and bank transfer as payment methods. However, each payment type incurs a different fee.
The board of directors wants to gain a better understanding of wholesale revenue by product line, and how this varies month-to-month and across warehouses. You have been tasked with calculating net revenue for each product line and grouping results by month and warehouse. The results should be filtered so that only "Wholesale" orders are included.
They have provided you with access to their database, which contains the following table called sales:
Sales
| Column | Data type | Description |
|---|---|---|
order_number | VARCHAR | Unique order number. |
date | DATE | Date of the order, from June to August 2021. |
warehouse | VARCHAR | The warehouse that the order was made from— North, Central, or West. |
client_type | VARCHAR | Whether the order was Retail or Wholesale. |
product_line | VARCHAR | Type of product ordered. |
quantity | INT | Number of products ordered. |
unit_price | FLOAT | Price per product (dollars). |
total | FLOAT | Total price of the order (dollars). |
payment | VARCHAR | Payment method—Credit card, Transfer, or Cash. |
payment_fee | FLOAT | Percentage of total charged as a result of the payment method. |
Your query output should be presented in the following format:
product_line | month | warehouse | net_revenue |
|---|---|---|---|
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_one | --- | --- | --- |
| product_two | --- | --- | --- |
| ... | ... | ... | ... |
DataFrameas
revenue_by_product_line
variable
-- Start coding here
select
product_line,
extract(month from Date) AS Month,
warehouse,
sum(total - (total * payment_fee)) as net_revenue
from sales
where client_type = 'Wholesale'
group by product_line, month, warehouse
order by product_line, month, warehouse
;Current Type: Bar
Current X-axis: net_revenue
Current Y-axis: product_line
Current Color: warehouse
Net Revenue By Warehouse And Product Line
Current Type: Line
Current X-axis: month
Current Y-axis: net_revenue
Current Color: warehouse
Warehouse Monthly Revenue Trends.
Project Conclusion:
Based on the analysis of wholesale sales data from June to August 2021, segmented by product line, warehouse, and month, the following insights have been derived:
Product Line Performance:
- Frame & Body consistently generated high revenue, especially in the North and Central warehouses, with peak sales in August. This indicates strong demand for these parts in both regions.
- Suspension & Traction also performed well, particularly in June and July, with the highest sales in the North warehouse. The steady revenue suggests a stable demand for this category across all months.
- Miscellaneous items showed varied performance, with Central and North warehouses generally outperforming the West. Revenue was lower overall for this category, indicating a niche demand that may benefit from targeted marketing or seasonal promotions.
Warehouse Comparison:
- North Warehouse consistently led in revenue generation across all major product lines, especially for Suspension & Traction, suggesting it serves a larger or more active wholesale market.
- Central Warehouse performed well in Frame & Body and Suspension & Traction sales but was surpassed by the North warehouse in overall revenue.
- West Warehouse consistently showed the lowest revenue across product lines, especially in August, suggesting potential for improvement. Optimizing inventory or focusing on high-demand products could boost performance here.
Monthly Trends:
- Revenue for Frame & Body increased steadily from June through August, peaking in August across both the North and Central warehouses.
- Suspension & Traction had strong revenue in June and July but showed a decline in August, particularly in the West warehouse, where it dropped significantly. This may indicate a seasonal or inventory-related factor impacting demand.
- Miscellaneous items had moderate revenue increases from June to July in Central and North warehouses but remained low in August, showing a less predictable demand trend.
Strategic Recommendations:
- Focus on High-Performing Warehouses: Leverage the North and Central warehouses for Frame & Body and Suspension & Traction products, which have consistent demand. Ensure inventory and marketing efforts are aligned to maximize potential in these regions.
- Inventory Optimization in West Warehouse: Investigate ways to boost sales in the West warehouse. Analyzing specific customer needs and adjusting product offerings could increase revenue.
- Product Line Promotion and Diversification: Frame & Body and Suspension & Traction parts have proven demand, but diversifying and promoting Miscellaneous items through targeted discounts or bundled deals may help drive additional sales.