Analyze E-Commerce Sales with Python

Clean retail data, query MySQL, and build a Power BI dashboard.

Introduction

30 Second Summary

Every online store generates thousands of transactions daily. Someone needs to turn that raw data into answers that drive real business decisions.

In this project, you will take a real e-commerce dataset with over 500,000 transactions, clean it with Python, load it into a MySQL database, query it with SQL, and build an interactive dashboard in Power BI that visualizes what sold, where, and when.

What You'll Build

You will run SQL queries that answer the exact questions a manager would ask about sales performance, then present those answers in an interactive dashboard you can demo in seconds.

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

  • A Jupyter Notebook documenting how you cleaned 500,000+ messy transactions into analysis-ready data, handling missing values, cancelled orders, and creating a revenue column.
  • A set of SQL queries you can run on demand to answer questions like "What are the top 10 products by revenue?" and "How does monthly revenue trend over time?"
  • An interactive Power BI dashboard with charts showing revenue trends, top products, and sales by country that you can show to anyone.
  • Secret Mission: A customer segmentation analysis using SQL window functions that classifies buyers into spending tiers, demonstrating intermediate-level analytics skills.

Are there any prerequisites?

This project assumes you already have Python 3.11, Jupyter Notebook, MySQL, and Power BI Desktop installed on Windows. Step 1 will walk you through installing the Python packages you need.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for your portfolio.

Set Up Your Python Environment

Before you can analyze e-commerce data, you need two Python packages installed.

In this step, you will install pandas for data manipulation and mysql-connector-python for connecting to your MySQL database. Then you will create the Jupyter Notebook you will use throughout this project and confirm everything imports correctly.

In this step, get ready to:
  • Install pandas and mysql-connector-python.
  • Create a new Jupyter Notebook for the project.
  • Verify both libraries import successfully.
Install the required Python packages

pandas handles loading, filtering, and transforming data in Python. mysql-connector-python lets Python talk directly to your MySQL database.

  • Press the Windows key to open your search bar.
  • Type cmd and press Enter to open Command Prompt.
  • Install both packages by running this command:
pip install pandas mysql-connector-python

What does this command do?

pip install pandas downloads the pandas library for working with tabular data (like spreadsheets in Python).

pip install mysql-connector-python downloads the official MySQL connector so Python can send queries to your database.

You should see output ending with Successfully installed followed by the package names and version numbers.

Seeing 'pip is not recognized'?

This means your Python installation is not on your system PATH. Try running python -m pip install pandas mysql-connector-python instead.

If that also fails, reinstall Python 3.11 and check the Add Python to PATH box during installation.

Still stuck? Help me fix my pip installation on Windows

Create your project notebook

Jupyter Notebook runs in your browser and lets you write and execute Python code in individual cells. You will launch it from your Desktop so the notebook file is easy to find later.

  • In the same Command Prompt window, navigate to your Desktop by running:
cd %USERPROFILE%\Desktop
  • Launch Jupyter Notebook by running:
jupyter notebook

What happens when you run this?

This command starts a local web server and opens the Jupyter Dashboard in your default browser. The dashboard shows files and folders on your Desktop.

