Retail Sales Analytics Dashboard

Build an end-to-end retail analytics pipeline with Python, SQL, and Power BI.

Introduction

30 Second Summary

Every retail business collects mountains of sales data. The companies that win are the ones with people who can turn that data into clear recommendations like "stop selling this product in that region" or "double down on marketing before the holiday spike."

In this project, you will build a complete data analytics portfolio piece by taking raw retail sales data through the full analyst pipeline: inspecting it in a spreadsheet, cleaning it with Python, querying it with SQL, and presenting it as a polished interactive dashboard with business recommendations.

What You'll Build

A complete data analyst portfolio project using the Superstore Sales Dataset, processed end-to-end from raw CSV through Google Sheets, Python, SQL, and Power BI.

By the end of this project, you'll have:

  • A cleaned and analysis-ready dataset processed through Google Sheets, Python, and SQL.
  • An interactive Power BI dashboard with KPIs, trend lines, regional performance visuals, and dynamic filters.
  • A documented set of business insights and recommendations ready to discuss in an interview.
  • Secret Mission: Add a calculated Profit Margin % column and create a profitability matrix showing which product sub-categories are losing money in which regions.

Are there any prerequisites?

You should have basic familiarity with spreadsheets (rows, columns, formulas) and Python syntax (variables, functions, loops). You do not need prior experience with SQL, Power BI, or data analysis workflows.

Inspect Data in Google Sheets

Your tools are installed and your dataset is ready. Now it's time to look at the actual data.

Before writing any code, data analysts inspect data manually to understand its structure and spot obvious problems. Google Sheets is perfect for this because you can scroll through rows, check column types, and create quick summaries without writing a single line of code.

In this step, get ready to:
  • Upload the CSV to Google Sheets and explore the data structure.
  • Create a pivot table summarizing Sales and Profit by Region and Category.
  • Document initial observations about data quality issues.
Upload the CSV and explore the data structure

The first thing any data analyst does with a new dataset is open it and look around. You need to understand what columns exist, how many rows you have, and whether anything looks wrong.

  • Open your browser and go to sheets.google.com.
  • Sign in to your Google account if you haven't already.
  • Click the blank spreadsheet option (the + icon) to create a new spreadsheet.
  • Click File in the top menu bar.
  • Click Import.
  • Click the Upload tab, then click Select a file from your device.
  • Select your Sample - Superstore.csv file from your project folder.
  • For Import location, select Replace spreadsheet.
  • For Separator type, leave it set to Detect automatically.
  • Click Import data.

Your spreadsheet now contains the full Superstore dataset. Rename the file to something descriptive like Superstore Analysis by clicking on the title in the top left corner.

What should I see?

You should see a spreadsheet filled with data starting from row 1. The first row contains column headers like Row ID, Order ID, Order Date, Ship Date, Ship Mode, Customer ID, Customer Name, Segment, Country, City, State, Postal Code, Region, Product ID, Category, Sub-Category, Product Name, Sales, Quantity, Discount, and Profit.

  • Now explore the data structure. Scroll right to see all columns and note their names.
  • Scroll down to check how many rows of data you have. Look at the row numbers on the left side.
  • Look at a few cells in the Postal Code column to spot any blanks or missing values.
  • Check the Order Date and Ship Date columns to see if the date formats are consistent.

What am I looking for?

You're building a mental map of the dataset. Key things to note: the dataset has approximately 9,994 rows of order data. Columns include a mix of text (Customer Name, City), numbers (Sales, Profit, Quantity), and dates (Order Date, Ship Date).

You might spot that some Postal Code cells are empty, and some date values may look inconsistent. These are the kinds of issues you'll clean with Python in the next step.

Create a pivot table summarizing Sales and Profit

A pivot table summarizes large datasets into digestible numbers. You'll create one to see total Sales and Profit broken down by Region and Category.

  • Click on any cell in your data (for example, cell A1).
  • Click Insert in the top menu bar.
  • Click Pivot table.
  • In the dialog that appears, make sure the data range covers your entire dataset. Leave the default selection.
  • Select New sheet for where to place the pivot table.
  • Click Create.

A new sheet tab opens with an empty pivot table and the Pivot table editor panel on the right.

  • In the Pivot table editor panel on the right, next to Rows, click Add and select Region.
  • Next to Rows again, click Add and select Category.
  • Next to Values, click Add and select Sales. Make sure Summarize by is set to SUM.
  • Next to Values again, click Add and select Profit. Confirm Summarize by is set to SUM.

What does the pivot table show?

Your pivot table now displays total Sales and Profit for each Region, broken down by Category (Furniture, Office Supplies, Technology). This gives you a high-level overview of where revenue and profit come from.

You can quickly spot patterns. For example, you might notice that one region generates high sales but low profit, which suggests a cost or discount issue worth investigating.

Rename this sheet tab to Pivot Table by double-clicking the tab name at the bottom of the screen.

Document initial observations in a notes sheet

Professional data analysts always document what they notice during initial inspection. This creates a record of data quality issues you need to address during cleaning.

  • Click the + icon at the bottom left of the screen to add a new sheet.
  • Double-click the new tab name and rename it to Notes.
  • In cell A1, type Data Quality Observations as a header.
  • In the cells below, document what you noticed during your exploration. Include observations like:
  • Missing Postal Code values in some rows (for example, rows with Burlington, Vermont).
  • Ship Date sometimes appears to precede Order Date, which could indicate data entry errors.
  • Possible duplicate rows (same Order ID, same product).
  • Total row count and column count.
  • Columns that appear to have consistent formatting versus those that don't.

Write for your future self

Your notes sheet serves two purposes. First, it reminds you what to fix when you write your Python cleaning code in the next step. Second, it shows interviewers that you follow a structured process and don't jump straight to analysis without checking data quality first.

Data not loading properly?

If your data looks garbled or all appears in one column, the separator type wasn't detected correctly. Go back to File > Import and try again, this time manually selecting Comma as the separator type.

If you see unusual characters in names or addresses, the file might have encoding issues. The Superstore CSV uses latin-1 encoding, which Google Sheets usually handles automatically.

