Analysing Bike Sales Data using SQL and Power BI
Introduction
Sales is the nucleus of every successful company. Analyzing sales data is a very important aspect in sales management and overall success of a business. It provides direction to the sales managers because it helps them compare sales budget to sales and inferences can be draw on ways to improve marketing and sales strategies. The insights from this analysis give perspective to the overall success of the sales team.
This blog is a summary of an analysis of bike sales using Adventure Works sample data. SQL and Power BI were the tools used for this project.
Business Requirements
Honestly, there are tons of analyses one can carry out on this data, but one thing that helps maintain focus is having a set of business requirements. These are outlined below:
- To improve internet sales report and move from static to visual by creating an executive sales report that sales managers could use.
- To find out what products the client has sold, how much, to which clients, and how it has been over time.
- To filter each sales person based on the different products and customers they work on.
- To compare the client’s budget values against performance.
Business Demand Overview and User Stories
These business goals for this project were achieved by defining user stories to highlight the goals more clearly, ensure delivery, and maintain acceptance criteria. See details below:
Data Cleansing and Transformation
The tables were cleaned and transformed in SQL and then exported as csv files. This involved a lot of proactiveness, as I had to think ahead of the important variables required to create the model.
Below are the SQL statements for cleaning and transforming the target data.
The Data Model
The tables were imported into Power BI and a bit of transformation was done again just to make sure the variables were in the right format. This is important to ensure accuracy and consistency of the data. I also made sure to define the relationship with these tables properly.
From the image below, we can see that each table has a one-to-many relationship with the fact table.
Sales Dashboard
The sales dashboard was designed to show the top 10 customers and products. The map chart is an overview of the sales in Europe and could be filtered to show other countries and continents. Each business requirement is addressed in the following paragraphs.
Have the sales report been improved through visualization?
The answer is YES. Below is an overview dashboard including the overall sales and performance.
The dashboard includes the following:
i. KPI Indicator Chart — showing the overall performance.
ii. Line Chart — showing the sales and budget by month each year.
iii. Stacked Bar Chart — showing the top 10 customers and products.
iv. Donut Chart — showing the percentage of sales by product category.
v. Map Chart — showing the total sales by country or continent.
vi. Filters — which are useful for selecting required information.
What products have the client sold, how much, to which clients, and how has it been over time?
In 2019, The performance was positive. Hailey Russel made the most sales and the Mountian-100 bike was the most sold product. Bikes were the most sold products with a whopping percentage of 99.95%. See more details below:
In 2020, Jordan Turner made the most sales, and the Mountain-100 was the product that sold the most. Again, bikes were the most sold products with a percentage of 93.93%. See more details below:
In 2021, Arthur Van sold the most products, and the Blue Sport-100 Helmet sold the most. Accessories sold the most this time around with a percentage of 66.5%. It is safe to say that bike accessories cost a lot less than the actual bikes, so this explains the decrease in sales performance in 2021. See more details below:
How can the client filter each sales person based on the different products and customers they work on?
This can be done by simply selecting a product name from the drop-down menu on the left. You can do this by clicking the drop-down arrow on the Product Name filter.
What are the client’s budget values against performance?
A KPI indicator chart was imported in the visualizations section to show the client an instant view of the budget values against performance. A green upward arrow represents a positive performance, while a red downward arrow represents a negative performance.
Conclusion
Although challenging, this project was very fun to do. I learned more about the different functions in Power BI and I was able to meet all business requirements which solved the client’s needs. This delivery added value and helped inform better decisions in the future.
