Merge Multi-Source Retail Data with SQL

Integrate 3 data sources into SQLite and query across them with JOINs.

Introduction

30 Second Summary

Data rarely lives in one place. A sales team tracks transactions in a spreadsheet, product categories sit in a separate catalog, and regional groupings live in someone's head.

In this project, you will load a real UK retailer's transaction data, build two reference tables from scratch, merge everything into a SQLite database, and write SQL queries that answer questions no single dataset could handle on its own.

What You'll Build

You'll run a query that joins all three tables and instantly see a bar chart showing which product category dominates in each world region.

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

  • A multi-table SQLite database you can query to see revenue breakdowns by product category and world region.
  • Queries using window functions that rank top-performing categories within each region and show month-over-month revenue growth in color-coded charts.
  • An RFM customer segmentation that classifies every customer as a Champion, Loyal, Recent, At Risk, or Lost, with a two-panel visualization.
  • Secret Mission: Build a monthly cohort retention heatmap that reveals how well the business retains customers over time.

Are there any prerequisites?

Basic familiarity with Python and pandas is helpful. You should also have Jupyter Notebook ready to go in VS Code.

Before We Start

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

Set Up Your Environment

Before you can analyze anything, you need the right tools and data in place. This project uses four Python libraries that handle everything from reading Excel files to building charts.

In this step, you will install those libraries, download a real retail dataset, and create a Jupyter Notebook where all your analysis will live.

In this step, get ready to:
  • Install pandas, matplotlib, seaborn, and openpyxl.
  • Download the Online Retail dataset and set up your project folder.
  • Create a Jupyter notebook and verify all library imports.
Install the required Python libraries

You need four libraries that Python does not include out of the box. pandas handles data manipulation, matplotlib and seaborn handle visualization, and openpyxl lets pandas read Excel files.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Install all four libraries by running this command:
pip install pandas matplotlib seaborn openpyxl

What does this command do?

  • pip install downloads and installs Python packages from PyPI (the Python Package Index).
  • pandas is the core library for loading, cleaning, and analyzing tabular data.
  • matplotlib and seaborn create charts and statistical visualizations.
  • openpyxl is the engine pandas uses to read .xlsx files.
  • Verify all four packages installed correctly by running this command:
python -c "import pandas; import matplotlib; import seaborn; import openpyxl; print('All libraries ready')"
  • You should see All libraries ready printed in your terminal.

Seeing 'command not found'?

  • If pip is not recognized, try pip3 instead. Some systems use pip3 for Python 3.
  • If python is not recognized in the verify command, try python3 instead.

Why can't my terminal find pip or python?

Download the dataset and create your project folder

The dataset you will analyze contains real transaction records from a UK-based online retailer. It is hosted on the UCI Machine Learning Repository, a well-known source of public datasets for research.

  • Move to your Desktop and create a project folder by running these commands:
cd ~/Desktop
mkdir retail-analysis
cd retail-analysis
  • Open this link in your browser: UCI Online Retail Dataset.
  • Scroll down to the Dataset Files section and download the zip file containing Online Retail.xlsx.
  • Extract the zip file, then move Online Retail.xlsx into the retail-analysis folder on your Desktop.
  • Confirm the file is in place by running:
ls
  • You should see Online Retail.xlsx in the output.

Don't see the file?

  • Make sure you extracted the zip file first. The .xlsx file is inside the zip, not the zip itself.
  • Double-check you moved the file into the retail-analysis folder on your Desktop, not into a subfolder or your Downloads folder.

Help me find my downloaded dataset file.

What is this dataset?

The Online Retail dataset contains 541,909 transaction records from a UK-based non-store online retailer, covering December 2010 to December 2011. Each row records an individual product purchase with details like quantity, price, customer ID, and country.

The file is about 22.6 MB. You will clean this data and build two additional reference tables on top of it in the next step.

Create your notebook and verify imports

Now you need a Jupyter notebook where all your analysis code will live. You will create it inside VS Code and run a quick import cell to confirm every library loads correctly.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Select the retail-analysis folder on your Desktop, then click Open.

You should see Online Retail.xlsx listed in the Explorer sidebar on the left.

  • Press Cmd+Shift+P (macOS) or Ctrl+Shift+P (Windows) to open the Command Palette.
  • Type new jupyter and select Create: New Jupyter Notebook.
  • Save the new notebook immediately by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Name the file retail_analysis.ipynb and save it in the retail-analysis folder.

Your notebook is ready. Time to write your first cell and make sure all the libraries load.

  • Click inside the first empty cell of your notebook and paste the following code:
# Cell 1: Imports
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
print(pd.__version__)

What does this code do?

  • import pandas as pd loads the data analysis library and gives it the shorthand pd.
  • import sqlite3 loads Python's built-in SQLite module. You do not need to install it separately.
  • import matplotlib.pyplot as plt and import seaborn as sns load the two visualization libraries.
  • print(pd.__version__) prints the installed pandas version to confirm everything loaded correctly.

Before you run this cell, what do you expect to see as output? Think about what print(pd.__version__) would produce.

  • Run the cell by clicking the play button to the left of the cell, or by pressing Shift+Enter.

What should I see?

A version number prints below the cell, such as 2.2.3 or 3.0.5. The exact version depends on your Python version. Any version number means your imports are working.

Why might the version differ?

pandas 3.0.5 requires Python 3.11 or higher. If your Python is an earlier version, pip installs the latest compatible 2.x version instead. All the code in this project works identically on both.

Seeing a ModuleNotFoundError?

  • Your notebook might be using a different Python than the one where you ran pip install. Click the kernel name in the top-right corner of the notebook and select the Python environment where you installed the libraries.
  • If you are unsure which kernel to pick, try re-running the pip install command inside a notebook cell by adding ! before the command (e.g. !pip install pandas matplotlib seaborn openpyxl).

