Institute of Emerging Careers

Capstone Project 1

​

• Topic: Restaurant Sales Analysis
• Course: Data Analytics
• Group: D
• Supervised By: Sir Muhammad Hamza

Restaurants , Members & Orders Dataset�

“Torture the data and it will confess to anything”

Objectives

• Query Writing for the solution of Required Data
• Visual Representation of Data
• RFM Analysis
• Recommendation to Problems

​

​

Restaurants , Members & Orders Dataset

The Dataset Includes:

• Restaurants’ Category information

​

• Customers information

​

• Order Details: Date, time, status, etc

​

• Location, Sales

​

• Finance information

Restaurants , Members & Orders Dataset

• Major Tables: City, Members, Total Monthly Members, Orders, Orders detail, Restaurants, Restaurants types Meals, Meals Types, Serve types

SCHEMA

What Does It show?

Restaurants database helps:

​

• To schedule the most efficient shifts,
• Using data will ultimately lead to an increase in revenue
• Storing, maintaining and accessing any sort of data
• Better decision-making
• To ensure all your staff are up to date on training

�

​

Cont.…

• Make us able to know the most demanded meals items
• To recognize the busiest hours
• Discover which City and period of the day the members do the most orders
• The ratio of meal types in restaurants in each city
• Cities have the most vegan meals

​

Exploratory Data Analysis

• Males and females have spent same number of expenses� �

• However, orders made by Males is more than Females.�

• Both genders made same expense but their order number is different which indicate female order more expensive meals

TAKE AWAY

• Herzelia earns highest amount of revenue from it’s food chain, followed by Ramat Hasharon and Ramat Gan

​

• Restaurants with less revenue should do more promotions like they have to introduce new categories of meals and food to attract customers. They can launch special offers.

RECOMMENDATION

• Each city has highest demand in cheese and vegan whereas beef and chicken are less consuming item as compared to other items

​

​

• It is observed that the meal type: Beef is least ordered in every city. Two reasons might be because either beef dishes take longer time to serve as it requires more time to be cooked completely or because meat doesn’t properly gets cooked which make customer experience poor. To resolve this issue restaurants should make sure that their meet is properly tender and they should keep semi-cooked meet so that it takes less time to serve the order.

​

RECOMMENDATION

• As the Expense category increases, number of customers decreases. Largest number of customers lies in the $500 category.

​

​

• Restaurants should do more promotions in order to attract high-paying customers to increase. They can launch some Elite products and market it to upper class.

​

RECOMMENDATION

• Fast-food and Asian are most in-demand restaurant types among customers while homemade is least preferred.

• Restaurants should promote home made items more. They can target office employees who prefer home made food for lunch/dinner daily in offices.

​

RECOMMENDATION

• Each of the restaurant has its different revenue based on there sales. Total revenue ranges from approximately $214k to $390k across the period of 7 months.

• Top 10 restaurants which generates higher revenue compared to other restaurants ranges from approximately $327k to $393k.

​

• Restaurants earning less revenue should carry out a customer feedback survey in order to know the reasons why they are less preferred so that they may improve that area. For e.g. if people don’t like their food taste then they must hire some good chefs to offer tasty food or if people feel they are more expensive then they should lower their prices or discount offers.

​

RECOMMENDATION

• Monthly ordered hot and cold meals

• There is a fluctuating pattern for orders made in each month where January and March are top months having highest no. of orders.

​

Recommendations

• A downfall is seen in the no. of orders placed in the months of January and April. Hence, we recommend that restaurants should roll out special offers and deals for Valentine’s week and April Fools’ week for Feb and April respectively so that more people may get attracted to such offers.

​

RECOMMENDATION

• The ratio of successful vs unsuccessful orders is approximately 1:5 which means after every 5 successful orders one order gets cancelled.

​

​

• Restaurants should gather evidence/data to know reason behind cancellation of orders and work to improve that area. For e.g. if it is due to waiters’ ill behavior then there is a dire need for employees training to be arranged.

​

RECOMMENDATION

• The Top 3 Busiest hours are 12 noon, 9pm and 10pm which are the peak hours for lunch and dinner, respectively.

​

• Restaurants should make sure that greater number of waiters are available during the top 3 busy hours.
• On the other hand, they can promote lunch offers/deals during late-afternoon hours to boost the number of orders.

​

RECOMMENDATION

• Recency: How recent a customer has ordered for the restaurant
• Frequency: Total number of orders a customer has placed
• Monetary: Total amount of expense made by a customer in any restaurant

RFM analysis is used categorize customers by scoring them in three categories:

RFM ANALYSIS

Calculating R Score

RFM Analysis

RFM ANALYSIS

Calculating F Score

RFM Analysis

RFM ANALYSIS

Calculating M Score

RFM Analysis

RFM ANALYSIS

Calculating RFM Score

​

​

RFM Score (ID=47) = (R score*100) + (F score*10) + (M score)

=(5*100)+(1*10)+(1)

= 511

​

RFM Score (ID=101) = (R score*100) + (F score*10) + (M score)

= (5*100)+(1*10)+(2)

=512

​

RFM Score (ID=144) = (R score*100) + (F score*10) + (M score)

= (5*100)+(5*10)+(1)

=551

​

​

RFM ANALYSIS

RFM ANALYSIS

• ‘Potential Loyalist’ can help business grow. It is recommended to promote new products, get their feedback on the products, and use them to get referrals.

​

• “New Customers” need nurturing and on boarding with attractive discounts.

​

• Previously “Loyal Customers” may have gone to a competitor and should be reactivated through a marketing campaign.

​

• Customers who purchase low-value products regularly are prime candidates to be moved up the value chain with a upsell offers.

​

• It is recommended to calculate, recalculate, and re-share these insights with Restaurant stakeholders at least once a month.

​

​

​

​

RECOMMENDATIONS BASED ON

RFM ANALYSIS

Tableau Dashboard