EXCEL-BASED DATA ANALYSIS PORTFOLIO PROJECT
E-commerce Analytics Dashboard:
Data-Driven Decision Making
Transforming raw E-commerce data into actionable business insights through advanced Excel analytics, Power Query, and Power Pivot.
Project Overview
This repository demonstrates a complete data analytics workflow using Excel to process and analyze sales data for FNP (Ferns & Petals), a fictitious online gifting company.
The project addresses critical business questions regarding revenue patterns, customer behavior, and operational efficiency, culminating in a dynamic, interactive dashboard for strategic decision-making.
Critical Business Questions
- How can revenue patterns be analyzed across multiple dimensions?
- What insights can we derive about customer spending behavior?
- How do operational metrics like delivery time impact performance?
- Which products and occasions drive the most value?
The Analytics Architecture
Data Source Integration
Imported multiple CSV data sources (customers, orders, products) via Power Query and established consistent loading processes.
Data Transformation
Cleansed inconsistent formats, created calculated fields (Revenue, Delivery Time), and extracted temporal components.
Data Modeling
Constructed a dimensional Star Schema with Power Pivot, creating relationships between Fact and Dimension tables.
Visualization
Designed an intuitive dashboard with interactive slicers, timelines, and professional formatting techniques.
Key Analytical Components
Revenue Framework
- Temporal: Spikes in Feb & Sept. Peak hours 5-7 PM & 10-11 PM.
- Hierarchy: "Magnam Set" is the best-selling product.
- Geo: Top markets: Jaipur, Kanpur, North Dumdum.
Customer Insights
- Spending: Average customer spend of ₹3,520.90.
- Efficiency: Average delivery time of 5.53 days.
- Occasions: Anniversary and Holi are highest-revenue events.
Data-Driven Recommendations
Based on the analysis, the following strategic actions are recommended to drive growth and efficiency:
Leverage Seasonal Peaks
Plan aggressive marketing for Feb & Sept. Increase inventory for peak months.
Optimize Categories
Expand "Colors" & "Sweets". Bundle best-sellers to increase basket size.
Focus on Occasions
Target Anniversary & Holi. Create occasion-specific bundles.
Hourly Optimization
Flash sales during 5-7 PM & 10-11 PM high-activity windows.
Delivery Speed
Reduce 5.53-day average. Analyze delivery bottlenecks by region.
Maximize CLV
Cross-sell to increase ₹3,520 avg spend. Develop loyalty programs.