Background
E-commerce is one of the fastest growing sector as now-a-days most of the customers prefer to purchase online. More and more business houses are implementing
web sites providing functionality for performing commercial transactions over the web. It is reasonable to say that the
process of shopping on the web is becoming commonplace.
Domain
E-Commerce
Business Requirements
- To find the Total Sales of all the orders over 12 months for each region and segment.
- To analyse the product's Category-wise Profit and Sales and prepare a chart for the Sales and Profit Pattern for various months.
- To determine the Top Paying customers so that special Premium discount can be offered to such clients.
- To find the Best Selling Products among the different segments
- To determine the YOY growth for multiple Key Metrics.
Installation of the Project
- Download the workbook named as 'Ecommerce Sales Analysis'
- 'Data' Sheet represents the combined data of all the orders of all regions,customers,sales,countries.
- 'Dashboard' Sheet represents the main dynamic dashboard of the application with 6interactive charts, 5 KPIs and filter for ship mode and region.
Learnings
- Dashboard Development
- Calculated Field
- Custom Charts and its Formatting
- Conditional Formatting
- YoY calculation
- Sparklines
- Filters and Sorting
- Pivot Table
Brief Description of the dataset
Fields and its Description
Order Date- Date on which customer has placed the order
Ship Date- Shipment date of placed order
Ship Mode- Mode of shipment- Same Day/First Class/Second Class/Standard
Customer Id- Unique Customer Id
Segment- Consumer/Home Office/Corporate
Country- Country Name of customer
City- Customer's City
State- Customer's State
Postal Code- Pincode of the customer
Region- Main Region acc. to Country
Product ID- Unique Product Id
Category- Category of the product
Sub Category- Sub-Category of the product
Product Name- Name of the Product which is ordered
Sales- Sales value by that product sold
Quantity- No. of quantities ordered
Discount- Offer/Discount % on that product
Profit- Profit earned value from product's order
Dashboard Outcomes (Tasks Accomplished)
- Conducted in-depth data analysis on E-commerce Sales Data to gain some meaningful insights for each region and segment.
- Developed an interactive dashboard with 5 KPIs,multiple filters and 6 dynamic charts in Excel.
- Created a Combo Chart for showcasing Sales of the products and Profits from each month.
- Determined the Best Selling Products and the Top 5 Premium Customers
- Build a Pie Chart and Column Chart for Category-wise Profit and Sales respectively.
- Implemented Map Chart for showcasing Sales by State.
Final Interactive Dashboard
