This project focuses on creating a comprehensive Customer Detail Dashboard using Excel. The dashboard visualizes key metrics and insights from transactional data spanning from 2020 to 2022. The aim is to provide an interactive and intuitive interface for analyzing customer behavior, sales performance, and profitability across different regions and demographics.
The dataset includes transactional data from 2020 to 2022, imported and transformed using Power Query. Multiple tables were normalized and relationships were established to ensure data integrity and ease of analysis. The key tables include transactions, customers, products, and stores.
- Data Importation: Imported raw data into Power Query from multiple sources.
- Data Transformation: Performed data cleaning, filtering, and transformation using Power Query to ensure consistency and accuracy.
- Database Normalization: Normalized data to reduce redundancy and improve data integrity.
- Relationship Creation: Established relationships between tables in Power Pivot to facilitate seamless data analysis.
- DAX Calculations: Created calculated fields using DAX (Data Analysis Expressions) to derive meaningful metrics.
- Pivot Tables: Utilized Pivot Tables to summarize and analyze data dynamically.
- Dashboard Development: Developed an interactive dashboard in Excel to visualize key metrics and insights.
-
Summary Metrics: Displays key performance indicators such as total quantity ordered, average order value, total revenue, and gross profit.
-
Transaction, Store, and Product Options: Displays detailed metrics related to transactions, stores, and products, including the number of transactions, products, stores, and customers.
-
Top-5 Customers by Revenue: A bar chart highlighting the top-5 customers based on total revenue.
-
Profit by Age Group: Pie chart showing profit distribution across different customer age groups.
-
Profit by Country: Geographic visualization of total profit by country.
-
Profit by Gender and Marital Status: Bar charts illustrating profit breakdown by gender and marital status.
-
Top and Least Profitable Cities: Bar charts showing the most and least profitable cities in Canada, Mexico, and the USA.
-
Filters: Interactive filters allowing users to select specific years, months, and countries for more focused analysis.
- Excel: Advanced proficiency in Excel for data importation, transformation, and analysis.
- Power Query: Expertise in using Power Query for data cleaning and transformation.
- Power Pivot: Skilled in creating data models and establishing relationships between tables.
- DAX: Proficient in writing DAX formulas for calculated fields and metrics.
- Attention to Detail: Ensured data accuracy and consistency throughout the project.
- Problem-Solving: Addressed and resolved data-related issues effectively.
- Analytical Thinking: Analyzed complex data to derive meaningful insights.
- Communication: Clearly communicated findings and insights through visualizations.
This project showcases the effective use of Excel for creating a detailed and interactive dashboard that provides valuable insights into customer behavior and sales performance. The skills and techniques demonstrated in this project can be applied to a wide range of data analysis and visualization tasks.