Help me troubleshoot my Google Sheets import

You now have a solid understanding of your dataset's shape and quality. Next, you'll use Python to clean these issues programmatically and perform deeper analysis that spreadsheets can't handle efficiently.

Clean and Analyze with Python

In the previous step, you explored the Superstore dataset in Google Sheets and documented data quality issues like missing postal codes and potential duplicates. Now it's time to fix those issues programmatically and extract real business insights from the data.

Python with pandas gives you the power to clean thousands of rows in seconds and ask precise analytical questions. You'll write a single script that cleans the raw data, computes business metrics, and generates publication-ready charts for your portfolio.

In this step, get ready to:
  • Load and clean the Superstore dataset with pandas.
  • Perform exploratory data analysis to uncover top regions, categories, and segments.
  • Create matplotlib visualizations and save them as PNG files for your portfolio.
Load the dataset and inspect its structure

Before you can clean anything, you need to load the raw CSV into a pandas DataFrame and understand what you're working with. This first section prints out the shape, column names, data types, and missing values so you can confirm the issues you spotted in Google Sheets.

  • In Cursor, create a new file called analysis.py inside your project folder.
  • Add the following code to load the dataset and display basic info:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# ============================================================
# PHASE 1: DATA CLEANING
# ============================================================

# Load the raw dataset
df = pd.read_csv('Sample - Superstore.csv', encoding='latin-1')

# Display basic info
print(f"Shape: {df.shape}")
print(f"\nColumn names:\n{df.columns.tolist()}")
print(f"\nData types:\n{df.dtypes}")
print(f"\nMissing values:\n{df.isnull().sum()}")

What does this code do?

The encoding='latin-1' parameter handles special characters in the Superstore CSV that would cause a read error with the default UTF-8 encoding.

The four print statements give you a quick health check: how many rows and columns exist, what each column is called, what data type pandas assigned to each, and where values are missing.

  • Save analysis.py.
  • Open a terminal in Cursor and run the script:
python analysis.py
  • You should see the dataset's shape (around 9994 rows and 21 columns), a list of column names, data types for each column, and a count of missing values per column.

Seeing a FileNotFoundError?

Make sure your terminal is in the same folder as Sample - Superstore.csv. Run ls (macOS/Linux) or dir (Windows) to confirm the file is there. The filename must match exactly, including the spaces and dash.

help me fix a FileNotFoundError when loading my CSV with pandas

Clean the data

Raw datasets almost always have problems that would skew your analysis. You'll now remove duplicates, parse dates properly, standardize text columns, handle missing values, and add helper columns for time-based analysis.

  • In analysis.py, add the following cleaning code below the display section:
# Remove duplicate rows
duplicates = df.duplicated().sum()
print(f"\nDuplicate rows found: {duplicates}")
df = df.drop_duplicates()

# Convert date columns to datetime
df['Order Date'] = pd.to_datetime(df['Order Date'], format='mixed')
df['Ship Date'] = pd.to_datetime(df['Ship Date'], format='mixed')

# Standardize category names (strip whitespace, title case)
df['Category'] = df['Category'].str.strip().str.title()
df['Sub-Category'] = df['Sub-Category'].str.strip().str.title()
df['Segment'] = df['Segment'].str.strip().str.title()
df['Region'] = df['Region'].str.strip().str.title()

# Handle missing Postal Code values (fill with 0 for analysis)
df['Postal Code'] = df['Postal Code'].fillna(0).astype(int)

# Add helper columns for time-based analysis
df['Year'] = df['Order Date'].dt.year
df['Month'] = df['Order Date'].dt.month
df['Year-Month'] = df['Order Date'].dt.to_period('M')

# Save cleaned data
df.to_csv('superstore_clean.csv', index=False)
print("\nCleaned data saved to superstore_clean.csv")
print(f"Final shape: {df.shape}")

What does this cleaning code do?

  • drop_duplicates() removes any rows that are exact copies, which would inflate your metrics.
  • pd.to_datetime() with format='mixed' tells pandas to infer the date format for each value individually, handling inconsistent date formats in the CSV.
  • str.strip().str.title() removes leading/trailing whitespace and converts text to Title Case so that "TECHNOLOGY" and "Technology" are treated as the same category.
  • fillna(0) replaces missing postal codes with 0 so the column can be cast to integer without errors.
  • The Year, Month, and Year-Month columns make it easy to group sales data by time period later.
  • Save analysis.py and run it again:
python analysis.py
  • You should see the duplicate count printed, followed by "Cleaned data saved to superstore_clean.csv" and the final shape of the dataset.
  • Confirm that superstore_clean.csv now exists in your project folder by running:
ls
  • You should see superstore_clean.csv listed alongside your original Sample - Superstore.csv and analysis.py.

Seeing a UnicodeDecodeError?

This means the encoding='latin-1' parameter is missing from your pd.read_csv() call. Double check that your load line matches the code in the first chunk exactly.

help me fix a UnicodeDecodeError when loading my Superstore CSV

Perform exploratory data analysis

With clean data in hand, you can now ask the business questions that matter. EDA reveals which regions drive the most revenue, which categories are most profitable, and how sales trend over time.

  • In analysis.py, add the following EDA code below the save section:
# ============================================================
# PHASE 2: EXPLORATORY DATA ANALYSIS
# ============================================================

print("\n" + "=" * 60)
print("EXPLORATORY DATA ANALYSIS")
print("=" * 60)

# Summary statistics
print(f"\nTotal Sales: ${df['Sales'].sum():,.2f}")
print(f"Total Profit: ${df['Profit'].sum():,.2f}")
print(f"Total Orders: {df['Order ID'].nunique()}")
print(f"Total Customers: {df['Customer ID'].nunique()}")

# Revenue by Region
print("\n--- Revenue by Region ---")
region_sales = df.groupby('Region')['Sales'].sum().sort_values(ascending=False)
print(region_sales)
print(f"\nTop region: {region_sales.index[0]} ({region_sales.iloc[0] / region_sales.sum() * 100:.1f}% of total)")

