A full end-to-end analysis. Excel, SQL Server, and Power BI. Author: Ayesha Muqadas
This report looks at whether delivery delays are contributing to Olist's low repeat-purchase rate, and identifies which regions offer the best opportunity for operational improvement.
The data shows a clear relationship. Customers whose first order arrived late had a repeat-purchase rate of 2.50%, compared to 3.06% for customers whose first order arrived on time or early. That is an 18.3% relative drop in retention tied directly to the outcome of a customer's very first delivery. Five high-revenue states in Brazil's Northeast region have late-delivery rates as high as 21% and rank at the bottom for retention, together accounting for more than $680K in revenue at risk.
Audit Olist's sales trend for business executives to determine whether delivery delays are driving the platform's low repeat-purchase rate, and identify high-delay regions where operational improvements could lift retention and revenue.
This framing was set at the start of the project and shaped every decision about what to include and exclude, covered in Section 6.
The analysis uses the public Brazilian E-Commerce dataset released by Olist, which spans eight relational tables covering orders, order items, payments, reviews, products, customers, sellers, and geolocation. Raw data is not included in the project repository; see the dataset note here: data/README.md. The full source is available on Kaggle.
Eight non-destructive cleaning views were built directly over the raw tables: vw_orders_cleaned, vw_order_reviews_cleaned, vw_products_cleaned, vw_geolocation_cleaned, vw_order_items_cleaned, vw_order_payments_cleaned, vw_customers_cleaned, and vw_sellers_cleaned. Every anomaly found was flagged, not deleted, so the underlying data stayed intact and every cleaning decision could be reviewed later.
A couple of decisions worth noting:
- Geolocation records were deduplicated by ZIP code prefix, averaging latitude and longitude and keeping the most common city and state per prefix.
- Duplicate reviews were resolved by keeping the most recent
review_answer_timestampper order.
All 19 cleaning decisions, including what the issue was, its scope, the decision made, and the reasoning behind it, are documented in the Data Cleaning Log.
The core question, whether a late first delivery predicts lower repeat-purchase behavior, was tested with a cohort approach. ROW_NUMBER() ranked each customer's orders chronologically to isolate their first order, then repeat-purchase rates were compared between customers whose first order arrived late versus on time or early. A HAVING COUNT(*) >= 300 threshold was applied at the state level so no conclusion was drawn from a small sample.
Baseline metrics (delivered orders only):
| Metric | Value |
|---|---|
| Total revenue | $15.42M |
| Delivered orders | 96,470 |
| Late-delivery rate | 6.77% |
| Overall repeat-purchase rate | 3.00% |
The Power BI model was kept simple: one SQL-built fact view, vw_fact_orders (order-level grain, with customer state and total payment), joined to a DAX-built dim_date table. The active relationship runs on purchase timestamp. An inactive relationship with USERELATIONSHIP() was planned to support delivery-date time intelligence without affecting the main purchase-date analysis. SQL-calculated figures were checked against their DAX equivalents and matched, confirming the model reflects the underlying SQL analysis correctly.
| Page | Title | Visual Contents |
|---|---|---|
| 1 | Executive Overview: Revenue Is Growing, But a Delivery Risk Is Emerging | KPI cards (late-delivery rate flagged at 6.77%), revenue trend over time |
| 2 | Regional Logistics & Delivery Performance | Late-rate bar chart by state, revenue and orders detail table, combo chart comparing late-rate against repeat-rate by region |
| 3 | How Late Deliveries Are Costing Us Repeat Customers | Bar chart comparing late (2.64%) vs. early/on-time (3.04%) repeat-purchase rate, donut chart of single vs. repeat customer share (97% / 3%), and average spend comparison ($309 repeat vs. $161 single-order customers) |
Note: Page 3's figures come from the dashboard's DAX calculation, while Section 3.2's figures (2.50% / 3.06%) come from the underlying SQL cohort analysis. The two use slightly different aggregation methods but point to the same conclusion: a late first delivery measurably reduces repeat-purchase behavior.
- A late first delivery costs Olist repeat customers. The 18.3% relative retention penalty is the clearest, most actionable finding in this analysis.
- The risk is concentrated geographically. Five high-revenue Northeastern states, Alagoas (AL), Maranhão (MA), Sergipe (SE), Piauí (PI), and Ceará (CE), combine late-delivery rates up to 21% with the lowest retention, representing over $680K in exposed revenue.
- Repeat customers are worth protecting. They make up only about 3% of the customer base but spend nearly double per order ($309 vs. $161), so even small retention gains carry outsized revenue impact.
- Prioritize logistics investment in the five flagged Northeastern states. Carrier renegotiation, warehousing changes, or delivery-partner audits are likely to have the highest retention return of any operational lever available.
- Treat the first delivery as a retention-critical moment. Consider a proactive recovery step, such as a discount code or priority support, for any customer whose first order is flagged as at risk of arriving late.
- Track the relationship between late-delivery rate and repeat-purchase rate as an ongoing KPI, not a one-time finding.
Product category, seller-level, and customer-satisfaction (review-score) data were left out of the model on purpose. The problem statement centers on delivery timing and retention, and adding dimensions unrelated to that question would have diluted the analysis and made the dashboard harder to read.
Separately, Power BI's desktop performance was a real constraint. The file crashed twice during development on the hardware used, which reinforced the case for keeping the model small. Both the scope decision and the hardware limitation are noted here directly, since both shaped why this analysis didn't expand further into seller, product, or satisfaction data in this version.
Delivery delays are not a side issue for Olist. They are measurably tied to whether a customer buys again. This report gives a defensible number, an 18.3% relative retention penalty, and a short list of five states where fixing delivery performance is likely to pay back fastest in retained revenue.
Full SQL scripts, the Power BI file, and the data cleaning log are available in the project repository. See the README for the full project structure.