# otkwesi/salesanalytics_britainshoppers architecture

> Architecture diagram of the GitHub repository otkwesi/salesanalytics_britainshoppers, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-11). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/otkwesi/salesanalytics_britainshoppers
- Repository: https://github.com/otkwesi/salesanalytics_britainshoppers

## Overview

##### Project Type and Purpose
- This is a data‐analytics/reporting project for British Shoppers. It ingests transactional data from a relational database, runs SQL‐based data quality checks, cleans and transforms data, executes analytical queries, and outputs interactive dashboards and PDF reports.

##### File Structure
- Top‐level items:
- Business Report Request.pdf & Final_Sales_Report.pdf – static deliverables
- SQL_Data_Inspection_and_Validation_Checks.pdf – raw data QA scripts
- SQL_Data_Cleaning.pdf – ETL/transformation scripts
- SQL_Business_Analysis_Queries.pdf – analytical queries
- Sales_Dashboard.xlsx – final interactive output
- README.md – project overview and schema snapshot
- There is no application code or services; the “pipeline” is implemented entirely via SQL and Excel.

##### Key Insights from README
- Seven‐table relational schema (sales, products, categories, customers, regions, sales_reps, payment_methods)
- Workflow: data validation → cleaning → analysis → dashboard and report generation
- Outputs: executive PDF report + interactive Excel dashboard

##### Main Components
a. Data Source Layer
- On‐prem or cloud RDBMS (PostgreSQL/MySQL/SQL Server)
- Seven tables with ~150k rows (sales, products, etc.)
b. ETL & Data Quality Layer
- SQL scripts for inspection & validation
- SQL scripts for cleaning & transformation
c. Analytical Query Layer
- SQL business‐analysis queries grouped by question (trend, segmentation, geography, ranking)
d. Reporting & Visualization Layer
- Excel Power Pivot/Data Model + Power Query (Sales_Dashboard.xlsx)
- Static PDF generator (Final_Sales_Report.pdf)
- Narrative & charts embedded in report
e. GitHub Repository
- Version control for PDFs, SQL docs, dashboard template

##### Relationships and Data Flow
- Data flows from the RDBMS into the ETL layer (SQL cleaning) → moves to a staging/cleaned schema → analytical queries pull from cleaned schema → the results feed into:
- Excel dashboard via Power Query/ODBC
- PDF report (exported from Excel or via a BI-to-PDF tool)

##### Architectural Patterns and Principles
- Layered architecture: Source → ETL → Analytics → Presentation
- Separation of concerns: QA scripts are distinct from transformation scripts; analysis scripts are focused on business questions
- Version control of artifacts (SQL and reports) ensures reproducibility and audit trail

##### Elements
- Clear labels on all components (e.g., “Raw Sales DB”, “Validation SQL”, “Cleaned Schema”, “Analysis Queries”, “Excel Dashboard”, “PDF Report”)
- Annotations for technologies (e.g., “MySQL 8.0”, “ODBC Connection”, “Excel 365”)

##### Final Recommendations
- Be exhaustive: break out each SQL script group as its own component if needed.
- Call out any manual steps (e.g., “Run SQL scripts in sequence”, “Refresh Excel data model manually or via scheduled task”).

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Data Source Layer
    DB["Raw Sales DB\n(MySQL 8.0)\n150k rows\nMonthly Refresh"]:::storage
    click DB "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/README.md"

    %% Business Requirements
    BRD["Business Report Request.pdf"]:::output
    click BRD "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/Business Report Request.pdf"

    %% ETL & Data Quality Layer
    subgraph "ETL & Data Quality" 
        direction TB
        QA["Data Inspection & Validation SQL"]:::processing
        click QA "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/SQL_Data_Inspection_and_Validation_Checks.pdf"
        Cleaning["Data Cleaning & Transformation SQL"]:::processing
        click Cleaning "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/SQL_Data_Cleaning.pdf"
    end

    %% Staging Schema
    Staging["Cleaned/Staging Schema\n(Relational)"]:::storage

    %% Analytical Query Layer
    subgraph "Business Analysis Queries" 
        direction TB
        Trend["Trend Analysis Queries"]:::processing
        Segmentation["Segmentation Queries"]:::processing
        Geography["Geography Analysis Queries"]:::processing
        Ranking["Ranking Analysis Queries"]:::processing
        click Trend "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/SQL_Business_Analysis_Queries.pdf"
        click Segmentation "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/SQL_Business_Analysis_Queries.pdf"
        click Geography "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/SQL_Business_Analysis_Queries.pdf"
        click Ranking "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/SQL_Business_Analysis_Queries.pdf"
    end

    %% Reporting & Visualization Layer
    Dashboard["Excel Dashboard\n(Excel 365\nODBC Connection)"]:::output
    click Dashboard "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/Sales_Dashboard.xlsx"
    PDF["Final_Sales_Report.pdf"]:::output
    click PDF "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/Final_Sales_Report.pdf"

    %% Version Control
    GitHub["GitHub Repository\n(Version Control)"]:::version
    click GitHub "https://github.com/otkwesi/salesanalytics_britainshoppers/blob/main/README.md"

    %% Data Flow
    BRD -->|Defines requirements| QA
    DB -->|Raw data| QA
    DB -->|Raw data| Cleaning
    QA -->|Validated data| Staging
    Cleaning -->|Cleaned data| Staging
    Staging --> Trend
    Staging --> Segmentation
    Staging --> Geography
    Staging --> Ranking
    Trend -->|Query results| Dashboard
    Segmentation --> Dashboard
    Geography --> Dashboard
    Ranking --> Dashboard
    Dashboard -->|Export PDF| PDF

    %% Version Control Links
    GitHub -.-> DB
    GitHub -.-> QA
    GitHub -.-> Cleaning
    GitHub -.-> Trend
    GitHub -.-> Segmentation
    GitHub -.-> Geography
    GitHub -.-> Ranking
    GitHub -.-> Dashboard
    GitHub -.-> PDF
    GitHub -.-> BRD

    %% Styling
    classDef storage fill:#add8e6,stroke:#333,stroke-width:1px
    classDef processing fill:#90ee90,stroke:#333,stroke-width:1px
    classDef output fill:#ffcc99,stroke:#333,stroke-width:1px
    classDef version fill:#e0e0e0,stroke:#333,stroke-width:1px,stroke-dasharray: 5 5
```
