"Are you tired of manually tracking restaurant performance metrics, spending hours analyzing data without getting clear insights, or missing out on opportunities to improve your business?" I asked my close friend, a restaurant manager, in my best 1920’s radio salesmen voice. She had asked me to do this project originally.
She had previously worked in large corporations that had access to a robust toolset, but now that she worked at a smaller independent restaurant, she was in need of a way to process data quickly that could be taught to someone with low computer skills.
"If so, you're not alone. The restaurant industry is incredibly competitive, and making data-driven decisions can be the difference between success and failure. That's where the Daily Restaurant Dashboard comes in."
This data project aims to provide restaurant owners and managers with a comprehensive overview of their business performance in an easy-to-use tool. The goal is to track and analyze the key metrics that matter most to the business while having it designed to be user-friendly and intuitive, giving easy access to the data needed to make informed decisions.
To make sure that this project is useful for restaurant owners and managers, it's crucial to plan its features, design, and functionality before starting development.
Define the scope of the project: Determine which metrics will be tracked and analyzed by the dashboard, and identify any limitations or challenges that may need to be addressed. It's important to ensure that the actual user's metrics are represented.
Identify data sources: Determine which data sources will be used to collect the necessary information, including point-of-sale (POS) systems and other surveys. Part of this step is also lining up the metrics with the data sources to confirm that the metrics can be calculated.
Create a data dictionary: Develop a data dictionary that outlines the meaning and format of each data element, as well as any transformations or cleaning that may be required. This should be done for each data stream that is being used.
Design the user interface: Create a wireframe or mockup of the dashboard's user interface, including the layout, color scheme, and data visualizations that will be used. It's important to get input from end-users as soon as possible since this is how they will interact with the tool.
The dashboard works by providing tabs in an Excel workbook for every day of the month. The restaurant only needs to copy and paste two reports from the POS that are sent out in the morning. The Excel document then uses formulas and pivot tables to collect and populate the dashboard, which is housed in another Excel file. The two Excel files are connected using Power Query.
Originally, I wanted to use a tool like AirTable to modernize this process by having a webpage where the reports could be submitted or an API connection. However, the restaurant team didn't want to learn any new technology. Since I was also not a permanent member of the team, I wanted to find a solution that could be repaired or evolved after I was done. Thus Excel became the default tool.
One of the primary data sources for the Daily Restaurant Dashboard is the point-of-sale (POS) system. This system records transactions, menu items ordered, and other relevant data that can be used to analyze restaurant performance. However, understanding the data outputs from a POS can be incredibly challenging for a restaurant management team. Often, the format is not ideal, and the data needed is scattered across several different reports. Cleaning the data involves removing duplicates, fixing typos, filling in generic placeholders (such as "Specials" or "Customizations"), and ensuring that the data is as complete and accurate as possible. These tasks can be a significant barrier to entry for teams looking to use data.
For this project , there is room for the team to input information about the specials, Social Media Standings, and other similar information on top of each day's tab
By implementing the Dashboard, the restaurant owners and managers gained a comprehensive overview of the business’ performance in an easy-to-use tool. The dashboard provides valuable insights into key metrics such as sales, menu items ordered, and customer feedback, enabling teams to make data-driven decisions that can help their business thrive. While data collection, cleaning, and analysis can be challenging, the benefits of using data to inform business decisions are well worth the effort.