# Profit by Category
print("\n--- Profit by Category ---")
category_profit = df.groupby('Category')['Profit'].sum().sort_values(ascending=False)
print(category_profit)

# Sales by Customer Segment
print("\n--- Sales by Segment ---")
segment_sales = df.groupby('Segment')['Sales'].sum().sort_values(ascending=False)
print(segment_sales)

# Monthly Sales Trend
monthly_sales = df.groupby('Year-Month')['Sales'].sum()

What does this EDA code do?

  • The summary statistics give you headline numbers: total revenue, total profit, number of unique orders, and number of unique customers.
  • groupby('Region')['Sales'].sum() calculates total sales per region and sorts them highest to lowest, revealing which geographic area drives the most revenue.
  • The same pattern applied to Category and Segment answers "which product type is most profitable?" and "which customer type spends the most?"
  • monthly_sales groups sales by year-month period. You'll use this variable in the next section to plot a trend line.
  • Save analysis.py and run it again:
python analysis.py
  • You should see the "EXPLORATORY DATA ANALYSIS" header followed by total sales and profit figures, revenue broken down by region, profit by category, and sales by segment.

Seeing a KeyError?

A KeyError like KeyError: 'Sales' means a column name doesn't match what's in the CSV. Column names are case-sensitive. Run print(df.columns.tolist()) to see the exact column names and compare them with your code.

help me fix a KeyError in my pandas groupby code

Create visualizations for your portfolio

Numbers in a terminal are useful for analysis, but charts tell the story at a glance. You'll create three matplotlib visualizations that you can include in a portfolio or present in an interview.

  • In analysis.py, add the monthly sales trend line chart below the EDA section:
# ============================================================
# PHASE 3: VISUALIZATIONS
# ============================================================

# 1. Monthly Sales Trend Line Chart
fig, ax = plt.subplots(figsize=(12, 5))
monthly_sales.plot(ax=ax, color='#2196F3', linewidth=2)
ax.set_title('Monthly Sales Trend', fontsize=14, fontweight='bold')
ax.set_xlabel('Month')
ax.set_ylabel('Sales ($)')
ax.grid(axis='y', alpha=0.3)
plt.tight_layout()
plt.savefig('monthly_sales_trend.png', dpi=150)
plt.close()
print("\nSaved: monthly_sales_trend.png")

What does this chart do?

This creates a line chart showing how sales fluctuate month over month across the entire dataset. The plt.savefig() call writes the chart to a PNG file at 150 DPI, and plt.close() frees memory so the next chart starts fresh.

  • Next, add the profit by category bar chart:
# 2. Profit by Category Bar Chart
fig, ax = plt.subplots(figsize=(8, 5))
colors = ['#4CAF50', '#2196F3', '#FF9800']
category_profit.plot(kind='bar', ax=ax, color=colors)
ax.set_title('Profit by Category', fontsize=14, fontweight='bold')
ax.set_xlabel('Category')
ax.set_ylabel('Profit ($)')
ax.set_xticklabels(ax.get_xticklabels(), rotation=0)
ax.grid(axis='y', alpha=0.3)
plt.tight_layout()
plt.savefig('profit_by_category.png', dpi=150)
plt.close()
print("Saved: profit_by_category.png")

What does this chart show?

This bar chart compares total profit across the three product categories (Technology, Office Supplies, Furniture). It makes it immediately obvious which category is driving profit and which might be underperforming.

  • Finally, add the regional sales pie chart:
# 3. Regional Sales Pie Chart
fig, ax = plt.subplots(figsize=(8, 8))
region_sales.plot(kind='pie', ax=ax, autopct='%1.1f%%', startangle=90,
                  colors=['#2196F3', '#4CAF50', '#FF9800', '#F44336'])
ax.set_title('Sales Distribution by Region', fontsize=14, fontweight='bold')
ax.set_ylabel('')
plt.tight_layout()
plt.savefig('sales_by_region.png', dpi=150)
plt.close()
print("Saved: sales_by_region.png")

print("\n\u2713 All visualizations saved successfully!")

What does this chart show?

The pie chart displays each region's share of total sales as a percentage. This is a quick way to communicate regional distribution to stakeholders who want to see relative proportions at a glance.

  • Save analysis.py and run the complete script:
python analysis.py
  • You should see all the previous output plus three "Saved:" confirmation lines at the end, followed by a checkmark message.
  • Confirm the PNG files exist by running:
ls
  • You should see monthly_sales_trend.png, profit_by_category.png, and sales_by_region.png listed in your project folder.

Not seeing the PNG files?

Make sure you have plt.savefig() before plt.close() in each visualization section. If you call plt.close() first, the figure is destroyed before it can be saved.

Also check that there are no errors earlier in the script. A failure in the EDA section would prevent the visualization code from running.

help me figure out why my matplotlib charts aren't saving as PNG files

✔️ Awesome, I've got everything!

Great. Double check you've saved analysis.py and that all three PNG files are in your project folder.

ⓧ I'd like to double check the full code

Here is the complete analysis.py file for comparison:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# ============================================================
# PHASE 1: DATA CLEANING
# ============================================================

# Load the raw dataset
df = pd.read_csv('Sample - Superstore.csv', encoding='latin-1')

# Display basic info
print(f"Shape: {df.shape}")
print(f"\nColumn names:\n{df.columns.tolist()}")
print(f"\nData types:\n{df.dtypes}")
print(f"\nMissing values:\n{df.isnull().sum()}")

# Remove duplicate rows
duplicates = df.duplicated().sum()
print(f"\nDuplicate rows found: {duplicates}")
df = df.drop_duplicates()

# Convert date columns to datetime
df['Order Date'] = pd.to_datetime(df['Order Date'], format='mixed')
df['Ship Date'] = pd.to_datetime(df['Ship Date'], format='mixed')

# Standardize category names (strip whitespace, title case)
df['Category'] = df['Category'].str.strip().str.title()
df['Sub-Category'] = df['Sub-Category'].str.strip().str.title()
df['Segment'] = df['Segment'].str.strip().str.title()
df['Region'] = df['Region'].str.strip().str.title()

