Mini Project: AtliQ sales data analysis and dashboard visualization

SQL, MySQL, PowerBI, Python, ETL, data analysis, data visualization

Mason Phung

Read the pdf report atliq_sales_analysis_report.pdf for a quick project summary.

A comprehensive data analysis project with SQL and PowerBI.

AtliQ is a B2B hardware & peripheral manufacturer headquartered in Mumbai, they have many regional branches across India. The company provides computer and network equipments for other businesses. In the previous quarter, the company was reported to have declining sales and their Sales director is having trouble tracking where business is falling in the local Indian market. We will help them to determine the issues by analyze and visualize their sales data.

Data analysis questions:

• Are there any trends on customer purchase behavior throughout the years?
• Is the any issues with certain products/markets/customers?
• What are the bottlenecks/problems that cause the decline in sales in certain markets?
• Any suggestions can be made to tackle found issues?

Tasks:

• Data Architecture & ETL: Designed and implemented a local MySQL database to consolidate 3 years of historical sales data, ensuring data integrity through rigorous SQL-based cleaning and transformation.
• Diagnostic SQL Analysis: Conducted deep-dive exploratory and diagnostic analysis to uncover the structural bottlenecks driving declining sales, specifically isolating customer concentration risks and unprofitable product margins.
• Business Intelligence & Automation: Architected an interactive Power BI dashboard to replace manual reporting processes, equipping management with real-time, actionable insights to resolve sales bottlenecks.

Some insights explored:

• Operational costs consuming 97.5% of total revenue.
• 46% of all transactions yields negative margins.
• 9 consumers carried the weight of 75% of the company's revenue

Findings: Two major issues found that could help to determine sales bottlenecks:

• High cost
• Dependency on major customers

By providing data-driven suggestions, I expected:

• Reduce stakeholders decision-making by 50%,** in 3-6 months, by providing access to a live PowerBI dashboard for real-time visual insights.
• Reduce operational costs by 40%, in the next year, through transforming smaller-revenue accounts to automated or low-touch e-commerce model.
• Increase the revenue generated from major accounts by 10% in the next year, by implementing VIP Priority programs.

To do next: Observe how the dashboard operate, gather informations and using data from stakeholders/users to further improve the dashboard.

Acknowledgement

• Special thanks to codebasics for problem statement and case explanation.
• Data mining, analysis and visualization by Mason Phung.

Project processes

• Step 1: Database management & Data processing: Set up local MySQL database and import data by reading .sql dumb file.
• Step 2: Data cleaning: Using Python to clean data, then export them back to MySQL database.HERE
• Step 3: Exploratory analysis: Using SQL, with the help of sqlalchemy to present the analysis. HERE
• Step 4: Data visualization: Using PowerBI, load the data directly from MySQL database. HERE
• Step 5: Report: Present findings, answer case questions & suggestions. HERE

Tools

• Local database: MySQL
• Database management: MySQL Benchmark or DBeaver (for its compatibility with MacOS ARM).
• IDE: Visual Studio Code
• Python libraries: sqlalchemy (to use SQL on Jupyter Notebook)
• Data cleaning & analysis: SQL
• Dashboard: PowerBI/Tableau(old version)

Latest update: Ver 3.1 - Convert all data cleaning process from Python pandas to SQL, refine reports & README

Ver 3.0

• Divide notebook into seperate files for easy interpretation.
• Add data_cleaning.py for quick data cleaning.
• Update linguist to detect python and sql.
• Add a SQL script includes some scripts used.

Ver 2.0

• Upload a jupyter notebook includes SQL queries and Python data manipulation process
• Create a refined interactive dashboard using Power BI

Uploads

• 2026/03: Update ver 3.1
• 2024/12: Update ver 3.0
• 2024/08: Update ver 2.0
• 2023/10: Update ver 1.0