# peachalex/delivery-delay-analysis architecture

> Architecture diagram of the GitHub repository peachalex/delivery-delay-analysis, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-09-29). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/peachalex/delivery-delay-analysis
- Repository: https://github.com/peachalex/delivery-delay-analysis

## Overview

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`](https://github.com/peachalex/delivery-delay-analysis/blob/main/docker-compose.yaml) (PostgreSQL), [`dashboard/delay_rate_dashboard.pbix`](https://github.com/peachalex/delivery-delay-analysis/blob/main/dashboard/delay_rate_dashboard.pbix) (Interactive Power BI dashboard), [`sql/Overall_delay_rate.sql`](https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Overall_delay_rate.sql) (Overall delay SQL), [`sql/Delay_rate_by_month.sql`](https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Delay_rate_by_month.sql) (Monthly delay SQL)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_ingest["Data setup"]
  node_postgres[("PostgreSQL")]
end

subgraph group_sql["SQL analysis"]
  node_overall_sql["Overall delay SQL"]
  node_monthly_sql["Monthly delay SQL"]
  node_state_sql["State delay SQL"]
  node_category_sql["Category delay SQL"]
  node_finance_sql["Freight reconciliation SQL"]
  node_overall_csv["Overall delay results"]
  node_monthly_csv["Monthly delay results"]
  node_state_csv["State delay results"]
  node_category_csv["Category delay results"]
end

subgraph group_python["Notebook analysis"]
  node_month_notebook["Monthly time-series analysis"]
  node_state_notebook["State analysis"]
  node_category_notebook["Category analysis"]
end

subgraph group_report["Reporting"]
  node_dashboard["Interactive Power BI dashboard"]
end

node_analyst(("Analyst"))
node_source_data[("Order dataset")]

node_analyst -->|"loads data"| node_postgres
node_source_data -->|"provides orders"| node_postgres
node_analyst -->|"runs query"| node_overall_sql
node_analyst -->|"runs query"| node_monthly_sql
node_analyst -->|"runs query"| node_state_sql
node_analyst -->|"runs query"| node_category_sql
node_analyst -->|"runs query"| node_finance_sql
node_overall_sql -->|"queries"| node_postgres
node_monthly_sql -->|"queries"| node_postgres
node_state_sql -->|"queries"| node_postgres
node_category_sql -->|"queries"| node_postgres
node_finance_sql -->|"compares tables"| node_postgres
node_overall_sql -->|"exports results"| node_overall_csv
node_monthly_sql -->|"exports results"| node_monthly_csv
node_state_sql -->|"exports results"| node_state_csv
node_category_sql -->|"exports results"| node_category_csv
node_analyst -->|"loads results"| node_month_notebook
node_monthly_csv -->|"analyzed by"| node_month_notebook
node_analyst -->|"loads results"| node_state_notebook
node_state_csv -->|"analyzed by"| node_state_notebook
node_analyst -->|"loads results"| node_category_notebook
node_category_csv -->|"analyzed by"| node_category_notebook
node_analyst -->|"imports results"| node_dashboard
node_overall_csv -->|"visualized in"| node_dashboard
node_monthly_csv -->|"visualized in"| node_dashboard
node_state_csv -->|"visualized in"| node_dashboard
node_category_csv -->|"visualized in"| node_dashboard

click node_postgres "https://github.com/peachalex/delivery-delay-analysis/blob/main/docker-compose.yaml"
click node_overall_sql "https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Overall_delay_rate.sql"
click node_monthly_sql "https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Delay_rate_by_month.sql"
click node_state_sql "https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Delay_rate_by_state.sql"
click node_category_sql "https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Delay_rate_by_product_category.sql"
click node_finance_sql "https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/freight_price_percentage.sql"
click node_overall_csv "https://github.com/peachalex/delivery-delay-analysis/blob/main/data/processed/Overall_delay_rate.csv"
click node_monthly_csv "https://github.com/peachalex/delivery-delay-analysis/blob/main/data/processed/Delay_rate_by_month.csv"
click node_state_csv "https://github.com/peachalex/delivery-delay-analysis/blob/main/data/processed/Delay_rate_by_state.csv"
click node_category_csv "https://github.com/peachalex/delivery-delay-analysis/blob/main/data/processed/Delay_rate_by_category.csv"
click node_month_notebook "https://github.com/peachalex/delivery-delay-analysis/blob/main/python_scripts/delay_analysis_by_month.ipynb"
click node_state_notebook "https://github.com/peachalex/delivery-delay-analysis/blob/main/python_scripts/delay_analysis_by_state.ipynb"
click node_category_notebook "https://github.com/peachalex/delivery-delay-analysis/blob/main/python_scripts/delay_analysis_by_category.ipynb"
click node_dashboard "https://github.com/peachalex/delivery-delay-analysis/blob/main/dashboard/delay_rate_dashboard.pbix"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_postgres toneBlue
class node_overall_sql,node_monthly_sql,node_state_sql,node_category_sql,node_finance_sql,node_overall_csv,node_monthly_csv,node_state_csv,node_category_csv,node_source_data toneAmber
class node_month_notebook,node_state_notebook,node_category_notebook toneMint
class node_dashboard toneRose
class node_analyst toneIndigo
```