Your Command Prompt will display a URL (usually http://localhost:8888) and stay running. Do not close this terminal window while you are working.

  • In the Jupyter Dashboard (in your browser), click the New button in the top-right area.
  • Select Python 3 from the dropdown to create a new notebook.
  • Click the title text that says Untitled at the top of the notebook page.
  • Type ecommerce_analysis and click Rename to save the new name.

Your browser now shows an empty notebook called ecommerce_analysis.ipynb with a single empty cell ready for code.

Browser didn't open automatically?

Check your Command Prompt output for a URL that looks like http://localhost:8888/?token=... and copy-paste it into your browser manually.

Need more help? Help me get Jupyter Notebook open in my browser

Test your imports

The first cell in every data project typically imports the libraries you need. Running this cell confirms the packages installed correctly and are available to your notebook.

  • Click into the first empty cell in your notebook and type the following code:
# Cell 1: Imports
import pandas as pd
import mysql.connector

What does this code do?

import pandas as pd loads the pandas library and gives it the short alias pd so you can type less when calling its functions.

import mysql.connector loads the MySQL connector library so you can connect to databases later in the project.

Before you run this cell, what do you think you will see if both packages installed correctly?

  • Press Shift+Enter to run the cell.

The cell runs with no output and no red error traceback. The cell number changes to [1] on the left, confirming it executed successfully.

Seeing a ModuleNotFoundError?

This means the package did not install into the same Python that Jupyter is using. Open a new Command Prompt and run the install with Jupyter's Python directly:

python -m pip install pandas mysql-connector-python

After installing, go back to your notebook and click Kernel in the menu bar, then Restart. Re-run Cell 1 with Shift+Enter.

Still seeing the error? Help me fix ModuleNotFoundError in Jupyter

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook (Ctrl+S).

ⓧ I'd like to double check the full code

Your Cell 1 should contain exactly this code:

# Cell 1: Imports
import pandas as pd
import mysql.connector

Your Python environment is ready to go. Next up, you will download a real e-commerce dataset and explore what is inside it.

Load and Explore the Raw Dataset

Your Python environment is ready with both packages importing successfully. Now it's time to get your hands on real data.

Before you can analyze anything, you need to see what you're working with. Real-world datasets are always messy. This step reveals exactly how messy the Online Retail dataset is, so you know what to fix in the next step.

In this step, get ready to:
  • Download the Online Retail dataset from Kaggle.
  • Load the CSV into a pandas DataFrame and explore its structure.
  • Identify data quality issues that need cleaning.
Download the dataset from Kaggle

The dataset you'll use is the UCI Online Retail dataset, hosted on Kaggle. It contains over 500,000 real transactions from a UK-based online retailer between December 2010 and December 2011.

  • Open your browser and go to the dataset page:
  • Kaggle Online Retail Dataset
  • Sign in to Kaggle (or create a free account if you don't have one).
  • Click the Download button on the right-hand side of the page.
  • The dataset downloads as a ZIP file. Extract it to get the online_retail.csv file inside.
  • Move online_retail.csv into the same folder where your ecommerce_analysis.ipynb notebook is saved.

How do I find my notebook folder?

In Jupyter Notebook, click the Jupyter logo in the top left to see your file browser. The folder shown there is where your notebook lives. Place online_retail.csv in that same location.

  • Confirm the file is in the right place by checking the Jupyter file browser. You should see both ecommerce_analysis.ipynb and online_retail.csv listed side by side.

Don't see the CSV file in Jupyter?

Make sure you extracted the ZIP file first. The CSV is inside the ZIP, not the ZIP itself.

Double-check that you placed the file in the exact same folder as your notebook, not in a subfolder. Help me find where to put my dataset file.

Load and explore the dataset structure

With the file in place, you can now load it into a DataFrame and see what's inside. This dataset uses a special character encoding, so you'll need to specify that when reading it.

  • In your notebook, create a new cell below Cell 1 and add this code to load and inspect the dataset:
# Cell 2: Load the dataset
df = pd.read_csv('online_retail.csv', encoding='ISO-8859-1')
print(f"Shape: {df.shape}")
print(df.info())
print(df.head())

What does this code do?

pd.read_csv() reads the CSV file into a pandas DataFrame. The encoding='ISO-8859-1' parameter tells pandas this file uses Western European character encoding (not the default UTF-8).

df.shape shows the number of rows and columns. df.info() shows column names and data types. df.head() displays the first five rows so you can see what the actual data looks like.

  • Run the cell by pressing Shift+Enter.

You should see output showing the shape as approximately 541,909 rows and 8 columns. The columns are InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, and Country.

Seeing a FileNotFoundError?

This means pandas cannot find online_retail.csv in the expected location. Make sure the CSV file is in the same folder as your notebook, and that the filename matches exactly (case-sensitive).

Help me fix the FileNotFoundError when loading my CSV.

Identify data quality issues

Now that you can see the raw data, it's time to dig into the quality problems lurking beneath the surface. Missing values, cancelled orders, and suspicious numbers all need to be identified before you can trust any analysis.

  • Create a new cell below Cell 2 and add this code to check for missing values and value ranges:
# Cell 3: Check missing values
print(df.isnull().sum())
print(df.describe())

What does this code do?

df.isnull().sum() counts how many missing (null) values exist in each column. This reveals which columns have gaps in the data.

df.describe() shows statistical summaries (min, max, mean) for numeric columns. This helps you spot impossible values like negative quantities or zero prices.

Before you run this cell, what do you think the data quality will look like? Do you expect a perfectly clean dataset, or problems hiding in the numbers?

  • Run the cell by pressing Shift+Enter.

You should see three major data quality issues in the output. The CustomerID column has approximately 135,000+ missing values.

In the describe() output, look at the Quantity column's minimum value. Negative quantities represent cancelled orders. The UnitPrice column has a minimum of zero, which means some items have no price recorded.

Why does messy data matter?

If you ran SQL queries on this raw data, your revenue calculations would include cancelled orders (negative quantities) and free items (zero prices). That would produce misleading business insights.

Cleaning the data before loading it into a database is a core skill employers look for in data analysts. The next step tackles each of these issues.

Not seeing any output from describe()?

Make sure you ran Cell 2 first. Cell 3 depends on the df variable that Cell 2 creates. If you restarted your notebook kernel, you need to re-run cells in order from the top.

Help me understand why my notebook cell isn't producing output.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook with all three cells.

ⓧ I'd like to double check the full code

Here is the complete notebook code up to this point:

# Cell 1: Imports
import pandas as pd
import mysql.connector

# Cell 2: Load the dataset
df = pd.read_csv('online_retail.csv', encoding='ISO-8859-1')
print(f"Shape: {df.shape}")
print(df.info())
print(df.head())

# Cell 3: Check missing values
print(df.isnull().sum())
print(df.describe())

You've loaded a real-world dataset and uncovered its hidden problems: missing customer IDs, cancelled orders, and zero-price items. Next up, you'll clean all of these issues and load the polished data into a MySQL database where you can query it with SQL.

Clean the Data and Load into MySQL

In Step 2, you explored the raw dataset and discovered some serious problems. Missing customer IDs, cancelled orders with negative quantities, and zero-price rows that would throw off any analysis.

Your MySQL database needs clean, structured data to produce trustworthy results. In this step, you'll write Python code to filter out the bad rows, create a Revenue column, and load everything into a MySQL database ready for SQL queries.

In this step, get ready to:
  • Clean the raw DataFrame by removing incomplete and invalid rows.
  • Create a MySQL database and table to store your sales data.
  • Load the cleaned data from Python into MySQL in batches.
Clean the data in your notebook

Your DataFrame currently has around 541,000 rows, but many of them are unusable. You need to remove rows that would corrupt your analysis before loading anything into a database.

  • In your Jupyter Notebook, create a new cell below Cell 3 and paste the following code:
# Drop rows with missing CustomerID
df = df.dropna(subset=['CustomerID'])

# Remove cancelled orders (InvoiceNo starts with 'C')
df = df[~df['InvoiceNo'].astype(str).str.startswith('C')]

# Remove rows with non-positive Quantity or UnitPrice
df = df[df['Quantity'] > 0]
df = df[df['UnitPrice'] > 0]

# Create Revenue column
df['Revenue'] = df['Quantity'] * df['UnitPrice']

# Convert CustomerID to integer
df['CustomerID'] = df['CustomerID'].astype(int)

print(f"Cleaned shape: {df.shape}")
print(df.head())

What does this code do?

Each line targets a specific data quality issue you discovered in Step 2.

  • dropna(subset=['CustomerID']) removes the ~135,000 rows where no customer is identified. You can't analyze customer behavior without a customer.
  • The InvoiceNo filter removes cancelled orders (invoice numbers starting with 'C') that represent returned goods, not real sales.
  • Filtering for positive Quantity and UnitPrice removes leftover invalid rows (free samples, stock adjustments).
  • The Revenue column multiplies Quantity by UnitPrice to give you the actual money earned per transaction.
  • Run the cell by pressing Shift+Enter.
  • You should see a cleaned shape of approximately 397,000+ rows and a preview showing the new Revenue column on the right.

Still seeing 541,000 rows?

  • Make sure you ran Cells 2 and 3 first. Cell 4 depends on the df variable created in Cell 2.
  • Check that you pasted the entire cell. The tilde (~) in df[~df['InvoiceNo']...] is easy to miss and it flips the filter logic.
  • Help me debug my data cleaning code.
Create the MySQL database and table

With your data clean, you need a place to store it. You'll connect to your local MySQL server from Python and create a database with a table structured to hold your sales data.

  • Create a new cell below Cell 4 and paste the following code:
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='your-mysql-password-here'
)
cursor = conn.cursor()

cursor.execute("CREATE DATABASE IF NOT EXISTS ecommerce")
cursor.execute("USE ecommerce")

cursor.execute("DROP TABLE IF EXISTS sales")
cursor.execute("""
    CREATE TABLE sales (
        InvoiceNo VARCHAR(20),
        StockCode VARCHAR(20),
        Description VARCHAR(255),
        Quantity INT,
        InvoiceDate DATETIME,
        UnitPrice DECIMAL(10, 2),
        CustomerID INT,
        Country VARCHAR(50),
        Revenue DECIMAL(10, 2)
    )
""")
conn.commit()
print("Database and table created successfully")
  • Replace your-mysql-password-here with the root password you set when you installed MySQL.

What does this code do?

  • mysql.connector.connect() opens a connection to your local MySQL server using the host, user, and password arguments.
  • CREATE DATABASE IF NOT EXISTS ecommerce creates a new database. The IF NOT EXISTS part means it won't error if you run this cell again.
  • The CREATE TABLE statement defines each column with a data type that matches your DataFrame columns.
  • conn.commit() saves the changes to the database.
  • Run the cell by pressing Shift+Enter.
  • You should see Database and table created successfully printed below the cell.

Seeing a connection error?

  • Confirm MySQL is running. Open Command Prompt as Administrator and run net start mysql (or your MySQL service name).
  • Double-check your root password. If you forgot it, you may need to reset it through MySQL's configuration.
  • If you see Access denied, the password in your code does not match your MySQL root password.
  • Help me fix my MySQL connection error in Python.
Load the cleaned data into MySQL

Your database and table exist, but they're empty. Now you'll push all 397,000+ rows from your cleaned DataFrame into MySQL. The code inserts rows in batches of 5,000 to avoid overwhelming the database connection.

  • Create a new cell below Cell 5 and paste the following code:
# Prepare data for insertion
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])

