AdventureWorks: Comprehensive Customer and Sales Insights

Introduction

The Adventure Works dataset offers a treasure trove of sales data, presenting a unique opportunity to uncover insights that can guide strategic business decisions. This analysis revolves around Adventure Works, a fictional yet beloved supplier of bicycles, parts, and accessories. Our goal is to journey through this comprehensive dataset, which spans 2005 to 2008, and includes detailed information on sales orders, customer demographics, product details, and sales territories. By exploring this data with Microsoft Excel and Power BI, we aim to identify patterns and trends that will empower the company to enhance its operations and better serve its diverse customer base, from the 5-year-old excited for their first bike to the adult who still cherishes the freedom of cycling. Through these tools, this project delves into the company's numbers to reveal the stories they tell, linking the data back to our shared love of bicycles and the joy they bring.

Problem Statement

AdventureWorks has a wealth of sales and customer data, but it's scattered and often lacks proper analysis, making it challenging to fully understand customer needs and boost sales effectively. This project dives deep into the data to uncover patterns in customer behavior, product profitability, and market trends, helping AdventureWorks make smarter decisions to improve marketing, refine product offerings, and ultimately increase sales.

Dataset Overview

FactInternetSales Table

1. Internet Sales Table (FactInternetSales): Stores transaction details, including unique identifiers for sales orders, product identifiers, order and shipment dates, financial details like order quantity, unit price, taxes, and freight, as well as currencies, discounts, and calculated sales amounts.
2. Customer Table (DimCustomer): Contains information that is specific to each customer, like unique identifiers, first and last names, and demographic data such as gender, birthdate, marital status, yearly income, education, and occupation.
3. Date Table (DimDate): Provides a structured view of dates with unique identifiers, formatted date strings, and date breakdowns for detailed time-based analysis.
4. Product Table (DimProduct): Offers comprehensive product information, including product identifiers, names, specifications like weight and size, and costing data.
5. Geography Table (DimGeography): Contains geographical data with unique identifiers, and detailed breakdowns into countries, states, and cities.
6. Sales Territory Table (DimSalesTerritory): Provides sales territory information, including unique identifiers and a detailed breakdown of regions, countries, and continents to which sales territories belong.

Relationships between tables:

• Customer — Internet Sales Relationship: Each customer can be linked to multiple sales transactions through the CustomerKey field.
• Geography — Customer Relationship: Each location can be linked to multiple customers through the GeographyKey field.
• Internet Sales — Date Relationship: Each transaction is associated with different date types (order, due, and ship) using corresponding date keys (OrderDateKey, DueDateKey and ShipDateKey).
• Product - InternetSales Relationship: Transactions are linked to specific products via the ProductKey.
• Geography — InternetSales Relationship: Sales transactions are linked to geographical information through the GeographyKey field.
• SalesTerritory — InternetSales Relationship: Transactions are associated with sales territories via the SalesTerritoryKey field.