# Handle missing Postal Code values (fill with 0 for analysis)
df['Postal Code'] = df['Postal Code'].fillna(0).astype(int)

# Add helper columns for time-based analysis
df['Year'] = df['Order Date'].dt.year
df['Month'] = df['Order Date'].dt.month
df['Year-Month'] = df['Order Date'].dt.to_period('M')

# Save cleaned data
df.to_csv('superstore_clean.csv', index=False)
print("\nCleaned data saved to superstore_clean.csv")
print(f"Final shape: {df.shape}")

# ============================================================
# PHASE 2: EXPLORATORY DATA ANALYSIS
# ============================================================

print("\n" + "=" * 60)
print("EXPLORATORY DATA ANALYSIS")
print("=" * 60)

# Summary statistics
print(f"\nTotal Sales: ${df['Sales'].sum():,.2f}")
print(f"Total Profit: ${df['Profit'].sum():,.2f}")
print(f"Total Orders: {df['Order ID'].nunique()}")
print(f"Total Customers: {df['Customer ID'].nunique()}")

# Revenue by Region
print("\n--- Revenue by Region ---")
region_sales = df.groupby('Region')['Sales'].sum().sort_values(ascending=False)
print(region_sales)
print(f"\nTop region: {region_sales.index[0]} ({region_sales.iloc[0] / region_sales.sum() * 100:.1f}% of total)")

# Profit by Category
print("\n--- Profit by Category ---")
category_profit = df.groupby('Category')['Profit'].sum().sort_values(ascending=False)
print(category_profit)

# Sales by Customer Segment
print("\n--- Sales by Segment ---")
segment_sales = df.groupby('Segment')['Sales'].sum().sort_values(ascending=False)
print(segment_sales)

# Monthly Sales Trend
monthly_sales = df.groupby('Year-Month')['Sales'].sum()

# ============================================================
# PHASE 3: VISUALIZATIONS
# ============================================================

# 1. Monthly Sales Trend Line Chart
fig, ax = plt.subplots(figsize=(12, 5))
monthly_sales.plot(ax=ax, color='#2196F3', linewidth=2)
ax.set_title('Monthly Sales Trend', fontsize=14, fontweight='bold')
ax.set_xlabel('Month')
ax.set_ylabel('Sales ($)')
ax.grid(axis='y', alpha=0.3)
plt.tight_layout()
plt.savefig('monthly_sales_trend.png', dpi=150)
plt.close()
print("\nSaved: monthly_sales_trend.png")

# 2. Profit by Category Bar Chart
fig, ax = plt.subplots(figsize=(8, 5))
colors = ['#4CAF50', '#2196F3', '#FF9800']
category_profit.plot(kind='bar', ax=ax, color=colors)
ax.set_title('Profit by Category', fontsize=14, fontweight='bold')
ax.set_xlabel('Category')
ax.set_ylabel('Profit ($)')
ax.set_xticklabels(ax.get_xticklabels(), rotation=0)
ax.grid(axis='y', alpha=0.3)
plt.tight_layout()
plt.savefig('profit_by_category.png', dpi=150)
plt.close()
print("Saved: profit_by_category.png")

# 3. Regional Sales Pie Chart
fig, ax = plt.subplots(figsize=(8, 8))
region_sales.plot(kind='pie', ax=ax, autopct='%1.1f%%', startangle=90,
                  colors=['#2196F3', '#4CAF50', '#FF9800', '#F44336'])
ax.set_title('Sales Distribution by Region', fontsize=14, fontweight='bold')
ax.set_ylabel('')
plt.tight_layout()
plt.savefig('sales_by_region.png', dpi=150)
plt.close()
print("Saved: sales_by_region.png")

print("\n\u2713 All visualizations saved successfully!")

Your data is cleaned, analyzed, and visualized. Next up, you'll load this cleaned data into a SQL database and write queries that answer the same business questions using SQL syntax.

Query with SQL

Your Python analysis identified sales trends, profit breakdowns, and top performers across regions and categories. Now you'll answer the same business questions using SQL, the query language every data analyst is expected to know in interviews.

DB Browser for SQLite gives you a visual workspace to write and run SQL against a real database. In this step, you'll import your cleaned data into a SQLite database and write six queries that demonstrate the exact skills hiring managers test for.

In this step, get ready to:
  • Create a SQLite database and import your cleaned CSV data.
  • Write SQL queries to answer business questions about revenue, profit, and trends.
  • Save your queries to a portfolio-ready SQL file.
Create a database and import your data

Before you can run SQL queries, you need a database to query against. You'll create a new SQLite database file and import superstore_clean.csv as a table.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type DB Browser for SQLite and press Enter to open it.
  • Click File in the menu bar, then click New Database... to create a new database.
  • Navigate to your project folder (the same folder where superstore_clean.csv lives).
  • Name the file superstore.db and click Save.

DB Browser may open an Edit Table Definition dialog. If it does, click Cancel to dismiss it. You'll import data from your CSV instead of creating a table manually.

  • Click File, then Import, then Table from CSV file... to open the import dialog.
  • Select superstore_clean.csv from your project folder and click Open.
  • In the import dialog, change the table name field to orders.
  • Make sure Column names in first line is checked.
  • Click OK to import the data.

What just happened?

DB Browser read every row from your CSV and created a table called orders inside superstore.db. Each column in the CSV became a column in the table.

You now have a real database you can query with SQL, just like you would in a production environment with PostgreSQL or MySQL.

  • Click the Browse Data tab at the top of DB Browser.
  • Confirm the orders table is selected in the dropdown. You should see your cleaned data with columns like Region, Category, Sales, and Profit.

Don't see the orders table?

Make sure you typed orders in the table name field during import. If the table has a different name (like superstore_clean), you can right-click it in the Database Structure tab and rename it.

If the import failed entirely, confirm your CSV file uses commas as separators and that the file is not open in another program. Help me troubleshoot my CSV import in DB Browser

Write and execute SQL queries

Now that your data lives in a proper database, you can query it with SQL. Each query below answers a specific business question that interviewers commonly ask.

  • Click the Execute SQL tab at the top of DB Browser to open the SQL editor pane.