insert_query = """
    INSERT INTO sales (InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, Country, Revenue)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""

# Convert DataFrame to list of tuples
data = df[['InvoiceNo', 'StockCode', 'Description', 'Quantity', 'InvoiceDate', 'UnitPrice', 'CustomerID', 'Country', 'Revenue']].values.tolist()

# Insert in batches of 5000
batch_size = 5000
for i in range(0, len(data), batch_size):
    batch = data[i:i + batch_size]
    cursor.executemany(insert_query, batch)
    conn.commit()
    print(f"Inserted batch {i // batch_size + 1} ({min(i + batch_size, len(data))}/{len(data)} rows)")

print("Data loading complete!")

What does this code do?

  • pd.to_datetime() converts the InvoiceDate column to proper datetime objects so MySQL can store them in its DATETIME column.
  • cursor.executemany() inserts multiple rows at once using the same INSERT template. This is much faster than inserting one row at a time.
  • The batch loop splits 397,000+ rows into groups of 5,000. Each batch gets committed to the database before the next one starts. This prevents memory issues and lets you see progress.
  • Run the cell by pressing Shift+Enter.
  • You should see batch progress messages printing one by one. This may take 30-60 seconds to finish. The final line should read Data loading complete!.

Data loading is failing partway through?

  • If you see a data truncation error, some Description values may be longer than 255 characters. Re-run Cell 5 to recreate the table, then try Cell 6 again.
  • If the cell seems stuck with no output, give it a minute. Inserting hundreds of thousands of rows takes time.
  • If you get a MySQL server has gone away error, your MySQL service may have restarted. Re-run Cell 5 first to reconnect, then re-run Cell 6.
  • Help me fix my MySQL data loading error.

Now verify that the data actually landed in MySQL. Before you run the next cell, think about how many rows you expect to see in the database.

  • Create a new cell below Cell 6 and paste the following verification code:
cursor.execute("SELECT COUNT(*) FROM sales")
count = cursor.fetchone()[0]
print(f"Total rows in MySQL: {count}")

cursor.execute("SELECT * FROM sales LIMIT 5")
for row in cursor.fetchall():
    print(row)
  • Run the cell by pressing Shift+Enter.
  • You should see a row count of approximately 397,000+ rows. Below that, five sample rows should display with all nine columns: InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, Country, and Revenue.

What should I see?

The row count confirms that all your cleaned data made it into MySQL. The sample rows let you spot-check that values look correct. Every row should have a positive Quantity, a positive UnitPrice, a Revenue value, and an integer CustomerID.

✔️ Awesome, I've got everything!

Great. Make sure you've saved your notebook using Ctrl+S.

ⓧ I'd like to double check the full code

Your notebook should now have 7 cells. Scroll through and confirm each one is present in this order:

  • Cell 1: Imports (import pandas as pd and import mysql.connector).
  • Cell 2: Load the dataset (pd.read_csv('online_retail.csv', encoding='ISO-8859-1') with shape, info, and head prints).
  • Cell 3: Check missing values (isnull().sum() and describe()).
  • Cell 4: Clean the data (dropna, cancel filter, positive filters, Revenue column, CustomerID cast).
  • Cell 5: Connect to MySQL and create database (mysql.connector.connect(), CREATE DATABASE, CREATE TABLE).
  • Cell 6: Load data into MySQL (cursor.executemany() in batches of 5000).
  • Cell 7: Verify the data loaded (SELECT COUNT(*) and SELECT * FROM sales LIMIT 5).

Your raw, messy CSV is now a structured MySQL database with clean sales data. Next up, you'll write SQL queries that answer real business questions from this data.

Write SQL Queries for Business Insights

You have 400,000 rows of clean sales data sitting in MySQL. In the last step, you ran SELECT * FROM sales LIMIT 5 and saw individual transaction rows. Those rows are accurate, but they answer no business questions on their own.

A manager does not ask "show me row 47." They ask "What are our top products?" and "How is revenue trending?" To answer those questions, you need GROUP BY, aggregations, and ordering. This step is where you prove SQL skills to employers.

In this step, get ready to:
  • Write and run basic aggregation queries (total revenue, unique customers).
  • Build business insight queries using GROUP BY, ORDER BY, and LIMIT.
  • Save your SQL queries in a portfolio-ready file.
Run basic aggregation queries

Aggregation functions like SUM and COUNT collapse thousands of rows into a single meaningful number. These one-line queries give you the big-picture numbers for the dataset.

  • Before you run this, think: what do you expect the total revenue to be across 397,000 transactions? Thousands? Millions?
  • Create a new cell in your notebook (Cell 8) and run the following code:
# Cell 8: Run business queries
cursor.execute("SELECT ROUND(SUM(Revenue), 2) AS total_revenue FROM sales")
print(f"Total Revenue: {cursor.fetchone()[0]}")
cursor.execute("SELECT COUNT(DISTINCT CustomerID) AS unique_customers FROM sales")
print(f"Unique Customers: {cursor.fetchone()[0]}")

What does this code do?

Each cursor.execute() sends a SQL query to your MySQL database and cursor.fetchone()[0] grabs the first column of the result.

  • ROUND(SUM(Revenue), 2) adds up every row's Revenue and rounds to 2 decimal places.
  • COUNT(DISTINCT CustomerID) counts how many unique customers exist, ignoring duplicates.
  • Run the cell.
  • You should see two numbers printed: a total revenue in the millions and a customer count in the thousands.

Seeing a 'cursor is not connected' error?

If your kernel restarted or you re-ran earlier cells, the MySQL connection may have dropped. Re-run Cell 5 (the connection cell) to re-establish conn and cursor, then try Cell 8 again.

Need more help? Why is my MySQL cursor not connected in Jupyter?

Query the top 10 products by revenue

A single total number is useful, but a manager wants to know which products drive that revenue. GROUP BY groups rows by a shared column value so you can aggregate within each group.

  • Create a new cell (Cell 9) and run this query to find the top 10 products:
# Cell 9: Top 10 products by revenue
cursor.execute("""
    SELECT Description, ROUND(SUM(Revenue), 2) AS total_revenue
    FROM sales
    GROUP BY Description
    ORDER BY total_revenue DESC
    LIMIT 10
