Online Bike Store Sales Report using Power BI
Hi! In this post, I want to share my learning project from my Power BI online course. In this project, I use Power BI as my tool to create reports for online transaction data. The dataset I used in this project is the AdventureWorks database of bike store sales, which is an example of an OLTP (Online Transaction Preprocessing) database created by Microsoft. The reports that will be shown are about KPIs, comparative regional performance, product level trends, and identifying high-value customers.
The AdventureWorks database I used in this project is about bike store sales. This first dashboard is called the executive dashboard because it only shows some general information, including revenue, profit, orders, and product returns. This dashboard filters by month, ranging from January 2020 to June 2022.
The dashboard above shows some general information about the general numbers. There was a total revenue of $24.9 million, a profit of $10.5 million, 25.2K orders, and a return rate of 2.2%. By time, the highest revenue occurred in June 2022, and the revenue KPIs show that the monthly revenue in June 2022 was higher than in May 2022. For comparison, in May 2022, the total revenue was $1.77 million, and in June 2022, the total revenue was $1.83 million, which means the revenue increased by 3.31%. We can also see that the time when revenue always increased was from January 2022 to June 2022.
The product categories sold are accessories, bicycles, and clothing. The most ordered product category is accessories, with a total order of 16.98K, followed by bicycles, with a total order of 13.93K, and clothing, with a total order of 6.98K. By product, the product with the highest order was the Water Bottle (30 oz). However, the product with the highest total return is Men’s Sports Shorts, L. The comparison between May 2022 and June 2022 orders is shown in the monthly order KPI, the result of which is that the total order in June 2022 is about 0.88% lower than that in May 2022. The monthly return comparison shows that June 2022 is better than May 2022, with a comparison value of 167 total returns in June 2022 and 169 total returns in May 2022.
There are six countries in the data where customers are from. This second dashboard shows countries in total order and can be filtered by continents to show specific countries based on their continent. The blue bubbles on the dashboard mean the number of total orders. The bigger the bubble, the more total orders in the country. Based on this dashboard, it can be concluded that the US (United States) is the country with the highest total order.
Meanwhile, on this third dashboard, we can see the product details. The product detail reports that will be shown in this dashboard are the monthly order target, revenue target, and profit target. To see product details, the first step that must be taken is to select a product. For example, in the dashboard above, the product selected is the AWC Logo Cap. Based on this product, it can be seen that the product order in June 2022 exceeded the target, as well as the revenue and profit of the product. Furthermore, the total profit for each month on this product is considered quite volatile. The total profit for each month is marked with a black line. Meanwhile, a metric is added that can adjust the price if it is increased by about 20%, and the adjusted profit can be seen through the blue line. and also on this dashboard provide monthly developments of the product in terms of total orders, profits, returns, quantity sold, revenue, and return rate. But what is currently being displayed is the total product order for each month, and based on the graph, it can be seen that the highest total order for AWC Logo Cap products occurred in June 2022.
In the customer detail report, we can see a few reports, including the total revenue, average revenue per customer, order by income level, order by occupation, and some top customer details. Based on time, there are also reports about total customers and average revenue per customer from 2020 until 2022. The dashboard above shows the report about customer detail for 2020–2022. The total order is around $17,000, and the average revenue per customer is $1,431. Based on revenue, there are 2.8K customers with high income, 11.6K customers with average income, and 10.2K customers with low income. Meanwhile, based on occupation, 7.8K customers work as professionals, 4.1K customers work in management, and 6K customers work as skilled manuals. A top customer by revenue is Mr. Maurice Shan, with a total of $12.41K and a total order of 6.
So, based on this dashboard, it can be concluded that most customers have an average income level, and most customers work as professionals. The top customer based on revenue is Mr. Maurice Shan.
Conclusion
- The highest revenue from 2020 to 2022 happened in June 2022.
- The country with the highest order is the United States.
- The category product with the most orders is accessories, and the product with the total highest order is a water bottle (30 oz).
- Most customers have professional occupations and have average incomes.
- The top customer from 2020–2022 by revenue is Mr. Maurice Shan, with a total revenue of $12.41K.
I will be very happy to discuss and accept any suggestions about the project since I’m still learning and still have a long way to go, please reach me through https://www.linkedin.com/in/muha-ilham/. Thank you!
