Olist: E-Commerce Marketplace Analytics

A migration and analytics system over Brazilian marketplace data, including a geospatial application mapping 35,000 seller-buyer trade routes across 1,600 cities.

Problem

The Olist dataset spans orders, customers, sellers, products, payments, reviews, geolocation, and marketing leads across separate tables in SQLite. Any substantive question cuts across most of them, so the relational structure has to be sound and the data has to live somewhere that supports real analytical work before analysis can begin.

Approach

A custom Python pipeline migrates the relational SQLite source into SQL Server and Azure SQL, handling entity ordering, foreign key dependencies, type conversion, validation, and source-to-target parity testing so the migration can be verified rather than assumed. On top of that, T-SQL analysis covers sales performance and customer behavior, and a JavaScript geospatial application renders seller-to-buyer trade routes with selectable metrics.

Result

A queryable marketplace database with verified parity to its source, plus an interactive map of more than 35,000 trade routes across over 1,600 cities, filterable by order count, value, distance, and freight intensity.

Parity testing as part of the migration

A migration that moves rows without verifying them produces a database nobody can trust, and the failure is silent: queries return results, they are just wrong. Comparing row counts, key relationships, and value distributions between source and target turns “the migration ran” into “the migration is correct.”

Geography as a first-class dimension

Seller and customer location is the difference between a marketplace analysis and a logistics one. Treating each seller-to-buyer pair as a trade route, rather than reducing both endpoints to a region label, exposes the distance and freight structure that a purely categorical analysis flattens away. Freight intensity against distance is where the interesting outliers live.

Stack

T-SQL
Azure SQL
Python
JavaScript
GeoPandas