How otkwesi/salesanalytics_britainshoppers works
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”).