Building a Data Analyst Portfolio Project with SQL and Excel

In this blog post, we will explore how to create a complete data analyst portfolio project using two powerful tools: MS SQL Server and Excel. This project will involve building an interactive dashboard that visualizes pizza sales data, validating the data through SQL queries, and generating key performance indicators (KPIs) and charts based on the data provided by a client.

Project Overview

The final output of our project will be an interactive dashboard created in Excel. The dashboard will include various charts and KPIs that provide insights into pizza sales. The client has provided us with specific requirements, including different problem statements that we need to address using SQL queries and Excel functionalities.

Tools Required

1. MS SQL Server: This will serve as our database to store and query the pizza sales data.
2. Excel: We will use Excel to create the dashboard and visualize the data.

Step 1: Understanding the Data

The dataset we will be working with is a CSV file containing pizza sales data for the year 2015. The data includes various fields such as:

• Pizza ID (Primary Key)
• Order ID
• Quantity
• Order Date and Time
• Unit Price
• Total Price
• Pizza Size
• Pizza Category

The dataset consists of approximately 48,000 rows, which we will analyze to derive insights.

Step 2: Setting Up the SQL Database

Creating the Database

1. Open SQL Server Management Studio.
2. Create a new database named PizzaDB.
3. Import the CSV file into the newly created database.

Importing Data

To import the data, right-click on the database, select "Tasks," and then choose "Import Flat File." Follow the prompts to select the CSV file and ensure that the data types are correctly assigned.

Validating Data with SQL Queries

Once the data is imported, we will write SQL queries to validate the data and generate the required KPIs. Here are some of the key queries we will execute:

1. Total Revenue: Calculate the total revenue from pizza sales.

   SELECT SUM(total_price) AS total_revenue FROM pizza_sales;
   

2. Average Order Value: Calculate the average order value.

   SELECT SUM(total_price) / COUNT(DISTINCT order_id) AS average_order_value FROM pizza_sales;
   

3. Total Pizzas Sold: Calculate the total number of pizzas sold.

   SELECT SUM(quantity) AS total_pizzas_sold FROM pizza_sales;
   

4. Total Orders: Count the total number of unique orders.

   SELECT COUNT(DISTINCT order_id) AS total_orders FROM pizza_sales;
   

5. Average Pizzas per Order: Calculate the average number of pizzas ordered.

   SELECT SUM(quantity) / COUNT(DISTINCT order_id) AS average_pizzas_per_order FROM pizza_sales;
   

Step 3: Creating the Dashboard in Excel

Connecting Excel to SQL Server

To connect Excel to the SQL Server database:

1. Open Excel and go to the "Data" tab.
2. Select "Get Data" > "From Database" > "From SQL Server Database."
3. Enter the server name and database name to establish the connection.

Data Cleaning and Processing

Once the data is imported into Excel, we will perform data cleaning and processing:

• Remove any duplicates or irrelevant data.
• Use formulas to manipulate and analyze the data as needed.

Creating Charts and Visualizations

We will create various charts to visualize the data, including:

• Daily trend for total orders (Bar Chart)
• Hourly trend for orders (Line Chart)
• Percentage of sales by pizza category (Pie Chart)
• Percentage of sales by pizza size (Bar Chart)
• Top five best-selling pizzas (Bar Chart)
• Bottom five worst-selling pizzas (Bar Chart)

Designing the Dashboard

Finally, we will design the dashboard in Excel, ensuring it is interactive and user-friendly. We will include slicers for filtering data by order date and other relevant parameters.

Conclusion

In this project, we have successfully built a data analyst portfolio project using MS SQL Server and Excel. We validated our data through SQL queries and created an interactive dashboard that provides valuable insights into pizza sales. This project not only showcases our technical skills but also demonstrates our ability to derive actionable insights from data, making it a valuable addition to our portfolio.