# sansk552/olist-ecommerce-sql-python-analysis architecture

> Architecture diagram of the GitHub repository sansk552/olist-ecommerce-sql-python-analysis, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-11-16). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/sansk552/olist-ecommerce-sql-python-analysis
- Repository: https://github.com/sansk552/olist-ecommerce-sql-python-analysis

## Overview

This repository is an Olist e-commerce analytics project, not a general application. Its documented workflow turns transactional data into SQL-based analytical datasets and business metrics, explores them in Python, and extends the analysis with bad-review prediction. The README also describes the prediction features, model comparison, and SHAP explanations. Source excerpts were unavailable, so relationships below follow only the README; individual SQL-query responsibilities and notebook internals are not inferred.

Main entry points: [`olist_analytical_dataset.csv`](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_analytical_dataset.csv) (Analytical datasets), [`master_dataset.sql`](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/master_dataset.sql) (Dataset construction), [`sql1.sql`](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/sql1.sql) (SQL metrics), [`Olist_analysis.ipynb`](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/Olist_analysis.ipynb) (Exploratory analysis)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_data["Data preparation"]
  node_master["Dataset construction<br/>[master_dataset.sql]"]
  node_analytical[("Analytical datasets")]
  node_complete[("Complete dataset")]
end

subgraph group_analysis["Business analysis"]
  node_sqlmetrics["SQL metrics<br/>[sql1.sql]"]
  node_sqlresults["SQL result files<br/>[sql1_output.csv]"]
  node_eda["Exploratory analysis"]
  node_insights["Business insights"]
end

subgraph group_prediction["Review prediction"]
  node_prediction["Bad-review prediction"]
  node_explanations["Model explanations"]
end

node_olist[("Olist data")]

node_olist -->|"transforms"| node_master
node_master -->|"produces"| node_analytical
node_analytical -->|"analyzes"| node_eda
node_analytical -->|"queries"| node_sqlmetrics
node_sqlmetrics -.->|"exports metrics"| node_sqlresults
node_eda -->|"derives"| node_insights
node_complete -.->|"supports"| node_prediction
node_prediction -->|"explains predictions"| node_explanations

click node_master "https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/master_dataset.sql"
click node_analytical "https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_analytical_dataset.csv"
click node_complete "https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_complete_analytical_dataset.csv"
click node_sqlmetrics "https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/sql1.sql"
click node_sqlresults "https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/sql1_output.csv"
click node_eda "https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/Olist_analysis.ipynb"
click node_prediction "https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_review_prediction.ipynb"
click node_explanations "https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_review_prediction.ipynb"

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_master,node_analytical,node_complete toneBlue
class node_sqlmetrics,node_sqlresults,node_eda,node_insights,node_olist toneAmber
class node_prediction,node_explanations toneMint
```

## Components

### Data preparation

- [Dataset construction](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/master_dataset.sql): `master_dataset.sql`
- [Analytical datasets](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_analytical_dataset.csv): `olist_analytical_dataset.csv`
- [Complete dataset](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_complete_analytical_dataset.csv): `olist_complete_analytical_dataset.csv`

### Business analysis

- [SQL metrics](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/sql1.sql): `sql1.sql`
- [SQL result files](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/sql1_output.csv): `sql1_output.csv`
- [Exploratory analysis](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/Olist_analysis.ipynb): `Olist_analysis.ipynb`
- **Business insights**

### Review prediction

- [Bad-review prediction](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_review_prediction.ipynb): `olist_review_prediction.ipynb`
- [Model explanations](https://github.com/sansk552/olist-ecommerce-sql-python-analysis/blob/main/olist_review_prediction.ipynb): `olist_review_prediction.ipynb`

### Other components

- **Olist data**

## Connections

- Olist data → Dataset construction: transforms (evidence: `README.md`)
- Dataset construction → Analytical datasets: produces (evidence: `README.md`)
- Analytical datasets → Exploratory analysis: analyzes (evidence: `README.md`)
- Analytical datasets → SQL metrics: queries (evidence: `README.md`)
- SQL metrics → SQL result files: exports metrics
- Exploratory analysis → Business insights: derives (evidence: `README.md`)
- Complete dataset → Bad-review prediction: supports
- Bad-review prediction → Model explanations: explains predictions (evidence: `README.md`)
