MY GOOGLE DATA ANALYTICS CAPSTONE PROJECT: AdventureWorks Sales Report

6 min readMay 26, 2023

For my Google Data Analytics Capstone Project, I decided to work on the AdventureWork sales data. My aim for this project was to analyse the sales data and derive insights pertaining to how profitable the company has been from 2017–2020.

Project Objectives

What is the most and least selling products?
Which country has the most and least customers?
Which country had the highest and lowest orders?
Which Product category had more orders? What could be the reason?
Which Product category had the most items and sales?
What was profitability like between 2017 and 2020?
What channel do customers use most frequently to order their goods?

Data Structure

The data had the following tables:

Sales Territory_data: This contains the region and countries of customers.
Sales_data: This contains information about all orders from all customers identified by the customer key.
Reseller_data: This contains information about the Resellers.
Product_data: This table contains information about various products and the category they belong too.
Customer_data: This contains information about all customers, their region and country of residence.
Sales Order_Data: It contains information about all orders.

Tools Used/ Skills Demonstrated

Microsoft PowerBI
DAX (Data Analysis Expression) Concept for Calculated Measures

Data Cleaning

Before commencing any analysis, the dataset was imported into Power Query on Microsoft Power BI. Data cleaning is an integral part of the data processing stage as it ensures data accuracy and credibility.

The following were checked for:

Blank spaces and missing values.
Proper formatting of dates.
Proper formatting of numbers.

Data Modelling

After data transformation was done in Power Query, it was loaded into PowerBI and all other tables were linked to the sales_data table using the primary key in each table.

Data Analysis

For this step, I carried out an exploratory data analysis on the data to answer the questions I listed above.

First of all, I did a quick summarization to determine total customers, total orders and product, total sales, profit and profit margin and also the geographical location of customers.

Between 2017 and 2020, the company had 18,000 customers from six countries. The total number of products available for sale are three hundred and ninety-seven(397) placed into 4 categories namely: Bikes, Accessories, Components and Clothing.

Cumulative amount generated from sales was $109million with a profit margin of 11.43%.

Q1. What is the product count by different Categories?

The components category has the highest number of products (189) while the Accessories category has the least number of products (35).

Q2. What is the most and least selling products?

I represented this with a column chart showing the top 5 selling product and the least 5 selling products.

Mountain-200 Black, 38 brought in the most sales to tune of $4.4 million while LL Road Seat/ Saddle brought in the least sales. From this analysis, I discovered that the Mountain-200 products was the top 5 selling products indicating high customer interest in this product and its profitability.

Q3. Which Country has the most and least customers?

The United states has the highest number of customers (7,819) while Canada has the least number of customers(1,571).

Q4. Which country had the highest and lowest orders?

As expected, the highest order can from the United state while the least order came from Germany. The second least order came from France.

Q5. Which Product category had more orders? What could be the reason?

The Accessories category has the highest number of orders (19,523), while the Components category has the lowest number of orders (2,600). It’s important to note that the component category has the highest number of products available for sale while the Accessories category has the least number of products available for sale. This huge difference in order count between the two categories could be linked to the average product unit price in each category. This is shown below;

Average Price of Products in each category

Comparing the average product price of Components to Accessories, there is a huge difference between them. Also, going by the usability of products in these categories, products listed under accessories are needed for day to day bike riding while that of components are required for replacements when damage occurs and when a rider wants to design a bike to meet a specific need or purpose.

Q6. Which Product category generated the most sales?

The Bike category generated the most sales. This could be as result of the price of a bike. The average price of a bike is $1,255 while that of an accessory is $20. Though the accessories category had the highest number of orders, but due to its relatively low price, it could not generate as much sales when compared to the Bikes.

Q7· What was sales and profitability like between 2017 and 2020?

Sales and Profitability between 2017–2020

The year 2018 was the most profitable year with a growth of 54% compared to 2017, while 2020 was the least profitable year, with a decline of -18% compared to 2019. Although, this could have resulted from the data provided as only the first six month of the year sales data was provided for 2020.

Q8· What channel do customers use most frequently to order their goods?