You just got hired as a data scientist within a manufacturing company with an international presence. The management is excited to have you on board and eager to see how you will provide periodic reports to support their decision making.
The management is particularly very interested in generating sales reports to understand sales patterns. A week after your resumption, you get a mail from your boss with the following details:
Hello,
Welcome on board...The management team is going on a retreat and we plan to do the following:
- Commence a loyalty program for our top customers
- Make more investment into locations with highest sales
- Make more investment into products with highest sales and profitability
- Commence a reward program for our most hard working workers
- Identify key customer segments within the beverage product line to prioritize
Kindly provide us with reports which will give us insights and aid our decision making.
- Your organization uses a mix of Microsoft based products and open source tools.
- PowerBI for visualization and automated reports
- The IT team says the data currently sits in an SQL server database. They have extracted the database tables in csv files because they are unable to grant you access to the database. There are 7 tables containing customer and sales information. Import these csv files into a MySQL database on your own system.
- Using SQL, generate the reports in CSV tables which will provide the management team with insights required for items 1-5 in the mail from your boss.
- Using PowerBI, you are expected to create stunning visual reports on items 1-4. Your organization has predefined PowerBI templates for its reports developed by a consultant. The consultant refers you to the link below to help you get inspiration on captivating templates... https://youtu.be/KZDFw_G-Rj8. Answering item 5 in PowerBI is optional.
Tip - for the key customer segments, you can explore using an RFM segmentation model (recency, frequency and monetary model). Resources on RFM will be provided during the course.
- Generate table reports in CSV which will provide the management team with insights required for items 1-5 in the brief. Complete this in SQL
- Create stunning PowerBI visualisations which will guide the management team in making optimal decisions for items 1-4 in the brief. Complete this in PowerBI
- Write commentary advising management on the optimal decisions based on your dashboards/reports. Complete the commentary in a 1-2 page Powerpoint document.
- Send your submissions to submissions@datajedi.io . You will be presenting your dashboards and commentary.