Customer Sales and Shipping Insights Dataset

This dataset is a comprehensive collection of sales and shipping records for a retail company. It consists of multiple sheets that capture various aspects of the business, including orders, customer returns, shipping costs, and personnel management. The dataset is used to perform detailed analysis on sales performance, customer behavior, product profitability, and shipping efficiency.

Dataset Overview

Number of Rows: 9,994 (for the Orders dataset)
Number of Columns: 21
Key Columns:
Order ID: Unique identifier for each order.
Customer ID: Unique identifier for each customer.
Sales: Sales value generated by each order.
Profit: Profit gained from each order after deducting costs.
Ship Mode: The shipping method used for the order.

Which city has the most sales value?

Findings: City: New York City
Sales: $256,368
Details: New York City is the leading city in terms of sales, contributing a significant portion to the overall revenue, with a total sales value of $256,368.

Which state generates the most sales value?

Findings: .State: California
Sales: $457,688
Details: California leads as the top state generating the most sales, with $457,688 in total sales. This indicates the state’s substantial contribution to the business's revenue.

Who are our top 5 customers in terms of sales?

Findings: Sean Miller: $25,043
Tamara Chand: $19,052
Raymond Buch: $15,117
Tom Ashbrook: $14,596
Adrian Barton: $14,474
Grand Total: $88,282

Details: The top 5 customers contribute a combined total of $88,282 in sales, with Sean Miller leading at $25,043. This highlights the importance of these key customers for the business's revenue stream.

Who are our top 5 customers in terms of sales?

Findings: Emily Phan: 17 orders
Noel Staavos: 13 orders
Joel Eaton: 13 orders
Chloris Kastensmidt: 13 orders
Erin Ashbrook: 13 orders
Grand Total: 69 orders

Details: Emily Phan leads with the most orders, at 17, while the other top customers (Noel Staavos, Joel Eaton, Chloris Kastensmidt, and Erin Ashbrook) each have 13 orders. This demonstrates strong loyalty among these key customers in terms of frequency.

Which segment of clients generates the most sales?

Findings: Consumer: $1,161,401
Corporate: $706,146
Home Office: $429,653
Grand Total: $2,297,201

Details: The Consumer segment contributes the highest sales value, with over $1.16 million in sales, followed by Corporate and Home Office. This breakdown shows that the Consumer segment is the strongest performer in terms of revenue generation.

Top 5 States by Sales

Findings: California: $457,688
New York: $310,876
Pennsylvania: $116,512
Texas: $170,188
Washington: $138,641

Details: California remains the top state with $457,688 in sales, followed by New York with $310,876. The map visualization provides a geographical distribution, showing that the western states like California and Texas, along with New York in the east, are key markets.

What is the most and lowest profitable product that we sell?

Details: The most profitable product is the Canon imageCLASS 2200 Advanced Copier, generating a total profit of $25,200. The least profitable product is the Cubify CubeX 3D Printer Double Head Print, which has actually incurred a loss of -$8,880. This indicates that this product may need reassessment or better cost management to avoid future losses.

What is the most used ship mode by our customer?

Findings: Standard Class: 5,968 orders
Second Class: 1,945 orders
First Class: 1,538 orders
Same Day: 543 orders
Grand Total: 9,994 orders

Details: The Standard Class is the most frequently used shipping mode, accounting for 5,968 orders, which is more than half of the total shipping orders. The other modes—Second Class, First Class, and Same Day—are used significantly less. The accompanying chart visually emphasizes the dominance of Standard Class in comparison to other shipping modes.

On average, how long does it take the orders to reach our clients? (Based on each shipping mode)

Findings: tandard Class: 5 days
Second Class: 3 days
First Class: 2 days
Same Day: 0 days
Grand Total: 4 days (average across all modes)

Details: The Standard Class takes the longest time, with an average of 5 days for delivery, while Same Day shipping understandably delivers on the same day, with 0 days. First Class and Second Class offer faster delivery times compared to Standard Class, averaging 2 and 3 days, respectively. The graph visualizes the shipping time range, with the trend decreasing as the shipping class becomes faster.

What are the top-performing product categories in terms of sales and profit?

Findings: Technology: Sales = $836,154; Profit = $145,455
Office Supplies: Sales = $719,047; Profit = $122,491
Furniture: Sales = $742,000; Profit = $18,451
Grand Total: Sales = $2,297,201; Profit = $286,397

Details: Technology products are the highest-performing category in both sales and profit, with $836,154 in sales and $145,455 in profit. Office Supplies and Furniture follow closely in sales, but the Furniture category has the lowest profit margin compared to Technology and Office Supplies. This indicates that although Furniture has strong sales, the profit from this category is much lower.

Sales KPIs Overview:

Anna Andreadi: $725,458 in sales (91% of the target)
Cassandra Brandow: $391,722 in sales (49% of the target)
Chuck Magee: $678,781 in sales (85% of the target)
Kelly Williams: $501,240 in sales (63% of the target)
Grand Total: $2,297,201

The target for the past period is set at $800,000.

Performance Breakdown:

Findings:

Anna Andreadi:

Achieved: $725,458 (91% of the target)
Missed Target: $74,542 (9% shortfall)

Cassandra Brandow:

Achieved: $391,722 (49% of the target)
Missed Target: $408,278 (51% shortfall)

Chuck Magee:

Achieved: $678,781 (85% of the target)
Missed Target: $121,219 (15% shortfall)

Kelly Williams:

Achieved: $501,240 (63% of the target)
Missed Target: $298,760 (37% shortfall)

Details: Anna Andreadi has performed the best, achieving 91% of the sales target with only a minor shortfall of 9%. Chuck Magee follows closely behind with 85% of the target achieved. Kelly Williams and Cassandra Brandow lag in their performance, achieving only 63% and 49% of their targets, respectively. Cassandra Brandow has the largest shortfall, missing 51% of the target. These KPIs provide a clear snapshot of individual sales performance, with Anna Andreadi leading in terms of target achievement.