Consumer Goods: Ad-Hoc Analysis

GitHub Repo

Project Objective:

In this project I conducted Ad Hoc Analysis in Consumer Goods domain for AtliQ Hardware (a hypothetical computer hardware producer) from Sep 2019 to Aug 2021 using MySQL Workbench as part of the Codebasics Resume Project Challenge 4.

• Analyzed 5.3 million records of Finance and Supply Chain data revealing insights into Customer, Product and Market trends.
• Automated reporting using 12 Stored Procedures to generate Monthly Gross Sales and Top Customer/Product/Market by Net Sales/Quantity sold Reports, providing a holistic real-time view of AtliQ's financial performance.
• Calculated Fiscal Year and Fiscal Quarters using Database Functions. Optimized Financial Analysis queries by configuring 4 Database Views.
• Created Supply Chain Forecast Accuracy Reports across FY for a custom user input customer parameter using Temporary Tables. Configured Database Triggers on Monthly Sales and Forecast tables, to automatically update the data whenever new records are generated for precise Forecast Accuracy reporting thereby ensuring Data Integrity.
• Presented key findings like 214 % Product Portfolio Growth in Desktop Segment in FY 21, Q1 being Highest Inventory sold FY 20 Quarter, Retailer Channel's highest contribution of 73 % to Gross Sales in FY 21 and Flipkart leading with Highest Average Pre-invoice Discount offered at 30.83 %.

Analysis Insights

Project Presentation

Tools used:

• MySQL Workbench: for Data Cleaning, Data Manipulation (UDFs & Database Triggers ) and Data Analysis (Sub Queries, CTEs, Views, Temporary tables, Window Functions & Stored Procedures)
• Miro: for Database Modelling and crafting ERD
• Datawrapper: for Ad-hoc insight Visualizations
• GitHub: for Documentation

Skills: SQL, MySQL, Data Preparation, Data Transformation

Note: Some Analysis Insights have been shared below, rest of the insights and project details documented in the project presentation available in the GitHub Repo.