Why is my notebook not finding the libraries I installed?

✔️ Awesome, I've got everything!

Great. Double-check you have saved retail_analysis.ipynb.

ⓧ I'd like to double check the full code

Your notebook should have one cell with this exact code:

# Cell 1: Imports
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
print(pd.__version__)

Your environment is ready and every library imports cleanly. Next up, you will load the raw transaction data, clean it, and build two reference tables that turn a flat spreadsheet into a proper multi-table dataset.

Load, Clean, and Build Your Three Datasets

Your environment is ready and the dataset is downloaded. Now it is time to open that file and look at what you are working with.

The raw transaction data has over 541,000 rows, but it is messy. There are missing customer IDs, cancellation rows mixed in with real sales, and no way to group products by category or countries by region. Before you can ask interesting cross-source questions, you need to clean the transactions and build two reference tables that enrich the data.

In this step, get ready to:
  • Load and clean the raw transaction data, removing bad rows and creating a Revenue column.
  • Build a product categories table by matching product descriptions to category keywords.
  • Build a country regions table that maps each country to a geographic region and continent.
Load and explore the raw data

The Online Retail.xlsx file contains transactional data from a UK-based online retailer. Before cleaning anything, you need to understand what is wrong with it.

  • In retail_analysis.ipynb, create a new cell below your imports and load the dataset by pasting this code:
# Cell 2: Load and explore raw data
df = pd.read_excel('Online Retail.xlsx')
print(f"Dataset shape: {df.shape}")
print(f"\nColumn types:\n{df.dtypes}")
print(f"\nMissing values:\n{df.isnull().sum()}")
print(f"\nRows with missing CustomerID: {df['CustomerID'].isnull().sum()}")
cancellations = df[df['InvoiceNo'].astype(str).str.startswith('C')]
print(f"Cancellation rows: {len(cancellations)}")
print(f"Rows with Quantity <= 0: {len(df[df['Quantity'] <= 0])}")
print(f"Rows with UnitPrice <= 0: {len(df[df['UnitPrice'] <= 0])}")
df.head()

What does this code do?

  • pd.read_excel() reads the .xlsx file into a pandas DataFrame. This requires the openpyxl library you installed in Step 1.
  • The print statements inspect three specific data quality problems: missing CustomerID values, cancellation rows (where InvoiceNo starts with C), and rows with zero or negative quantities or prices.
  • df.head() displays the first five rows so you can see the raw column structure.
  • Run the cell.

You should see the dataset shape (541909 rows, 8 columns) and a breakdown of missing values, cancellation rows, and invalid entries.

What should I see?

The output tells you three things that need fixing. There are over 135,000 rows with missing CustomerID values. There are thousands of cancellation rows. There are rows with zero or negative Quantity or UnitPrice values.

These are the exact problems you will clean up in the next cell.

Seeing a FileNotFoundError?

  • Make sure Online Retail.xlsx is in the same folder as your notebook. The filename includes a space between "Online" and "Retail".
  • If you extracted the zip file, make sure you moved the .xlsx file out of any nested folder and into your project folder directly.

Why can't my notebook find Online Retail.xlsx?

Clean the transaction data

Now that you can see the problems, it is time to fix them. Each cleaning step targets one of the issues the exploration cell revealed.

  • Create a new cell below and clean the data by pasting this code:
# Cell 3: Clean transaction data
df_clean = df.dropna(subset=['CustomerID'])
df_clean = df_clean[~df_clean['InvoiceNo'].astype(str).str.startswith('C')]
df_clean = df_clean[(df_clean['Quantity'] > 0) & (df_clean['UnitPrice'] > 0)]
df_clean['Revenue'] = df_clean['Quantity'] * df_clean['UnitPrice']
df_clean['CustomerID'] = df_clean['CustomerID'].astype(int)
df_clean['InvoiceDate'] = pd.to_datetime(df_clean['InvoiceDate'])
print(f"Cleaned transactions: {df_clean.shape[0]} rows (removed {len(df) - df_clean.shape[0]})")
df_clean.head()

What does this code do?

  • dropna(subset=['CustomerID']) removes rows where the customer is unknown. You cannot analyze customer behavior without a customer ID.
  • The InvoiceNo filter removes cancellation rows. In this dataset, invoices starting with C indicate a cancellation.
  • The Revenue column is calculated by multiplying Quantity by UnitPrice. This is the value you will aggregate in later queries.
  • Run the cell.

You should see output showing roughly 397,000 cleaned rows and a preview table that now includes a Revenue column.

Seeing a NameError for df?

Make sure you ran Cell 2 (the load cell) before this cell. Jupyter Notebook cells must be run in order for variables to exist.

Help me debug a NameError in my Jupyter notebook

Build a product categories table

The cleaned transactions tell you what was sold, but not what kind of product it is. A product called "RED WOOLLY HOTTIE WHITE HEART" does not tell you at a glance that it belongs in the Textiles category.

You will build a lookup table that assigns every product to a category based on keywords in its description. This is a real feature engineering technique that analysts use to enrich flat data.

  • Create a new cell and define the category keyword rules by pasting this code:
# Cell 4: Build product categories table
category_rules = {
    'Bags': ['BAG', 'SHOPPER'],
    'Home Decor': ['CANDLE', 'T-LIGHT', 'HOLDER', 'LANTERN', 'FRAME'],
    'Seasonal': ['CHRISTMAS', 'XMAS', 'EASTER'],
    'Kitchen': ['CAKE', 'NAPKIN', 'BAKING', 'BOWL', 'MUG', 'CUP', 'PLATE', 'BOTTLE'],
    'Storage': ['BOX', 'TIN', 'JAR', 'BASKET'],
    'Stationery': ['CARD', 'WRAP', 'RIBBON', 'TAG', 'NOTEBOOK'],
    'Toys': ['TOY', 'GAME', 'DOLL', 'PUZZLE'],
    'Garden': ['GARDEN', 'PLANT', 'FLOWER', 'SEED'],
    'Jewelry': ['NECKLACE', 'BRACELET', 'EARRING', 'RING'],
    'Textiles': ['CUSHION', 'TOWEL', 'APRON', 'BLANKET'],
}

What does this dictionary do?

Each key is a category name and each value is a list of keywords. If a product description contains any of those keywords, the product gets that category. Products that do not match any keyword will fall into an "Other" category.

This keyword-matching approach is simple but effective. It is exactly what analysts do when they need to categorize unstructured text without a machine learning model.

  • Still in the same cell, paste the code below right after the closing } of the category_rules dictionary to extract products and apply the categories:
products = df_clean[['StockCode', 'Description']].drop_duplicates(subset='StockCode')
products = products.dropna(subset=['Description'])
products['Description_upper'] = products['Description'].str.upper()

def assign_category(desc):
    for category, keywords in category_rules.items():
        if any(kw in desc for kw in keywords):
            return category
    return 'Other'

products['Category'] = products['Description_upper'].apply(assign_category)
products = products[['StockCode', 'Description', 'Category']]
print(f"Product categories table: {len(products)} products")
print(f"\nCategory distribution:\n{products['Category'].value_counts()}")

What does this code do?

  • The first three lines extract a unique list of products from the cleaned data, drop any without descriptions, and convert the text to uppercase for case-insensitive matching.
  • assign_category() loops through each category's keyword list. If any keyword appears in the product description, it returns that category. If nothing matches, it returns 'Other'.
  • .apply(assign_category) runs the function on every row, assigning each product its category. The final DataFrame keeps only StockCode, Description, and Category.
  • Run the cell.

You should see a count of roughly 3,600 products and a breakdown showing how many products landed in each category. The "Other" category will be the largest because many product descriptions do not match any keyword.

All products showing as Other?

Make sure the category_rules dictionary and the assign_category() function are both in the same cell. If you put them in separate cells and only ran one, the function will not have access to the rules.

Help me debug my product category matching

Build a country regions table

The transactions include a Country column, but individual country names make it hard to see regional patterns. Grouping "France", "Germany", and "Belgium" under "Western Europe" lets you compare entire regions at once.

You will create a manual mapping dictionary. This is common in real-world analysis when no reference API exists and the list is small enough to define by hand.

  • Create a new cell and start the country mapping with the European countries by pasting this code:
# Cell 5: Build country regions table
country_data = {
    'United Kingdom': ('Western Europe', 'Europe'),
    'Germany': ('Western Europe', 'Europe'),
    'France': ('Western Europe', 'Europe'),
    'Spain': ('Southern Europe', 'Europe'),
    'Portugal': ('Southern Europe', 'Europe'),
    'Italy': ('Southern Europe', 'Europe'),
    'Belgium': ('Western Europe', 'Europe'),
    'Netherlands': ('Western Europe', 'Europe'),
    'Switzerland': ('Western Europe', 'Europe'),
    'Austria': ('Western Europe', 'Europe'),
    'Norway': ('Northern Europe', 'Europe'),
    'Sweden': ('Northern Europe', 'Europe'),
    'Denmark': ('Northern Europe', 'Europe'),
    'Finland': ('Northern Europe', 'Europe'),
    'Iceland': ('Northern Europe', 'Europe'),
    'Poland': ('Eastern Europe', 'Europe'),
    'Czech Republic': ('Eastern Europe', 'Europe'),
    'Lithuania': ('Eastern Europe', 'Europe'),
    'EIRE': ('Western Europe', 'Europe'),
    'Channel Islands': ('Western Europe', 'Europe'),
    'Cyprus': ('Southern Europe', 'Europe'),
    'Malta': ('Southern Europe', 'Europe'),
    'Greece': ('Southern Europe', 'Europe'),
}

What is this mapping?

Each key is a country name that matches the Country column in the transaction data exactly. Each value is a tuple of (Region, Continent). Notice that EIRE is how the dataset spells Ireland.

  • Still in the same cell, paste the remaining countries and the DataFrame creation code right below the closing }:
country_data.update({
    'Australia': ('Oceania', 'Oceania'),
    'Japan': ('East Asia', 'Asia'),
    'Singapore': ('Southeast Asia', 'Asia'),
    'Hong Kong': ('East Asia', 'Asia'),
    'United Arab Emirates': ('Middle East', 'Asia'),
    'Israel': ('Middle East', 'Asia'),
    'Lebanon': ('Middle East', 'Asia'),
    'Bahrain': ('Middle East', 'Asia'),
    'Saudi Arabia': ('Middle East', 'Asia'),
    'USA': ('North America', 'Americas'),
    'Canada': ('North America', 'Americas'),
    'Brazil': ('South America', 'Americas'),
    'RSA': ('Southern Africa', 'Africa'),
    'Nigeria': ('West Africa', 'Africa'),
    'Unspecified': ('Unknown', 'Unknown'),
    'European Community': ('Europe (unspecified)', 'Europe'),
})
countries = pd.DataFrame([
    {'Country': k, 'Region': v[0], 'Continent': v[1]}
    for k, v in country_data.items()
])
print(f"Country regions table: {len(countries)} countries")
countries.head(10)

