Database Design, Querying & Analysis

Advancing an FMCG company's data maturity through effective data integration and analysis

Part 1: Database Design

Part 2: Querying & Data Analysis

Bonus: Query Optimization

Background

Managing an FMCG company comes with a complex set of challenges, particularly in optimizing the supply chain. The US Candy Distribution business handles 16 products across 3 categories—Chocolate, Sugar, and Other—manufactured in 5 plants across various states. Orders are received from 51 states, divided into 4 regions: Pacific, Interior, Gulf, and Atlantic.

⁠

Data maturity stages by Cobry (2024)

Data maturity stages by Cobry (2024)

⁠

Despite dealing with a large volume, velocity, and variety of data, the company struggles with low data maturity, as:

Data reporting is siloed within individual functions

Data is often inconsistent or incomplete

There is limited use of advanced analytics, and

Decision-making is largely driven by intuition rather than data-driven insights.

Recognizing the potential of business analytics to enhance performance and profitability, the Board of Directors has tasked me with laying the foundation for better data management and utilization. My objective is to make data-driven insights accessible and relevant to key stakeholders, including the Chief Financial Officer, Product Division Leads, and Regional Leads.

Objectives

The primary goal of this project is to elevate the company from Level 1 to Level 2-3 in the Data Maturity Framework.

Design a database schema normalized to the 3rd Normal Form (3NF) to facilitate efficient data integration from multiple sources and ensure high data quality.

Conduct comprehensive data analysis using SQL-extracted data to assist executives in making key decisions and initiatives, including:

💵 Regional Revenue Growth Analysis

How can monthly and yearly revenue growth across four key regions be tracked to support strategic planning for regional managers?

📊 Forecasting Method Evaluation

Which standardized forecasting methods provide the most accurate revenue projections for each division?

🛒 Product Line Discontinuation

How can sales and performance data help identify underperforming product lines for discontinuation and better portfolio management?

🏭 Factory Proximity Optimization

How can factory reassignment optimize the production and delivery of top-selling products by minimizing transportation costs?

There are no rows in this table

⁠

Demonstrate query optimization skills by conducting an experiment and outlining strategies that enhance data retrieval efficiency, reduce processing time, and improve overall system performance.

This project is only meant to demonstrate a subset of my skills. For machine learning, and web scraping samples, you may check out the other projects in my portfolio!

References

Wiktor, J. (2024, January). How to Conduct a Data Maturity Assessment. Corby. Retrieved from

https://www.cobry.co.uk/data-maturity-assessment⁠

⁠

Broken link⁠

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.