Chun-Jie Cai's Data Science Blog

Sales analysis enables the expansion of Buycycle's market

Why do I want to perform this case study?

As mentioned in the case study of Speedybike, I enjoy riding a bike to get to know a new city. The case study of Speedybike provides recommendations to convert casual riders into subscribers for the sustainable growth. However, for bike lovers, they would prefer to own their personal bikes instead of renting a public bike. Therefore, I am curious about the sales market of bikes and the related products.

Background

Bikes become trendy among young generations as it is more convenient and affordable than taking public transportation in terms of short-distance commuting. In addition, there is a gorwing interest of buying a bike rather than renting a bike since it is more accessable and flexible in customization. Buycycle (a fictional company) arising among this trend in 2015 as a local retail store of bike. Step by step, it opened branchs in other cities, continents , and becomes an international company selling not only bikes, but also biking clothes and accessories.

Challenges

David Brunoro is the director of Global Marketing looking for an effective strategy to advance their business. Knowing the performance of product sales is a critical step before making a strategy. Particularly, he would like to understand the KPIs of product sales across continents in time series.

Data source

The data used in this case study was provided by this course "Power BI Desktop for Business Intelligence" with permission to share the analysis and visualization based on the data.

Tools

Microsoft Power BI, including DAX and Query Editor

Data cleaning and process

About 56K customer data was processed and a data model was build by interacting 8 datasets, including sales, geographical, time series datasets.

Data analysis and visualization

To analyze the sales data, we need to define key performance indicators. Here I focus on product orders, returns, and profits. Through looking into the product orders, we can know which products are popular, while product returns show the customer satisfacations among products, and it potential allows us either to improve qualities of the products, or to redesign them. Though products can be popular with high qualities, this doesn't guarantee they can be more profitable. Therefore, we also need to investigate highly profitable products. All of these three indicators give people overview of sales performance.

In the following executive summary (Figure 1), the sales data was presented based on orders, revenue, and returns from 2016 to 2017. In general, BuyCycle was growing well. Both Monthly Revenue and Monthly Returns reached their goals, though the Monthly Orders is slightly below expectation. When looking into the historal sales data, we can see the most popular product is Water Bottle (30 oz.) and the most profitable product is Montain-200 Black (46) bike. Most orders came from the category of Accessories, and Tires and Tubes subcategoty accumulated the highest orders among Accessories. In addition, its largest market is in USA.

Figure 1 Executive summary of sales performance from Buycycle

Now, we can look deeply into any specific product details to understand its sales performance with time. Since Montain-200 Black (46) bike is the most profitable one, we then have a closer look at it (FIgure 2). Overall, the sales of this bike was growing with time though it didn't reach the sales goals. We can also have an ideal about how profit would have been increased by adjusting the price of this bike (assuming the sales of this bike remains the same). The other good thing is the return number of this bike remained low and steady showing customers were satisfied with it.

Figure 2 Product detail of Mountain-200 Black (46) bike.

In addition to product details, we can also focus on "who" contribute to profits and orders by understainding customers' compositions. This will help BuyCycle to design a new strategy to increase customers' fidelity and attract new groups of customers. The following figure (Figure 3) reveals the top customers' composition, including gender, income level, etc. We can even look into specific customer details to understand statistics of any focus group by interacting the figure. From this figure, we know the gender distribution of customers is nearly equal, most orders came from customers with average or low incomes, and customers are mostly middle-age or old-age adults. In summary, with the above available information presented by three figures, David Brunoro, the director of Global Marketing, can design an appropriate strategy to their business augument.

Conclusion

BuyCycle is steadily growing and its major market is in USA. Its most popular products and most profital products are discovered.
Every product can be individually investigated to understand its performance with time.
Customers details are analyzed to provide insights about customer composition, trend of ordering, and individual profits from them.

Deliverables

BuyCycle should maintain the sufficint supplies of highly demanded products (ex. Water Bottle (30 oz.)) and promote the sales of most profitable products (ex. Montain-200 Black (46) bike).
From analyzing individual product details, Buycycle can make new marketing strategies accordingly. For highly demanded products, it can increase the supply and price of these products to bring more profits while considering removing low-demanded and low-profitable products from stores. By investigating the return history of specific product, it can indicate if there were untolorated defects of the product causing the high return rate.
Customer details plays an important role of designing a new marketing strategy to target specific focus group. BuyCycle is recommended to maintain the fidelity of middle-age and old-age customers while aiming at attracting young generation to purchase their products.

Page updated

Google Sites

Report abuse