You'll write each query in this editor, then click the play button (or press Ctrl+Enter) to execute it.

  • Paste the following query into the SQL editor to see total revenue by region:
-- 1. Total Revenue by Region
SELECT Region,
       ROUND(SUM(Sales), 2) AS Revenue,
       ROUND(SUM(Sales) * 100.0 / (SELECT SUM(Sales) FROM orders), 1) AS Pct_of_Total
FROM orders
GROUP BY Region
ORDER BY Revenue DESC;

What does this query do?

This query groups all orders by Region, sums the Sales for each, and calculates what percentage of total revenue each region represents.

The subquery (SELECT SUM(Sales) FROM orders) calculates the overall total so you can compute each region's share. This is a technique interviewers love to test.

  • Click the play button or press Ctrl+Enter to execute the query. You should see 4 rows showing each region's revenue and percentage.
  • Clear the SQL editor, then paste this query to analyze profit by category:
-- 2. Profit by Category
SELECT Category,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit) / SUM(Sales) * 100, 1) AS Profit_Margin_Pct
FROM orders
GROUP BY Category
ORDER BY Total_Profit DESC;

What does this query do?

This calculates profit margin percentage for each product category. It divides total profit by total sales and multiplies by 100 to get a percentage.

A category with high sales but low profit margin (like Furniture) signals a business problem worth investigating.

  • Execute the query. You should see 3 rows showing Technology, Office Supplies, and Furniture ranked by profit.
  • Clear the editor, then paste this query to find the top-selling products:
-- 3. Top 10 Products by Sales
SELECT [Product Name],
       Category,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       SUM(Quantity) AS Units_Sold
FROM orders
GROUP BY [Product Name], Category
ORDER BY Total_Sales DESC
LIMIT 10;

Why the square brackets?

Column names with spaces (like Product Name) need square brackets in SQLite so the database knows the space is part of the column name, not a separator.

  • Execute the query. You should see 10 products ranked by total sales.
  • Clear the editor, then paste this query to see monthly trends:
-- 4. Monthly Sales Trend
SELECT strftime('%Y-%m', [Order Date]) AS Month,
       ROUND(SUM(Sales), 2) AS Monthly_Sales,
       ROUND(SUM(Profit), 2) AS Monthly_Profit
FROM orders
GROUP BY Month
ORDER BY Month;

What is strftime?

SQLite stores dates as text, so strftime is SQLite's date-formatting function. The format '%Y-%m' extracts the year and month from the Order Date, letting you group sales by month.

  • Execute the query. You should see rows for each month showing sales and profit trends over time.
  • Clear the editor, then paste this query for customer segment analysis:
-- 5. Customer Segment Analysis
SELECT Segment,
       COUNT(DISTINCT [Customer ID]) AS Customers,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(AVG(Sales), 2) AS Avg_Order_Value
FROM orders
GROUP BY Segment
ORDER BY Total_Sales DESC;
  • Execute the query. You should see 3 segments (Consumer, Corporate, Home Office) with their metrics.
  • Clear the editor, then paste this final query to find money-losing sub-categories:
-- 6. Sub-Categories with Negative Profit (Money Losers)
SELECT [Sub-Category],
       Region,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(SUM(Profit) / SUM(Sales) * 100, 1) AS Profit_Margin_Pct
FROM orders
GROUP BY [Sub-Category], Region
HAVING Total_Profit < 0
ORDER BY Total_Profit ASC;

What does HAVING do?

The HAVING clause filters groups after aggregation. Unlike WHERE (which filters individual rows before grouping), HAVING lets you filter based on aggregate results like SUM(Profit) < 0.

This query surfaces the specific sub-category and region combinations that are losing money. These are the insights that make stakeholders sit up and pay attention.

  • Execute the query. You should see multiple rows showing sub-categories with negative profit in specific regions.

Seeing a "no such column" error?

Check that the column names in your query match the imported table exactly. Column names are case-sensitive in SQLite. Click the Database Structure tab and expand the orders table to see the exact column names.

If you see column names like sales (lowercase) instead of Sales (capitalized), update the query to match what your table actually has. Help me fix my SQL column name error

Save your queries to a file

Interviewers often ask candidates to walk through their SQL queries. Saving them in a dedicated file makes them easy to reference and share as part of your portfolio.

  • Switch back to Cursor.
  • Create a new file called queries.sql in your project folder.
  • Paste all six queries together into this file:
-- ============================================================
-- RETAIL SALES ANALYTICS - SQL QUERIES
-- Database: superstore.db | Table: orders
-- ============================================================

-- 1. Total Revenue by Region
SELECT Region,
       ROUND(SUM(Sales), 2) AS Revenue,
       ROUND(SUM(Sales) * 100.0 / (SELECT SUM(Sales) FROM orders), 1) AS Pct_of_Total
FROM orders
GROUP BY Region
ORDER BY Revenue DESC;

-- 2. Profit by Category
SELECT Category,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit) / SUM(Sales) * 100, 1) AS Profit_Margin_Pct
FROM orders
GROUP BY Category
ORDER BY Total_Profit DESC;

-- 3. Top 10 Products by Sales
SELECT [Product Name],
       Category,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       SUM(Quantity) AS Units_Sold
FROM orders
GROUP BY [Product Name], Category
ORDER BY Total_Sales DESC
LIMIT 10;

-- 4. Monthly Sales Trend
SELECT strftime('%Y-%m', [Order Date]) AS Month,
       ROUND(SUM(Sales), 2) AS Monthly_Sales,
       ROUND(SUM(Profit), 2) AS Monthly_Profit
FROM orders
GROUP BY Month
ORDER BY Month;

-- 5. Customer Segment Analysis
SELECT Segment,
       COUNT(DISTINCT [Customer ID]) AS Customers,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(AVG(Sales), 2) AS Avg_Order_Value
FROM orders
GROUP BY Segment
ORDER BY Total_Sales DESC;

-- 6. Sub-Categories with Negative Profit (Money Losers)
SELECT [Sub-Category],
       Region,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(SUM(Profit) / SUM(Sales) * 100, 1) AS Profit_Margin_Pct
