Monthly revenue and profit
p1_monthly_trend.sqlTop 3 alerts
p1_alerts.sqlActual shipping days by mode
Share of each mode's items by actual shipping days. Red bars arrive after the promised day (the vertical line).
p2_actual_days_distribution.sqlLate rate by shipping mode and region
Columns differ; rows barely do. Cells with fewer than 200 items are left blank.
p2_mode_region_heatmap.sqlOn-time and late rates
What if the promise matched reality?
p2_repromise_whatif.sqlMonthly late rate
p2_late_trend.sqlProfit margin by discount band
Profit as a share of net revenue. Flat bands mean the discount bought nothing extra.
p3_margin_by_band.sqlUnits per order line by discount band
If deeper discounts drove volume, these bars would rise to the right.
p3_margin_by_band.sqlCap discounts at a maximum rate
p3_whatif_cap.sql · notebooks/q2_discounts.ipynbCategories: at or below vs above a 10% discount
Click a row to filter the whole dashboard to that category. Gaps under 1 point are within noise for most categories (Holm-corrected tests in the Q2 notebook).
p3_category_band.sqlSegments as of 1 Oct 2017
Recency, frequency and monetary quintiles. Revenue is for the filtered orders.
p4_segment_summary.sqlShare ordering in the next 6 months
Segments scored on data to Mar 2017, outcome Apr to Sep 2017. Not affected by filters.
p4_rfm_validation.sqlMonthly cohort retention
Share of each cohort (first order in the filtered data) ordering again k months later. Cohorts under 50 customers hidden.
p4_cohort_retention.sql · p4_month3_retention.sqlCumulative share of profit and revenue
p5_product_pareto.sql · p5_kpis.sqlTop products by profit
"Sells well, loses money"
Top-half revenue with margin under 10%, plus any loss-making product. Most margins here sit within small-sample noise (Q4 notebook).
p5_sells_well_loses_money.sqlKPI definitions
| KPI | Definition |
|---|---|
| Revenue | Sum of order item total (net of discount), excluding cancelled and suspected-fraud orders |
| Profit | Sum of order item profit, same exclusions |
| Profit margin % | Profit ÷ revenue |
| Late-delivery rate | Order items where actual shipping days > scheduled days ÷ all valid order items |
| Avg delay days | Mean of (actual − scheduled) days over late items only |
| Orders | Distinct order ids among valid order items |
| Avg order value | Revenue ÷ orders |
| Discount spend | Sum of order item discount |
| Month-3 retention | Share of a monthly cohort's customers who order again in the third month after their first |
Data notes
- Source. DataCo Smart Supply Chain (Constante, Silva & Pereira, 2019), Mendeley Data v5, DOI 10.17632/8gx2fvg2k6.5, CC BY 4.0. Currency is treated as USD.
- Excluded statuses. CANCELED and SUSPECTED_FRAUD items (7,754) are excluded from every KPI and from this page's data.
- Default range. Jan 2015 to Sep 2017. From Oct 2017 every order has one item, the catalogue changes and almost all buyers are new, so trends and cohorts stop there. Extend "To" to see the full range.
- Market rotates with time. In a typical month most items go to one market, so market comparisons are also period comparisons.
- Discount rate. The rate actually applied (discount ÷ sales). Rows listed at 6% were applied at 5.5%.
- Observational. Every comparison is an association, not a cause. The what-if states its assumptions on the page.
Traceability
Each chart names the query in sql/analysis/ that produces its default-view numbers. scripts/check_dashboard.py loads this page and checks its numbers against those queries.