Adventure Works Sales Report (2020 - 2023)

It’s my final week in the #30daysofdata BTM 2.0 bootcamp hosted by Kedrus Academy and our lead instructor, Stella Justin-Tochukwu. It’s been an intensive but amazing 30 days.

For my week 4 unguided project, I analyzed and carried out a sales analysis on a free dataset provided by Microsoft – the Adventure Works database.

Tools Used:

SQL

PowerBI

DATASET OVERVIEW

Adventure Works is a large multinational company that manufactures and distributes 4 categories of products: bikes, bike components /, accessories, and clothing. The company’s commercial market is spread across 3 continents and 6 countries namely North America (United States & Canada), Europe (France, Germany, and the United Kingdom), and Australia (Australia). The company sells through 2 main channels: Online sales and Wholesalers.

DATA PREPARATION

I downloaded a backup copy of the dataset from the Microsoft website and restored it to the Microsoft SQL server. Once restored, I explored the tables in the database and determined that I would need only 7 out of the 30 tables to answer the following business questions:

1. What is our customer profile?

2. How does sales vary by product category, sub-category, and by region?

3. What product categories are being ordered the most?

4. Who are our top 5 customers and what top 5 products bring in the most sales?

5. How do sales and profit vary over time?

6. What customer-related factors drive revenue?

DATA QUERYING

I queried each of the selected tables and returned summary statistics and calculated fields for the KPIs needed to populate my dashboard such as the total number of products, total product categories and sub-categories, the total number of customers, total number of countries, total order quantity, total product price, total sales amount, total profit and profit margin.

Once done with querying the data in SQL server, I imported the tables into PowerBI’s Power Query editor for data cleaning, transformation and modeling.

DATA CLEANING

To clean each table in the dataset, I

1. Changed the data types for columns with incorrect data types.

2. Split some columns that contained concatenated information and renamed the split tables.

3. Removed columns that were not necessary for my analysis.

4. Replaced values for easy analysis (e.g., M for Male, F for Female, M for Married, and S for Single, 0 for No, and 1 for Yes).

5. Created custom columns where necessary.

DATA MODELLING

Once done with the cleaning and transformation process, I closed and loaded the dataset into the model and created relationships among the 7 tables. The final model had 6 dimensions tables and 1 fact table.

DATA ANALYSIS

I analyzed the data in PowerBI desktop and created some DAX measures to enrich my analysis (e.g., calculating total profit, profit margin, Last year sales, Year-on-year (YoY) sales, and sales Year-to-date (YTD).

The results of the analysis are shown in the report and seeks to answer the aforementioned business questions.

FINDINGS

1. The Bike product category was the most profitable in terms of revenue generated. However, most orders were placed in the Accessories product category.

2. Mountain, road, and touring bikes were the most lucrative product sub-categories.

3. The most revenue was generated by the United States (over $9.39 million), while Canada generated the least (about $1.97 million).

4. The highest revenue & profit was generated in 2022 ($365K & $153k respectively), while the lowest was in 2023 (only Q1 figures).

5. Customers who had a Bachelors' degree, were in Professional positions, with a yearly income of $60k & owned a house drove the most revenue.

6. Customers who had a Partial High school degree, were in Management positions, with a yearly income of $160k & did not own a house drove the least revenue.

RECOMMENDATIONS

Based on my findings, I would recommend that Adventure Works:

1. Increase production and marketing of top revenue-generating product categories and sub-categories (Bikes specifically mountain, road & touring bikes) and pair them with accessories as bundle offers to maximize sales & profit.

2. Focus on replicating the profitable US & Australian marketing strategies in the least revenue-generating countries (Canada, France, Germany & the United Kingdom).

3. Incentives and rewards should be given to top-performing customers to encourage more sales. They can also conduct capacity-building sessions to mentor low-performing customers on effective strategies to improve sales.

4. Owning a house may be a more important priority to customers driving the least revenue than buying a bike, despite the fact they earn a high yearly income. Targeted marketing of these groups of customers, educating them on the usefulness of bikes for physical activity, and improved health outcomes is key.

PS: **SQLSteps links to the SQL scripts I wrote to calculate my summary statistics (KPIs). Find the link here: https://sqlscripts.vzy.io

Link to portfolio: https://adannaanalytics.vzy.io/