What does this code do?

  • country_data.update() adds the non-European countries to the mapping you started above.
  • The list comprehension unpacks each key-value pair into a dictionary with Country, Region, and Continent columns. pd.DataFrame() turns the list of dicts into a proper table.
  • Run the cell.

You should see "Country regions table: 39 countries" and a preview of the first 10 rows with Country, Region, and Continent columns.

Seeing fewer than 39 countries?

Make sure both the initial country_data dictionary and the .update() call are in the same cell. If you accidentally put them in separate cells, rerun both.

Help me figure out why my country regions table is missing entries

Before you move on, take a moment to predict: how many of your three DataFrames do you think are ready to load into a database?

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook.

ⓧ I'd like to double check the full code

Scroll up through your notebook and confirm these cells are present, in order:

  • Cell 1: Imports containing your import pandas as pd, import sqlite3, import matplotlib.pyplot as plt, and import seaborn as sns.
  • Cell 2: Load and explore raw data loading Online Retail.xlsx and printing shape, missing values, cancellation count, and invalid row counts.
  • Cell 3: Clean transaction data creating df_clean by removing nulls, cancellations, and invalid rows, then adding a Revenue column.
  • Cell 4: Build product categories table with the category_rules dictionary, the assign_category() function, and the products DataFrame.
  • Cell 5: Build country regions table with the country_data dictionary (39 countries) and the countries DataFrame.

You now have three DataFrames: cleaned transactions, product categories, and country regions. Right now they are separate tables floating in your notebook. In the next step, you will load all three into a SQLite database and connect them with SQL JOINs to unlock questions that no single table can answer alone.

Load All Three Tables into SQLite

You now have three clean DataFrames sitting in memory: transactions, product categories, and country regions. Each one holds useful data on its own, but the real power comes from connecting them.

Loading all three into a SQLite database creates a proper relational model. Once they share the same database, you can write SQL queries that JOIN across them to answer questions no single table could handle alone.

In this step, get ready to:
  • Load all three DataFrames into a SQLite database as separate tables.
  • Verify the tables exist and check their row counts.
  • Run a test JOIN query that pulls columns from all three tables at once.
Connect to SQLite and load all three tables

Python's built-in sqlite3 module lets you create a database file and write to it without installing any server software. The pandas to_sql() method handles converting each DataFrame into a database table automatically.

  • Create a new cell in your notebook (Cell 6) and paste the following code to connect to SQLite and load all three DataFrames as tables:
# Cell 6: Load all three tables into SQLite
conn = sqlite3.connect('retail.db')
df_clean.to_sql('sales', conn, if_exists='replace', index=False)
products.to_sql('product_categories', conn, if_exists='replace', index=False)
countries.to_sql('country_regions', conn, if_exists='replace', index=False)

tables = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", conn)
print(f"Tables in database: {tables['name'].tolist()}")
for t in tables['name']:
    count = pd.read_sql(f"SELECT COUNT(*) as cnt FROM {t}", conn)
    print(f"  {t}: {count['cnt'][0]} rows")

What does this code do?

  • sqlite3.connect('retail.db') creates a new SQLite database file called retail.db in your project folder. If it already exists, it opens it.
  • to_sql() writes each DataFrame as a SQL table. The if_exists='replace' flag drops and recreates the table if you rerun the cell, so you can safely iterate.
  • The sqlite_master query is SQLite's built-in catalog. Querying it confirms which tables are in the database.
  • The for loop runs a COUNT(*) query on each table so you can see the row counts and verify the data loaded correctly.
  • Run the cell.

You should see three tables listed: sales, product_categories, and country_regions. The sales table has roughly 397K rows, product_categories has around 3,600 rows, and country_regions has 38 rows.

Not seeing three tables?

  • Make sure you ran all the previous cells first (Cells 1 through 5). The DataFrames df_clean, products, and countries must exist in memory before this cell can load them.
  • If you see a NameError for one of the DataFrames, click **Run All** in the notebook toolbar to re-execute every cell from the top.
  • Still stuck? Why is my SQLite table not loading from my DataFrame?

Why SQLite instead of PostgreSQL or MySQL?

SQLite is a file-based database that ships with Python's built-in sqlite3 module. There is no server to install or configure. You get a single .db file that supports full SQL including JOINs, CTEs, and window functions.

The SQL syntax you write here transfers directly to PostgreSQL and MySQL when you work with production databases later.

Test a three-table JOIN

The tables are loaded, but you need proof that they can talk to each other. An INNER JOIN connects rows from two tables where a shared column matches. Chaining two INNER JOINs connects all three tables in a single query.

Before you run this test, think about what columns you expect to see. The query pulls from sales, product_categories, and country_regions. What will a single row that combines all three look like?

  • Create a new cell (Cell 7) and paste the following three-table JOIN query:
# Cell 7: Test three-table JOIN
test_join = pd.read_sql("""
SELECT s.InvoiceNo, s.Description, s.Revenue,
       pc.Category,
       cr.Region, cr.Continent
FROM sales s
INNER JOIN product_categories pc ON s.StockCode = pc.StockCode
INNER JOIN country_regions cr ON s.Country = cr.Country
LIMIT 5
""", conn)
print("Three-table JOIN works:")
test_join

What does this query do?

  • FROM sales s starts with the transaction table and gives it the alias s so you can reference its columns as s.Revenue, s.InvoiceNo, etc.
  • The first INNER JOIN matches each sale to its product category using StockCode. Only sales with a matching StockCode in product_categories appear in the result.
  • The second INNER JOIN matches each sale to its country's region using Country. This adds Region and Continent columns to each row.
  • LIMIT 5 keeps the output small for a quick sanity check.
  • Run the cell.

You should see a DataFrame with 5 rows. Each row combines transaction data (InvoiceNo, Description, Revenue) with its product Category and its country's Region and Continent. Three separate data sources, one unified row.

