How peachalex/delivery-delay-analysis works
This repository presents an analytics workflow for investigating U.S. e-commerce delivery delays: load order data into PostgreSQL, calculate delay metrics in SQL, analyze exported results in notebooks, and present findings in Power BI. It also documents financial reconciliation between item and payment totals. The README describes the intended handoffs; sampled source was limited to requirements.txt, so detailed runtime wiring cannot be verified.
Main entry points: docker-compose.yaml PostgreSQL · delay_rate_dashboard.pbix Interactive Power BI dashboard · Overall_delay_rate.sql Overall delay SQL · Delay_rate_by_month.sql Monthly delay SQL
Components 16
Data setup
- PostgreSQL
docker-compose.yaml
SQL analysis
- Overall delay SQL
sql/Overall_delay_rate.sql - Monthly delay SQL
sql/Delay_rate_by_month.sql - State delay SQL
sql/Delay_rate_by_state.sql - Category delay SQL
sql/Delay_rate_by_product_category.sql - Freight reconciliation SQL
sql/freight_price_percentage.sql - Overall delay results
data/processed/Overall_delay_rate.csv - Monthly delay results
data/processed/Delay_rate_by_month.csv - State delay results
data/processed/Delay_rate_by_state.csv - Category delay results
data/processed/Delay_rate_by_category.csv
Notebook analysis
- Monthly time-series analysis
python_scripts/delay_analysis_by_month.ipynb - State analysis
python_scripts/delay_analysis_by_state.ipynb - Category analysis
python_scripts/delay_analysis_by_category.ipynb
Reporting
- Interactive Power BI dashboard
dashboard/delay_rate_dashboard.pbix
Other components
- Analyst
- Order dataset
Connections 27
- Analyst → PostgreSQL: loads data
- Order dataset → PostgreSQL: provides orders
- Analyst → Overall delay SQL: runs query
- Analyst → Monthly delay SQL: runs query
- Analyst → State delay SQL: runs query
- Analyst → Category delay SQL: runs query
- Analyst → Freight reconciliation SQL: runs query
- Overall delay SQL → PostgreSQL: queries
- Monthly delay SQL → PostgreSQL: queries
- State delay SQL → PostgreSQL: queries
- Category delay SQL → PostgreSQL: queries
- Freight reconciliation SQL → PostgreSQL: compares tables
- Overall delay SQL → Overall delay results: exports results
- Monthly delay SQL → Monthly delay results: exports results
- State delay SQL → State delay results: exports results
- Category delay SQL → Category delay results: exports results
- Analyst → Monthly time-series analysis: loads results
- Monthly delay results → Monthly time-series analysis: analyzed by
- Analyst → State analysis: loads results
- State delay results → State analysis: analyzed by
- Analyst → Category analysis: loads results
- Category delay results → Category analysis: analyzed by
- Analyst → Interactive Power BI dashboard: imports results
- Overall delay results → Interactive Power BI dashboard: visualized in
- Monthly delay results → Interactive Power BI dashboard: visualized in
- State delay results → Interactive Power BI dashboard: visualized in
- Category delay results → Interactive Power BI dashboard: visualized in