FP20 Analytics September 2024 Challenge E-commerce KPIs

About the dataset

You are tasked with creating a comprehensive Power BI report using an order dataset from a global skincare and beauty e-store.

Your report should analyze sales volumes and profitability across various product groups and markets, focusing on key performance indicators (KPIs) critical to the e-store's success.

Specifically, the e-store aims to achieve an average annual profit margin of 15% across all product groups, 20% annual overall sales growth with a higher target of 30% for the corporate segment, and at least $400K in annual sales for each market.

The final report should not only visualize these KPIs but also provide actionable insights that can drive strategic decision-making for the e-store's future growth.

What have we found with our Analysis?

With the analysis done, we can see that e-store general KPIs objectives had been:

• • Average annual profit of 15% across all product groups : Achieved, average annual profit is 16,3%.

Body Care Profit Margin : Achieved : 22%

Face Care Profit Margin : Achieved : 35,7%

Hair Care Profit Margin : Not achieved : 0,4 %

Home & Accessories Profit Margin : Not Achieved : -4,6%

Make up Profit Margin : Achieved : 32,9 %

• 20% annual overall sales growth with a higher target of 30% for the corporate segment, and at least $400K in annual sales for each market.

Africa : Both 20% annual overall sales growth & $400k in annual sales : Not achieved

Asia Pacific : 20% annual overall sales growth : Achieved & $400k in annual sales : Not achieved in 2020 & 2021. Achieved in 2022 & 2023

Europe : 20% annual overall sales growth : Achieved & $400k in annual sales : Not achieved in 2020 & 2021. Achieved in 2022 & 2023

LATAM : 20% annual overall sales growth : Not achieved in 2020. Achieved in 2021 & 2022. $400k in annual sales : Not achieved in 2020, 2021 & 2022. Achieved in 2023.

USCA : 20% annual overall sales growth : Not achieved in 2020 & 2021. Achieved in 2022 . $400k in annual sales : Not achieved in 2020, 2021 & 2022. Achieved in 2023.

In addition, we have found answers to the following questions:

1. What were the most profitable segments from 2020 to 2023? Most profitable segment was Corporate, followed by Consumer & last one by difference is Self-Employed.
2. Can we have a map visual to interact with locations to see the profit distribution? We can see it in first page right below visual.
3. What were the most profitable markets since 2020? Profit Ranking by Markets are: Europe, Asia-Pacific, USCA, Latam & Africa.
4. What were the best selling products since 2020? Best selling products since 2020 are: Herbal Essences Bio, Rose Gold Petal Studs, Sterling Wave Earrings, Golden Blace Necklace & Sterling Solstice Anklet.
5. Following above, can we also view the best selling categories and subcategories? Best selling categories are: Body Care, Home & Accessories, Make up, Hair Care & Face Care. Top 3 subcategories are: Nail Care Products, Shampoos and Conditioners & Eye Shadows & Pencils.
6. In each market, can we view the sales trend of each product/category/subcategory from 2020 to 2023? Ranking of Sales for each category is: Body Care, Make up, Home and Accessories, Face Care & Hair Care.
7. In each market, can we view the profits trend of each product/category/subcategory from 2020 to 2023? Ranking of Profit for each category is: Body Care, Make up, Face Care, Hair Care & Home and Accessories.
8. Did we have a good discount strategy last a few years? Discount Strategy can be improved, as we can saw in the Sales vs Profits Distributions & also in the last page table . It shows us the products that are giving us negative profit, and it's average discount rate. If we focus on top products, we can see in the last 2 columns (Profits and Discounts) which ones are giving us problems. If that strategy is to clear these failed buys in our stock, we should talk to the suppliers or take care of what we buy in the future. The main goal is to achieve profits through sales not negative profits.
9. Can we list top 10 customers who spent most from each country every year? They are listed in the upper left table, and can be filtered by year between 2020 - 2023.
10. Which countries contributed most profits to each category? We can see the country sales distribution in the last page right visual. In resume, for the most profitable categories the top 3 are:

Body Care : USA, France & Mexico.

Make up : USA, France & Germany.

Face Care : USA, Australia & Mexico.