Restaurant Sales Analysis Report using Power BI
The objective of this analysis is to empower restaurant owners and aspiring entrepreneurs in the food industry by providing valuable insights into restaurant performance, identifying areas for improvement, and guiding strategic decisions for expansion and profitability. Through data-driven analysis, this project aims to uncover actionable insights that drive business growth and enhance customer satisfaction.
Approach:
The approach involved a comprehensive analysis of restaurant data to answer key questions and derive meaningful insights. Leveraging Power BI’s capabilities, the project focused on data cleaning, transformation, exploratory analysis, and visualization to facilitate informed decision-making for restaurant businesses.
Process Involved:
Questions to answer through this analysis :-
1. What are the top selling cuisines ?
2. Do the bestselling cuisines also provide the highest levels of sales ?
3. What categories do the top selling cuisines fall under?
4. Who are the top customers?
5. Which Zone has the busiest traffic?
6. How many restaurants are in these zones?
7. When do orders seem to be the highest?
8. Which cuisine sells most for this time?
Data Collection :The data was downloaded from Kaggle, containing two tables — Restaurant (consisting 21 rows and 5 columns of data on the restaurants) and Orders (with 500 rows and 10 columns). The various columns of the datasets are outlined below:
From the table above, we can see that both tables have a shared column “Restaurant ID” , which is the key in both tables. The Orders’ table is the fact table which contains a primary and foreign key, Order ID and Restaurant ID respectively. On the other hand, the Restaurants’ table is the dimension table with Restaurant ID as primary key.
Primary key is a column in a table that uniquely identifies the rows in that table, while foreign keys are columns in a table that refers to the primary keys in another table, therefore linking two or more tables in a relational database. An advantage of Power BI is that it is able to detect these keys and create a connection in the data model.
Data Cleaning and Transformation
The both datasets were loaded to power query in Power BI for cleaning. Data cleaning is an important aspect in data analysis process as it ensures accuracy and credibility of the insights generated from the data.
Under this step, I took into consideration of the following: data types, duplicates, null values, incorrect spellings.
Using the data preview feature on Power Query, I was able to identify that there were no null values and errors for each column in the Orders’ table. Also, I checked for duplicates using column distribution such that under Order ID, I had an equal amount of distinct and unique data.
Next, I split ‘Order Date’ column by position to extract the actual time and dates.
I renamed the new extracted columns to Order Date and Time of Day. Thereafter, I changed the data types to their correct format.
In order to extract the actual period of day instead of just hours, I established a conditional column into which I fed a set of conditions, and this new format of “Morning, Afternoon, Night” was produced.
Having three distinct entries of “Debit card, Credit card, and Cash on delivery” as payment mode, I created a conditional column which replaced entries of Debit and Credit as “Card” otherwise “Cash” and renamed the column name to Payment Mode.
At the end of this process, the Orders table contain same 500 rows and 13 columns and Restaurants remain the same. Now the data is ready for exploratory analysis.
Exploratory Data Analysis and Visualization
In this stage, I answered the questions I raised earlier, in the data requirement gathering process.
Firstly, I displayed a quick summary/ preview of the data which will reveals some Key Performance Indicators (KPIs) .
20 different restaurants received $299k from sales, from 500 orders placed by 21 different consumers.
The doughnut chart above shows that 66.2% of customers paid with card. This is most likely to happen, especially in a nation where electronic payments is a very efficient and widely used mode of payments.
The column chart shows that there is more traffic in the afternoon than any other time of day. This maybe as a result of lunch breaks of workers and students, and hangouts in the evenings.
From the chart above, Srini has the highest orders, followed by Revandh and Rifa.
There are 4 zones in total and Zone D has most traffic. This can be attributed to the fact they have highest number of restaurants unlike other zones.
North Indian, Chinese, French, South Indian and African are bestselling cuisines. Also, it is likely that North Indian cuisine will have maximum average customer-food rating.
The doughnut chart shows that 13 out of 20 restaurants are under Ordinary category.
From the chart above, we can see the top selling restaurants and their various categories. Veer generates highest which is an Ordinary restaurant, followed by The Cave Hotel and Anand Restaurant. I also want to note that, despite the fact that top selling restaurant (Veer) is under Ordinary category, 3 restaurants of Pro category are in top 5 selling restaurants.
Insights
1. Most of the orders are made in the afternoon, followed by night then morning.
2. Srini, Revandh, Rifa , Ram and Veer are top 5 customers in these restaurants with Srini leading by 168 orders.
3. Zone D seems to be the dominant zone with a total of 9 restaurants generating 42.85% sales. Another good zone might be Zone B with 5 restaurants.
4. 66.2% of the orders were paid with cards (credit/debit) while cash had 33.8%
5. The top 3 selling cuisines are North Indian, Chinese, and French, with North Indian accounting for $46.7k in sales.
Conclusion / Recommendations
Based on my analysis, I recommended the following:
1. Restaurants can implement electronic mode of payment since 66% of orders are made by cards.
2. Since the majority of orders are received in the afternoon, additional employees ought to be transferred to the afternoon shift to speed up operations and decrease delivery times for consumers. This will in turn raise these restaurants’ ratings for customer service delivery.
3. Zone D has more customers which translates to more sales. I’d recommend to open a restaurant in Zone D since it has the largest market. From another perspective, more restaurants can be opened in the other zones to attract more customers.
4. Due to the fact that Zone D is where the majority of North Indian restaurants are located, new businesses in other zones may specialize in North Indian food.
5. The afternoon has the highest volume of orders, with Chinese orders accruing the majority of them. Chinese and North Indian cuisines are specialties in restaurants that guarantee top sales. In that case, restaurants can hire more cooks that specialize in these cuisines.
Dashboard-
https://www.novypro.com/project/restaurant-sales-analysis-report-using-power-bi-power-bi
LinkedIn-
https://www.linkedin.com/in/nikk996/
