generate_report.py

# -*- coding: utf-8 -*-
"""generate_report.ipynb

Automatically generated by Colab.

Original file is located at
    https://colab.research.google.com/drive/1EZeyqV2DrFUMrFB1R5mQySchtesIdWJu

# **E-Commerce Data Export & Reporting**

## **Introduction**

This assignment focuses on using Python and SQL to analyze and report on e-commerce data. The project begins with importing raw CSV files containing customer, product, and order information and organizing them into a structured SQLite database.

Once the database is created, a series of SQL queries and Python functions are used to analyze sales performance, customer behavior, and product trends. The assignment also emphasizes file I/O operations by exporting query results to CSV files and generating a text-based summary report with key business metrics.

Overall, this project demonstrates how Python can be used to manage data pipelines end-to-end, from data ingestion and database creation to analysis, reporting, and file generation.

## **Part 3: File I/O Operations**

This section demonstrates file input and output operations using Python.
The database created earlier is queried, and selected results are exported
to external files for reporting purposes.

Specifically, this part:
- Exports query results to CSV files using Python’s `csv` module
- Generates a text-based summary report using file writing
- Demonstrates how database results can be saved and reused outside Python

The outputs include CSV files for further analysis and a simple text report
summarizing key business metrics.

## **Upload CSV Files**

In this step, we upload all three CSV files into the Colab environment. These files contain the raw e-commerce data and will be used in the next steps to create tables and import records into the SQLite database.  
Please upload the files one by one in the order shown to keep the workflow consistent.

## **Upload File 1: Customers**

Run the cell below to open the file picker and upload **`customers (1).csv`**.  
Once uploaded, Colab will confirm that the customers file has been successfully added to the runtime.
"""

from google.colab import files

print("Please select 'customers (1).csv' file")
uploaded = files.upload()
print("Customers file uploaded!")

"""**Upload Successful**

The `customers (1).csv` file was selected and uploaded successfully.  
The file has been saved to the Colab runtime and is now ready to be used for database creation and analysis.

## **Upload File 2: Products**

Run the cell below to open the file picker and upload **`products (1).csv`**.  
This file contains the product catalog (product ID, name, category, price, and cost) and will be imported into the `products` table in the SQLite database.
"""

print("Please select 'products (1).csv' file")
uploaded = files.upload()
print("Products file uploaded!")

"""**Upload Successful**

The `products (1).csv` file was selected and uploaded successfully.  
The file has been saved to the Colab runtime and is now available for database creation and subsequent analysis.

## **Upload File 3: Orders**

Run the cell below to open the file picker and upload **`orders (1).csv`**.  
This file contains the order transactions (order ID, customer ID, product ID, quantity, and order date) and will be imported into the `orders` table, which links customers and products through foreign keys.
"""

print("Please select 'orders (1).csv' file")
uploaded = files.upload()
print("Orders file uploaded!")

"""**Upload Successful**

The `orders (1).csv` file was selected and uploaded successfully.  
The file has been saved to the Colab runtime and is now available for database creation and subsequent analysis.

## **Database Setup (Create Tables + Import CSV Data)**

In this section, we create a new SQLite database called `ecommerce.db`, define the table schemas (`customers`, `products`, and `orders`) with basic constraints, and then import the uploaded CSV files into the database.  
After this step, the database is fully populated and ready for verification and analysis queries.
"""

import sqlite3
import csv
from datetime import datetime

DB_PATH = "ecommerce.db"

