We all love a good pizza! However, it takes more than love to create and monitor a business. This project outlines the needs of a hypothetical business, “Ben’s Pizza”, and the visualizations I created to help the business owner, Ben, monitor his order activity and inventory, and manage his staff efficiently.
Project Brief
The client, Ben, is opening a takeout and delivery pizzeria in his hometown.
- Design and build a tailormade, bespoke relational database for his business, that will capture and store all the information and data that the business generates
- Help Ben monitor business performance using Dashboards
There are three main areas Ben requires us to focus on, orders, inventory and staff.
Relational database design
For this project, I used Quick Database Diagrams to conceptualize the database for Ben’s business based on data he collected over a few months. The Enhanced Entity-Relationship (EER) daigram generated can be seen below:
Custom SQL queries for dashboarding
Before creating the dashboards, I created SQL views of the data needed for the three business trackers.
- Order Activity: To gather the data required data, I joined the orders, item, and address tables, and selected the fields that needed to be displayed on the dashboard.
- Total orders
- Total sales
- Total items
- Average order value
- Sales by category
- Sales by hour
- Top selling items
- Orders by hour
- Orders by address
- Orders by delivery/pickup
- Inventory Management: Two data sources were generated to monitor Ben’s inventory. The first contains the data required to calculate the cost of menu items and the used inventory, while the second holds the data required to determine which ingredients need to be reordered based on the remaining inventory.
- Total quantity by ingredient
- Total cost of ingredients
- Calculated cost of each item
- Percentage stock remaining by ingredient
- List of ingredients to reorder based on remaining inventory
- Staff Management
- Staff shifts
- Staffing costs
The custom queries for the monitor can be found here.
Dashboard
The business monitors were built using Looker Studio and can be found here.