The client for this project is a sports company specializing in extreme activities, including modalities like skiing, hiking, climbing, and scuba diving, among others. However, the company’s database has not been properly analyzed, leaving many potential insights unexplored. The company seeks to uncover valuable information, particularly related to sales, clients, products, and distribution channels. To achieve this, a plan involving five sprint weeks, managed by the directors of various departments, has been proposed to extract and analyze this critical data.
The main objective of this project is to thoroughly analyze the company’s database to extract insights and valuable information related to sales, clients, products, and distribution channels. The project is designed to simulate real-world operations, with five sprint weeks planned and regular interactions with various departments. The insights gained from this analysis are expected to significantly enhance the company’s performance, leading to increased profitability and reduced costs.
This entire project is conducted using MySQL, an open-source relational database management system. Specifically, the MySQL Workbench IDE (Integrated Development Environment) is used for the database analysis.
In this project, we conducted an extensive analysis of a sports company’s database using SQL technology. The project was structured into five sprint weeks, aiming to uncover valuable insights and information, particularly related to sales, clients, products, and distribution channels.
1. First sprint week We collaborated with the IT Director to access the company’s database and made initial improvements to data quality, including checking granularity and eliminating duplicate records. At this stage, we also properly established the entity relationships for the corrected data.
2. Second sprint week Working with the Strategy and Marketing Directors, we conducted simple queries to gain an overview of database performance. We also gathered valuable insights on top channels, key clients, and their turnover trends.
3. Third sprint week Our focus was on margin analysis and cost reduction in the company’s portfolio, in collaboration with the Financial and Marketing Directors. We analyzed the main product lines, evaluated their turnover contributions, and identified trending products.
4. Fourth sprint week With the Sales Director, we developed a client segmentation model, categorizing stores into four main groups based on their performance, considering turnover and order volume. We also evaluated the growth potential of each client and created a system to identify inactive clients.
5. Fifth sprint week In the final sprint, we worked with the Financial Director to develop a basic yet effective recommendation system based on each store’s historical purchases. This system helps the company offer more relevant recommendations to clients, ultimately boosting profitability.
- 📁 Data:
- 📁 Images: Contains project images.
- project_database.sql: Database of the project.
- SQL_Project.sql: File containing all the queries developed in the entire database analysis project.
- Remember to update the project_path to the path where you have replicated the project.
- Open your preferred SQL IDE (we recommend MySQL Workbench for this project).
- Establish a new connection between the SQL server and your selected IDE.
- Load the project_database.sql file into the IDE. In MySQL Workbench, you can do this by navigating to:
- Server -> Data Import -> Import from Self-Contained File -> Start Import.
- Open the SQL_Project.sql file and run the queries as you scroll through to view the results.