Seeing zero rows or an error?

  • If the result is empty, check that Cell 6 ran without errors. The conn variable must point to a populated database.
  • If you see a column name mismatch error, double check that the JOIN conditions use s.StockCode = pc.StockCode and s.Country = cr.Country. Column names are case-sensitive in the query.
  • Still stuck? Why is my three-table INNER JOIN returning zero rows or an error in SQLite?

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook before moving on.

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm these cells are present, in order:

  • Cell 1: Imports (pandas, sqlite3, matplotlib, seaborn).
  • Cell 2: Load and explore raw data.
  • Cell 3: Clean transaction data (creates df_clean).
  • Cell 4: Build product categories table (creates products).
  • Cell 5: Build country regions table (creates countries).
  • Cell 6: Load all three tables into SQLite and verify row counts.
  • Cell 7: Test three-table JOIN (shows 5 rows with columns from all three tables).

If any cell is missing, scroll up to the matching substep and add it. Save your notebook after confirming.

Your relational data model is live. Three separate data sources are now connected through a single SQLite database. Next up, you will write JOIN queries that answer cross-source business questions that no single table could answer alone.

Answer Cross-Source Business Questions with JOINs

Your SQLite database is loaded with three tables and the JOIN between them works. Now it's time to put those joins to work answering real business questions.

With a single flat table, you could only answer "what are the top-selling products?" With three joined tables, you can ask richer questions that span data sources. "What is the top product category in Europe?" or "Which region drives the most revenue?" These are the kinds of questions a stakeholder actually asks.

In this step, get ready to:
  • Write JOIN queries that calculate total revenue by product category and by world region.
  • Visualize both breakdowns as horizontal bar charts.
  • Combine all three tables to explore category performance across regions.
Query revenue by product category

Your first cross-source question is: "Which product categories bring in the most revenue?" The answer lives across two tables. The sales table has the revenue numbers, and the product_categories table knows which category each product belongs to.

  • Create a new cell in your notebook (Cell 8) and paste the following code to query category revenue and visualize it:
# Cell 8: Revenue by product category (JOIN query)
query_by_category = """
SELECT pc.Category,
       ROUND(SUM(s.Revenue), 2) as TotalRevenue,
       COUNT(DISTINCT s.InvoiceNo) as TotalOrders
FROM sales s
INNER JOIN product_categories pc ON s.StockCode = pc.StockCode
GROUP BY pc.Category
ORDER BY TotalRevenue DESC
"""
cat_revenue = pd.read_sql(query_by_category, conn)

fig, ax = plt.subplots(figsize=(10, 6))
ax.barh(cat_revenue['Category'], cat_revenue['TotalRevenue'])
ax.set_xlabel('Total Revenue (GBP)')
ax.set_title('Revenue by Product Category')
ax.invert_yaxis()
plt.tight_layout()
plt.show()

What does this code do?

  • The INNER JOIN connects each sale to its product category by matching on StockCode. Only sales that have a matching category are included.
  • GROUP BY pc.Category collapses all rows into one row per category, so SUM(s.Revenue) gives the total revenue for each.
  • ax.invert_yaxis() flips the bar chart so the highest-revenue category sits at the top, making it easy to read as a leaderboard.
  • Run the cell.

You should see a horizontal bar chart with product categories on the y-axis and total revenue in GBP on the x-axis. The highest-revenue category sits at the top.

Seeing an empty chart or an error?

  • If you see a OperationalError about a missing table, make sure you ran Cell 6 (which loads all three tables into SQLite) before this cell.
  • If the chart appears but has no bars, check that your conn variable is still connected to retail.db. If you restarted your kernel, re-run Cell 6 to recreate the connection.
  • Still stuck? Why is my category revenue query returning empty results or an error?
Query revenue by region

Your second cross-source question is: "Which world region generates the most revenue?" This time the JOIN connects sales to country_regions on the Country column.

  • Create a new cell (Cell 9) and paste the following code to query regional revenue and chart it:
# Cell 9: Revenue by region (JOIN query, excluding UK)
query_by_region = """
SELECT cr.Region,
       cr.Continent,
       ROUND(SUM(s.Revenue), 2) as TotalRevenue,
       COUNT(DISTINCT s.CustomerID) as UniqueCustomers
FROM sales s
INNER JOIN country_regions cr ON s.Country = cr.Country
WHERE s.Country != 'United Kingdom'
GROUP BY cr.Region, cr.Continent
ORDER BY TotalRevenue DESC
"""
region_revenue = pd.read_sql(query_by_region, conn)

fig, ax = plt.subplots(figsize=(10, 6))
bars = ax.barh(region_revenue['Region'], region_revenue['TotalRevenue'])
ax.set_xlabel('Total Revenue (GBP)')
ax.set_title('Revenue by Region (excl. UK)')
ax.invert_yaxis()
plt.tight_layout()
plt.show()

What does this query do?

  • The INNER JOIN on Country enriches each sale with its region and continent from the country_regions lookup table.
  • The WHERE s.Country != 'United Kingdom' filter excludes the UK because this is a UK-based retailer. UK sales would dwarf every other region and flatten the chart.
  • COUNT(DISTINCT s.CustomerID) counts unique customers per region, giving you a sense of the customer base alongside revenue.
  • Run the cell.

You should see a horizontal bar chart showing revenue by world region, with the UK excluded. Western Europe likely leads, followed by Northern and Southern Europe.

Chart only shows one or two regions?

  • Make sure Cell 5 (country regions table) mapped all countries in the dataset. If any countries are missing from the country_data dictionary, their sales won't appear in the JOIN results.
  • Check that Cell 6 loaded the country_regions table into SQLite. Re-run Cell 6 if your kernel restarted.
  • Need help? My region revenue chart only shows a few regions instead of the full list. How can I debug this?