## Components

### Data setup

- [PostgreSQL](https://github.com/peachalex/delivery-delay-analysis/blob/main/docker-compose.yaml): `docker-compose.yaml`

### SQL analysis

- [Overall delay SQL](https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Overall_delay_rate.sql): `sql/Overall_delay_rate.sql`
- [Monthly delay SQL](https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Delay_rate_by_month.sql): `sql/Delay_rate_by_month.sql`
- [State delay SQL](https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Delay_rate_by_state.sql): `sql/Delay_rate_by_state.sql`
- [Category delay SQL](https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/Delay_rate_by_product_category.sql): `sql/Delay_rate_by_product_category.sql`
- [Freight reconciliation SQL](https://github.com/peachalex/delivery-delay-analysis/blob/main/sql/freight_price_percentage.sql): `sql/freight_price_percentage.sql`
- [Overall delay results](https://github.com/peachalex/delivery-delay-analysis/blob/main/data/processed/Overall_delay_rate.csv): `data/processed/Overall_delay_rate.csv`
- [Monthly delay results](https://github.com/peachalex/delivery-delay-analysis/blob/main/data/processed/Delay_rate_by_month.csv): `data/processed/Delay_rate_by_month.csv`
- [State delay results](https://github.com/peachalex/delivery-delay-analysis/blob/main/data/processed/Delay_rate_by_state.csv): `data/processed/Delay_rate_by_state.csv`
- [Category delay results](https://github.com/peachalex/delivery-delay-analysis/blob/main/data/processed/Delay_rate_by_category.csv): `data/processed/Delay_rate_by_category.csv`

### Notebook analysis

- [Monthly time-series analysis](https://github.com/peachalex/delivery-delay-analysis/blob/main/python_scripts/delay_analysis_by_month.ipynb): `python_scripts/delay_analysis_by_month.ipynb`
- [State analysis](https://github.com/peachalex/delivery-delay-analysis/blob/main/python_scripts/delay_analysis_by_state.ipynb): `python_scripts/delay_analysis_by_state.ipynb`
- [Category analysis](https://github.com/peachalex/delivery-delay-analysis/blob/main/python_scripts/delay_analysis_by_category.ipynb): `python_scripts/delay_analysis_by_category.ipynb`

### Reporting

- [Interactive Power BI dashboard](https://github.com/peachalex/delivery-delay-analysis/blob/main/dashboard/delay_rate_dashboard.pbix): `dashboard/delay_rate_dashboard.pbix`

### Other components

- **Analyst**
- **Order dataset**

## Connections

- 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