In the world of gastronomy, understanding customer preferences, managing inventory, and optimizing staffing are pivotal to a successful restaurant business. This data analysis case study dives into the operations of Gourmet Delights, a fictional upscale restaurant, to extract insights that drive strategic decision-making. Using SQL Workbench for data analysis and Looker for dynamic visualizations, the project aims to empower stakeholders with actionable insights.
The journey began by meticulously designing the data model using Quick Database Diagrams. Relationships between orders, items, addresses, recipes, ingredients, inventory, staff, rota, and shifts were captured to form the foundation of the analysis.
After the initial sketching, data formatting in Excel ensured consistency and accuracy. The curated tables were then imported into SQL for in-depth analysis.
The analysis commenced with SQL queries that unveiled the story hidden in the data:
- Dashboard 1: Order and Sales Insights
- Extracted critical sales and order metrics such as total orders, sales, average order value, sales by category, top-selling items, and more.
- Dashboard 2: Ingredient Insights and Cost Analysis
- Delved into ingredient-level insights, costs, and inventory management. Analyzed total quantity by ingredients, ingredient cost, calculated pizza cost, and remaining stock percentages.
- Dashboard 3: Staffing and Cost Management
- Focused on staffing-related insights including total staff cost, hours worked, and staff details summary.
Throughout the analysis, a strong emphasis was placed on robust data modeling, ensuring integration, well-established relationships, and efficient data retrieval for accurate insights.
The SQL-derived insights were further explored in Looker, where dynamic dashboards were crafted to provide visually engaging representations of the data.
- Dashboard 1 — Orders
- Visualized order-related insights such as total orders, sales distribution, and geographic order distribution.
- Dashboard 2 — Inventory
- Presented inventory-related metrics, ingredient costs, and remaining inventory percentages.
- Dashboard 3 — Staff
- Provided insights into staffing costs, total hours worked, and staffing details for workforce optimization.
The synergy between SQL analysis and Looker visualizations created a comprehensive approach to extracting insights and empowering decision-makers.
- SQL Workbench: Utilized for in-depth data analysis, query formulation, and data modeling.
- Looker (BI Tool): Employed for creating dynamic and visually appealing dashboards based on SQL-derived insights.
Empowering Decision-Makers: By combining SQL-driven insights with Looker's dynamic visualizations, the project aimed to empower restaurant owners, managers, and stakeholders to make informed decisions and optimize business operations.