""")
print("\nTop 10 Products by Revenue:")
for row in cursor.fetchall():
    print(f"  {row[0]}: ${row[1]}")

What does this query do?

  • GROUP BY Description groups all rows with the same product description together.
  • SUM(Revenue) totals the revenue within each group.
  • ORDER BY total_revenue DESC sorts from highest to lowest.
  • LIMIT 10 keeps only the top 10 results.
  • Run the cell.
  • You should see exactly 10 rows printed, each showing a product description and its total revenue.

Seeing 0 rows or an empty output?

Make sure you did not accidentally call cursor.fetchall() twice on the same query. The first call consumes all results, so a second call returns nothing. Re-run the cell from the top.

Still stuck? Help me debug my GROUP BY query returning no results.

  • Create a new cell (Cell 10) and run this query to see how revenue changes month to month:
# Cell 10: Monthly revenue trend
cursor.execute("""
    SELECT DATE_FORMAT(InvoiceDate, '%Y-%m') AS month,
           ROUND(SUM(Revenue), 2) AS monthly_revenue
    FROM sales
    GROUP BY DATE_FORMAT(InvoiceDate, '%Y-%m')
    ORDER BY month
""")
print("\nMonthly Revenue Trend:")
for row in cursor.fetchall():
    print(f"  {row[0]}: ${row[1]}")

What does DATE_FORMAT do?

DATE_FORMAT(InvoiceDate, '%Y-%m') converts a full datetime like 2011-03-15 10:22:00 into just 2011-03. This lets you group all transactions from the same month together regardless of the exact day or time.

  • Run the cell.
  • You should see a list of year-month values with revenue amounts showing a clear trend across the dataset's time range.
  • Create a new cell (Cell 11) to find the top countries by revenue, then close the MySQL connection:
# Cell 11: Revenue by country
cursor.execute("""
    SELECT Country, ROUND(SUM(Revenue), 2) AS total_revenue
    FROM sales
    GROUP BY Country
    ORDER BY total_revenue DESC
    LIMIT 10
