Data Analytics Training Case Study: Superstore Sales

Analysis
Connect with the Trainer: https://www.linkedin.com/in/shivavashishtha/

Enroll in 4 Month Data Corporate Training

https://www.jobaaj.com/golearnings/checkout_datatableau?coupon=Lakshay2024
Full Syllabus of the Program -

Google Data Studio

https://lookerstudio.google.com/u/0/

Datasets
https://docs.google.com/spreadsheets/d/16IF_-1W-ymW7jGgA2zy3kOGYyJo4_kC0Iev-oE_LJTE/edit?
usp=sharing

https://docs.google.com/spreadsheets/d/1SGskUsr2K4hqPHsmIX-BG4g7HzyjhUrUa9-TWUBVsfs/edit?
usp=sharing

https://docs.google.com/spreadsheets/d/1kluKB2RJKntTDrTWmI-YiU9r-tuIpeJ-Z6vmPskFicA/edit?
usp=sharing

https://docs.google.com/spreadsheets/d/1izJR_BTDIghIZaDfo_z1Ii0aDrmAUaQj-tYD0foVZ6c/edit?
usp=sharing

Data Studio Tutorial

https://blog.coupler.io/google-data-studio-tutorial-for-beginners/

SuperStore Dashboard
https://medium.com/@cytizen/superstore-sales-dashboard-with-looker-studio-1ac29e0bd4fd

Case Study Background

You have been hired as a data analyst by ShipFast Logistics, a leading logistics company in India. They face
challenges in managing shipments efficiently across the diverse Indian geography. Your task is to analyze
order shipment data to improve delivery times and reduce costs.

Problem Selection
Optimize the shipment process to reduce delays and costs. Timely deliveries and cost management are critical
for customer satisfaction and profitability.

Stakeholder Involvement
Internal Stakeholders:
 ● Logistics Team
● Finance Department
● Customer Service Team

External Stakeholders:

● Customers
● Shipping Partners

Problem Definition
Reduce shipment delays and minimize shipping costs to enhance operational efficiency and customer
satisfaction.

Data Requirements

● Order ID: Unique order identifier

● Order Date: Order placement date
● Ship Date: Shipment date
● Ship Mode: Shipping mode
● Customer ID: Customer identifier
● Customer Name: Customer name
● Segment: Market segment
● Country: Customer country
● City: Customer city
● State: Customer state
● Region: Customer region
● Product ID: Product identifier
● Category: Product category
● Sub-Category: Product sub-category
● Product Name: Product name
● Sales: Sales amount
● Quantity: Quantity Ordered
● Discount: Discount applied
● Profit: Profit from the order

Metric Development
● Average shipping time
● Number of orders per customer
● On-time shipment rate
● Shipment delays
● Sales and profit analysis

Insights & Actions

Identify patterns and anomalies in shipping data, evaluate shipping partners, and improve shipping efficiency.

Communication
Present findings through a detailed report and visualizations, offering actionable recommendations.

Data Dictionary
 ● Order ID: Unique identifier for each order (String)
● Order Date: Date when the order was placed (Date)
● Ship Date: Date when the order was shipped (Date)
● Ship Mode: Mode of shipping (String)
● Customer ID: Unique identifier for each customer (String)
● Customer Name: Name of the customer (String)
● Segment: Market segment (String)
● Country: Country of the customer (String)
● City: City of the customer (String)
● State: State of the customer (String)
● Region: Region of the customer (String)
● Product ID: Unique identifier for the product (String)
● Category: Category of the product (String)
● Sub-Category: Sub-category of the product (String)
● Product Name: Name of the product (String)
● Sales: Sales amount (Float)
● Quantity: Quantity ordered (Integer)
● Discount: Discount applied (Float)
● Profit: Profit from the order (Float)

Detailed Questions and Visualizations

1. Total Sales: Score Card

Guide:

● Step 1: Calculate the total sales using the sum of the Sales column.
● Step 2: Use a visualization tool (e.g., Looker Studio, Tableau, Power BI) to create a scorecard.
● Step 3: Display the total sales as a single numerical value on the scorecard.

Insight:

● This provides a clear picture of the overall revenue generated by the company.

Impact of this Insight in Problem Solving:

● Knowing the total sales helps in evaluating the company's revenue performance and informs strategic
decisions regarding sales targets and marketing efforts.

2. Total Profit: Score Card

Guide:

● Step 1: Calculate the total profit using the sum of the Profit column.
● Step 2: Use a visualization tool to create a scorecard.
● Step 3: Display the total profit as a single numerical value on the scorecard.

Insight:

● This metric gives a clear understanding of the overall profitability of the company.

Impact of this Insight in Problem Solving:

● Understanding total profit is crucial for financial planning, resource allocation, and identifying areas
where cost efficiency can be improved.
3. Total Orders: Score Card

Guide:

● Step 1: Calculate the total number of unique orders using the count of unique Order IDs.
● Step 2: Use a visualization tool to create a scorecard.
● Step 3: Display the total number of orders as a single numerical value on the scorecard.

Insight:

● This metric shows the volume of orders processed.

Impact of this Insight in Problem Solving:

● Knowing the total number of orders helps in capacity planning and identifying trends in order volume
which can impact logistics and inventory management.

4. Average Sales: Score Card

Guide:

● Step 1: Calculate the average sales amount per order using the mean of the Sales column.
● Step 2: Use a visualization tool to create a scorecard.
● Step 3: Display the average sales as a single numerical value on the scorecard.

Insight:

● This metric indicates the average revenue generated per order.

Impact of this Insight in Problem Solving:

● Understanding average sales per order aids in pricing strategy and can highlight opportunities for
increasing sales per transaction.

5. Unique Customers: Score Card

Guide:

● Step 1: Calculate the number of unique customers using the count of unique Customer IDs.
● Step 2: Use a visualization tool to create a scorecard.
● Step 3: Display the number of unique customers as a single numerical value on the scorecard.

Insight:

● This metric shows the breadth of the customer base.

Impact of this Insight in Problem Solving:

● Knowing the number of unique customers helps in customer segmentation and targeting strategies to
improve customer acquisition and retention.

6. Quarterly Sales: Line Chart

Guide:

● Step 1: Convert the Order Date column to a datetime format.

● Step 2: Extract the quarter and year from the Order Date.
● Step 3: Group the sales data by quarter and year and calculate the total sales for each quarter.
● Step 4: Use a visualization tool to create a line chart with the quarters on the x-axis and the total sales
on the y-axis.
Insight:

● This visualization shows the sales trend over different quarters.

Impact of this Insight in Problem Solving:

● Understanding quarterly sales trends helps identify seasonal patterns, which can inform inventory
planning and marketing strategies.

7. Sales by Segment: Bar Chart

Guide:

● Step 1: Group the sales data by segment and calculate the total sales for each segment.
● Step 2: Use a visualization tool to create a bar chart with the segments on the x-axis and the total sales
on the y-axis.

Insight:

● This visualization shows the distribution of sales across different market segments.

Impact of this Insight in Problem Solving:

● Knowing the distribution of sales by segment helps in tailoring marketing strategies and resource
allocation to different customer segments.

8. Sales by Region: Pie Chart

Guide:

● Step 1: Group the sales data by region and calculate the total sales for each region.
● Step 2: Use a visualization tool to create a pie chart with the regions as slices and the total sales as the
slice values.

Insight:

● This visualization shows the sales distribution by region.

Impact of this Insight in Problem Solving:

● Understanding the sales distribution by region helps in regional performance analysis and can guide
region-specific strategies to boost sales.

9. Sales by Region and Ship Mode: Treemap

Guide:

● Step 1: Group the sales data by region and ship mode and calculate the total sales for each
combination.
● Step 2: Use a visualization tool to create a treemap with the regions and ship modes as hierarchical
levels and the total sales as the size of the rectangles.

Insight:

● This visualization shows the sales distribution by region and shipping mode.

Impact of this Insight in Problem Solving:

● Analyzing sales by region and ship mode helps evaluate the efficiency of shipping methods in different
regions, informing logistics optimization strategies.
Deliverables
● Comprehensive case study document with problem, dataset, analysis, and questions.
● Solution guide with detailed answers and explanations.
● Additional resources and references for further exploration.

This case study will help trainees build analytical skills and provide actionable insights to improve ShipFast
Logistics' operations.