def create_database(db_path):
    """
    Create a fresh SQLite database and define all table schemas.

    This function:
    Creates customers, products, and orders tables
    Applies constraints like PRIMARY KEY, NOT NULL, UNIQUE, CHECK
    Enables foreign key support so orders must reference valid customers/products
    """
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Turn on foreign key enforcement (important for the orders table references)
        cursor.execute("PRAGMA foreign_keys = ON;")

        # Drop tables first so we always start clean and avoid duplicate inserts
        cursor.execute("DROP TABLE IF EXISTS orders;")
        cursor.execute("DROP TABLE IF EXISTS products;")
        cursor.execute("DROP TABLE IF EXISTS customers;")

        # Create customers table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS customers (
                customer_id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                city TEXT NOT NULL,
                join_date DATE NOT NULL
            )
        """)
        print("  Created 'customers' table")

        # Create products table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                product_id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                category TEXT NOT NULL,
                price REAL NOT NULL CHECK(price >= 0),
                cost REAL NOT NULL CHECK(cost >= 0)
            )
        """)
        print("  Created 'products' table")

        # Create orders table with foreign keys to customers and products
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                order_id INTEGER PRIMARY KEY,
                customer_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                quantity INTEGER NOT NULL CHECK(quantity > 0),
                order_date DATE NOT NULL,
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
                FOREIGN KEY (product_id) REFERENCES products(product_id)
            )
        """)
        print("  Created 'orders' table")

        # Save schema changes
        conn.commit()

    print(f"\nDatabase created: {db_path}")


def import_csv_to_db(db_path, csv_file, table_name):
    """
    Import rows from a CSV file into a given database table.

    This function:
    Reads the CSV header to count columns
    Builds an INSERT statement with the correct number of placeholders
    Inserts each CSV row into the target table
    Returns the number of rows imported
    """
    rows_imported = 0

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Open CSV file from the Colab runtime
        with open(csv_file, "r", newline="", encoding="utf-8") as file:
            csv_reader = csv.reader(file)

            # Skip header row (column names)
            header = next(csv_reader)

            # Build INSERT statement dynamically based on number of columns
            placeholders = ", ".join(["?" for _ in range(len(header))])
            insert_sql = f"INSERT INTO {table_name} VALUES ({placeholders})"

            # Insert each row from the CSV into the table
            for row in csv_reader:
                cursor.execute(insert_sql, row)
                rows_imported += 1

        # Save inserted rows
        conn.commit()

    print(f"  Imported {rows_imported} rows into '{table_name}'")
    return rows_imported


# Run database setup steps
print("=" * 50)
print("DATABASE SETUP")
print("=" * 50)

print("\nCreating database and tables...")
create_database(DB_PATH)

print("\nImporting data from CSV files...")
import_csv_to_db(DB_PATH, "customers (1).csv", "customers")
import_csv_to_db(DB_PATH, "products (1).csv", "products")
import_csv_to_db(DB_PATH, "orders (1).csv", "orders")

print("\n" + "=" * 50)
print("DATABASE SETUP COMPLETE!")
print("=" * 50)

"""## **Database Creation and Data Import Completed**

The SQLite database (`ecommerce.db`) has been successfully created, and all required tables (`customers`, `products`, and `orders`) were initialized with the correct schema and constraints.

Each CSV file was imported without errors:
- **30** customer records loaded into the `customers` table  
- **20** product records loaded into the `products` table  
- **100** order records loaded into the `orders` table  

With the database fully populated, the data is now ready for querying, analysis, and reporting in the next sections of the assignment.

## **Export Functions**

In this section, we define helper functions that handle file input and output operations.  
These functions allow us to save query results outside the database for reporting and sharing purposes.

Specifically, we will:
1. Export selected query results to CSV files for further analysis
2. Generate a simple text-based summary report with key statistics

## **Function 1: Export to CSV**

This function takes the output of a database query and writes it to a CSV file using Python’s `csv` module.  
Exporting results to CSV makes the data easy to review, share, and reuse in external tools such as Excel or Google Sheets.
"""

def export_to_csv(data, filename, headers):
    """
    Export query results to a CSV file.

    Args:
        data: List of tuples containing query results
        filename: Name of the CSV file to create
        headers: List of column headers
    """
    # Open (or create) the CSV file in write mode
    # newline='' prevents extra blank lines on Windows
    with open(filename, 'w', newline='', encoding='utf-8') as f:
        # Create a CSV writer object
        writer = csv.writer(f)

        # Write the header row first (column names)
        writer.writerow(headers)

        # Write all data rows (each tuple becomes one CSV row)
        writer.writerows(data)

    # Confirmation message so we know the export worked
    print(f"Data exported to {filename}")


# Simple confirmation that the function cell ran successfully
print("export_to_csv() function created!")

"""## **Export Function Created Successfully**

