ESEN
← All articles and case studies

Data · Articles and case studies

Ecommerce Returns & Profitability Analytics

End-to-end ecommerce analytics project focused on returns, profitability, product performance, acquisition quality, and fulfillment. I built the analytical pipeline from PostgreSQL and Python through reusable data marts and an interactive Metabase dashboard, then reconciled dashboard metrics directly against SQL before publishing the final analysis.

By Henry Mariño ·

Ecommerce Returns & Profitability Analytics

Ecommerce Returns & Profitability Analytics

I built an end-to-end ecommerce analytics project to understand how revenue, returns, product performance, traffic sources, and fulfillment affect profitability.

The project goes beyond creating a dashboard. I built the data pipeline from PostgreSQL and SQL analysis through Python exploration, reusable analytics marts, Metabase visualization, and final SQL validation.

Interactive Dashboard

http://bi.romahomestore.com/public/dashboard/91e02296-ee2e-41c3-b389-84115d1771c3 

 

Use the filters to explore performance by date, category, brand, and traffic source.

 

The dashboard is powered by analytics marts built in PostgreSQL and validated directly against SQL before publication.

What I Built

  • Loaded and modeled ecommerce data in PostgreSQL.
  • Created relational tables, keys, joins, and an enriched analytical layer.
  • Used Python, pandas, and Jupyter for exploratory and statistical analysis.
  • Built reusable analytics marts for business intelligence.
  • Created an interactive Metabase dashboard with global filters.
  • Validated dashboard calculations directly against PostgreSQL queries.

Analytics Architecture

Raw Ecommerce Data
        ↓
PostgreSQL
        ↓
Relational / Enriched Data Model
        ↓
SQL + Python Analysis
        ↓
Analytics Marts
        ↓
Metabase Dashboard
        ↓
SQL QA & Reconciliation

Key Results

  • $5.74M analyzed revenue
  • 66,019 orders
  • 10.16% item return rate
  • 51.91% margin rate proxy

Revenue increased considerably over the analyzed period while the overall return rate remained relatively stable around 10%.

Search generated approximately $4.05M, or about 71% of analyzed revenue. Its return rate, however, was similar to the other major traffic sources, suggesting that its larger return exposure was primarily a result of its much larger sales volume.

I also tested whether longer delivery times were associated with more returns. The item-level correlation between ship-to-deliver time and return behavior was approximately 0.0027, indicating essentially no meaningful linear relationship in this dataset.

A Data Modeling Problem I Had to Solve

One of the most useful challenges came from the Top Products dashboard.

My original product mart was aggregated too early. It worked for a static chart, but once I added dashboard filters for date, category, brand, and traffic source, the BI tool no longer had the dimensions required to correctly recalculate the product rankings.

I redesigned the mart to preserve those dimensions and moved the Top 25 ranking to the dashboard layer so products are ranked after filters are applied.

I also stored additive components such as revenue, margin, returned items, and order items instead of relying on pre-calculated percentages. This allows metrics such as return rate and margin rate to be recalculated correctly for any filtered population.

Quality Assurance

I did not consider the dashboard complete just because the charts looked correct. I reconciled Metabase against PostgreSQL for:

  • Revenue, orders, return rate, and margin rate
  • Date, category, brand, and traffic-source filters
  • Category × brand analysis
  • Margin burn calculations
  • Traffic-source metrics
  • Fulfillment-time bins
  • Top-product rankings and profitability

The final dashboard values reconciled with the PostgreSQL source of truth.

Skills Demonstrated

SQL · PostgreSQL · Python · pandas · Jupyter · Data Modeling · Data Marts · ETL/ELT · Metabase · Business Intelligence · Data Visualization · Statistical Analysis · Data Validation · Git · GitHub · Data Storytelling

What I Learned

  • Analytical grain directly affects what a BI dashboard can do.
  • Ratios should often be recalculated from additive components after filtering.
  • Updating SQL in Git does not automatically update the database.
  • Metric definitions must remain consistent across every layer of the stack.
  • A dashboard should be validated against its database source before it is trusted.

View the project on GitHub →

WhatsApp