""")
print("\nTop 10 Countries by Revenue:")
for row in cursor.fetchall():
    print(f"  {row[0]}: ${row[1]}")

# Close connection
cursor.close()
conn.close()

Why close the connection?

cursor.close() and conn.close() release the MySQL connection so other programs can use it. Leaving connections open can exhaust MySQL's connection pool over time.

  • Run the cell.
  • You should see 10 countries listed with their total revenue. The United Kingdom will likely dominate since this is a UK-based retailer.

Getting an error on cursor.close()?

If you see an error that the cursor is already closed, it means you ran this cell twice. That is fine. The query results already printed successfully on the first run.

Need help? Why am I getting an error when closing my MySQL cursor?

✔️ Awesome, I've got everything!

Great. Make sure you have saved your notebook with all cells from Cell 8 through Cell 11.

ⓧ I'd like to double check the full code

Your notebook should now have Cells 8 through 11 added after the cells from the previous step. Scroll through and confirm each is present in order:

  • Cell 8: Two cursor.execute() calls for total revenue and unique customers.
  • Cell 9: Top 10 products query with GROUP BY Description.
  • Cell 10: Monthly revenue query with DATE_FORMAT.
  • Cell 11: Revenue by country query followed by cursor.close() and conn.close().
Save your queries to a SQL file

Employers reviewing your GitHub portfolio want to see clean, standalone SQL files they can read without opening a notebook. Saving your queries separately also makes them easy to reuse in other tools.

  • Create a new file called business_queries.sql in the same folder as your notebook. You can create it with any text editor or by right-clicking in Jupyter's file browser and selecting **New File**.
  • Paste the following basic queries into the top of the file:
-- Total revenue
SELECT ROUND(SUM(Revenue), 2) AS total_revenue FROM sales;

-- Total unique customers
SELECT COUNT(DISTINCT CustomerID) AS unique_customers FROM sales;

-- Total unique products
SELECT COUNT(DISTINCT StockCode) AS unique_products FROM sales;

-- Date range of transactions
SELECT MIN(InvoiceDate) AS first_transaction,
       MAX(InvoiceDate) AS last_transaction
FROM sales;
  • Below the basic queries, paste the business insight queries:
-- Top 10 products by total revenue
SELECT Description,
       ROUND(SUM(Revenue), 2) AS total_revenue,
       SUM(Quantity) AS total_quantity
FROM sales
GROUP BY Description
ORDER BY total_revenue DESC
LIMIT 10;

-- Monthly revenue trend
SELECT DATE_FORMAT(InvoiceDate, '%Y-%m') AS month,
       ROUND(SUM(Revenue), 2) AS monthly_revenue,
       COUNT(DISTINCT InvoiceNo) AS num_orders
FROM sales
GROUP BY DATE_FORMAT(InvoiceDate, '%Y-%m')
ORDER BY month;

-- Revenue by country (top 10)
SELECT Country,
       ROUND(SUM(Revenue), 2) AS total_revenue,
       COUNT(DISTINCT CustomerID) AS num_customers
FROM sales
GROUP BY Country
ORDER BY total_revenue DESC
LIMIT 10;

Why do these queries have extra columns?

The SQL file versions include additional columns like SUM(Quantity) AS total_quantity and COUNT(DISTINCT InvoiceNo) AS num_orders that the notebook versions did not print. These extra columns make the standalone file more useful for anyone who runs it directly in a MySQL client.

  • Save business_queries.sql.
  • Confirm the file exists by checking your Jupyter file browser or running this in a new notebook cell:
import os
print(os.path.exists('business_queries.sql'))
  • You should see True printed, confirming the file is saved in the right location.

Seeing False?

The file is not in the same folder as your notebook. Make sure you saved business_queries.sql in the same directory where ecommerce_analysis.ipynb lives. Check the Jupyter file browser sidebar to see both files listed together.

Still stuck? Help me find where my SQL file was saved.

✔️ Awesome, I've got everything!

Great. Confirm you have saved business_queries.sql with all seven queries (4 basic + 3 business insight).

ⓧ I'd like to double check the full code

Your business_queries.sql file should contain both blocks you pasted above, in order:

  • First block: four basic queries (total revenue, unique customers, unique products, date range).
  • Second block: three business insight queries (top 10 products, monthly revenue trend, revenue by country).

Scroll up and compare your file against both code blocks in this substep to confirm they match.

You now have real, formatted answers to business questions that any manager would ask. Your SQL queries can tell a story about revenue, products, and geography. Next up, you will turn these numbers into visuals that communicate insights at a glance.

Build a Power BI Dashboard

Your SQL queries are pulling real business insights from 400,000 rows of transaction data. The numbers are accurate, but they live in terminal output that no manager wants to read.

In this step, you will connect Power BI Desktop to your MySQL database and build an interactive dashboard with charts that make your insights immediately visual. This is the skill that turns a data analyst from someone who writes queries into someone who drives decisions.

In this step, get ready to:
  • Connect Power BI Desktop to your MySQL database.
  • Build three visuals: a revenue trend line, a top products bar chart, and a revenue-by-country chart.
  • Format, save, and screenshot your dashboard for your GitHub portfolio.
Install MySQL Connector/NET and connect Power BI to your database

Power BI Desktop needs a driver called MySQL Connector/NET to communicate with your MySQL server. Without it, the MySQL connector option will fail when you try to connect.

  • Open your browser and navigate to the MySQL Connector/NET download page:
  • https://dev.mysql.com/downloads/connector/net/
  • Select Microsoft Windows as the operating system.
  • Download the MSI Installer file (mysql-connector-net-26.7.0.msi).
  • Run the downloaded MSI file and follow the installer prompts to complete the installation.

What is MySQL Connector/NET?

MySQL Connector/NET is a driver that lets Windows applications (like Power BI) communicate with MySQL databases. Power BI requires this driver to be installed before it can read data from your MySQL server.

Now you need to ensure your MySQL server is running so Power BI can connect to it.

  • Open a Command Prompt window by pressing the Windows key, typing cmd, and pressing Enter.
  • Verify MySQL is running by executing this command:
net start mysql

If MySQL is already running, you will see a message saying the service has already been started. If it was stopped, this command starts it.

  • Open Power BI Desktop by pressing the Windows key, typing Power BI Desktop, and pressing Enter.
  • In the Home tab of the ribbon, click Get Data.
  • In the Get Data dialog, select the Database category on the left.
  • Select MySQL database from the list.
  • Click Connect.

The MySQL database connection dialog appears, asking for your server and database details.

  • In the Server field, enter localhost.
  • In the Database field, enter ecommerce.
  • Click OK.

Power BI now asks you to authenticate. By default it may try Windows credentials, which will not work with your MySQL installation.

  • Select the Database authentication type on the left side of the credentials dialog.
  • Enter root in the User name field.
  • Enter your MySQL password in the Password field (the same password you used in your notebook's mysql.connector.connect() call).
  • Click OK.

Why Database authentication?

Power BI defaults to Windows credentials when connecting to databases. MySQL uses its own user/password system, so you must switch to Database authentication and provide the MySQL username and password directly.

If Power BI shows an encryption warning dialog, click OK to proceed with an unencrypted connection. This is safe for a local development database.

The Navigator window appears, showing the tables in your ecommerce database.

  • Check the box next to sales in the Navigator list.
  • Click Load at the bottom of the Navigator window.

Power BI loads approximately 397,000 rows from your sales table. After loading completes, you should see the sales table listed in the Data pane on the right side, with all nine columns expandable beneath it.

Connection failing?

If you see a connector-not-installed error, restart Power BI Desktop after installing MySQL Connector/NET. Power BI only detects newly installed drivers on startup.

If authentication fails, open File in the Power BI ribbon. Select Options and settings. Click Data source settings. Select your MySQL source, click Edit Permissions, and change the credentials to Database type.

Help me connect Power BI to my local MySQL database

Build three dashboard visuals

With your data loaded, you will now create three charts that answer the same business questions your SQL queries answered, but in a visual format anyone can understand at a glance.

Start with a Line Chart showing how revenue changes over time.

  • In the Visualizations pane on the right, click the Line chart icon (it looks like a line graph).
  • From the Data pane, drag InvoiceDate to the X-axis well.
  • Drag Revenue to the Y-axis well.

An empty chart placeholder appears on the canvas, then fills with data. Power BI automatically aggregates Revenue as a sum and groups by date. You should see a line chart showing monthly revenue patterns.

Adjust the date hierarchy

Power BI may default to showing a date hierarchy (Year, Quarter, Month). If your chart shows a drill-down hierarchy instead of a continuous timeline, click the small double-arrow icon in the top-left of the chart to switch to a flat date axis.

Next, create a Bar Chart showing the top 10 products by revenue.

  • Click on an empty area of the canvas to deselect the line chart.
  • In the Visualizations pane, click the Clustered bar chart icon (horizontal bars).
  • Drag Description to the Y-axis well.
  • Drag Revenue to the X-axis well.

The bar chart shows all products ranked by total revenue. To limit it to the top 10, apply a filter.

  • With the bar chart selected, expand the Filters pane on the right.
  • Under Filters on this visual, find Description.
  • Change the filter type to Top N.
  • Set it to show the Top 10 items.
  • Drag Revenue into the By value field of the filter.
  • Click Apply filter.

Your bar chart now shows exactly 10 bars representing the highest-revenue products.

Finally, create a Bar Chart showing revenue by country.

  • Click on an empty area of the canvas to deselect the previous chart.
  • In the Visualizations pane, click the Clustered bar chart icon again.
  • Drag Country to the Y-axis well.
  • Drag Revenue to the X-axis well.

You should now see three populated visuals on your canvas: a line chart trending over time, a bar chart of top products, and a bar chart of revenue by country. The United Kingdom will dominate the country chart since this is a UK-based retailer.

Charts showing no data?

Make sure you dragged the fields to the correct wells (X-axis vs Y-axis). If a chart is blank, check that the sales table is listed in the Data pane with all its columns visible.

If fields are missing from the Data pane, click Refresh in the Home tab to reload the data from MySQL.

Help me fix blank Power BI visuals

Format, save, and screenshot your dashboard

A polished dashboard needs a clear title and readable labels so anyone viewing it understands the story without explanation.

  • In the Insert tab of the ribbon, click Text box.
  • Type E-Commerce Sales Dashboard in the text box.
  • Increase the font size to 24 and bold it.
  • Drag the text box to the top of the canvas as your dashboard title.

Now format each visual with clear labels.

  • Click on your Line Chart to select it.
  • In the Visualizations pane, click the Format your visual icon (paint roller).
  • Expand General, then expand Title.
  • Set the title text to Monthly Revenue Trend.
  • Repeat for the top products chart: set its title to Top 10 Products by Revenue.
  • Repeat for the country chart: set its title to Revenue by Country.

Arrange your three visuals so they do not overlap and the dashboard looks balanced. Drag the corners of each chart to resize them as needed.

Before you save, what do you expect your dashboard to show? You should have three distinct charts, each populated with data, plus a title at the top.

  • Save your report by pressing Ctrl+S.
  • In the Save dialog, navigate to your project folder (the same folder containing ecommerce_analysis.ipynb).
  • Name the file ecommerce_dashboard.pbix and click Save.

Finally, take a screenshot of your completed dashboard for your GitHub README.

  • Press Windows+Shift+S to open the Snipping Tool.
  • Select the entire Power BI canvas showing all three visuals and the title.
  • Save the screenshot as dashboard_screenshot.png in your project folder.

Your Power BI report now shows three visuals: a line chart for monthly revenue, a bar chart for top 10 products, and a bar chart for revenue by country. All three are populated with data from your MySQL database.

Power BI not saving?

If the save fails, check that you have write permissions to the folder. Try saving to your Desktop first, then moving the file to your project folder.

Help me save my Power BI report

Your data has come full circle. Raw CSV rows became a cleaned DataFrame, then structured MySQL records, then SQL-powered insights, and now a visual dashboard anyone can read at a glance. Next up, you will extend this project with an advanced customer segmentation analysis.

Secret mission

Customer Segmentation with SQL Window Functions

Your business queries answer important questions, but they treat every customer the same. Can you classify customers into spending tiers using intermediate SQL techniques that separate junior analysts from the pack?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine, so there are no ongoing cloud costs. Decide whether to keep your resources for future practice, pause MySQL to free memory, or delete everything entirely.

Resources you used:

  • MySQL database ecommerce with the sales table on localhost (no cost, local storage only).
  • Project files on your Desktop: ecommerce_analysis.ipynb, business_queries.sql, customer_segmentation.sql, online_retail.csv, and ecommerce_dashboard.pbix.
  • Python packages installed: pandas and mysql-connector-python.

Keep everything running

No action needed. Choose this if you plan to keep practicing SQL queries against your dataset or want to extend your Power BI dashboard further.

  • The MySQL database uses minimal disk space and costs nothing to keep running locally.
  • Your project files remain on your Desktop ready for future use or GitHub upload.
  • The installed Python packages stay available for other data projects.

Pause - I'll come back to this later

Stop the MySQL service to free up memory while keeping all your data and files intact for later.

  • Open an administrator Command Prompt or PowerShell window.
  • Stop the MySQL service by running:
net stop MySQL

Service name may differ

The default MySQL service name is MySQL. If yours has a different name (like MySQL80), check the Windows Services app to find the exact name and replace it in the command.

  • To restart MySQL later, open an admin terminal and run:
net start MySQL
  • Your project files and database data remain on disk. Nothing is lost.

Delete - I don't want to use this again

Remove all project resources completely. This frees disk space and cleans up your environment.

  • Drop the MySQL database by opening a terminal and running:
mysql -u root -p -e "DROP DATABASE ecommerce;"
  • Enter your MySQL root password when prompted. This permanently deletes the ecommerce database and the sales table.
  • Delete your project files from the Desktop by running:
del %USERPROFILE%\Desktop\ecommerce_analysis.ipynb
del %USERPROFILE%\Desktop\business_queries.sql
del %USERPROFILE%\Desktop\customer_segmentation.sql
del %USERPROFILE%\Desktop\online_retail.csv
del %USERPROFILE%\Desktop\ecommerce_dashboard.pbix
  • Uninstall the Python packages you installed for this project by running:
pip uninstall pandas mysql-connector-python -y

Keep pandas if you do other data work

If you use pandas for other projects, skip the uninstall step above. Only remove packages you no longer need.

Nice Work!

Nice Work!

You just built a complete, end-to-end data analytics pipeline from raw data to executive-ready dashboard. That is the exact workflow entry-level data analyst roles expect you to demonstrate.

Here is what you accomplished:

  • Cleaned a real-world e-commerce dataset with pandas in Jupyter Notebook and loaded 397,000+ rows into a MySQL database programmatically from Python.
  • Wrote SQL queries answering real business questions using GROUP BY, ORDER BY, date functions, and aggregations to find top products, monthly revenue trends, and country performance.
  • Connected Power BI to your MySQL database and built an interactive dashboard with line charts, bar charts, and geographic breakdowns.
  • Secret Mission: Classified customers into spending tiers using CTEs and CASE statements, demonstrating intermediate SQL skills that separate you from other entry-level candidates.

Ready to quiz yourself?