An end-to-end data analysis project — Excel → SQL Server → Power BI investigating whether delivery delays are quietly reducing Olist's repeat-purchase rate, and which regions need operational attention first.
Full write-up: Analysis Report · Dashboard: olist_dashboard.pbix · Portfolio: ayeshaforger.github.io
Olist's business executives wanted to know: is the platform's low repeat-purchase rate connected to delivery delays, and if so, where should operational improvements be prioritized to protect revenue and retention?
Customers whose first order arrived late had a 2.50% repeat-purchase rate, vs 3.06% for on-time or early deliveries, an 18.3% relative retention penalty tied directly to a customer's first delivery experience.
Five high-revenue Northeastern states (AL, MA, SE, PI, CE) show late-delivery rates as high as 21%, sit at the bottom of the retention rankings, and together represent $680K+ in revenue at risk.
Baseline metrics (delivered orders only): $15.42M total revenue · 96,470 delivered orders · 6.77% late-delivery rate · 3.00% overall repeat-purchase rate.
| Executive Overview -> 
| Regional Logistics -> 
| Retention Impact -> 
| Phase | Tool | What Happened |
|---|---|---|
| 1–2 | Excel | Initial exploration and structural audit of the raw Olist dataset |
| 3 | SQL Server | 8 non-destructive cleaning views built over the raw tables with anomalies flagged, never deleted, and logged in a 19-entry Data Cleaning Log |
| 4 | SQL Server | Exploratory analysis: cohort-based repeat-purchase analysis (ROW_NUMBER()-ranked first orders, HAVING COUNT(*) >= 300 sample-size guard) to isolate the delivery |
| 5 | Power BI / DAX | Lean two-table star model (vw_fact_orders + a DAX-built dim_date), with a planned inactive relationship + USERELATIONSHIP() for delivery-date time intelligence |
| 6 | Power BI | 3-page executive dashboard translating the SQL findings into a decision-ready story |
Full methodology, findings, and scope decisions are in the report.
olist-ecommerce-analysis/
├── README.md
├── LICENSE
├── .gitignore
├── report/
│ ├── olist_analysis_report.md
│ └── olist_analysis_report.pdf
├── sql/
│ ├── 00_data_audit.sql
│ ├── 01_data_cleaning.sql
│ ├── 02_eda_analysis.sql
│ └── vw_fact_orders.sql
├── documentation/
│ └── olist_data_cleaning_log.xlsx
├── power_bi/
│ ├── olist_dashboard.pbix
│ └── screenshots/
└── data/
└── README.md
SQL Server Power BI DAX Excel Power Query
Product, seller, and customer-satisfaction dimensions were deliberately excluded from the data model. This was a locked scope decision, not an oversight, made to keep the analysis focused on the delivery → retention question the business asked. Details and the reasoning are in the report.
Ayesha Muqadas CS undergraduate, Data Analyst LinkedIn · GitHub · Portfolio