🍕 Pizza Sales Analysis — SQL + Power BI

An end-to-end data analytics project that turns a year of raw pizza order data into an interactive Power BI dashboard, using PostgreSQL for data storage and querying.

---

Overview

This project analyzes one year (Jan–Dec 2015) of transactional sales data from a pizza restaurant chain. The goal was to build a complete analytics pipeline — from raw data to business-ready insights — covering:

• Loading and structuring raw sales data in a PostgreSQL database
• Writing SQL queries to compute key business metrics (KPIs, trends, top/bottom performers)
• Connecting Power BI directly to PostgreSQL for live data refresh
• Designing a two-page interactive dashboard for sales performance and product-level insights

Business questions answered:

• What are our overall sales, orders, and revenue trends?
• Which pizzas are best-sellers and which are underperforming?
• When are our busiest days and months?
• How do sales break down by pizza category and size?

---

Dataset

Source file	pizza_sales.csv
Records	~48,600 order line items
Time period	January 2015 – December 2015
Columns	pizza_id, order_id, pizza_name_id, quantity, order_date, order_time, unit_price, total_price, pizza_size, pizza_category, pizza_ingredients, pizza_name

Each row represents one pizza within a customer order, allowing analysis at both the order level (via order_id) and the item level.

---

Tools & Technologies

• PostgreSQL — relational database for storing and querying the dataset
• SQL — aggregation, filtering, and ranking queries for KPIs and trends
• Power BI Desktop — data modeling, DAX measures, and dashboard visualization
• Power BI–PostgreSQL Connector — live database connection for report refresh

---

Steps

1. Set up the database Created a pizza_sales table in PostgreSQL and imported pizza_sales.csv using COPY / pgAdmin's import tool.

2. Clean and validate the data Checked data types (dates, numeric fields), confirmed no duplicate pizza_id values, and verified total_price = quantity × unit_price.

3. Write analytical SQL queries Queried the database directly in PostgreSQL to calculate KPIs, trends, and rankings (see SQL Highlights below). Full query set is in PIZZA_SALES_SQL_QUERIES.docx.

4. Connect Power BI to PostgreSQL In Power BI Desktop: Get Data → PostgreSQL database → entered server/database credentials → imported the pizza_sales table directly, keeping the dashboard connected to live data rather than a static file.

5. Build the data model & measures Created DAX measures for Total Revenue, Average Order Value, Total Pizzas Sold, Total Orders, and Average Pizzas per Order.

6. Design the dashboard Built a two-page report — a Home/Sales Performance overview and a Best/Worst Sellers deep-dive — with slicers for pizza category and date range.

---

SQL Highlights

A few examples from the full query set (PostgreSQL syntax):

-- Total Revenue
SELECT SUM(total_price) AS total_revenue FROM pizza_sales;

-- Average Order Value
SELECT SUM(total_price) / COUNT(DISTINCT order_id) AS avg_order_value FROM pizza_sales;

-- Daily Trend for Total Orders
SELECT TO_CHAR(order_date, 'Day') AS order_day, COUNT(DISTINCT order_id) AS total_orders
FROM pizza_sales
GROUP BY TO_CHAR(order_date, 'Day');

-- Top 5 Pizzas by Revenue
SELECT pizza_name, SUM(total_price) AS total_revenue
FROM pizza_sales
GROUP BY pizza_name
ORDER BY total_revenue DESC
LIMIT 5;

The complete set of KPI, trend, and top/bottom-performer queries — including category and size breakdowns — is documented in PIZZA_SALES_SQL_QUERIES.docx.

---

Dashboard

Page 1 — Sales Performance Overview

Tracks overall business health: total revenue, orders, pizzas sold, daily/monthly order trends, and sales share by category and size.

Page 2 — Best & Worst Sellers

Ranks pizzas by revenue, quantity, and order count, surfacing top performers and products that may need a menu review.

Both pages include a pizza category filter and a date range slicer for interactive exploration.

(See Home_page_pizza_dashboard.png and Best_worst_sellers_Dashboard.png for dashboard previews, and PIIZA_SALES_DASHBOARD.pbix for the full interactive file.)

---

Results & Key Insights

• 817.86K total revenue generated from 21K orders (50K pizzas sold), averaging 2.32 pizzas per order and a $38.31 average order value.
• The Thai Chicken Pizza drives the most revenue, while The Classic Deluxe Pizza leads in both quantity sold and total orders.
• The Brie Carre Pizza is the lowest performer across revenue, quantity, and orders — a candidate for menu re-evaluation.
• Orders peak on Fridays and Saturdays, and July and January are the strongest months for order volume.
• The Classic category and Large size pizzas contribute the highest share of total sales.

---

How to Run

1. Clone this repository and ensure PostgreSQL is installed and running.
2. Create the database and table, then load the dataset:

   CREATE TABLE pizza_sales (
       pizza_id INT, order_id INT, pizza_name_id VARCHAR(50), quantity INT,
       order_date DATE, order_time TIME, unit_price NUMERIC, total_price NUMERIC,
       pizza_size VARCHAR(5), pizza_category VARCHAR(20),
       pizza_ingredients TEXT, pizza_name VARCHAR(100)
   );

   Import pizza_sales.csv into this table via \copy (psql) or pgAdmin's Import/Export tool.
3. Run the analysis queries from PIZZA_SALES_SQL_QUERIES.docx to validate the KPIs.
4. Open PIIZA_SALES_DASHBOARD.pbix in Power BI Desktop.
5. Go to Home → Transform Data → Data Source Settings, update the PostgreSQL server/database credentials to match your local setup, then Refresh.
6. Explore the dashboard using the category filter and date slicer.

---

Repository Contents

File	Description
pizza_sales.csv	Raw dataset
PIZZA_SALES_SQL_QUERIES.docx	Full SQL query set with outputs
PIIZA_SALES_DASHBOARD.pbix	Power BI dashboard file
Home_page_pizza_dashboard.png	Dashboard preview — Sales Performance page
Best_worst_sellers_Dashboard.png	Dashboard preview — Best/Worst Sellers page

---

Contact

Ashmit Srivastava 📧 ashmitsrivastava0208@gmail.com 💻 GitHub · LinkedIn