How to Build a Coffee Shop Sales Dashboard in Power BI + MySQL

TL;DR

An end-to-end coffee shop sales portfolio project splits into two tools: MySQL for firing queries against the raw data to answer business requirements, then Power BI for the dashboard. The MySQL half follows six steps, from data walkthrough and raw file preparation through database creation, import, cleaning and data type changes, to the business-requirement queries.

Transcript

hey guys welcome back to my channel so in today's video we are going to see a data analyst portfolio project and in this we are going to make use of powerbi plus MySQL softwares so this will be a complete project and in this video we are going to see step by step complete Solutions and in front of your screen at at this end you can see the project ... Read More

Key Insights

• The project pairs MySQL and Power BI on a single coffee shop sales dataset, with SQL queries answering the business requirements first and the Power BI dashboard built to match those query results afterwards, so the numbers can be cross-checked between the two tools.
• The dashboard header carries three KPIs, total sales, total orders and total quantity sold, and each is accompanied by smaller sub KPIs that indicate month-on-month increase or decrease relative to the month chosen in a single-select month slicer.
• A calendar heat map renders the month as a calendar grid where darker colors mark higher sales days, and hovering over any day surfaces total sales, total quantity sold, total orders, and the revenue difference versus last month for that day.
• The stacked bar chart showing sales by store location across three locations is a customized build rather than a default Power BI visual, carrying extra information plus sub KPIs comparing current month store performance against last month.
• The daily sales trend chart includes an average line, and bars are colored differently depending on whether they fall above or below that average, making it immediately visible which days performed better than the period average.
• A second heat map plots days against hours of the day, so hovering on a cell such as Wednesday at hour nine returns total sales, quantity and orders for that day-hour combination along with revenue versus last month.
• Interactive action filters make every element clickable as a cross-filter: clicking weekday, weekend, a store location, a product category such as coffee, or an individual product such as espresso refilters the entire dashboard to that selection.
• The MySQL workflow proceeds in six ordered steps, data walkthrough, raw file preparation, database creation, file import, cleaning including data type changes, then business-requirement queries, and the import step alone is expected to take 15 to 20 minutes.

Questions & Answers

Q: What tools does this coffee shop sales project use?

The project uses two softwares together, MySQL and Power BI. MySQL holds the raw coffee shop sales data and is where queries are fired to solve the business problems and produce results for the client. Power BI is where the final sales dashboard is built. The results returned by the SQL queries are then compared against the dashboard so the two tools validate each other. The build is delivered across two parts, with the MySQL portion covered first and the Power BI portion following in the second part.

Q: What are the steps to complete the MySQL part of the project?

Six steps are laid out for the MySQL portion. First, a complete data walkthrough covering how the data looks and how to read it. Second, preparation of the raw file so it can be imported into the MySQL database. Third, creating the database. Fourth, importing the file, which is described as a long process taking around 15 to 20 minutes. Fifth, cleaning the imported file, since imported data carries issues such as incorrect data types that need changing. Sixth, firing SQL queries against the business requirements to get results for the client.

Q: How long does importing the file into MySQL take?

Importing the file into MySQL is called out as a long process, taking roughly 15 to 20 minutes. Viewers are told to be patient with it and not get frustrated, since the wait is expected rather than a sign that something has gone wrong. It sits as the fourth step in the MySQL sequence, after creating the database and preparing the raw file, and before the cleaning stage where data types are corrected on the imported table.

Q: Can these SQL queries be used on databases other than MySQL?

Yes. If you are working in MS SQL Server, PostgreSQL or Oracle instead, the standard queries stay the same. Around 85 percent of the queries carry over unchanged, and about 15 percent of the query functions need to be swapped. The differences are mainly in the date functions, which vary between database systems. The suggested approach is to search for the alternative function in your database of choice and substitute it in, after which all of the queries in the project can be used.

Q: What KPIs appear at the top of the sales dashboard?

Three header KPIs sit at the top of the dashboard, showing total sales, total orders and total quantity sold. Under each of those, smaller numbers act as sub KPIs indicating the month-on-month increase or decrease for that metric, tied to whichever month is picked in the single-select month slicer. Each of the three also carries a sparkline of daily trends, showing how the metric performs day by day and where the higher and lower spikes fall. A dynamic data range is also displayed at the top.

Q: How does the calendar heat map work in this dashboard?

The calendar map, also called a calendar heat map, lays the month out in calendar form and colors each day by sales volume, with higher sales days shaded darker and the rest shaded lighter. Hovering the mouse over a day surfaces a detailed tooltip. For example, hovering on 25th May shows total sales, total quantity sold and total orders for that day, plus revenue versus last month and the month-on-month difference figures. The calendar also changes when a different month is selected in the slicer.

Q: How do the interactive filters change the dashboard?

Action or interactive filters are applied so that clicking on a chart element refilters everything else. Clicking the weekday slice shows only weekday performance and drops weekends out, and clicking weekends does the reverse. Clicking a store location changes the entire dashboard for that location. Clicking a product category such as coffee filters the whole dashboard to coffee sales, and clicking an individual product such as espresso, described as the best selling product, shows how that product performs and which category it falls under.

Q: What charts are included in the final Power BI dashboard?

Beyond the three header KPIs with sparklines, the dashboard includes a vertical filter panel on the left, a calendar heat map, a sales versus weekday and weekend split built with custom calculations, sales by store location across three locations as a customized stacked bar chart, a daily sales trend over the selected month with an average line and bars colored above or below that average, sales by product category, a top 10 sales by product chart, and a second heat map covering days against hours of the day.

Summary & Key Takeaways

• The project is a data analyst portfolio build covering a coffee shop sales topic using MySQL and Power BI together, split across two video parts. Raw data is downloadable from a link in the description. SQL queries are fired against the MySQL database to produce results matching business requirements, and those results are then compared against the finished dashboard.

• The final dashboard opens with three header KPIs for total sales, total orders and total quantity sold, each with sub KPIs showing month-on-month increase or decrease driven by a single-select month slicer. Each KPI also carries a sparkline of daily trends showing which days spike higher or lower. A dynamic data range at the top updates automatically as new data enters the database.

• Charts include a calendar heat map where higher sales days get darker colors and hovering reveals sales, quantity, orders and revenue versus last month; a customized weekday versus weekend split built with calculations; a customized stacked bar chart of sales by store location across three locations with month-on-month sub KPIs; and a daily sales trend with an average line coloring bars above and below average differently.

• Additional visuals cover sales by product category, top 10 sales by product, and a second heat map mapping days against hours to show sales performance by hour. Interactive action filters let a click on weekday, weekend, store location, product category such as coffee, or a product such as espresso refilter the entire dashboard, and switching months to June, March, April or February changes every metric.

• The MySQL portion runs through six steps: data walkthrough, preparation of the raw file for import, creating the database, importing the file, cleaning the imported file including data type changes, and firing SQL queries for the business requirements. Importing the file into MySQL is described as a long process taking roughly 15 to 20 minutes.

• The queries transfer to other databases with modest rework. Around 85 percent of the queries stay the same on MS SQL Server, PostgreSQL or Oracle, while about 15 percent, mainly date functions, need alternative functions that can be looked up. The description timestamps mark Project Overview at 02:00, Business Requirements at 14:18, Data Walkthrough at 28:22, and Total Sales Analysis at 56:26.