Pizza Place Sales Insight

I worked on a sales analysis project with data collected by a third-party with the goal of coming up with visualization of specific KPIs and recommendations. You may interact with the spreadsheet dashboard and dataset by clicking here

The data analysis cycle

1. Data Objectives and Understanding: As stated above, the dataset was acquired from a third party and the objectives of the data analysis was to use the data to obtain and answer the following questions about the business or KPIs of interest;

• How many customers do we have each day? Are there any peak hours?
• How many pizzas are typically in an order? Do we have any bestsellers?
• How much money did we make this year? Can we identify any seasonality in the sales?
• Are there any pizzas we should take of the menu?

The dataset contains data on Order Details, Orders, Pizza types, Pizzas and a data_dictionary for description of the fields in each table of the spreadsheet.

2. Data Cleaning and Analysis: The dataset was generally checked for errors. Cells with extra spaces were trimmed and removal of duplicates if exist. The various dataset was merged as sheets into a single Excel document for ease of access.

• New columns were created in the “Order Details” table for name and size which were inputted by looking up the data in the “Pizzas” table with the VLOOKUP function;name(=VLOOKUP(C2,Pizzas!A$1:E$97,5,0) and size(=VLOOKUP(C2,Pizzas!$A$1:$E$97,3,0).
• The price was calculated by looking up the price per unit in “Pizzas” table and multiplying it by the corresponding quantity bought per order(=(VLOOKUP(C2,Pizzas!$A$2:$D$97,4,0)*E2)
• New columns were also created in the orders table for day (=TEXT(B2,”DDDD”)), month (=TEXT(B2,”DDDD”)), Time(Hours) (=TEXT(C2,”hh AM/PM”)).

Data Analysis: The data was thoroughly cleaned and analyzed with Excel because the company also uses Excel for its data storage and management and hence the tasked was completed in Excel. Although Excel is a powerful tool for data cleaning and analysis, there are other tools such as SQL, Tableau and Power BI that can also be used for data cleaning and analysis.

From the above dashboard, it can be seen that the highest customers for the year are on Tuesday and Sunday have the lowest. The peak hours is also 12PM. This analysis can be further detailed for specific months with the aid of the slicers.

It can also be seen that there’s an average of about 10–15 pizza per order and the The Mexicana Pizza was the best selling Pizza for the year. This could also be filtered by size for more details.

A total of $817,860.05 sales was made for the year and sales was consistently high between March and August.

Recommendations

With this data and the dashboard, Pizza Place can investigate the reason for the low sales of The Mediterranean Pizza as well as The Spinach Pesto Pizza and if any pizza is to be taken off the menu, it should be those with the lowest sales which are The Mediterranean Pizza and The Spinach Pesto Pizza.

Also, Pizza Place might need more workers specifically on Thursday, Friday and Saturday to handle the customers influx especially for the The Mexicana Pizza which has the highest sales for the year.

I will appreciate your thoughts and suggestions on this analysis.