From Messy CSV to Business Insight: A Small Python + SQL Analytics Pipeline

Knowing SQL and pandas separately isn't quite the same as completing an analysis from start to finish.

Give yourself a messy CSV and a business question, and suddenly there are decisions that syntax tutorials don't always cover:

What does one row represent?
Which missing values matter?
What actually counts as a repeat purchase?
Which metric answers the question?

Here's a small example of how I'd connect those pieces.

We'll go from:

CSV → validation → transformation → SQLite → SQL → visualisation

The question is deliberately narrow:

Which product categories generate the most repeat purchases?

1. Start with the question, not the code

Imagine we have an e-commerce orders CSV containing:

order_id
customer_id
order_date
category
amount

Before writing Python, we need to define what “repeat purchase” means.

For this example:

A repeat purchase is an order placed by a customer after their first purchase.

That definition seems simple, but it affects everything we do later.

2. Inspect the raw data

Let's load the file with pandas.

import pandas as pd

df = pd.read_csv("orders.csv")

print(df.head())
df.info()
print(df.isnull().sum())
print(df.duplicated().sum())

At this stage, I'm not trying to answer the business question.

I'm checking the data itself.

One particularly important question is:

What does one row represent?

If one row represents an entire order, order_id may need to be unique.

But if one row represents an order item, the same order_id could legitimately appear several times.

That's why I wouldn't immediately do this:

df.drop_duplicates(subset="order_id")

It could delete valid data.

For this example, we'll remove only exact duplicate rows:

df = df.drop_duplicates()

3. Create the repeat-purchase logic

First, convert the dates:

df["order_date"] = pd.to_datetime(
    df["order_date"],
    errors="coerce"
)

Then sort each customer's orders chronologically:

df = df.sort_values(
    ["customer_id", "order_date"]
)

Now number each customer's purchases:

df["purchase_number"] = (
    df.groupby("customer_id").cumcount() + 1
)

And flag everything after purchase number one:

df["is_repeat_purchase"] = (
    df["purchase_number"] > 1
)

This distinction matters.

If a customer places three orders, simply detecting that their ID appears three times would identify them as a repeat customer.

But their first order wasn't a repeat purchase.

Our new field captures that difference.

4. Load the result into SQLite

For a small project, SQLite gives us a convenient way to bring SQL into the workflow without setting up a database server.

import sqlite3

conn = sqlite3.connect("orders.db")

df.to_sql(
    "orders",
    conn,
    if_exists="replace",
    index=False
)

We now have:

CSV → pandas → SQLite

Let's query it.

5. Find the categories with the most repeat orders

SELECT
    category,
    COUNT(DISTINCT order_id) AS repeat_orders
FROM orders
WHERE is_repeat_purchase = 1
GROUP BY category
ORDER BY repeat_orders DESC;

That gives us repeat-order volume by category.

But there's a trap here.

Suppose Electronics has the most repeat orders.

Can we conclude that Electronics has the strongest repeat behaviour?

Not yet.

6. Question your first metric

A large category will naturally have more opportunities to generate repeat orders.

So I'd also want to compare the number of repeat orders with the total number of orders in each category.

SELECT
    category,

    COUNT(DISTINCT CASE
        WHEN is_repeat_purchase = 1
        THEN order_id
    END) AS repeat_orders,

    COUNT(DISTINCT order_id) AS total_orders,

    ROUND(
        100.0 *
        COUNT(DISTINCT CASE
            WHEN is_repeat_purchase = 1
            THEN order_id
        END)
        / COUNT(DISTINCT order_id),
        1
    ) AS repeat_order_pct

FROM orders
GROUP BY category
ORDER BY repeat_order_pct DESC;

Now we have two different perspectives:

Repeat-order volume tells us where the largest number of repeat orders occurs.

Repeat-order percentage gives us another way to compare repeat behaviour between differently sized categories.

Neither metric automatically tells the whole story.

That's the useful part of the exercise.

The job isn't just to make SQL return a number. It's to ask whether that number actually supports the conclusion we're about to make.

7. Visualise the result

We can bring the query result back into pandas:

query = """
SELECT
    category,
    COUNT(DISTINCT order_id) AS repeat_orders
FROM orders
WHERE is_repeat_purchase = 1
GROUP BY category
ORDER BY repeat_orders DESC;
"""

result = pd.read_sql(query, conn)

Then create a simple chart:

import matplotlib.pyplot as plt

result.plot(
    kind="bar",
    x="category",
    y="repeat_orders"
)

plt.title("Repeat Orders by Product Category")
plt.xlabel("Product Category")
plt.ylabel("Repeat Orders")
plt.tight_layout()

plt.show()

I prefer one chart with a clear purpose over a dashboard full of visuals that don't help answer the question.

What this small project actually demonstrates

The interesting part isn't the amount of code.

It's the reasoning connecting each step:

Business question → data grain → validation → transformation → SQL → metric → interpretation

That's something worth documenting in a portfolio project too.

Don't just show that you used pandas and SQL.

Explain why you defined a metric a certain way, what assumptions you made, what could make the result misleading, and what you'd investigate next.

For this project, my next questions would be:

Are customers repeating within the same category?
How many days pass between purchases?
Which categories generate the most repeat revenue?
Do discounts affect repeat behaviour?
Is a small group of frequent customers distorting the result?

Each question could lead to another iteration of the analysis.

Final thought

You don't need a huge tech stack to practise end-to-end analytics.

This example only used:

Python + pandas + SQLite + SQL + matplotlib

The important part is finishing the loop.

Start with a question. Understand the data. Define the metric. Build the analysis. Challenge your first conclusion. Then communicate what you found.

That's much closer to real analytical work than collecting isolated SQL queries or pandas methods.