Introduction

I wanted to practice my recently acquired skills in data analysis and chose the Maven Analytics Pizza Challenge to start off with. In this project, I acted as a data analyst for a pizza business and sought to understand how the business was doing in terms of pizza sales and identified insights that could help boost sales during low months and days.

Understand the Problem to be Solved

I sought to address the following questions:

1. How much money did we make this year? Can we identify any seasonality in the sales?
2. How many pizzas are typically in an order? Do we have any bestsellers?
3. Are there any peak hours?
4. Any promotions we could leverage?

Exploration

The data

This dataset consisted of 4 tables in CSV format:

• The Order table consisted of unique orders placed together with the date and time.
• The Order Details table consisted of all the orders that were placed, the order id, the pizzas ordered with their sizes, and the number of pizzas ordered.
• The Pizzas table had fields on pizza ID, pizza types, sizes, and pricing information.
• The Pizza Types table consisted of the pizza types, names, and categories of the different pizzas and ingredients used.

Gather and Organize the Data in SQL

Creating tables

CREATE TABLE orders(
    order_id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    time TIME NOT NULL
)

`CREATE TABLE pizza_types(
    pizza_types_id SERIAL PRIMARY KEY,
    name VARCHAR(500) UNIQUE NOT NULL,
    category VARCHAR(500) NOT NULL
)

`CREATE TABLE pizzas(
    pizza_id VARCHAR(5000) UNIQUE NOT NULL,
    pizza_type_id VARCHAR(8000) REFERENCES pizza_types(pizza_type_id),
    size VARCHAR(5) NOT NULL,
    price NUMERIC(5,2) NOT NULL
)

ALTER TABLE pizzas
    ADD CONSTRAINT pizzas_pk PRIMARY KEY(pizza_id);

CREATE TABLE order_details(
    order_details_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    pizza_id VARCHAR(5000) REFERENCES pizzas(pizza_id),
    quantity INTEGER NOT NULL
)

I then uploaded the relevant data from the CSV files provided into these tables.

Joining the tables with relevant data

SELECT 
    typ.category,
    typ.pizza_type_id,
    piz.pizza_id,
    det.order_id,
    ord.date,
    TO_CHAR(ord.date, 'Day') AS "day",
    ord.time,
    SUM(det.quantity) AS total_units,
    SUM(det.quantity * piz.price) AS revenue
FROM pizza_types AS typ
JOIN pizzas AS piz
ON typ.pizza_type_id = piz.pizza_type_id
JOIN order_details AS det
ON piz.pizza_id = det.pizza_id
JOIN orders AS ord
ON ord.order_id = det.order_id
GROUP BY
    typ.category,
    typ.pizza_type_id,
    piz.pizza_id,
    det.order_id,
    ord.date,
    ord.time
ORDER BY order_id

I then downloaded the table as a CSV file.

In MS Excel I used the filter tool to check for any null values or spelling errors in the columns.

Analyzing Data in Tableau

Created a calculated field to extract hour from date time:

DATEPART(‘Hour’, [TIME]) 

I focused on creating visualizations for the following:

• Banner containing the total sales, total pizzas sold, total orders, and average order value.
• Peak times and days based on orders
• Revenue per month and per category
• Top-selling pizzas

Insights and Recommendations

Insights

• The Large pizza was the best-selling among the different sizes in every category. Chicken & Veggie Large were the most preferred.
• It was interesting to note that the Big Meat Small had the highest sales compared to others in the small & medium categories.
• Peak hours were between 12 -1 pm and 5 pm - 6 pm while the busiest days were Thursdays, Fridays, and Saturdays.

Recomendations

• Thai Chicken( L) and Four Cheese (L) were consistently best-selling pizzas even in low seasons. We could have promotions for these two during low months to boost sales.
• We could do promotions for the top 5 pizzas during peak times on Sundays & Mondays to boost sales.