Why INNER JOIN instead of LEFT JOIN?

An INNER JOIN only keeps rows that match in both tables. A LEFT JOIN would keep every sale even if it has no matching category or region.

For this analysis, unmatched rows would add noise (sales with no category, or countries with no region). Dropping them with INNER JOIN keeps the results clean and meaningful.

Combine all three tables for category-by-region analysis

You've seen revenue by category and revenue by region separately. The natural next question is: "What is the top-selling category in each region?" This requires data from all three tables at once.

  • Create a new cell (Cell 10) and paste the following three-table JOIN query:
# Cell 10: Category revenue by region (three-table JOIN)
query_cat_region = """
SELECT cr.Region,
       pc.Category,
       ROUND(SUM(s.Revenue), 2) as TotalRevenue
FROM sales s
INNER JOIN product_categories pc ON s.StockCode = pc.StockCode
INNER JOIN country_regions cr ON s.Country = cr.Country
WHERE s.Country != 'United Kingdom'
GROUP BY cr.Region, pc.Category
ORDER BY cr.Region, TotalRevenue DESC
"""
cat_region = pd.read_sql(query_cat_region, conn)
print("Category revenue by region (first 15 rows):")
cat_region.head(15)

What does this query do?

  • This query joins all three tables: sales to product_categories on StockCode, and sales to country_regions on Country.
  • GROUP BY cr.Region, pc.Category creates one row for every region-category pair, with total revenue summed up across all matching sales.
  • ORDER BY cr.Region, TotalRevenue DESC sorts alphabetically by region, then highest revenue first within each region.

Before you run this cell, think about what you expect to see. Will the output tell you which single category is the top seller in each region?

  • Run the cell.

You should see a DataFrame with 15 rows showing Region, Category, and TotalRevenue columns. The data is sorted by region, with categories listed from highest to lowest revenue within each region.

Now look closely at the output. You can see all categories for each region, sorted by revenue. But if a stakeholder asks "give me just the top category per region," you have a problem.

GROUP BY can aggregate rows into groups. It cannot rank rows within those groups. To extract the single top category per region, you would need a nested subquery for each region, or a separate query for each one. That's messy, slow, and brittle.

Why is GROUP BY not enough here?

GROUP BY collapses rows into one per group and applies aggregate functions like SUM() or COUNT(). It tells you "how much revenue did each category earn in each region?" perfectly well.

But ranking within groups is a different operation. You need to compare rows within each region and assign a rank based on revenue. That's exactly what window functions are designed for, and you'll use them in the next step.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm you have these three new cells in order, after Cell 7:

  • Cell 8: Revenue by product category. Defines query_by_category, reads it into cat_revenue, and produces a horizontal bar chart with ax.barh().
  • Cell 9: Revenue by region (excluding UK). Defines query_by_region, reads it into region_revenue, and produces a horizontal bar chart.
  • Cell 10: Category revenue by region. Defines query_cat_region, reads it into cat_region, and prints the first 15 rows.

You now have two bar charts and a clear picture of which categories and regions drive revenue. But you've also hit a wall: GROUP BY alone can't rank categories within each region. In the next step, you'll break through that wall with window functions.

Use Window Functions and RFM Segmentation

In the previous step, you queried your three-table JOIN and visualized revenue by category and by region. You also saw the raw breakdown of category revenue within each region, but GROUP BY alone could not rank the top category within each region.

Window functions solve that problem. They compute values across related rows without collapsing the result set. In this step, you will use RANK(), LAG(), and NTILE() to rank categories per region, track month-over-month growth, and segment your customers.

In this step, get ready to:
  • Rank the top product category per region using a CTE with RANK().
  • Track month-over-month revenue growth with LAG() and a color-coded bar chart.
  • Segment customers into Champions, Loyal, Recent, At Risk, and Lost using RFM analysis with NTILE().
Rank the top category per region with RANK()

A window function computes a value across a set of rows related to the current row. Unlike GROUP BY, it does not collapse the rows into a single output row.

Before you run this, think about the last step: what would it take to find the single top category for each region using only GROUP BY? You would need nested subqueries or a separate query per region. Do you think the window function approach will be simpler?

  • Create a new cell in your notebook and paste the following code to rank categories within each region using a CTE with RANK():
# Top category per region with RANK window function
query_ranked_cat = """
WITH category_region_revenue AS (
    SELECT cr.Region,
           pc.Category,
           ROUND(SUM(s.Revenue), 2) as TotalRevenue
    FROM sales s
    INNER JOIN product_categories pc ON s.StockCode = pc.StockCode
    INNER JOIN country_regions cr ON s.Country = cr.Country
    WHERE s.Country != 'United Kingdom'
    GROUP BY cr.Region, pc.Category
),
ranked AS (
    SELECT Region,
           Category,
           TotalRevenue,
           RANK() OVER (PARTITION BY Region ORDER BY TotalRevenue DESC) as category_rank
    FROM category_region_revenue
)
SELECT Region, Category, TotalRevenue, category_rank
FROM ranked
WHERE category_rank <= 3
ORDER BY Region, category_rank
"""
top_cat_region = pd.read_sql(query_ranked_cat, conn)
print(f"Top 3 categories per region ({len(top_cat_region)} rows):")
top_cat_region

What does this query do?

This query uses two CTEs chained together. The first CTE, category_region_revenue, runs the same three-table JOIN from the previous step to calculate total revenue per category per region.

The second CTE, ranked, applies RANK() OVER (PARTITION BY Region ORDER BY TotalRevenue DESC). This ranks categories independently within each region. The final SELECT filters to keep only the top 3 per region.

  • Run the cell.