The `export_to_csv()` function has been defined and is ready to use.  
This function will take query results from the database and export them into a CSV file with appropriate column headers.  

It will be used in the next steps to save analytical results such as top products, top customers, and category-level revenue into separate CSV files for reporting and further analysis.

## **Function 2: Get Top Products**

This function retrieves the top-selling products from the database based on total quantity sold and total revenue generated.

**Business question addressed**  
Which products are selling the most units and generating the highest revenue?

**Function overview**
- Connects to the SQLite database
- Joins the `products` and `orders` tables
- Aggregates total quantity sold and total revenue per product
- Sorts products by quantity sold in descending order
- Returns the top N products based on the specified limit

The results from this function are used for analysis and later exported to a CSV file as part of the reporting task.
"""

def get_top_products(db_path, limit=10):
    """
    Get top selling products by total quantity sold and total revenue.

    Business question
    Which products are selling the most units and generating the most revenue

    Args
        db_path: Path to the SQLite database file
        limit: Number of top products to return

    Returns
        List of tuples
        (product_id, name, category, quantity_sold, revenue)
    """
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Aggregate total units sold and total revenue per product
        results = cursor.execute(
            '''
            SELECT
                p.product_id,
                p.name,
                p.category,
                SUM(o.quantity) AS total_quantity_sold,
                ROUND(SUM(o.quantity * p.price), 2) AS total_revenue
            FROM products p
            INNER JOIN orders o ON p.product_id = o.product_id
            GROUP BY p.product_id, p.name, p.category
            ORDER BY total_quantity_sold DESC
            LIMIT ?
            ''',
            (limit,)
        ).fetchall()

    return results


# Test the function
print("Testing get_top_products()...")
results = get_top_products(DB_PATH, 5)

print(f"Found {len(results)} top products")
for row in results:
    # row format: (product_id, name, category, quantity_sold, revenue)
    print(f"  {row[1]}: {row[3]} sold, ${row[4]} revenue")

"""## **Output Interpretation**

The results show the top 5 products ranked by total quantity sold.  
USB-C Cable leads in unit sales, while Wireless Mouse generates the highest revenue among the top products.  

This indicates that some products perform strongly due to high volume sales, while others contribute more through higher price points. These insights can help prioritize inventory planning and pricing strategies.

## **Function 3: Get Top Customers**

This function identifies the highest value customers by calculating total spending across all their orders.  
It helps answer the business question: **Who are our most valuable customers based on revenue contributed?**
"""

def get_top_customers(db_path, limit=10):
    """
    Get top customers by total spending.

    Business question
    Who are our most valuable customers based on total spending

    Args
        db_path: Path to the SQLite database file
        limit: Number of top customers to return

    Returns
        List of tuples
        (customer_id, name, email, city, total_orders, total_spent)
    """
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Join customers, orders, and products so we can compute spending per customer
        results = cursor.execute(
            '''
            SELECT
                c.customer_id,
                c.name,
                c.email,
                c.city,
                COUNT(o.order_id) AS total_orders,
                ROUND(SUM(o.quantity * p.price), 2) AS total_spent
            FROM customers c
            INNER JOIN orders o ON c.customer_id = o.customer_id
            INNER JOIN products p ON o.product_id = p.product_id
            GROUP BY c.customer_id, c.name, c.email, c.city
            ORDER BY total_spent DESC
            LIMIT ?
            ''',
            (limit,)
        ).fetchall()

    return results


# Test the function to confirm it works
print("Testing get_top_customers()...")
results = get_top_customers(DB_PATH, 5)

print(f"Found {len(results)} top customers")
for row in results:
    # row format: (customer_id, name, email, city, total_orders, total_spent)
    print(f"  {row[1]}: ${row[5]} spent")

"""## **Output Interpretation**

