002 — E-Commerce Analytics

Amazon
Product Catalog
Analytics

End to end analytics project that loads 1.4 million Amazon product listings from Kaggle into PostgreSQL. Cleaning in Python, SQL analysis, and a Chart.js dashboard built from the SQL exports, covering pricing patterns, rating distributions, and purchase behavior across 248 categories.

End to End Analytics ETL Pipeline 1.4M Rows Python PostgreSQL SQL Pandas Matplotlib Chart.js Kaggle API

Year

2025

Role

Data Analyst

Type

Solo Project

Domain

E-Commerce

Data Source

Kaggle · Amazon Products Dataset

Process

Workflow

Ingest

Authenticated Kaggle API, downloaded 1.4M-row Amazon products CSV directly into the pipeline.

Clean

Parsed price strings, computed discount percentages, removed nulls and duplicates, renamed columns to snake_case using Pandas.

Database

Loaded cleaned data into PostgreSQL via SQLAlchemy in 50K row chunks with indexes on category, price, stars, and best seller flag.

Analyze

Wrote SQL queries joining the products and categories tables. Extracted price tiers, rating distributions, and purchase snapshots.

Visualize

Built a Chart.js dashboard from the exported SQL results, with filter buttons that update the KPI cards.

Visualization

Product Catalog
Dashboard

Amazon Product Catalog Analytics — 2023 Snapshot

Source: Kaggle · Amazon Products Dataset 2023 · 1,426,336 products
Filters update the KPI cards only. The charts do not change with the filters and are Chart.js recreations of my SQL exports.

Total Products

1,426,336

All categories combined

Avg Listed Price

$44.40

Products with a listed price

Avg Star Rating

4.40 ★

Rated products, out of 5.0

Total Customer Reviews

257.8M

Cumulative all time

Last Month Buys

202.5M

2023 snapshot period

Total products by category — top 10

Avg monthly purchases by star rating (products with at least one purchase)

Product count by listed price tier (products with a listed price)

Last month purchases by category — top 5

39M

top 5 buys

Key findings

$10 to $25 is the largest price tier 41.7% of the products with a listed price are priced between $10 and $25, more than any other tier.

Higher rated products get more purchases Among products with at least one purchase last month, 4.5 stars average 434 monthly purchases, nearly 3× the 154 at 3.0★. This is an association, not proof that ratings cause sales.

Kitchen & Dining leads purchases 10.4M units purchased last month, the highest of any category, though it does not have the most listings.

What I Found

Key Findings

Pricing Insight

$10 to $25 Is the
Largest Price Tier

Over 581,000 products. 41.7% of the products with a listed price are priced between $10 and $25, the largest of the five price tiers. This shows where listings cluster. It does not show how competitive or profitable the band is.

41.7%of priced products at $10 to $25

Rating vs Purchases

Higher Ratings Go With
More Purchases

Among the 502,789 rated products with at least one purchase last month, those at 4.5 stars average 434 monthly purchases versus 154 at 3.0 stars. The jump from 4.0★ to 4.5★ is the largest single increase on the rating scale. The 1.0 and 1.5 star groups have only 317 and 83 products, so read the low end cautiously. This is an association only. Popular products also attract more ratings, so the data cannot show that ratings cause sales.

2.8×more purchases at 4.5★ vs 3.0★

Category Insight

Most Listed ≠
Most Purchased

Girls' Clothing has the most product listings (28,619), but Kitchen & Dining leads last month purchases (10.4M units). The category with the most listings is not the category with the most purchases, so listing volume alone is a weak guide to demand.

10.4MKitchen & Dining last month buys

Context

Audience &
Limitations

Who This Is For

Written for a marketplace seller or category analyst deciding where to list products and how to price them.
Use the price tier counts to see where listings cluster, then check that band inside a specific category before choosing a price.
Compare categories by purchases, not by listing counts, when the question is where demand is.
Treat the rating and purchase pattern as a lead to investigate, not a lever to pull.

Limitations

One snapshot of 2023 listings from a Kaggle dataset. It is not the full Amazon catalog and not a time series.
Purchases cover a single month, so seasonality is not captured. The purchase counts are rounded, so totals are approximate.
Averages are means. The median listed price is about $20 against a mean of $44.40, so a small number of expensive products pull the average up.
Findings are associations. The analysis does not show that price or ratings cause sales.
Purchase averages include only products with at least one purchase last month, so they describe products that sell, not the whole catalog. About 64% of products (917,619) had none.
32,772 products have no listed price and 131,023 have no rating. They are left out of the price and rating averages and the price tier chart.

Stack

Tools & Data

Data Source

Kaggle — Amazon Products Dataset 2023
1,426,336 product listings
248 product categories

Software Used

Python (Pandas, Matplotlib, SQLAlchemy)
PostgreSQL + pgAdmin
SQL (window functions, CTEs, joins)
Jupyter Notebook (Anaconda)
Kaggle API
Chart.js

Project Lifecycle

From Raw CSV
to Dashboard

Problem

Define the Question

What does Amazon's product catalog actually look like at scale? What is the relationship between price, rating, purchase behavior and which categories dominate by listings versus actual sales?

Collect

Kaggle API Ingestion

Authenticated the Kaggle API and downloaded the Amazon Products Dataset (1.4M rows) programmatically. Raw data included messy price strings, nulls, and uncategorized entries requiring significant cleaning before analysis.

Build

PostgreSQL Pipeline

Cleaned and normalized data using Pandas, then loaded into PostgreSQL via SQLAlchemy in 50K-row chunks. Joined the products table with the categories lookup table on category_id. Created indexes on key columns for query performance at 1.4M row scale.

Analyze

SQL Analysis & Export

Wrote SQL queries for price tier segmentation, rating distributions, best seller breakdowns, and last-month purchase snapshots. Exported results as CSVs for downstream visualization.

Ship

Dashboard Delivery

Delivered a Chart.js dashboard built from the SQL exports, with filter buttons for best sellers, budget, and premium price bands that update the KPI cards. The charts do not change with the filters.

AmazonProduct CatalogAnalytics

Amazon
Product Catalog
Analytics