DAD220 Project 2
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
DAD220
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
docx
Pages
7
Uploaded by ChiefPheasant2716
DAD220: Project Two
Southern New Hampshire University
April 12, 2023
This report is intended to address the request for analysis (RFA) from the product manager.
Analyze
the number of returns
by state
:
MYSQL Query -
SELECT Customers.State, COUNT(*) AS Total_Returns
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
GROUP BY STATE
ORDER BY Total_Returns DESC
LIMIT 20;
The screenshot above shows the returns by the top 20 states. Massachusetts is the state with the most returns at 972, followed by Arkansas with 844, and Oregon with 840. This information
could be used for your department to further investigate the need for staffing estimates and supply ordering for the regions that these states fall into. However, it should be noted to take caution in using this data set as the sole source of evidence to make staffing or supply changes as
this high-level view is only intended to steer further investigation. Other considerations should include which SKUs are being returned and the reason, as well as where they originated from. The potential for a faulty lot from one of the many supplier’s factories or warehouses should be tracked in order to quickly recognize patterns to initiate recalls to help retain and improve levels of customer satisfaction.
Analyze the percentage of returns by product type
:
Return Percentage by state - MYSQL Query -
SELECT Customers.State, (COUNT(*) * 100 / (SELECT COUNT(*)
FROM Orders INNER JOIN RMA ON Orders.OrderID= RMA.OrderID)) AS Return_Percent
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
GROUP BY STATE
ORDER BY Return_Percent DESC
LIMIT 20;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
The screenshot above shows the percentage of returns of all orders by state. The highest is Massachusetts with 2.59%, second is Arkansas with 2.25%, and third is Oregon with 2.24%. This
statistic could be used to normalize the returns you see per state and help to identify anomalies and outliers with orders and the number of returns by state. Again, this data should not be used solely as the basis for staffing or policy change, as it is intended to provide a catalyst for further investigation if necessary. The reason for the above returns could help to explain why some states have higher return rates than others. Total returns by SKU –
MYSQL Query -
SELECT sku, description, COUNT(*) AS Total_Returns
FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID
GROUP BY SKU
ORDER BY Total_Returns DESC;
This screenshot shows all SKUs that have been returned, their description and the total number of times they were returned. BAS-48-1-C has been returned 8282 times, ENT-48-40F has been returned 6118 times and ENT-48-10F has been returned 4287 times. Your department could use this data to identify products that have a high return rate, and speak to the manufacturer about a possible recall, or substitution. Additional data that would be helpful in forming a conclusion would be the total number of orders per SKU, so there is a comparable metric to base these numbers on. Percentage of returns per SKU –
MYSQL Query -
SELECT Orders.SKU, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM RMA) AS Percent
FROM RMA INNER JOIN Orders ON Orders.OrderID = RMA.OrderID
GROUP BY SKU
ORDER BY Percent DESC;
This list shows the percentage of all SKUs that have been ordered and returned. This data provides context to the query above, that 8282 or 22% of BAS-48-1 C, Basic Switch 10/100/1000 BaseT 48 Port switches are returned, 6118 or 16% of ENT-48-40F, Enterprise Switch 40GigE SPF+ 48 Port switches are returned, and4 287 or 11% of ENT-48-10F, Enterprise
Switch 10GigE SPF+ 48 Port switches have been returned. Again, these data points could be used to help identify which products are returned the most and to provide evidence to the suppliers or manufacturers for a product recall or substitution. As with all the data above, these high-level data points should not be used as the sole source for personnel or policy changes. This
data should only be used to provide a narrower scope for potential issues and trends with returned products.
In summary, the product manager’s RFA yielded the following results. The top three number of returns by state are: Massachusetts with 972 returns, Arkansas with 844, and Oregon with 840. See the first figure above for the top 20 states with the most returns. If we look at the percentage
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
of returns per state compared to orders, we provide a little more context for the first set of numbers. Using the top three percentages of returns by state directly correlates to the top three states numbers of returns. The highest is Massachusetts with 2.59%, second is Arkansas with 2.25%, and third is Oregon with 2.24%. This statistic could be used to normalize the datapoint of
returns per state and help to identify anomalies and outliers with orders and the number of returns by state. As we switch focus to which product types are returned by SKU, we can see that 8282 or 22% of
BAS-48-1 C, Basic Switch 10/100/1000 BaseT 48 Port switches are returned, 6118 or 16% of ENT-48-40F, Enterprise Switch 40GigE SPF+ 48 Port switches are returned, and4 287 or 11% of
ENT-48-10F, Enterprise Switch 10GigE SPF+ 48 Port switches have been returned. The queries above work together to create a perspective of the data offering limited context to make the data more useful. Please exercise caution when using these statistics as the sole evidence for personnel or policy change, as its purpose is to identify trends for further investigation.