The results show the **top 5 customers ranked by total spending** across all their orders.  
Each line reports the customer’s name and the total amount they have spent in the store.

This output confirms that the function correctly:
- Aggregates spending across multiple orders per customer  
- Ranks customers by total revenue contribution  
- Identifies the most valuable customers for targeted marketing or loyalty programs

## **Function 4: Get Revenue by Category**

This function calculates how much revenue each product category generates by combining order quantities with product prices.  
It helps answer the business question: **Which categories drive the most revenue so we know where to focus inventory and marketing?**
"""

def get_category_revenue(db_path):
    """
    Get revenue breakdown by product category.

    Business question
    Which product categories generate the most revenue

    Args
        db_path: Path to the SQLite database file

    Returns
        List of tuples
        (category, total_orders, total_quantity, total_revenue)
    """
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Aggregate total orders, total units sold, and total revenue per category
        results = cursor.execute(
            '''
            SELECT
                p.category,
                COUNT(o.order_id) AS total_orders,
                SUM(o.quantity) AS total_quantity,
                ROUND(SUM(o.quantity * p.price), 2) AS total_revenue
            FROM products p
            INNER JOIN orders o ON p.product_id = o.product_id
            GROUP BY p.category
            ORDER BY total_revenue DESC
            '''
        ).fetchall()

    return results


# Test the function
print("Testing get_category_revenue()...")
results = get_category_revenue(DB_PATH)

print(f"Found {len(results)} categories")
for row in results:
    # row format: (category, total_orders, total_quantity, total_revenue)
    print(f"  {row[0]}: ${row[3]} revenue")

"""## **Output Interpretation**

The revenue breakdown shows that **Electronics** is the highest-performing category, generating the majority of total revenue.  
**Accessories** contribute a moderate share, while **Office Supplies** generate the lowest revenue among the three categories.  

This indicates that sales are strongly driven by electronics products, suggesting they should remain the primary focus for inventory planning, promotions, and revenue growth strategies.

## **Function 5: Generate Summary Report**

