Ecommerce Business Intelligence
What makes ecommerce reporting reliable?
Our MSc team cleaned the Olist ecommerce data and built a nine table Power BI model, eight linked source tables plus a DAX date table, to report on delivery performance, seller activity and customer satisfaction.
Eight linked source tables covering 98,582 orders were cleaned before cross table reporting.
Explore the evidence ↓01 · Decision
Our team defined the decision before selecting a method.
How can linked ecommerce data support reliable reporting on deliveries, sellers and customer experience?
The quality of the joined data affects the quality of the decision.
Our team prepared eight linked source tables covering 98,582 ecommerce orders, cleaning problematic values and relationships before creating Power BI reporting on delivery, sellers and customer satisfaction.
What the reporting depended on
Accurate cross table KPIs depended on the preparation work. Once keys, dates and category names were consistent, delivery, seller and review measures could be compared across tables with confidence. I later rebuilt the analysis in Python as an interactive dashboard so anyone can explore the cleaned data.
02 · Approach
Our team organised the analysis in the following sequence.
- 01
Our team reviewed the related ecommerce tables and assessed missing values, duplicates, inconsistent names, dates and relationships.
- 02
We used Power Query to prepare the data for a connected reporting model.
- 03
We developed reporting with Power BI and DAX around delivery performance, seller activity and customer satisfaction.
- 04
We organised the pages around operational questions rather than presenting metrics without a decision context.
03 · Evidence
The results support the following interpretation.
The cleaned workbook holds eight source tables, including 98,582 orders and 110,929 order items, with a DAX date table completing the nine table model. The key finding was about data quality: source inconsistencies had to be resolved before delivery, seller and customer measures could be compared reliably across tables.
The cleaned Olist Dataset Clean v3.xlsx workbook contains eight source tables covering customers, orders, order items, reviews, products, sellers, category translation and geolocation. A DAX date table completed the nine table Power BI model.
Workbook inventory
- Customers · 99,441 data rows
- Orders · 98,582 data rows
- Order Items · 110,929 data rows
- Order Reviews · 97,334 data rows
- Products · 32,341 data rows
- Sellers · 3,095 data rows
- Category Translation · 73 data rows
- Geolocation · 50,000 data rows
04 · Decision implications
Our team translated the analysis into practical next steps.
- Validate join keys and date logic before comparing ecommerce metrics across related tables.
- Separate reliable measures from unavailable or incomplete ones when reporting to decision makers.
- Use delivery, seller and customer views to investigate operational questions, as the interactive Olist dashboard on this site does.
05 · Limits
The evidence has defined limits.
This was assessed team coursework on a public dataset rather than a deployed reporting system, so the outcome is a reporting design and data preparation approach, not a measured business improvement.
06 · Next iteration
Our team would strengthen the next iteration.
Publish each DAX measure with a test against the cleaned data, and extend the delivery analysis to compare late delivery rates by seller and region.
07 · Attribution
I distinguish my contribution from the complete project output.
This was a shared responsibility MSc team project. The data model, DAX measures and dashboard pages are described as our team’s work.
Toolkit