A complete end-to-end sales analysis project following a real industry-level data analytics workflow — from data extraction and cleaning to data modeling, analysis, reporting, and dashboard creation using Microsoft Excel.
Ferns & Petals is a popular gifting brand offering products such as cakes, flowers, plants, and personalized gifts. Despite its wide presence, the company faces several key challenges related to sales performance, customer behavior, and operational efficiency.
- Unclear sales trends across months, cities, and product categories
- No clear visibility of top-performing products and occasions
- Delivery time fluctuations affecting customer satisfaction
- Inconsistent revenue across festivals and special occasions
- Limited understanding of gender-wise and city-wise customer behavior
- Lack of actionable insights from raw transactional data
The company aims to answer critical questions such as:
- Which product categories drive the most revenue?
- Which cities are performing well or underperforming?
- How do customer demographics impact purchase behavior?
- What occasions generate peak sales?
- Where can delivery operations be improved?
Transform raw sales data into meaningful insights to help the company:
- Improve marketing and promotional strategies
- Optimize delivery operations
- Identify and focus on high-performing products
- Understand customer buying trends
- Boost overall sales performance
The Ferns & Petals Sales Analysis project analyzes sales performance across products, categories, cities, occasions, customers, and time periods.
This project replicates a real-world BI workflow used by companies for sales monitoring and decision-making.
This project demonstrates skills in:
- ETL using Power Query
- Data Modeling using Power Pivot
- Pivot Table–based exploration
- Interactive Dashboard Design
- Business Insights & Reporting
📄 Included in this repository:
- Ferns & Petals Sales Analysis Report.pdf
- Problem Statement.pdf
- Interactive Excel Dashboard
- Raw dataset (Customers, Orders, Products)
To analyze the sales performance of Ferns & Petals and uncover:
- Revenue patterns across occasions, locations, and categories
- Customer buying behavior & segmentation
- Most profitable product categories
- Delivery trends & order frequency patterns
- Key areas for business improvement
Imported 3 raw CSV files using Power Query:
customers.csvorders.csvproducts.csv
Performed using Power Query:
- Removed duplicates & blanks
- Cleaned and standardized date/time formats
- Added calculated columns:
Delivery DaysRevenueProfit Margin
- Ensured consistent data types
- Cleaned categorical fields (Occasion, Category, City)
Created a Star Schema:
- Fact Table: Orders
- Dimension Tables: Customers, Products
- Relationships using
Customer_ID&Product_ID
Using Pivot Tables & DAX:
- Total Revenue & Total Orders
- Avg. Delivery Days
- Avg. Revenue per Order
- Category-wise sales
- Occasion-wise performance
- City & Gender-wise insights
Built an interactive dashboard with:
- KPIs
- Dynamic charts
- Filters (Slicers)
- Category & Occasion rankings
- City-wise performance insights
A professional PDF report summarizing:
- Business problem
- Key insights
- Sales trends
- Recommendations
| Metric | Value |
|---|---|
| Total Orders | 15 |
| Unique Customers | 99 |
| Unique Products | 15 |
| Total Revenue | ₹17,691 |
| Average Revenue per Order | ₹1,179 |
| Average Delivery Days | ~6.2 days |
- Rajkot
- Bilaspur
- Jaipur
- Bardhaman
- Ambala
- Colors
- Sweets
- Cake
- Plants
- Mugs
- Diwali – Highest revenue
- Anniversary – Consistent demand
- Birthday – High order volume
- Valentine’s Day – Seasonal spike
- Holi – Moderate revenue
- Female customers generated slightly higher revenue
- Male customers placed more orders but had lower AOV
- 🎨 Colors category dominates revenue
- 🚚 Avg. delivery time ~6.2 days → improvement area
- 🎁 Festival seasons (Diwali, Raksha Bandhan) create high spikes
- ⏰ Most orders placed in evening hours
- 📅 High sales during February, July & September
| Tool | Purpose |
|---|---|
| Microsoft Excel | Analysis & Dashboard |
| Power Query | Data Cleaning & ETL |
| Power Pivot | Data Modeling |
| Pivot Tables & DAX | Calculations & KPIs |
| Excel Charts | Visualization |
| File | Description | Key Columns |
|---|---|---|
customers.csv |
Customer details | Customer_ID, Name, City, Gender |
products.csv |
Product catalog | Product_ID, Category, Price |
orders.csv |
Order transactions | Order_ID, Customer_ID, Product_ID, Quantity, Order_Date, Delivery_Date |
📁 Ferns-and-Petals-Sales-Analysis/
│
├── 📁 data/
│ ├── customers.csv
│ ├── products.csv
│ └── orders.csv
│
├── 📊 Ferns_and_Petals_Sales_Analysis.xlsx
├── 📄 Ferns & Petals Sales Analysis Report.pdf
├── 📄 Problem Statement.pdf
├── 🖼️ dashboard_image.png
└── 📘 README.md
This project demonstrates real-world business intelligence skills using Excel, including:
- End-to-end ETL pipeline
- Data modeling with relationships
- Pivot Table & DAX-based analysis
- Interactive Dashboard creation
- Turning raw data into meaningful business insights
It reflects how retail companies track performance and optimize sales & marketing decisions.
- Power Query → Professional data cleaning
- Power Pivot → Star schema modeling
- DAX → Business metric calculations
- Dashboard → Visual storytelling
- Insight writing → Business communication
This analysis helps Ferns & Petals:
- Identify profitable categories (Colors, Cakes, Plants)
- Plan inventory for high-demand festivals (Diwali, Raksha Bandhan)
- Improve delivery operations (reduce 6.2-day avg delivery time)
- Personalize marketing based on gender & city insights
- ETL Pipeline
- Data Cleaning
- Data Modeling
- Pivot Tables
- DAX Measures
- Dashboard Design
- Retail Analytics
- Business Insights
👤 Harsh Belekar
📍 Data Analyst | Python | SQL | Power BI | Excel | Data Visualization
📬 LinkedIn | 🔗GitHub
excel · power-query · power-pivot · data-analysis · dashboard · etl · business-intelligence · retail-analytics · sales-insights
⭐ If you found this project helpful, feel free to star the repo and connect with me for collaboration!