This function calculates key sales statistics from the SQLite database and writes them into a clean **report.txt** file.  
It summarizes overall performance (revenue, orders, customers, products), highlights top performers (best category and top customer), and records the order date range covered in the dataset.
"""

def generate_summary_report(db_path, output_file="report.txt"):
    """
    Generate a text-based summary report with key statistics.

    Business question
    What is the overall sales performance of the store, and who/what are the top performers?

    What this function does
    Reads summary statistics from the database
    Writes results into a text file called report.txt
    Returns a dictionary of results so we can also display them in the notebook

    Args
        db_path: Path to database file
        output_file: Name of the output text file

    Returns
        Dictionary containing the calculated summary statistics
    """
    # Connect to the SQLite database and run summary queries
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # Statistic 1: Total revenue across all orders
        cursor.execute("""
            SELECT ROUND(SUM(o.quantity * p.price), 2)
            FROM orders o
            INNER JOIN products p ON o.product_id = p.product_id
        """)
        total_revenue = cursor.fetchone()[0]

        # Statistic 2: Total number of orders
        cursor.execute("SELECT COUNT(*) FROM orders")
        total_orders = cursor.fetchone()[0]

        # Statistic 3: Total number of customers
        cursor.execute("SELECT COUNT(*) FROM customers")
        total_customers = cursor.fetchone()[0]

        # Statistic 4: Average order value (average revenue per order row)
        cursor.execute("""
            SELECT ROUND(AVG(o.quantity * p.price), 2)
            FROM orders o
            INNER JOIN products p ON o.product_id = p.product_id
        """)
        avg_order_value = cursor.fetchone()[0]

        # Statistic 5: Best selling category by total units sold
        cursor.execute("""
            SELECT p.category, SUM(o.quantity) AS total_sold
            FROM orders o
            INNER JOIN products p ON o.product_id = p.product_id
            GROUP BY p.category
            ORDER BY total_sold DESC
            LIMIT 1
        """)
        best_category = cursor.fetchone()

        # Statistic 6: Top customer by total spending
        cursor.execute("""
            SELECT c.name, ROUND(SUM(o.quantity * p.price), 2) AS total_spent
            FROM customers c
            INNER JOIN orders o ON c.customer_id = o.customer_id
            INNER JOIN products p ON o.product_id = p.product_id
            GROUP BY c.customer_id, c.name
            ORDER BY total_spent DESC
            LIMIT 1
        """)
        top_customer = cursor.fetchone()

        # Statistic 7: Total number of products
        cursor.execute("SELECT COUNT(*) FROM products")
        total_products = cursor.fetchone()[0]

        # Statistic 8: Date range covered in orders table
        cursor.execute("SELECT MIN(order_date), MAX(order_date) FROM orders")
        date_range = cursor.fetchone()

    # Write the report to a text file using basic file I O
    with open(output_file, "w", encoding="utf-8") as f:
        f.write("=" * 50 + "\n")
        f.write("        E-COMMERCE SALES SUMMARY REPORT\n")
        f.write("=" * 50 + "\n\n")

        f.write(f"Generated: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
        f.write(f"Database: {db_path}\n\n")

        f.write("=" * 50 + "\n")
        f.write("KEY STATISTICS\n")
        f.write("=" * 50 + "\n\n")

        f.write(f"Total Revenue:        ${total_revenue:,.2f}\n")
        f.write(f"Total Orders:         {total_orders}\n")
        f.write(f"Total Customers:      {total_customers}\n")
        f.write(f"Total Products:       {total_products}\n")
        f.write(f"Average Order Value:  ${avg_order_value:,.2f}\n\n")

        f.write("=" * 50 + "\n")
        f.write("TOP PERFORMERS\n")
        f.write("=" * 50 + "\n\n")

        f.write(f"Best Selling Category: {best_category[0]} ({best_category[1]} units sold)\n")
        f.write(f"Top Customer:          {top_customer[0]} (${top_customer[1]:,.2f} spent)\n\n")

        f.write("=" * 50 + "\n")
        f.write("DATA COVERAGE\n")
        f.write("=" * 50 + "\n\n")

        f.write(f"Order Date Range:      {date_range[0]} to {date_range[1]}\n\n")

        f.write("=" * 50 + "\n")
        f.write("              END OF REPORT\n")
        f.write("=" * 50 + "\n")

    # Confirm the file was created
    print(f"Report generated: {output_file}")

    # Return results so we can also show them in the notebook if needed
    return {
        "total_revenue": total_revenue,
        "total_orders": total_orders,
        "total_customers": total_customers,
        "total_products": total_products,
        "avg_order_value": avg_order_value,
        "best_category": best_category,
        "top_customer": top_customer,
        "date_range": date_range
    }


print("generate_summary_report() function created!")  # Quick confirmation message

"""## **Summary Report Function Created**

The `generate_summary_report()` function has been successfully defined.  
It connects to the SQLite database, calculates key sales statistics, and writes a well-structured text report (`report.txt`) to disk.

This confirms that the reporting and file I/O requirements for this task have been implemented correctly and are ready to be executed to generate the final summary report.

## **Main Function: Run All Exports and Generate Report**

