End-to-End Data Analytics Project: From SQL Query to Interactive Excel & Tableau Sales Dashboard

---

In this project, I set out to transform raw sales data into a dynamic, interactive dashboard that management could use to track performance, uncover trends, and make faster decisions.

I used three key tools:

• SQL for querying and aggregating raw data

• Excel for pivot analysis and an executive dashboard

• Tableau for advanced interactive visualizations

Extracting and Aggregating Data with SQL

I started with a structured sales database containing multiple related tables:

• Customers

• Orders

• Order Items

• Products

• Categories

• Stores

• Staff

My goal was to combine these datasets into a single, analysis-ready table that included:

• Order details

• Customer demographics

• Product category information

• Store and sales representative names

• Key metrics such as total units sold and revenue

Here’s the SQL query I wrote:

  SELECT
  ord.order_id,
  CONCAT(cus.first_name, ' ', cus.last_name) AS customer_name,
  cus.city,
  cus.state,
  ord.order_date,
  SUM(ite.quantity) AS total_units,
  SUM(ite.quantity * ite.list_price) AS revenue,
  pro.product_name,
  cat.category_name,
  sto.store_name,
  CONCAT(sta.first_name, ' ', sta.last_name) AS sales_rep
FROM sales.orders AS ord
JOIN sales.customers AS cus
  ON ord.customer_id = cus.customer_id
JOIN sales.order_items AS ite
  ON ord.order_id = ite.order_id
JOIN production.products AS pro
  ON ite.product_id = pro.product_id
JOIN production.categories AS cat
  ON pro.category_id = cat.category_id
JOIN sales.stores AS sto
  ON ord.store_id = sto.store_id
JOIN sales.staffs AS sta
  ON ord.staff_id = sta.staff_id
GROUP BY
  ord.order_id,
  customer_name,
  cus.city,
  cus.state,
  ord.order_date,
  pro.product_name,
  cat.category_name,
  sto.store_name,
  sales_rep;

This query did the heavy lifting, joining multiple tables, aggregating sales at the order level, and calculating revenue in one pass.

Building the First Dashboard in Excel

Once I had my clean, aggregated dataset from SQL, I exported it into Excel for further exploration.

I structured my workbook into three worksheets:

1. Clean Data Table: The raw query output for reference.

2. Pivot Analysis: Pivot tables for aggregating sales by product, category, location, and sales rep.

3. Executive Dashboard: A management-ready view with slicers for interactivity.

Key Excel Features Used:

• Pivot tables for flexible aggregation

• Slicers to filter by date, category, store, or sales rep

• Conditional formatting to highlight top performers

The result was a clean, interactive Excel dashboard that could be used in management meetings without requiring SQL knowledge.

Creating an Interactive Tableau Dashboard

While Excel was great for initial analysis, I wanted to take interactivity and visual storytelling to the next level. So I connected the SQL query output directly to Tableau.

Why Tableau?

• Faster filtering and real-time responsiveness

• More advanced chart types (heatmaps, geographic maps, trend lines)

• Ability to create drill-down paths from summary KPIs to detailed transaction data

I kept the design minimal yet impactful — focusing on KPIs like:

• Total Revenue

• Units Sold

• Top 5 Products by Sales

• Sales by Region

• Monthly Sales Trends

Impact and Takeaways

By the end of this project, I had:

• Streamlined data extraction from multiple tables into a single dataset with SQL

• Built an Excel dashboard for quick, offline management review

• Developed a Tableau dashboard for deeper, interactive analysis

---

Data Source: Bike Store Relational Database | SQL

Tableau Public Dashboard: Click here