Pizza Store Sales Dashboard

Tableau Public Dashboard Presentation Video

Github Repository

Project Aim:

• Use Python Pandas to read in and explore sales data for a fictional pizza store and determine if any data cleaning or transformation is required
• Build a monthly sales and KPI dashboard
• Used Python to analyze the data for confirming the accuracy of the calculations on the dashboard
• Find insights in sales data for December 2015 (most recent month in the dataset) and recommend business decisions based on the data

Resources

• Data for year 2015 stored in four separate CSV files

  • orders.csv
  • order_details.csv
  • pizzas.csv
  • pizza_types.csv

• Tools Used:

• Tableau Public - Paramters - Calculated Fields - Conditional Formatting

  • Python 3.9.7, Pandas, Jupyter Notebook
    • Groupby
    • Time Series Resampling
    • Merging Dataframes
    • Checking for nulls and duplicates
  • Powerpoint - Dashboard wire frame background

Questions to Answer:

• Find monthly totals for KPIs with percentage change from previous month
  • Total Sales Dollars
  • Total Units Sold
  • Total Orders
• At what times and days are sales the strongest and weakest?
• Which pizza sizes are the strongest and weakest?
• Which pizza types are the strongest and weakest?
• What opportunities are there to capitalize on strong sellers and cut costs on weak sellers?

Findings:

• Total dollars sold, total orders, and total units sold are all down in December from November
• Total dollars is down a slightly more than number of orders and total units
  • Fewer people are ordering multiple pizzas on an order
• Total dollars are stronger at lunch time hours than dinner hours
• Total dollars are stronger on weekdays than weekends
• Avg units sold per order is highest during lunch time hours
• Very few orders are being placed before 11:00 am and after 10:00 pm
• Very few XL or XXL pizzas are being sold
• Items such as Brie Carre pizza sell far fewer units than other types in the same category

Recommendations

• Offer buy-one-get-one 25% off in the evening hours to encourage more orders and increase pizzas per order, thus improving total dollars in evening
• Reduce business hours to 11:00 am to 10:00 pm to eliminate labor costs during low-sales times
• Eliminate XL and XXL sizes from the menu, cutting down on inventory costs and inventory spoilage
• Eliminate Brie Carre and other pizza types from the menu, cutting costs for specialty ingredients

Troubleshooting

The coloring of the heat map initially did not update when the month parameter was changed, while the tooltips did update. Upon checking the dashboard against the data analysis done in python, I determined that the tooltips were correctly showing the sales data for the chosen month, while the coloring was summarizing the entire year. I changed fields for the heat map to use the Chosen Month Sales calculated field which fixed the problem.

Techniques Used

Tableau Techniques:

• Bar Charts, KPI Cards, Heatmap
• Used Parameters to allow users to select the month of sales to view for the whole dashboard and which category to view for one chart
• Used Calculated Fields to display total dollars, units, and orders for the user's chosen month, to calculate month-over-month changes in the KPIs, and create up and down arrows for KPI cards
• Conditional Formatting to color up arrows green and down arrows red

Python Techniques:

• Read in csv files with pandas
• Checked for null values and duplicates in columns where values should be unique
• Checked number of unique values in certain columns to validate foreign key columns
• Checked data types and converted strings to datetime object for time series analysis
• Merged dataframes
• Groupby and resample to aggregate data