FROM orders
GROUP BY [Sub-Category], Region
HAVING Total_Profit < 0
ORDER BY Total_Profit ASC;

Why save queries separately?

In a real analytics role, you'd store reusable queries in version-controlled files so teammates can review, run, and build on them. For your portfolio, this file shows interviewers your SQL thinking at a glance.

  • Save queries.sql.

✔️ Awesome, I've got everything!

Great. Double check you've saved queries.sql in the same project folder as superstore.db and superstore_clean.csv.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file for comparison:

-- ============================================================
-- RETAIL SALES ANALYTICS - SQL QUERIES
-- Database: superstore.db | Table: orders
-- ============================================================

-- 1. Total Revenue by Region
SELECT Region,
       ROUND(SUM(Sales), 2) AS Revenue,
       ROUND(SUM(Sales) * 100.0 / (SELECT SUM(Sales) FROM orders), 1) AS Pct_of_Total
FROM orders
GROUP BY Region
ORDER BY Revenue DESC;

-- 2. Profit by Category
SELECT Category,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit) / SUM(Sales) * 100, 1) AS Profit_Margin_Pct
FROM orders
GROUP BY Category
ORDER BY Total_Profit DESC;

-- 3. Top 10 Products by Sales
SELECT [Product Name],
       Category,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       SUM(Quantity) AS Units_Sold
FROM orders
GROUP BY [Product Name], Category
ORDER BY Total_Sales DESC
LIMIT 10;

-- 4. Monthly Sales Trend
SELECT strftime('%Y-%m', [Order Date]) AS Month,
       ROUND(SUM(Sales), 2) AS Monthly_Sales,
       ROUND(SUM(Profit), 2) AS Monthly_Profit
FROM orders
GROUP BY Month
ORDER BY Month;

-- 5. Customer Segment Analysis
SELECT Segment,
       COUNT(DISTINCT [Customer ID]) AS Customers,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(AVG(Sales), 2) AS Avg_Order_Value
FROM orders
GROUP BY Segment
ORDER BY Total_Sales DESC;

-- 6. Sub-Categories with Negative Profit (Money Losers)
SELECT [Sub-Category],
       Region,
       ROUND(SUM(Sales), 2) AS Total_Sales,
       ROUND(SUM(Profit), 2) AS Total_Profit,
       ROUND(SUM(Profit) / SUM(Sales) * 100, 1) AS Profit_Margin_Pct
FROM orders
GROUP BY [Sub-Category], Region
HAVING Total_Profit < 0
ORDER BY Total_Profit ASC;

You now have a real SQL portfolio piece. Next up, you'll bring all of this analysis to life in an interactive Power BI dashboard.

Build the Power BI Dashboard

Your data is cleaned, analyzed, and queried. Now it's time to turn those numbers into a visual story that stakeholders can interact with.

In this step, you'll load your cleaned dataset into Power BI Desktop and build an interactive dashboard with KPIs, trend charts, geographic visuals, and filters. This is the deliverable hiring managers want to see in a portfolio.

In this step, get ready to:
  • Load your cleaned CSV into Power BI Desktop as a data source.
  • Create six dashboard visuals covering KPIs, trends, and breakdowns.
  • Add slicers for dynamic filtering and format the dashboard layout.
Load your cleaned data into Power BI

Power BI needs a data source before you can build any visuals. You'll connect it to the superstore_clean.csv file you created in the Python step.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • In the Home tab on the ribbon, click Get data.
  • Select Text/CSV from the list of data sources.
  • Browse to your project folder and select superstore_clean.csv.
  • Click Open.
  • In the Navigator preview window, confirm you can see your data columns and rows.
  • Click Load to import the data into Power BI's data model.

What does Load vs Transform Data mean?

Load imports the data directly into Power BI's model as-is. Transform Data opens Power Query Editor where you can reshape the data before loading. Since your CSV is already clean from the Python step, Load is all you need.

After loading completes, you should see your data fields listed in the Data pane on the right side of the screen. Fields like Sales, Profit, Region, and Category should all appear.

Don't see your fields in the Data pane?

Make sure you clicked Load and not Cancel in the Navigator window. If you accidentally closed it, go to Home > Get data > Text/CSV again and repeat the import.

help me load a CSV into Power BI Desktop

Create your dashboard visuals

You'll now build six visuals that tell the story of your retail sales data. Each visual answers a different business question, from high-level KPIs to granular product performance.

Revenue Card
  • Click on an empty area of the report canvas so nothing is selected.
  • In the Visualizations pane, select the Card visual icon.
  • From the Data pane, drag Sales into the Values field well.
  • Power BI displays the sum of all sales as a single number.
  • Position the card in the top-left area of your canvas.
Profit Card
  • Click an empty area of the canvas.
  • In the Visualizations pane, select the Card visual icon again.
  • Drag Profit into the Values field well.
  • Position this card next to the Revenue card at the top of the canvas.

Why use Card visuals for KPIs?

Card visuals display a single number prominently. They're the standard way to show high-level KPIs like total revenue or profit at a glance. Placing them at the top of a dashboard gives viewers immediate context before they explore the detailed charts below.

Monthly sales trend Line Chart
  • Click an empty area of the canvas below your cards.
  • In the Visualizations pane, select the Line chart icon.
  • Drag Order Date to the X-axis field well.
  • Drag Sales to the Y-axis field well.
  • Power BI may automatically group dates by year hierarchy. If so, click the down-arrow on the X-axis to drill into months.
  • Resize the chart to fill the left half of the canvas below the cards.
Regional performance Map
  • Click an empty area of the canvas.
  • In the Visualizations pane, select the Map visual icon (the globe icon).
  • Drag State to the Location field well.
  • Drag Sales to the Bubble size field well.
  • Bubbles appear on the map sized proportionally to each state's total sales.
  • Position the map on the right side of the canvas, next to the Line Chart.
