Aim

The aim of this project was to create a database to dashboard system for a pizza company using SQL to query the database, and power BI to create a dashboard containing filters, KPIs, summary visualizations of consumer trends, and a few key high-level findings regarding consumer trends. Furthermore, the aim was to create a robust framework where results from the dashboard are validated using SQL queries.

KPIs

The KPIs in this project chosen to provide critical business insights for decision making, as well as milestones for gauging company progress. The KPIs chosen for this project were:

• Average order revenue

• Total revenue

• Total pizzas sold

• Total orders

• Average pizzas per order

Visualisations

The visualisations decided upon for the final dashboard were:

• Weekly order trends (bar)

• Yearly order trends (line)

• Pizzas sales by category (pie)

• Pizza sales by size (pie)

• Total pizzas sales by category (bar)

• Top + bottom 5 pizzas by revenue (bar)

• Top + bottom 5 pizzas by quantity (bar)

• Top + bottom 5 pizzas by number of orders (bar)

SQL Queries for KPIs and Visualisations

To ensure the values displayed in the final Power BI dashboard are correct, SQL queries were written and the results were recorded in a word document. The full document can be found at: queryValidation.docx 

Data Manipulation in Power BI

Once the database was connected to power BI, the dataset was then manipulated to make a dashboard in Power BI, the KPIs had to be recreated (as measurements) and the order date column had to be transformed to create new order month and order day columns using power query.

Dashboard

Once all the data was in the necessary states, the visualisations were created and added to the dashboard in power BI. The final dashboard. These values also match the values found from the SQL queries. The dashboard has two pages, a home page and best and worst sellers page as well as filters by date range and pizza category in the top right corner. 

Insights

The results of the analysis showed that the busiest days of the week were Thursdays to Saturdays with an average of over 3.2k in revenue on those days. The day of the week with the lowest average revenue was Sunday with just over 2.6k in average revenue. These results could be used to better determine the required staff hours, open hours as well as select days to host promotions.

The revenue by pizza categories chart shows that there was an even spread of revenue between the four pizza categories, however the pizza size sales show that large was by far the most popular size with over 45% of sales whereas the extra large and extra extra large both made up less than 2% of the annual revenue. The medium and small pizza categories made up a fairly equal share of the remaining revenue. This suggests that the removal of the extra large and extra extra large pizza sizes may be beneficial for the company as it contributed minimally to sales.

The revenue by top 5 pizzas showed that the Thai Chicken pizza had the greatest revenue whereas the Classic pizza was in the most orders and was sold the most. The bottom 5 pizzas showed that the Brie Carre pizza had the lowest revenue, was in the least orders and was sold the least. This may suggest the the Brie Carre pizza should be removed from the menu to reduce ingredient costs.