E-Commerce Analytics Dashboard

SQL & Python Analysis of 100K Real Orders with a Live Dashboard

SQLDuckDBPythonPandasMatplotlibSeabornPlotlyJupyterHTMLJavaScript
Project Overview

Analyzed ~100K real orders (Sep 2016 – Oct 2018) from a Brazilian e-commerce marketplace (public Kaggle dataset) to answer one business question: what drives revenue and customer satisfaction, and what should the business fix first? I cleaned 9 raw tables into an analysis-ready fact table in DuckDB, answered 16 business questions in SQL, explored the results in a Jupyter notebook, and published a live interactive Plotly dashboard with period and state filters.

Key Features
  • 16 business questions answered in SQL (KPIs, growth, cohorts, RFM, seller Pareto)
  • Data model: 9 raw tables joined into one clean fact table
  • RFM customer segmentation into 7 actionable segments
  • Monthly cohort retention analysis
  • Seller revenue concentration (Pareto) and seller scorecard
  • Delivery-timing vs review-score analysis
  • Live interactive dashboard with period and state filters (GitHub Pages)
  • Fully reproducible pipeline: download → build database → run queries → export
Results
  • Found that late deliveries are the #1 driver of bad reviews: 2.57★ vs 4.29★ for on-time orders, and 46% of late orders get 1 star (vs 7%)
  • Showed 97% of customers never order again, explaining why revenue plateaued in 2018 after 140% YoY growth
  • Identified an "At risk, high value" segment (14% of customers) holding R$4.3M (27.5%) of revenue
  • Found the top 10% of sellers generate 67% of revenue, a concentration risk
  • Turned the findings into 5 prioritized business recommendations, e.g. halving late deliveries would cut 1-star reviews by ~16%
Technical Challenges
  • Messy raw data across 9 tables: duplicate reviews, Portuguese category names, misspelled columns, zip codes missing leading zeros
  • Incomplete months at the start and end of the date range distorting trends
  • Segmenting ~95K customers by value and engagement
  • Making a live dashboard without a backend server
Solutions Implemented
  • Built a cleaning pipeline in DuckDB: removed 551 duplicate reviews with QUALIFY, translated categories to English, fixed column names and zip codes
  • Excluded partial months from all trend analysis
  • Used NTILE / PERCENT_RANK scoring for RFM segmentation and seller tiers
  • Pre-aggregated the data to a ~300 KB JSON file; filters re-calculate in the browser, hosted free on GitHub Pages
Technology Stack
SQLDuckDBPythonPandasMatplotlibSeabornPlotlyJupyterHTMLJavaScript

Interested in Similar Projects?

Let's discuss how I can help bring your ideas to life with modern technologies and data-driven solutions.