FlipInsight — Flipkart Product Analytics Platform

An end-to-end data analytics platform built on the Flipkart e-commerce sample dataset (20,000 product listings). Covers the full pipeline: ETL → PostgreSQL → ML models → REST API → live dashboard.

Architecture

flowchart LR
  RawCSV["Raw CSV<br>(flipkart_com-ecommerce_sample.csv)"]
  ETL[/"ETL: etl/clean.py"/]
  Processed["data/processed/products_clean.csv"]
  DBInit["db/init_db.py"]
  DB[("SQLite / Postgres DB")]
  MLTrain{"Train ML models"}
  Price["ml/train_price_model.py"]
  Category["ml/train_category_model.py"]
  Recommender["ml/train_recommender.py"]
  MLModels["ml/models (joblib artifacts)"]
  API["api/main.py (FastAPI)"]
  Dashboard["dashboard/static/index.html<br>(Chart.js UI)"]
  Client["User / Dashboard / API Clients"]
  CI["CI / Docker"]
  Docker["docker-compose / Dockerfile"]

  RawCSV --> ETL --> Processed --> DBInit --> DB
  DB --> MLTrain
  MLTrain --> Price
  MLTrain --> Category
  MLTrain --> Recommender
  Price --> MLModels
  Category --> MLModels
  Recommender --> MLModels
  MLModels --> API
  DB --> API
  API --> Dashboard
  Client --> Dashboard
  Client --> API
  CI --> Docker
  Docker --> API
  Docker --> DB

Loading

Key data quality fixes in ETL

The raw dataset has several messy fields that needed real cleaning, not just pd.read_csv:

• Category tree parsing: product_category_tree is a stringified single-element list like ["Clothing >> Women's Clothing >> Shorts"], split on >> into category_l1..l4.
• Malformed category rows: ~1.6% of rows have a broken tree with no >> separator, where the "category" is actually the full product title (e.g. "Vishudh Printed Women's Straight Kurta"). These were detected and excluded — without this fix, category cardinality was inflated from ~30 real categories to 265.
• Specifications parsing: product_specifications is stored in Ruby-hash syntax ("key"=>"value"), converted to proper Python dicts via ast.literal_eval after a => → : substitution.
• Ratings: "No rating available" strings converted to proper nulls rather than being treated as text.
• Discount calculation: derived discount_amount and discount_pct from retail vs. discounted price, guarding against data-entry errors where discounted > retail.

Excel Analysis

In addition to the Python/ML pipeline, this project includes a standalone Excel deliverable demonstrating spreadsheet-native analysis on the cleaned dataset:

• analysis/FlipInsight_Excel_Analysis.xlsx
  • Data sheet — full cleaned 19,672-row listing with formula-derived Price Band classification and a VLOOKUP-based brand/category consistency check
  • Brand_Lookup — reference table mapping 3,400+ brands to their most common category
  • Category_Price_Summary — SUMIFS/COUNTIFS/AVERAGEIFS crosstab of revenue by category and price band, plus a native Excel PivotTable
  • Dashboard — bar charts and a PivotChart visualizing revenue by category
  • Conditional formatting flags price and discount outliers (99th percentile / ≥90% discount)

Open in Excel to interact with the PivotTable and PivotChart directly.

Running it

pip install -r requirements.txt

# 1. Clean the raw data
python3 etl/clean.py

# 2. Load into SQLite
python3 -m db.init_db

# 3. Train the ML models
python3 -m ml.train_price_model
python3 -m ml.train_category_model
python3 -m ml.train_recommender

# 4. Start the API + dashboard
python3 -m uvicorn api.main:app --reload

Then open http://localhost:8000 for the dashboard, or http://localhost:8000/docs for interactive API docs (Swagger UI, auto-generated by FastAPI).

Running with Docker

docker compose up --build

The image runs the ETL + ML training at build time. The database is seeded at startup via the lifespan hook — so it works with Railway, Render, or any managed PostgreSQL provider. Dashboard at http://localhost:8000.

To run with plain Docker instead of Compose:

docker build -t flipinsight .
docker run -p 8000:8000 flipinsight

Switching to Postgres

The DB layer reads DATABASE_URL from the environment (defaults to local SQLite):

export DATABASE_URL="postgresql://flipinsight:flipinsight@localhost:5432/flipinsight"
pip install psycopg2-binary
python3 -m db.init_db

docker-compose.yml includes a PostgreSQL 16 service wired to the API via DATABASE_URL.

CI

.github/workflows/ci.yml runs on every push/PR to main:

1. Installs dependencies
2. Runs the full pipeline from scratch (ETL → DB → train all 3 models)
3. Runs the test suite
4. Builds the Docker image and smoke-tests the container's /health endpoint

Running tests

python3 -m pytest tests/ -v

19 tests covering health checks, analytics aggregation, product filtering/search, both ML prediction endpoints, and the recommender (similarity search + similar-by-ID).

API endpoints

Endpoint	Description
GET /products	Paginated product listing with filters (category, brand, search, min_price, max_price)
GET /products/{uniq_id}	Single product detail
GET /analytics/summary	Headline stats (totals, avg price, avg discount, rating coverage)
GET /analytics/categories	Product counts by top-level category
GET /analytics/price-by-category	Avg price & discount per category
GET /analytics/top-brands	Brand ranking by listing count
GET /analytics/discount-distribution	Discount % histogram buckets
POST /predict/price	Predict retail price from category/brand/description
POST /predict/category	Predict product category from description text (top-3 with confidence)
GET /recommend/similar/{uniq_id}	Find products similar to a given product
POST /recommend/search	Find products similar to a free-text query

Model performance

• Price prediction (RandomForest, log-target): MAE ≈ Rs. 1,056, R² ≈ 0.77 on held-out test set
• Category classification (TF-IDF + LogisticRegression, 25 classes): 98.2% test accuracy
• Price prediction (RandomForest, log-target): MAE ≈ Rs. 1,056, R² ≈ 0.77 on held-out test set
• Category classification (TF-IDF + LogisticRegression, 25 classes): 98.2% test accuracy
• Recommender (sentence-transformers MiniLM-L6-v2): dense semantic embeddings, cosine similarity search; free-text queries and product-ID lookups both supported

Tech stack

Python · pandas · scikit-learn · sentence-transformers · FastAPI · SQLAlchemy · PostgreSQL · Docker · GitHub Actions · Chart.js · pytest

Possible extensions

• Add a /recommend embedding-based upgrade using sentence-transformers instead of TF-IDF
• Add a /models/retrain endpoint to trigger retraining without redeploying
• Add JWT-based user authentication with per-key usage tracking
• Deploy permanently to Koyeb or Fly.io for a fixed public URL