The aim of this project is to create a dashboard for coffee sales. Transform raw data into meaningful and interactive dashboard which include many stages such as Data Collecting, Data Cleaning, Data Modelling that offer deep insights.
Steps include:
- Data Gathering
- Data Cleaning
- Data Modelling
- Filtering, Sorting, Excel functions -xlookup, if ,Power Query
- Pivot Table & Pivot Chart
- Data Visualization & Dashboard Creation Data Gathering: The dataset can be found on Mo Chen 's YouTube Channel.
Data Cleaning:
This stage began with creating a working sheet and transforming the datasets presented in different sheets into "Tables". After this we start getting to explore the data and check for any data quality issues.
Checking NULL values, blanks and Errors. Removing duplicates, and irrelevant columns. Made sure data is consistent and clean with respect to data type, data format and values used. With the help of Excel Power Query Editor I take all the table(Orders,Customers,Products) together and after merging them make I single sheet (ETL Sheet). Using Xlookup function to find the loyalty card from the customers and products table. Created “Sales” column by calculation (Unit Price & Quantity)and “Coffee Type Name” and “Roast Type Name” columns by using conditional column in Power Query. Data Analysis 6 pivot tables were created to summarise the data and help identify trends in the dataset focusing on relationship between sales and other factors such as Month, customers, country, Loyalty, Roast Type, Coffee Type. Data Visualization Finally, the dashboard was created by inserting and customizing the pivot charts of corresponding pivot table on a separate sheet named “Dashboard”. Dashboard which I create is user friendly and interactive with 1 Size slicer,2 Pie charts for Roast Type & Coffee Type,1 Donut Chart for Loyalty Customer in Percentage,1 Bar Chart for Customers,1 Column Chart for Country,1 Timeline for Year,1 Line Chart for month wise sale.
Insights:
- United States is the highest Coffee selling country.
- Liberica Coffee is leading in terms of Revenue Generation.
- Light Roast Coffee is the winner in Revenue Generation
- In the Year 2021 there is a sudden high in coffee sales
- Coffee Sales by country wise: • In Ireland Liberica is the highest selling coffee • In US Arabica is the Leader • In UK Excelsa earns the first spot