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.

Advanced Excel Power Query Power Pivot DAX Data Modeling Dashboarding

View Repository

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

1

Data Source Integration

Imported multiple CSV data sources (customers, orders, products) via Power Query and established consistent loading processes.

2

Data Transformation

Cleansed inconsistent formats, created calculated fields (Revenue, Delivery Time), and extracted temporal components.

3

Data Modeling

Constructed a dimensional Star Schema with Power Pivot, creating relationships between Fact and Dimension tables.

4

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.

View More Projects Back to Top

E-commerce Analytics Dashboard:Data-Driven Decision Making