You should see a DataFrame with Region, Category, TotalRevenue, and category_rank columns. Each region lists its top 3 revenue-generating categories, ranked 1 through 3. One query, all regions at once.

Why window functions instead of nested subqueries?

Without window functions, you would need a correlated subquery for each region to find its top category. That approach is harder to read, harder to maintain, and slower on large datasets.

RANK() OVER (PARTITION BY Region) handles all regions in a single pass. The SQL engine partitions the rows by region, ranks within each partition, and returns every row with its rank attached.

Seeing an empty DataFrame?

Make sure the three tables (sales, product_categories, country_regions) are still loaded in your retail.db database. If you restarted the notebook kernel, re-run the cells from Step 3 that create the conn connection and load the tables.

Help me debug my RANK() window function query.

Track month-over-month revenue growth with LAG()

LAG() is a window function that accesses a value from the previous row in the result set. It is perfect for comparing each month's revenue to the one before it.

  • Create a new cell and paste the following query to calculate month-over-month revenue growth:
# Month-over-month revenue growth using LAG window function
query_growth = """
WITH monthly_rev AS (
    SELECT strftime('%Y-%m', InvoiceDate) as Month,
           ROUND(SUM(Revenue), 2) as MonthlyRevenue
    FROM sales
    GROUP BY Month
    ORDER BY Month
)
SELECT Month,
       MonthlyRevenue,
       LAG(MonthlyRevenue) OVER (ORDER BY Month) as PrevMonthRevenue,
       ROUND(
           (MonthlyRevenue - LAG(MonthlyRevenue) OVER (ORDER BY Month))
           / LAG(MonthlyRevenue) OVER (ORDER BY Month) * 100,
           1
       ) as GrowthPct
FROM monthly_rev
"""
growth = pd.read_sql(query_growth, conn)

What does this query do?

The CTE monthly_rev groups all sales by month using strftime('%Y-%m', InvoiceDate) and sums the revenue. The outer SELECT then uses LAG(MonthlyRevenue) OVER (ORDER BY Month) to peek at the previous month's revenue.

GrowthPct calculates the percentage change: (this month minus last month) divided by last month, times 100. The first row's growth will be NULL because there is no previous month to compare against.

  • Now visualize the growth as a color-coded bar chart by adding this code in the same cell, below the line you just pasted:
# Color-coded bar chart: green for positive, red for negative growth
fig, ax = plt.subplots(figsize=(12, 5))
colors = ['#2ecc71' if x >= 0 else '#e74c3c' for x in growth['GrowthPct'].fillna(0)]
ax.bar(growth['Month'], growth['GrowthPct'].fillna(0), color=colors)
ax.set_xlabel('Month')
ax.set_ylabel('Growth (%)')
ax.set_title('Month-over-Month Revenue Growth')
ax.axhline(y=0, color='black', linewidth=0.5)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

What does this visualization do?

The colors list uses a conditional expression to assign green (#2ecc71) to months with positive growth and red (#e74c3c) to months with negative growth. The black horizontal line at zero makes it easy to spot which months dipped below baseline.

  • Run the cell.

You should see a bar chart where each bar represents one month's revenue growth percentage. Green bars point up (positive growth), red bars point down (negative growth). Notice the seasonal spike around November, the holiday shopping rush.

Chart showing all zeros?

Check that the growth DataFrame has values in the GrowthPct column. Run growth.head() in a new cell. The first row should show None for GrowthPct (no previous month), and subsequent rows should have numbers.

Help me debug my LAG() growth chart.

Segment customers with RFM analysis

RFM stands for Recency, Frequency, and Monetary. It is a customer segmentation framework that scores each customer on three dimensions: how recently they purchased, how often they purchase, and how much they spend.

You will use NTILE(4) to split customers into four equal-sized buckets for each dimension, then classify them into segments with a CASE statement.

  • Create a new cell and paste the following RFM segmentation query. This is the longest query in the project because it chains two CTEs and a final SELECT:
# RFM customer segmentation
query_rfm = """
WITH customer_metrics AS (
    SELECT CustomerID,
           julianday('2011-12-10') - julianday(MAX(InvoiceDate)) as Recency,
           COUNT(DISTINCT InvoiceNo) as Frequency,
           ROUND(SUM(Revenue), 2) as Monetary
    FROM sales
    GROUP BY CustomerID
),
rfm_scores AS (
    SELECT CustomerID,
           Recency,
           Frequency,
           Monetary,
           NTILE(4) OVER (ORDER BY Recency DESC) as R_Score,
           NTILE(4) OVER (ORDER BY Frequency ASC) as F_Score,
           NTILE(4) OVER (ORDER BY Monetary ASC) as M_Score
    FROM customer_metrics
)
SELECT CustomerID,
       ROUND(Recency, 0) as Recency,
       Frequency,
       Monetary,
       R_Score,
       F_Score,
       M_Score,
       CASE
           WHEN R_Score >= 3 AND F_Score >= 3 AND M_Score >= 3 THEN 'Champions'
           WHEN R_Score >= 3 AND F_Score >= 2 THEN 'Loyal'
           WHEN R_Score >= 3 THEN 'Recent'
           WHEN F_Score >= 3 THEN 'At Risk'
           ELSE 'Lost'
       END as Segment
FROM rfm_scores
"""
rfm = pd.read_sql(query_rfm, conn)

What does each part of this query do?

  • customer_metrics CTE: Calculates each customer's recency (days since their last purchase relative to 2011-12-10, one day after the dataset's last transaction), purchase frequency (distinct invoices), and total monetary value.
  • rfm_scores CTE: Uses NTILE(4) to divide customers into 4 equal buckets per dimension. Recency is ordered DESC so that lower recency (more recent) gets a higher score.
  • CASE statement: Combines the three scores into named segments. Customers scoring high on all three are Champions. Those with high recency and moderate frequency are Loyal. High recency alone is Recent. High frequency but low recency means At Risk. Everyone else is Lost.
  • Now add the aggregation and two-panel visualization in the same cell, below the code you just pasted:
# Summarize and visualize customer segments
segment_summary = rfm.groupby('Segment').agg(
    Customers=('CustomerID', 'count'),
    AvgRevenue=('Monetary', 'mean')
).reset_index().sort_values('Customers', ascending=False)

fig, axes = plt.subplots(1, 2, figsize=(14, 5))

axes[0].bar(segment_summary['Segment'], segment_summary['Customers'], color='steelblue')
axes[0].set_title('Customers per Segment')
axes[0].tick_params(axis='x', rotation=45)
axes[0].set_ylabel('Count')

axes[1].bar(segment_summary['Segment'], segment_summary['AvgRevenue'], color='darkorange')
axes[1].set_title('Average Revenue per Segment')
axes[1].tick_params(axis='x', rotation=45)
axes[1].set_ylabel('Avg Revenue (GBP)')

plt.tight_layout()
plt.show()

What does this visualization show?

segment_summary groups the RFM results by segment and counts the customers in each group. It also calculates the average revenue per segment.

The two-panel figure puts the story side by side. The left panel shows how many customers fall into each segment. The right panel shows how much revenue each segment generates on average. Champions should have far higher average revenue than Lost customers.

Before you run this cell, what do you expect to see? Which segment do you think will have the most customers? Which will have the highest average revenue?

  • Run the cell.

You should see a two-panel bar chart. The left panel shows customer count per segment, and the right panel shows average revenue per segment. Champions are the smallest group but the highest spenders.

Seeing a NameError for rfm?

Make sure the query_rfm definition and the rfm = pd.read_sql(query_rfm, conn) line are in the same cell as the visualization code, or that you ran the query cell first. If you split them into separate cells, run them in order from top to bottom.

Help me debug my RFM segmentation query.

Close the database connection

Your analysis is complete. Closing the connection releases the database file so other processes can access it.

  • Create a final cell and close the connection by running:
# Close the database connection
conn.close()
print("Database connection closed. Analysis complete!")
  • Run the cell. You should see Database connection closed. Analysis complete! printed.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook.

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm these cells are present in order, from top to bottom:

  • Cells 1-10 from the previous steps (imports, data loading, cleaning, building reference tables, loading into SQLite, JOINs, and visualizations).
  • Cell 11: Top category per region with RANK() window function.
  • Cell 12: Month-over-month revenue growth using LAG() with the color-coded bar chart.
  • Cell 13: RFM customer segmentation query using NTILE(4) and CASE, with the two-panel bar chart.
  • Cell 14: conn.close() and the completion message.

You have ranked categories per region, tracked month-over-month growth, and segmented your entire customer base into actionable groups. These are the exact SQL patterns data analysts use in production. Next up, there is an optional Secret Mission that takes your analysis even further.

Secret mission

Build a Cohort Retention Heatmap

Cohort retention is one of the most powerful metrics in business analytics. Build a monthly retention heatmap that reveals what percentage of customers come back in each month after their first purchase. This is a portfolio showpiece that tells a product manager instantly whether the business is keeping or losing its customers.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your files and installed packages, or remove them entirely.

Resources you used:

  • Your project folder on the Desktop (contains retail_analysis.ipynb, Online Retail.xlsx, and retail.db).
  • Installed Python packages: pandas, matplotlib, seaborn, and openpyxl.

Keep everything running

No action needed. Choose this if you plan to revisit or extend this analysis.

  • Your project folder stays on the Desktop with all notebooks, data, and the SQLite database intact.
  • The Python packages remain installed and available for other projects.

Pause - I'll come back to this later

There are no running processes or servers to stop. Everything in this project is file-based, so pausing and keeping are the same.

  • Your notebook, dataset, and database will be waiting in your project folder whenever you return.

Delete - I don't want to use this again

Remove all project files and optionally uninstall the Python packages.

  • Delete your project folder. Open your terminal and navigate to your Desktop by running:
cd ~/Desktop
  • Delete the project folder by running the following command, replacing the folder name with whatever you named your project folder:

macOS

rm -rf your-project-folder-name

Windows

rmdir /s /q your-project-folder-name
  • This removes retail_analysis.ipynb, Online Retail.xlsx, retail.db, and any other files in that folder.
  • Optionally, uninstall the Python packages you installed for this project by running:
pip uninstall pandas matplotlib seaborn openpyxl -y

Keep the packages if you do data work

These are among the most widely used Python libraries for data analysis and visualization. If you plan to do any future data work with Python, keeping them installed saves you from reinstalling later.

Nice Work!

Nice Work!

You just built a complete multi-source data analysis notebook from scratch. You took raw, messy retail transactions, engineered two reference tables, loaded everything into a SQLite database, and queried across all three tables to uncover business insights that no single dataset could reveal.

You've learned how to:

  • Clean real-world transaction data, build reference tables using keyword matching and manual mapping, and load all three into a relational SQLite database.
  • Write multi-table JOIN queries with CTEs and window functions like RANK, LAG, and NTILE to answer cross-source business questions and rank categories within regions.
  • Perform RFM customer segmentation to classify customers into Champions, Loyal, Recent, At Risk, and Lost segments, and visualize month-over-month revenue growth with color-coded charts.
  • Secret Mission: Completed an optional challenge to push your data analysis skills further.

Ready to quiz yourself?