Top 10 products Bar Chart
  • Click an empty area of the canvas.
  • In the Visualizations pane, select the Clustered bar chart icon.
  • Drag Product Name to the Y-axis field well.
  • Drag Sales to the X-axis field well.
  • To show only the top 10, click the visual to select it, then open the Filters pane on the right.
  • Under Product Name, change the filter type from Basic filtering to Top N.
  • Set the value to 10 and drag Sales into the By value area.
  • Click Apply filter.
Customer segment Donut Chart
  • Click an empty area of the canvas.
  • In the Visualizations pane, select the Donut chart icon.
  • Drag Segment to the Legend field well.
  • Drag Sales to the Values field well.
  • The donut chart shows the proportion of sales contributed by each customer segment (Consumer, Corporate, Home Office).

You should now have six visuals on your canvas. Two Card visuals at the top showing total Revenue and total Profit. A Line Chart, a Map, a Bar Chart, and a Donut Chart filling the remaining space.

Map visual showing a blank globe?

Power BI uses Bing Maps to render geographic data. Make sure you dragged State (or City) to the Location well, not the Legend or Values well. Also confirm your internet connection is active since maps require it.

help me fix my map visual in Power BI

Add slicers and format the dashboard

Slicers are on-canvas filters that let users interactively narrow the data shown across all visuals. Adding them transforms a static report into a dynamic, explorable dashboard.

  • Click an empty area of the canvas (along the top or side where you have space).
  • In the Visualizations pane, select the Slicer icon.
  • Drag Region into the Field well. A list of regions appears in the slicer.
  • Resize the slicer to fit neatly along the top or side of your dashboard.
  • Click another empty area of the canvas.
  • Select the Slicer icon again in the Visualizations pane.
  • Drag Category into the Field well.
  • Position this slicer next to the Region slicer.
  • Click another empty area of the canvas.
  • Select the Slicer icon one more time.
  • Drag Order Date into the Field well. Power BI automatically creates a date range slicer with a slider set to Between by default.
  • Position this slicer alongside the other two.

How do slicers filter the whole dashboard?

By default, slicers on a report page filter all other visuals on that page. When you select a value in one slicer, every chart and card updates to show only the matching data. Multiple slicers work together by intersection. Selecting "West" in Region and "Technology" in Category shows only West-region Technology data across all visuals.

Now format the dashboard to look polished and professional.

  • Select the Revenue Card visual. In the Format pane (paint roller icon), expand Callout values and set the Color to a dark blue.
  • Do the same for the Profit Card, choosing a dark green color.
  • For each visual, select it and expand the General tab in the Format pane. Under Title, give each visual a clear name (e.g., "Total Revenue", "Total Profit", "Monthly Sales Trend", "Sales by State", "Top 10 Products", "Sales by Segment").
  • Arrange all visuals so there is consistent spacing between them. Drag the edges to align visuals into a grid layout.
  • Test your slicers by clicking a region value. All six visuals should update to show only that region's data. Click the value again to deselect it.
  • Save your dashboard by pressing Ctrl+S.
  • Name the file superstore_dashboard.pbix and save it to your project folder.

Your completed dashboard should show two KPI cards at the top, four charts below them, and three slicers along the top or side for filtering.

Slicers not filtering all visuals?

By default, all visuals on a page respond to slicers. If a visual isn't updating, select the slicer and go to Format > Edit interactions on the ribbon. Make sure the filter icon (not the "none" icon) is active for each visual you want the slicer to affect.

help me fix slicer interactions in Power BI

Your interactive Power BI dashboard is complete. Next up, you'll document the business insights and recommendations that make this project interview-ready.

Document Business Insights

Your Power BI dashboard is live and interactive. Stakeholders can now slice through regional performance, track monthly trends, and compare customer segments at a glance.

A polished dashboard is only half the story. Interviewers want to see that you can translate data into decisions. In this step, you'll review your findings from Python, SQL, and Power BI, then write clear business insights paired with actionable recommendations.

In this step, get ready to:
  • Identify 3-5 key business insights from your analysis results.
  • Write actionable recommendations for each insight.
Review your analysis and identify key insights

An insight is a non-obvious finding that a business stakeholder could act on. "Sales exist" is not an insight. "Furniture generates high revenue but the lowest profit margin of all categories" is.

  • Open the terminal output from your analysis.py script. Look at the EDA results: revenue by region, profit by category, and sales by segment.
  • Switch to DB Browser for SQLite and review your query results. Pay attention to the sub-categories with negative profit and the monthly trends.
  • Open your Power BI dashboard and interact with the slicers. Filter by each region and category to spot patterns that weren't visible in aggregate numbers.

What patterns should I look for?

Strong insights usually fall into one of these categories: surprising gaps (high sales but low profit), time-based patterns (seasonal spikes or dips), segment differences (one customer group outperforms others), or geographic imbalances (one region dominates or underperforms).

Compare your findings across tools. If Python's groupby, your SQL queries, and your Power BI visuals all point to the same pattern, that's a high-confidence insight worth documenting.

  • Write down 3-5 patterns you noticed. Here are examples based on common Superstore findings:
  • Furniture category generates high sales but low profit (or even negative profit in some regions).
  • Technology has the highest profit margin across all categories.
  • Sales peak in November-December (holiday season) with a dip in January-February.
  • The Consumer segment drives the most revenue but Corporate has a higher average order value.
  • The West region leads in total sales while the Central region has the weakest profit margins.

Your specific numbers may differ slightly. Use the exact values from your own analysis output.

  • Create a new file in Cursor called business_insights.md inside your project folder by pressing Cmd+N (macOS) or Ctrl+N (Windows), then saving it with that filename.
  • Add a header and your first insight to the file:
# Business Insights & Recommendations
## Retail Sales Analytics - Superstore Dataset

---

## Key Insights

### Insight 1: Furniture drives revenue but destroys profit
- Furniture generates strong sales volume but has the lowest profit margin of all three categories.
- Several furniture sub-categories (e.g., Tables, Bookcases) show negative profit in certain regions.
- This suggests aggressive discounting or high shipping costs are eating into margins.

What makes a good insight?

