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 | --- | --- | --- |
| ... | ... | ... | ... |
-- Analysis of Payment Fees
SELECT AVG(payment_fee) AS avg_fees,
MIN(payment_fee) AS min_fee,
MAX(payment_fee) AS max_fee,
SUM(payment_fee) AS total_fee
FROM public.sales;--Analysis of Payment Fees Data Key Insights:
- The average payment fee across all transactions is approximately $0.022 (or 2.2 cents).
- The minimum fee observed is $0, indicating some transactions might have no fee.
- The maximum fee observed is $0.03 (or 3 cents).
- The total sum of all payment fees is $22.02.
Implications:
-
Low Individual Transaction Cost: The payment fees are very low, with a maximum of 3 cents per transaction. This indicates that individual transaction fees are not a significant expense for single sales.
-
Cumulative Impact: Although the individual fees are minor, the total fee of $22.02 shows that these small fees can accumulate. Depending on the volume of transactions, even minor fees can add up to a considerable amount over time.
-
Potential for Fee-Free Transactions: The minimum fee of $0 suggests that certain payment methods or transaction types do not incur fees. Identifying these could present an opportunity for optimization.
Recommendations to Senior Managers: Promote Low-Fee Payment Methods:
- Action: Explore strategies to encourage customers to use payment methods with lower or no fees. This could include offering small incentives or providing clearer communication regarding payment options.
- Justification: Encouraging customers to adopt more cost-effective payment methods can directly reduce overall transaction costs.
Negotiate with Payment Providers:
- Action: If certain payment methods (such as credit card processing) consistently incur high fees, investigate the possibility of negotiating better rates with the payment service providers.
- Justification: Even a small reduction in percentage fees can lead to substantial savings over a large volume of transactions.
Review Fee Structure for $0 Fee Transactions:
- Action: Understand why some transactions have a $0 fee. Are they associated with cash payments? Are there specific types of bank transfers that are free? Document these processes.
- Justification: Gaining this knowledge can help promote these methods or replicate the conditions that result in fee-free transactions when feasible.
-- Overall Sales & Revenue Distribution
SELECT AVG(total) AS avg_sales,
MIN(total) AS min_sale,
MAX(total) AS max_sale,
SUM(total) AS total_sales
FROM public.sales;Key Insights from Sales and Revenue Data
- The average transaction or sale value is $289.11.
- The smallest transaction is $10.43, likely representing low-margin items or discounted sales.
- The largest transaction is $2,546.33, signifying a high-value purchase or bulk order.
- The total revenue generated for the period is $289,113.
- 1,000 transactions occurred during the period under review (calculated by
Implications for Senior Managers Revenue Volatility Risk:
- A heavy reliance on a few large or high-value transactions (such as the $2.5K sale) creates a vulnerability. For example, losing just five high-value customers could lead to a revenue decrease of 10-15%.
Low-Value Transaction Inefficiency:
- Sales around $10.35 are likely unprofitable, although this depends on the cost of materials.
Untapped Pricing Potential:
- The significant gap between minimum and maximum sales (ranging from
Recommendations:
1. Stabilize Revenue Streams
For instance, launching loyalty tiers:
- Bronze (
The goal is to increase repeat purchases from mid-tier buyers by 25%.
2. Eliminate Profit-Draining Sales By implementing minimum order quantity (MOQ). For example, setting the floor at $25 (below-cost transactions blocked).
3. Bundle low-value items:
For example, creating a "$50 starter " to boost average order value or quantity.
--Revenue Trends by Client Type
SELECT client_type,
ROUND(SUM(total), 2) AS total_sales_revenue
FROM sales
GROUP BY client_type;Analysis of Sales Trends by Client Type
Key Insights:
- The "Wholesale" client type generates significantly more revenue (
- However, while wholesale clients represent 55.2% of total sales, retail clients still contribute a substantial 44.8% of revenue, indicating that both segments are important to the company’s overall sales.
Business Implications:
-
Strategic Focus on Wholesale: The higher revenue from wholesale clients suggests that this segment is a primary driver of the company's top line. Implementing strategies to grow or optimize wholesale operations could yield significant returns.
-
Retail Potential: Although the retail segment generates less revenue, its contribution is still considerable. There may be untapped potential or opportunities to increase its revenue through targeted initiatives.
-
Resource Allocation: Decisions regarding marketing expenditure, sales team focus, inventory management, and operational support should take into account the current revenue contributions of each client type.
Recommendations for Senior Managers: Diversification of Revenue Streams
- Initiate retail strategies, such as optimizing e-commerce platforms and implementing loyalty programs, to decrease reliance on wholesale channels.
- Explore hybrid business models, including partnerships with premium retailers for exclusive offerings.
Retail Gap Analysis
- Identify and analyze the underlying factors contributing to suboptimal retail performance, focusing on aspects such as pricing strategies, marketing initiatives, and distribution efficacy.
- Evaluate the effectiveness of targeted promotions and consider store refurbishments aimed at enhancing customer traffic and engagement.
--Average Payment Fees by Method
SELECT payment AS payment_method,
AVG(payment_fee) AS avg_fee
FROM sales
GROUP BY payment_method;--Total Payment Fees by Method
SELECT payment AS payment_method,
SUM(payment_fee)*100 AS total_fees
FROM sales
GROUP BY payment_method;--Categorical Distribution (Frequencies)
SELECT client_type,
COUNT(*)
FROM sales
GROUP BY client_type; --Categorical Distribution (Frequencies)
SELECT product_line,
COUNT(*)
FROM sales
GROUP BY product_line;Key Performance Indicators (KPIs) and Metrics
- Net revenue
- Wholesale revenue by product line
- Payment Method Cost Efficiency
--NET REVENUE
--The core metric (total sales - payment_fee).
--This shows the actual income after transactional costs.
SELECT
ROUND(SUM((total) * (1 - payment_fee)), 2) AS net_revenue
FROM public.sales;Overall Net Revenue Analysis Key Insight:
- The total net revenue for the period stands at $284,208.43.
Implication:
- This amount reflects the company's earnings after accounting for certain direct costs, such as payment fees previously discussed.
- It serves as a vital indicator of the overall financial health and profitability of sales operations.
- However, it's important to note that this net revenue figure does not encompass all business expenses, including the cost of goods sold, operational overhead, marketing, salaries, and rent. Thus, while it marks progress toward profitability, it doesn't represent the final profit.
Recommendations to Senior Managers: Establish Performance Benchmarks:
- Action: Set clear benchmarks for net revenue that incorporate historical performance data — e.g., monthly, quarterly, and annually — and establish realistic targets aligned with business goals.
- Justification: A single revenue figure lacks actionable context. Benchmarks enable performance evaluation (Is $284K a good figure?) and help identify trends in growth or decline.
--WHOLESALE REVENUE BY PRODUCT LINE
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