Pizza Paradiso's Sizzlin' Sales: A 2015 Sales Analysis Report

This was a project to show the sales of the restaurant over the year 2015. And the aim was to provide information on monthly revenue, show peak times and days and the volume of orders. The revenue by pizza, the revenue by size and category. As well as identifying the top selling pizza, pizza category and the worst selling pizza and category.

The report also showed observations and made recommendations based on the data for management to follow up.

Maven Analytics provided the data in their Data Playground. They provided the information in csv files. I created the data model and added in additional supporting tables and columns to support the analysis. I summarize the steps below.

Process

• After downloading the zip file, I extracted the csv files into a folder.
• Then I used the Power BI Query Editor to use the folder as a new data source.
• This created a query which I set not to be loaded into the Data model as the Power BI Desktop does not need to show this query.
• I then created a new blank query for each file and set the source to be the data source query I created. I did this to simplify amendments as only the folder in the data source query would need to be changed, otherwise in the event of a change of folder for the dataset, the query for each table would need to be updated.
• For the Orders table, I added a column called "Hour", which extracted the hour from the time column. I then added a column called "12 hour" which held the Hour from the Hour Column but converted it to a 12 hour time with am and pm added to the hour.
• Then changed the column data types to suit the data.
• Next, I filtered out null and blank values from the order details table. I merged this table with the pizzas table and kept the pizza type id column and the price. I did this to address a problem when trying to create visuals using the pizza type and orders tables. I then created a custom column that calculated the value of the order by multiplying the price by the quantity.
• For the pizzas table, I changed the data types to be the correct data type for that column as Power BI had set them to any data type. Then sorted the table by pizza id and pizza type id in ascending order. And then filtered out rows where the pizza id was a null value.
• The pizza type table needed me to promote the first line of the csv file to be the field names. I then sorted the table by pizza type id in ascending order. To simplify the name of the pizza for reporting, I then removed the words The and Pizza from the name. I did this by splitting the name column first into two columns by splitting it at the 4th character to remove "The " from the name. I then split the name column without the word "The " in from the 6th character from the end of the pizza name. this removed the word " pizza" from the pizza name. I then removed the Columns containing The and Pizza from the table and renamed the remaining name column back to name.
• I then created a separate calendar table to create a table containing a list of dates each day of 2015. This table used a function query that used code from my library of functions.
• Then I created a monthly revenue table that was the order details table merged with the orders table. I just kept the dates column from the orders table. I then merged this table with the calendar table and I kept the Month of year number and the month name columns from the calendar table. I then grouped the tale by the month of year column and summed the revenue for each month.
• I created a pizza name revenue table that merged the Pizza types table with the order details. I kept the quantity and value from the order details table. Then I grouped this table by pizza type id and summed up the revenue and quantity columns.
• I merged the pizzas and order details to create a pizza size revenue table. I removed the pizza id, pizza type id and price columns. And kept the value column from the order details table. I then grouped the rows by size and summed the value column to create a revenue column.
• Finally, I merged the orders and calendar tables to create the orders by hour table. I kept the day in week and day of week name columns from the calendar table. The table was then sorted by the day in week, day of week name, 12 hour, hour order id, date and time columns. I then grouped the table by the day in week, day of week name, 12 hour and hour columns and performed a count of the rows to create an orders column containing a count of the rows.
• I then loaded the data into the data model and then created the relationships between the tables in Power BI desktop.

Impact

The impact of the created report is that it is easy to see the busy and low periods so that management and update staff planning quicker. It is easier to see the top selling and bottom selling pizzas, so that management can update the store menu might remove poor selling pizzas. It is now easy to see the volume of sales for each month of the year, which will help in considering cash flow.