Each insight starts with a clear headline that states the finding. The bullet points underneath provide the evidence from your data. Quantify where possible: "Tables lost $X in the Central region" is stronger than "Tables aren't doing well."

  • Continue adding your remaining insights (aim for 3-5 total). Use the same structure: a headline summarizing the finding, followed by 2-3 supporting data points from your analysis.
  • Save business_insights.md.
Write actionable recommendations for each insight

A recommendation tells a stakeholder what to do about the insight. It should be specific enough that someone could start working on it next week.

  • For each insight you documented, add a Recommendation section directly underneath it. Here's an example for the first insight:
**Recommendation:** Optimize shipping costs for furniture products. Audit the current discount strategy for Tables and Bookcases - consider reducing discount depth or setting minimum order quantities. Investigate whether consolidating furniture shipments to the Central region could improve margins.

What makes a recommendation actionable?

A strong recommendation names the specific action ("reduce discount depth for Tables"), the scope ("in the Central region"), and the expected outcome ("improve margins"). Avoid vague suggestions like "look into this further" or "do more research."

In interviews, the recommendation is where you show business sense. You're proving you can think beyond the data and understand what a team should actually do next.

  • Write recommendations for each of your remaining insights. Here are examples to guide your phrasing:
  • For a Technology profit insight: "Increase marketing budget for Technology products ahead of Q4. The category's high margins mean each additional sale contributes more to the bottom line."
  • For a seasonal sales insight: "Build inventory and marketing campaigns 6-8 weeks before November. Front-load promotional spending in September-October to capture early holiday demand."
  • For a regional performance insight: "Investigate what the West region is doing differently (sales tactics, product mix, shipping partners) and pilot those practices in the Central region."
  • Add a closing summary section at the bottom of your document:
---

## Summary

This analysis covered 4 years of retail sales data across 3 categories, 4 regions, and 3 customer segments. The primary opportunities are:
1. Fix the furniture profitability problem (high urgency - actively losing money)
2. Double down on Technology (high margin, scalable)
3. Align marketing spend with seasonal patterns (November-December peak)

---
*Analysis completed using: Google Sheets, Python (pandas, matplotlib), SQL (DB Browser for SQLite), Power BI Desktop*
  • Save business_insights.md.
  • Read through the full document from top to bottom. Confirm each insight has supporting data points and a specific recommendation.

Tailor the numbers to your data

Replace the example figures with the actual values from your Python output and SQL results. "Tables lost $17,725 in the Central region" is far more credible in an interview than a generic statement.

Not sure which insights to pick?

Focus on findings where the data surprised you, or where there's a clear gap between what you'd expect and what the numbers show. If everything looks "normal," try filtering your Power BI dashboard by region or category to surface hidden patterns.

Help me identify the strongest business insights from my Superstore analysis results.

Your portfolio is now complete. You have a cleaned dataset, Python analysis, SQL queries, a Power BI dashboard, and a business insights document. Next up, there's an optional Secret Mission that takes your analysis one level deeper with a profitability matrix.

Secret mission

Build a Profitability Matrix

Your dashboard shows the big picture, but can you find the hidden money-losers? Build an advanced profitability analysis that identifies exactly which product sub-categories are bleeding money in which regions. Then surface those findings in Power BI with conditional formatting that makes losses impossible to miss.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine with free tools. There are no cloud resources or ongoing costs to worry about. Decide whether to keep your files for your portfolio, pause by closing running apps, or delete everything entirely.

Resources you used:

  • Project folder on your Desktop (contains analysis.py, analysis_secret_mission.py, superstore_clean.csv, superstore.db, queries.sql, PNG visualizations, and the original CSV).
  • Power BI Dashboard (.pbix file).
  • Google Sheets file (in Google Drive).
  • DB Browser for SQLite (installed application).
  • Python libraries (pandas, numpy, matplotlib).
  • Kaggle account (used to download the dataset).

Keep everything running

No action needed. Choose this if you plan to keep building on this project or want it ready for interview prep.

  • All project files remain on your Desktop.
  • DB Browser for SQLite stays installed for future SQL practice.
  • Your Python libraries remain available for other data projects.
  • Your Kaggle account stays active for downloading other datasets.

Pause - I'll come back to this later

Close any running applications to free up system memory. Your files stay on disk and you can pick up where you left off.

  • Close DB Browser for SQLite if it is still open.
  • Close Power BI Desktop if it is still open.
  • Close any terminal sessions running in Cursor.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to clean up completely.

Delete your project folder by opening a terminal and running:

cd ~/Desktop
rm -rf superstore-analytics

On Windows, use:

cd ~\Desktop
rmdir /s /q superstore-analytics

Not sure of your folder name?

If you named your project folder something different, replace the folder name in the command above with whatever you called it.

Delete your Power BI file:

  • If you saved your .pbix file inside the project folder, it was already removed in the previous step.
  • If you saved it elsewhere, locate it in File Explorer and delete it manually.

Delete your Google Sheets file:

  • Open Google Drive in your browser.
  • Right-click the Superstore spreadsheet you created.
  • Select Move to trash.

Uninstall DB Browser for SQLite:

  • Open Windows Settings (press the Windows key and type Settings).
  • Select Apps, then Installed apps.
  • Search for DB Browser for SQLite.
  • Click the three-dot menu next to it and select Uninstall.

Uninstall Python libraries:

  • Open a terminal and run:
pip uninstall pandas numpy matplotlib -y

Delete your Kaggle account (optional):

  • Go to kaggle.com/contact.
  • Under Account/Login, click I'd like to delete my account and follow the prompts.

Nice Work!

Nice Work!

You just built a complete, end-to-end data analyst portfolio project. From raw CSV to polished dashboard, you walked through the exact workflow that hiring managers test for in data analyst interviews.

You've learned how to:

  • Clean and analyze raw retail data with Python using pandas for data wrangling and matplotlib for visualizations.
  • Write SQL queries against a real SQLite database to answer business questions about revenue, profit, and customer segments.
  • Build an interactive Power BI dashboard with KPI cards, trend lines, regional maps, and dynamic slicers for filtering.
  • Created a profitability matrix identifying money-losing product sub-category and region combinations with actionable recommendations.

Ready to quiz yourself?