Lumessa Sales Analysis (2020-23)

Overview

Lumessa is a global skincare and beauty e-store. It has a direct to consumer (D2C) model where it sells its own products via the e-store. The foregoing analysis of sales and profitability will be used by the finance team and the marketing team, to guide budgeting and investment decisions by the former and pricing adjustment and recalibration of efforts by the latter. The objective is to analyse sales volumes and profitability across various product groups and markets, focussing on key performance indicators (KPIs) critical to the e-store’s success.

North star metrics and dimensions

Sales : the dollar value of products sold, available as feature sales
Number of orders : the count of orders including repeat orders by customers
Profit margin : the value of profit divided by the value of sales
Average order value : for repeat customers, it is the combined dollar value of all orders divided by number of orders
Segments : There are three segment of customers - Consumer, Corporate and Self-Employed
Markets : There are five markets - Africa, USCA (US and Canada), LATAM (Latin America), Europe, Asia Pacific
Categories : There are five distinct categories - Make Up, Face Care, Body Care, Hair Care, and Home and Accessories
Subcategories : There are 17 distinct subcategories divided across the five categories

Goals

Sales performance analysis : The analysis is done to better understand sales growth, trend, seasonality and any anomalies, if present. The performance will be compared across markets, segments and categories, to find areas that drive sales and which under-perform. This will help in formulating actionable insights to achieve the annual target of $400K in annual sales in each market. In addition, there is an annual sales growth target of 20% for all segments and a higher target of 30% for corporate segment.
Profitability analysis : This is done to identify markets, segments and categories that yield a high profit margin and those that yield a low profit margin. In addition, profit margin will be tracked across theses areas to achieve the company’s target of average annual profit margin of 15% across all product groups.

Data structure overview

The dataset is taken from the following source: zoomchart-challenge

The data is structured in the following way:

orders - This table contains details of the date on which order was placed, customer who placed the order, delivery address of the order and the associated region and market.
order_details - It contains details of products included in an order, along with their sales, quantity, discount and profit.
products - It contains product names, and the category and subcategory to which they belong.
customer - It maps each customer to the segment they belong.

Insights

Sales performance analysis

The overall annual sales have seen an upward trend growing from $1.15 million in 2020 to $2.19 million in 2023. There are seasonal variations with Q1 of each year experiencing a dip as compared to Q4 of last year.
The best-selling category overall between 2020 and 2023 is Body Care and the best-selling subcategory is Nail care products.
The best-selling product overall in 2020 was Rose Gold Petal Studs, followed by Herbal Essences Bio. In 2021, Silver Frost Bracelet occupied the top position, followed by Herbal Essences Bio. In 2022 and 2023, Herbal Essences Bio remained in the top spot.
From 2020 to 2022, the annual sales target of $400k was surpassed among all market by only Asia-Pacific once in 2022. In 2023, all markets except Africa achieved the target. The highest sales were in Asia-Pacific totalling to $606k whereas Africa achieved sales totalling $163k.
In 2022, Africa clocked sales growth of 45%, which was driven mainly by increased sales in Corporate segment by 50%. In 2023, Consumer segment showed higher sales growth.
Average discount in Africa has hovered between 30% and 50%, which is much higher than the world average. With profit margin among the lowest in markets (13.6% as compared to the world’s 16.35%), the discount strategy has already been exhausted.
The average order value in Africa is $213, which is lower than the world average of $253. The proportion of one-time customers in Africa is 89.83%, which is much higher than the world average at 68.79%. Repeat customers tend to have a higher order value.
From 2020 to 2023, overall Corporate segment has driven the most sales, followed by Consumer segment.
The annual overall sales growth target of 20% was met in all years by all segments starting from 2021 till 2023. However, the Corporate sales growth target of 30% has not been achieved yet in any year. In the most recent two years (2022 and 2023), Corporate sales growth has hovered around 25%.
Corporate segment has a high base, with its sales surpassing Consumer and Self-Employed segments every year between 2020 and 2023. Within the Corporate segment, Make up category has shown growth potential, with its sales surpassing Hair care category in 2023. Body care remains the best-selling category while Face care is the least-selling category.
In subcategories, Nail care products (category: Body care) have been best-selling, while the second and third spots have been occupied by Eye shadows and pencils (category: Make up) and Shampoos and conditioners (category: Hair care).
The average order value within the Corporate segment is $497, much higher than the world average of $253.
The discount has varied between 20-25% which is on par with the average discounting strategy.

Profitability analysis

The most profitable segment from 2020 to 2023 was Corporate, followed by Consumer segment.
The most profitable market between 2020 and 2023 was Europe. Africa remained the market with the least profits. Second to fourth position traded between the rest, with Asia-Pacific occupying the second position in 2020 and 2021, Latin America in 2022, and United States and Canada in 2023.
Home and Accessories category has had a negative profit margin for all years between 2020 and 2023. Hair care category’s profit margin has been below 1% in the same period. These two remain the worst performing in terms of profit margin. In the remaining three categories, the average annual profit margin of 15% has been met in all years between 2020 and 2023. However, the profit margin for these three categories has been declining over the years.
Fragrances (category: Home and Accessories) have been the worst-performing subcategory with a profit margin remaining below (-)15% in all years between 2020 and 2023. Similarly, all other subcategories in Home and Accessories, and Hair care category have either had negative profit margin or a sub-1% profit margin in all these years. In body care, face mask and exfoliators and body moisturisers have had a negative profit margin. In all other subcategories profit margin has been above 15%.

Recommendations

In order to achieve annual sales target of $400K in Africa we can focus on increasing average order value in Africa by building customer loyalty. This can be achieved by giving the existing discounts on successive purchases rather than current purchases. With customer’s making repeat purchases, average order value will increase.
In order to increase sales and align with the growth target, a higher discounting strategy for corporate segment can be explored.
To increase the overall profit margin, sales of Home and Accessories and Hair care category can be curtailed, or prices in these categories can be increased to boost the profit margin.