This final step runs every export in one place. It creates three CSV files (top products, top customers, and revenue by category) and then generates a text summary report (`report.txt`) with key statistics from the database.
"""

def main():
    """Main function to generate all reports and exports."""

    print("=" * 60)
    print("E-COMMERCE DATA EXPORT & REPORTING")
    print("=" * 60)

    # Export 1: Pull the top-selling products and save them to a CSV file
    print("\n" + "-" * 60)
    print("EXPORT 1: Top Products")
    print("-" * 60)

    top_products = get_top_products(DB_PATH, 10)
    export_to_csv(
        top_products,
        "top_products.csv",
        ["Product ID", "Name", "Category", "Quantity Sold", "Revenue"]
    )
    print(f"Exported {len(top_products)} products")

    # Export 2: Pull the highest-spending customers and save them to a CSV file
    print("\n" + "-" * 60)
    print("EXPORT 2: Top Customers")
    print("-" * 60)

    top_customers = get_top_customers(DB_PATH, 10)
    export_to_csv(
        top_customers,
        "top_customers.csv",
        ["Customer ID", "Name", "Email", "City", "Total Orders", "Total Spent"]
    )
    print(f"Exported {len(top_customers)} customers")

    # Export 3: Pull revenue totals by category and save them to a CSV file
    print("\n" + "-" * 60)
    print("EXPORT 3: Revenue by Category")
    print("-" * 60)

    category_revenue = get_category_revenue(DB_PATH)
    export_to_csv(
        category_revenue,
        "category_revenue.csv",
        ["Category", "Total Orders", "Total Quantity", "Total Revenue"]
    )
    print(f"Exported {len(category_revenue)} categories")

    # Generate the text-based summary report with key statistics
    print("\n" + "-" * 60)
    print("GENERATING SUMMARY REPORT")
    print("-" * 60)

    stats = generate_summary_report(DB_PATH, "report.txt")

    # Display key statistics on screen so we can confirm the report values quickly
    print("\nSummary Statistics:")
    print(f"  Total Revenue:       ${stats['total_revenue']:,.2f}")
    print(f"  Total Orders:        {stats['total_orders']}")
    print(f"  Total Customers:     {stats['total_customers']}")
    print(f"  Avg Order Value:     ${stats['avg_order_value']:,.2f}")
    print(f"  Best Category:       {stats['best_category'][0]}")
    print(f"  Top Customer:        {stats['top_customer'][0]}")

    # Final status message showing everything finished successfully
    print("\n" + "=" * 60)
    print("ALL EXPORTS COMPLETE!")
    print("=" * 60)

    # List all output files created by this script
    print("\nFiles created:")
    print("  - top_products.csv")
    print("  - top_customers.csv")
    print("  - category_revenue.csv")
    print("  - report.txt")


# Run main function
main()

"""## **Results Overview**

The export and reporting workflow ran successfully. All CSV files were generated directly from the SQLite database, including top products, top customers, and revenue by category. A text-based summary report was also created, capturing key metrics such as total revenue, total orders, total customers, average order value, and top-performing segments.

This confirms that the full pipeline—from database queries to file export and reporting—functions correctly and produces clear, structured outputs ready for further analysis.

## **View Generated Files**

In this section, we inspect the outputs created by the export and reporting process.  
The following step opens and displays the contents of the generated **report.txt** file directly in the notebook, allowing us to verify that the summary statistics were written correctly and formatted as expected.
"""

# View the generated report.txt
print("=" * 60)
print("CONTENTS OF report.txt")
print("=" * 60)

with open("report.txt", "r", encoding="utf-8") as f:
    print(f.read())

"""## **Key Insights from the Summary Report**

- **Strong overall sales performance:** The dataset generated a total revenue of **$4,651.39** from **100 orders**, indicating consistent purchasing activity across customers.

- **Electronics dominate sales:** The **Electronics** category is the best-selling category by units sold, making it the primary driver of revenue.

- **High-value customers matter:** A small group of customers contributes a significant share of total spending. **William Martinez** is the top customer by total spend, highlighting the importance of customer retention.

- **Healthy average order value:** An **average order value of $46.51** suggests that customers often purchase multiple items or higher-priced products per order.

- **Strong temporal coverage:** The data spans from **February 2023 to November 2025**, providing sufficient coverage to analyze sales trends over time.

## **View `top_products.csv`**

This cell prints the full contents of **top_products.csv** so we can confirm the export worked and review the top-selling products.
"""

# View top_products.csv

print("=" * 60)
print("CONTENTS OF top_products.csv")
print("=" * 60)

with open("top_products.csv", "r", encoding="utf-8") as f:
    print(f.read())

"""## **Insights from `top_products.csv`**

