The Challenge
Raw transactional data does not automatically become business intelligence.
The challenge was to consolidate multiple datasets into a reliable Power BI model and build a reporting solution that could help stakeholders understand overall performance, product performance, customer behavior, and geographic concentration.
What I Built
I followed a structured BI workflow:
Business Problem → Data Audit → Data Model → Transformation → KPIs → Dashboard → Insights
Data & Modeling
I analyzed the data grain and relationships before building the report.
One key modeling decision: orders and order items have different grain. Revenue was calculated from order-item data, while orders required a distinct count.
I then built a relational model using customer, product, seller, and date dimensions connected to transactional fact tables.
Data Preparation
Using Power Query, I:
- Cleaned and structured reporting tables
- Applied appropriate data types
- Prepared date fields for time analysis
- Merged product category translations
- Created business-friendly dimensions
KPIs
The core KPI framework included:
- Total Revenue
- Total Orders
- Total Customers
- Average Order Value
- Total Items Sold
- Average Revenue per Customer
- Average Orders per Customer
During validation, I also identified incomplete September 2018 data and excluded it from YoY analysis to avoid misleading growth results.
The Solution
The final Power BI report contains three focused dashboards.
Executive Summary
A leadership-level view of:
- Revenue
- Orders
- Customers
- Average Order Value
- Revenue trends
- Top states
- Top product categories
[Insert Executive Summary Dashboard Image]
Sales & Category Performance
A detailed comparison of:
- Revenue by category
- Orders by category
- Average order value
- Items sold
This helps identify where high revenue and high order volume do not necessarily overlap.
[Insert Sales & Category Performance Image]
Customer & Geographic Performance
Analysis of:
- Customer concentration
- Revenue per customer
- Orders per customer
- Top states and cities
- Seller geography
[Insert Customer & Geographic Performance Image]
Key Insights
$13.59M Revenue
The dataset generated approximately $13.59M in revenue across 99K orders and 96K customers.
São Paulo Is the Anchor Market
São Paulo generated approximately $5.2M in revenue and had the largest customer base at roughly 40K customers.
Revenue concentration in one market creates both an opportunity for expansion and potential dependency risk.
Revenue ≠ Demand
The highest-revenue product categories did not always have the highest order volume.
This highlights why business performance should be evaluated using multiple KPIs rather than revenue alone.
Recommended Actions
1. Deep-dive into São Paulo Analyze retention, product preferences, profitability, and growth opportunities in the largest market.
2. Look beyond revenue Evaluate high-performing categories alongside margin and repeat purchasing behavior.
3. Investigate volume vs. revenue gaps Use bundling and upselling opportunities to improve Average Order Value.
4. Explore underperforming markets Identify regions with strong customer bases but relatively lower revenue.
Outcome
Built an end-to-end Power BI analytics solution that transformed raw e-commerce data into three decision-focused dashboards covering:
Executive Performance · Product Strategy · Customer & Geographic Analysis
The project demonstrates end-to-end BI capability across data auditing, Power Query, data modeling, DAX, KPI design, data validation, and business analysis.
Portfolio Summary
Business Problem → Data Audit → Data Modeling → Transformation → KPI Design → DAX → Dashboard → Insights → Recommendations
Want a dashboard like this built for your business? Get in touch to talk about turning your operational data into something your leadership team can actually act on.