- **USB-C Cable** is the top-performing product by volume, indicating strong demand for essential, low-cost accessories.
- **Electronics dominate the list**, appearing frequently among the top products, which highlights this category as the primary revenue driver.
- **Wireless Mouse** generates high revenue with relatively fewer units than USB-C Cables, suggesting a higher price point and strong perceived value.
- **Office Supplies** (e.g., Notebook Set, Pen Set) show steady sales, indicating consistent but moderate demand.
- **Accessories** such as Water Bottles and Coffee Mugs perform well, reflecting opportunities for bundling or cross-selling with electronics.

Overall, the results suggest that focusing on **electronics for revenue growth** while leveraging **accessories for volume and upsell strategies** could be effective.

## **View `top_customers.csv`**

This cell prints the full contents of the exported **top_customers.csv** file so we can quickly verify the output.
"""

# View top_customers.csv
print("=" * 60)
print("CONTENTS OF top_customers.csv")
print("=" * 60)

with open("top_customers.csv", "r", encoding="utf-8") as f:
    print(f.read())

"""## **Insights from `top_customers.csv`**

- **Revenue concentration:** A small group of customers contributes a significant share of total revenue, with the top customer (William Martinez) spending noticeably more than others.
- **Geographic pattern:** New York, Boston, and Chicago dominate the top-customer list, suggesting these cities are key markets for high-value customers.
- **Order behavior:** Most top customers place a similar number of orders (3–5), indicating that **higher spending is driven by product mix and order value**, not just order frequency.
- **Customer targeting opportunity:** Repeat high-spending customers are strong candidates for loyalty programs, personalized promotions, or early access to new products.
- **Balanced top tier:** After the top 2–3 customers, spending levels are relatively close, showing a healthy distribution rather than reliance on a single buyer.

## **View `category_revenue.csv`**

This cell prints the full contents of `category_revenue.csv` so we can quickly review revenue performance by product category.
"""

# View category_revenue.csv

print("=" * 60)
print("CONTENTS OF category_revenue.csv")
print("=" * 60)

with open("category_revenue.csv", "r", encoding="utf-8") as f:
    print(f.read())

"""## **Insights from `category_revenue.csv`**

- **Electronics clearly dominates overall performance**, generating the highest revenue ($3,506.06) and accounting for the majority of total orders and units sold. This indicates strong and consistent demand for electronic products.

- **Accessories form a solid secondary category**, with moderate revenue ($748.67) and fewer orders. These products likely benefit from being add-on or complementary purchases alongside higher-value items.

- **Office Supplies contribute the least to revenue** ($396.66) despite having a similar number of orders to Accessories. This suggests lower price points and thinner margins in this category.

- Overall, revenue is **highly concentrated in Electronics**, implying that inventory planning, promotions, and growth strategies should prioritize this category while selectively improving performance in lower-revenue segments.

## **Download All Generated Files**

This cell downloads all generated output files (CSV files and the text report) from the Google Colab runtime to local computer for submission or review.
"""

from google.colab import files

print("Downloading files...")
print("-" * 40)

# Download all files
files.download('report.txt')
files.download('top_products.csv')
files.download('top_customers.csv')
files.download('category_revenue.csv')
files.download('ecommerce.db')

print("\nAll files downloaded!")

"""All generated files have been successfully downloaded to our computer.  
We can now access the CSV exports and the summary report locally for review or submission.

## **Conclusion**

In this assignment, CSV files containing customer, product, and order data were uploaded and used to create a structured SQLite database. Tables were created with appropriate relationships, and the data was successfully imported and validated.

Multiple SQL queries and Python functions were implemented to analyze the data, including identifying top-selling products, top customers by spending, revenue by category, and overall sales performance. The results were exported to CSV files, and a text-based summary report was generated containing key statistics such as total revenue, total orders, average order value, and top performers.

Overall, this assignment demonstrated the use of Python, SQL, and file I/O operations to transform raw data into meaningful insights and reusable output files.
"""