📊 AdventureWorks Business Insights with BigQuery

👤 Author

Vuong Minh Toan

---

🚀 1. Project Overview

This project analyzes ecommerce business performance using Google BigQuery and the AdventureWorks2019 dataset. The analysis is organized across three business domains: Sales, Production, and Purchasing. The objective is to answer business-driven analytical questions with SQL and transform transactional data into meaningful insights for decision-making.

📌 The project covers:

• 📈 Sales trend analysis
• 📊 Year-over-Year (YoY) growth analysis
• 🌍 Territory ranking
• 💸 Seasonal discount cost evaluation
• 👥 Customer retention (cohort analysis)
• 📦 Inventory trend and Month-over-Month (MoM) tracking
• ⚖️ Stock-to-sales efficiency analysis
• 🛒 Pending purchase order monitoring

---

❓ 2. Business Questions

This project answers the following business questions:

1. 📌 What are the quantity of items sold, total sales value, and order quantity by subcategory in the last 12 months?
2. 📌 Which subcategories have the highest YoY growth rate, and what are the top 3 performers?
3. 📌 Which TerritoryIDs rank in the top 3 by order quantity each year?
4. 📌 What is the total discount cost generated by Seasonal Discount for each subcategory?
5. 📌 What is the customer retention pattern in 2014 for successfully shipped orders using cohort analysis?
6. 📌 What is the monthly stock trend and MoM percentage change for all products in 2011?
7. 📌 What is the stock-to-sales ratio by product and month in 2011?
8. 📌 How many purchase orders remained pending in 2014, and what was their total value?

---

🗂️ 3. Dataset Description

This project uses the AdventureWorks2019 dataset, a sample business database containing sales, product, inventory, and purchasing data.

🧱 Main Schemas Used

• Sales
• Production
• Purchasing

🧾 Main Tables Used

• Sales.SalesOrderDetail
• Sales.SalesOrderHeader
• Sales.SpecialOffer
• Production.Product
• Production.ProductSubcategory
• Production.ProductInventory
• Production.WorkOrder
• Purchasing.PurchaseOrderHeader

---

🔑 4. Key Fields Used

📈 Sales Analysis Fields

• ModifiedDate – for time filtering and period extraction
• OrderQty – quantity of items sold
• LineTotal – sales value
• SalesOrderID – distinct order count
• CustomerID – customer retention analysis
• Status – shipped / pending status filtering
• TerritoryID – territory ranking

🏷️ Product / Discount Fields

• ProductID – product join key
• ProductSubcategoryID – subcategory join key
• Name – product or subcategory name
• DiscountPct – discount percentage
• Type – promotion type
• UnitPrice – used for discount cost calculation

🏭 Production / Purchasing Fields

• StockedQty – stock quantity
• PurchaseOrderID – purchase order count
• TotalDue – purchase order value

---

🧠 5. Query Overview

The SQL file contains 8 analytical queries mapped to three domains:

• 📊 Queries 1–5: Sales analysis
• 📦 Queries 6–7: Production / inventory analysis
• 🛒 Query 8: Purchasing analysis

⚙️ SQL Techniques Used

• JOIN, LEFT JOIN, FULL JOIN
• Common Table Expressions (WITH)
• Aggregation: SUM, COUNT(DISTINCT ...), ROUND
• Time functions: EXTRACT, FORMAT_TIMESTAMP, FORMAT_DATETIME, DATE_SUB
• Window functions: LAG, ROW_NUMBER, DENSE_RANK

---

💡 6. Key Insights

Because this repository currently contains the SQL logic rather than executed output files, the insights below describe the intended analytical value of each query:

1. 📈 Sales concentration by subcategory helps identify which product groups contribute most to volume, revenue, and orders.
2. 🚀 YoY growth analysis highlights the fastest-growing subcategories and emerging opportunities.
3. 🌍 Territory ranking reveals the strongest geographic sales areas by yearly order quantity.
4. 💸 Seasonal discount cost analysis measures promotional cost exposure by subcategory.
5. 👥 Customer retention analysis shows repeat purchase behavior after the first successful shipment month.
6. 📦 Stock trend analysis helps detect inventory volatility and unusual monthly changes.
7. ⚖️ Stock-to-sales ratio helps evaluate inventory efficiency and detect overstock / understock conditions.
8. 🛒 Pending purchase order analysis supports procurement monitoring and supply chain control.

---

✅ 7. Recommendations

Based on the query objectives, the following recommendations can be derived:

• 🎯 Prioritize high-performing product subcategories with strong sales and growth.
• 🚚 Monitor fast-growth categories to ensure supply readiness.
• 🌍 Review top-performing territories as benchmarks for other regions.
• 💰 Compare seasonal discount cost against sales uplift to assess promotion effectiveness.
• 🤝 Use cohort findings to design customer retention and loyalty programs.
• 📦 Review products with unstable MoM stock movement or abnormal stock-to-sales ratios.
• ⏳ Track pending purchase orders closely to reduce procurement bottlenecks.

---

🛠️ 8. Skills Demonstrated

This project demonstrates the following skills:

• 🧮 Writing analytical SQL in Google BigQuery
• 🔗 Multi-table data integration across schemas
• 🪟 Window functions and ranking logic
• ⏱️ Time-series analysis: YoY, MoM, and cohort analysis
• 📊 Sales, inventory, and purchasing KPI development
• 🧠 Translating business questions into SQL solutions
• 📌 Business-oriented data interpretation

---

📜 9. Query Details

📈 Query 1 – Sales Performance by Subcategory in the Last 12 Months

Purpose: Calculate quantity sold, total sales value, and order count by subcategory in the most recent 12-month period.

select format_datetime('%b %Y', a.ModifiedDate) month 
 ,c.Name 
 ,sum(a.OrderQty) qty_item 
 ,sum(a.LineTotal) total_sales 
 ,count(distinct a.SalesOrderID) order_cnt 
FROM `adventureworks2019.Sales.SalesOrderDetail` a 
left join `adventureworks2019.Production.Product` b 
 on a.ProductID = b.ProductID 
left join `adventureworks2019.Production.ProductSubcategory` c 
 on b.ProductSubcategoryID = cast(c.ProductSubcategoryID as string) 
where date(a.ModifiedDate) >= (select date_sub(date(max(a.ModifiedDate)), INTERVAL 12 month) 
 from `adventureworks2019.Sales.SalesOrderDetail` )
group by 1,2 
order by 2,1;

🚀 Query 2 – Top 3 Subcategories by YoY Growth Rate

Purpose: Identify the top 3 subcategories with the highest YoY growth rate based on item quantity.

WITH summary AS ( 
 SELECT 
 EXTRACT(YEAR FROM a.ModifiedDate) AS year, 
 c.name, 
 SUM(a.OrderQty) AS item_count 
 FROM `adventureworks2019.Sales.SalesOrderDetail` a 
 LEFT JOIN `adventureworks2019.Production.Product` b 
 ON a.ProductID = b.ProductID 
 LEFT JOIN `adventureworks2019.Production.ProductSubcategory` c 
 ON CAST(b.ProductSubcategoryID AS INT64) = c.ProductSubcategoryID 
 GROUP BY year, c.name 
), 
raw_data AS ( 
 SELECT 
 year, 
 name, 
 item_count AS Qty_item, 
 LAG(item_count) OVER (PARTITION BY name ORDER BY year) AS Prv_qty 
 FROM summary 
), 
diff_calc AS ( 
 SELECT 
 year, 
 name, 
 Qty_item, 
 Prv_qty, 
 ROUND(Qty_item / Prv_qty - 1, 2) AS qty_diff 
 FROM raw_data 
 WHERE Prv_qty IS NOT NULL AND Prv_qty < Qty_item 
), 
ranked AS ( 
 SELECT 
 year, 
 name, 
 Qty_item, 
 Prv_qty, 
 qty_diff, 
 DENSE_RANK() OVER (PARTITION BY year ORDER BY qty_diff DESC) AS rank 
 FROM diff_calc 
) 
SELECT 
 year, 
 name, 
 Qty_item, 
 Prv_qty, 
 qty_diff, 
 rank 
FROM ranked 
WHERE rank <= 3 
ORDER BY year, rank;

🌍 Query 3 – Top 3 Territories by Order Quantity Each Year

Purpose: Rank the top 3 territories by total item quantity for every year using dense ranking.

WITH summary AS ( 
 SELECT 
 EXTRACT(YEAR FROM a.ModifiedDate) AS year, 
 b.TerritoryID, 
 SUM(a.OrderQty) AS item_count 
 FROM `adventureworks2019.Sales.SalesOrderDetail` a 
 LEFT JOIN `adventureworks2019.Sales.SalesOrderHeader` b 
 ON a.SalesOrderID = b.SalesOrderID 
 GROUP BY year, b.TerritoryID 
), 
ranked AS ( 
 SELECT 
 year, 
 TerritoryID, 
 item_count, 
 DENSE_RANK() OVER ( 
 PARTITION BY year 
 ORDER BY item_count DESC 
 ) AS rnk 
 FROM summary 
) 
SELECT 
 year, 
 TerritoryID, 
 item_count, 
 rnk 
FROM ranked 
WHERE rnk <= 3 
ORDER BY year, item_count DESC;

💸 Query 4 – Seasonal Discount Cost by Subcategory

Purpose: Calculate the total seasonal discount cost for each product subcategory.

select 
 FORMAT_TIMESTAMP("%Y", ModifiedDate) 
 , Name 
 , sum(disc_cost) as total_cost 
from ( 
 select distinct a.ModifiedDate 
 , c.Name 
 , d.DiscountPct, d.Type 
 , a.OrderQty * d.DiscountPct * UnitPrice as disc_cost 
 from `adventureworks2019.Sales.SalesOrderDetail` a 
 LEFT JOIN `adventureworks2019.Production.Product` b on a.ProductID = b.ProductID 
 LEFT JOIN `adventureworks2019.Production.ProductSubcategory` c on cast(b.ProductSubcategoryID as int) = c.ProductSubcategoryID 
 LEFT JOIN `adventureworks2019.Sales.SpecialOffer` d on a.SpecialOfferID = d.SpecialOfferID 
 WHERE lower(d.Type) like '%seasonal discount%' 
) 
group by 1,2;

👥 Query 5 – Customer Retention Rate in 2014 (Cohort Analysis)

Purpose: Measure customer retention in 2014 based on successfully shipped orders.

with 
info as ( 
 select 
 extract(month from ModifiedDate) as month_no 
 , extract(year from ModifiedDate) as year_no 
 , CustomerID 
 , count(Distinct SalesOrderID) as order_cnt 
 from `adventureworks2019.Sales.SalesOrderHeader` 
 where FORMAT_TIMESTAMP("%Y", ModifiedDate) = '2014' 
 and Status = 5 
 group by 1,2,3 
 order by 3,1 
), 
row_num as (
 select 
 month_no 
 ,year_no 
 ,CustomerID 
 ,order_cnt 
 , row_number() over (partition by CustomerID order by month_no) as row_numb 
 from info 
), 
first_order as (
 select 
 month_no 
 ,year_no 
 ,CustomerID 
 ,order_cnt 
 from row_num 
 where row_numb = 1 
), 
month_gap as ( 
 select 
 a.CustomerID 
 , b.month_no as month_join 
 , a.month_no as month_order 
 , a.order_cnt 
 , concat('M - ',a.month_no - b.month_no) as month_diff 
 from info a 
 left join first_order b 
 on a.CustomerID = b.CustomerID 
 order by 1,3 
) 
select month_join 
 , month_diff 
 , count(distinct CustomerID) as customer_cnt 
from month_gap 
group by 1,2 
order by 1,2;

📦 Query 6 – Stock Trend and MoM Difference in 2011

Purpose: Track stock levels by product and calculate monthly percentage change in stock.

WITH stock_data AS ( 
 SELECT 
 g.Name AS ProductName, 
 EXTRACT(YEAR FROM s.ModifiedDate) AS Year, 
 EXTRACT(MONTH FROM s.ModifiedDate) AS Month, 
 SUM(p.StockedQty) AS Stock_current 
 FROM adventureworks2019.Production.ProductInventory s 
 JOIN adventureworks2019.Production.WorkOrder p 
 ON s.ProductID = p.ProductID 
 JOIN adventureworks2019.Production.Product g 
 ON g.ProductID = p.ProductID 
 WHERE EXTRACT(YEAR FROM s.ModifiedDate) = 2011 
 GROUP BY g.Name, Year, Month 
), 
with_prev AS ( 
 SELECT 
 ProductName, 
 Year, 
 Month, 
 Stock_current, 
 LAG(Stock_current) OVER (PARTITION BY ProductName ORDER BY Year, Month) AS Stock_prv 
 FROM stock_data 
) 
SELECT 
 ProductName AS `Product Name`, 
 Month, 
 Year, 
 Stock_current, 
 IFNULL(Stock_prv, 0) AS Stock_prv, 
 ROUND( 
 IFNULL((Stock_current - Stock_prv) / NULLIF(Stock_prv, 0) * 100, 0), 
 1 
 ) AS `%diff` 
FROM with_prev 
ORDER BY ProductName ASC, Year, Month;

⚖️ Query 7 – Stock / Sales Ratio by Product in 2011

Purpose: Compare stock quantity with sales quantity to evaluate inventory efficiency by product and month.

with 
sale_info as ( 
 select 
 extract(month from a.ModifiedDate) as mth 
 , extract(year from a.ModifiedDate) as yr 
 , a.ProductId 
 , b.Name 
 , sum(a.OrderQty) as sales 
 from `adventureworks2019.Sales.SalesOrderDetail` a 
 left join `adventureworks2019.Production.Product` b 
 on a.ProductID = b.ProductID 
 where FORMAT_TIMESTAMP("%Y", a.ModifiedDate) = '2011' 
 group by 1,2,3,4 
), 
stock_info as ( 
 select 
 extract(month from ModifiedDate) as mth 
 , extract(year from ModifiedDate) as yr 
 , ProductId 
 , sum(StockedQty) as stock_cnt 
 from 'adventureworks2019.Production.WorkOrder' 
 where FORMAT_TIMESTAMP("%Y", ModifiedDate) = '2011' 
 group by 1,2,3 
) 
select 
 a.mth 
 , a.yr 
 , a.ProductId 
 , a.Name 
 , a.sales 
 , b.stock_cnt as stock 
 , round(coalesce(b.stock_cnt,0) / sales,2) as ratio 
from sale_info a 
full join stock_info b 
 on a.ProductId = b.ProductId 
and a.mth = b.mth 
and a.yr = b.yr 
order by 1 desc, 7 desc;

🛒 Query 8 – Pending Purchase Orders in 2014

Purpose: Count pending purchase orders and calculate total order value in 2014.

SELECT 
 EXTRACT(YEAR FROM ModifiedDate) AS year, 
 Status, 
 count(distinct PurchaseOrderID) as order_Cnt, 
 sum(TotalDue) as value 
FROM adventureworks2019.Purchasing.PurchaseOrderHeader 
WHERE EXTRACT(YEAR FROM ModifiedDate) = 2014 AND Status = 1 
GROUP BY Status, year;

---

🏁 10. Final Conclusion & Recommendations

This project demonstrates how Google BigQuery SQL can be used to solve practical business problems across multiple operational domains. Together, the queries provide a strong foundation for business monitoring in revenue, customer retention, inventory movement, and purchasing workload.

📌 Final Recommendations

• 📅 Run these queries regularly as part of a monthly business performance review.
• 📊 Build dashboards in Power BI, Looker Studio, or Tableau for stakeholder reporting.
• 💰 Compare discount cost with revenue uplift to evaluate promotion effectiveness.
• 🔁 Extend cohort analysis to more years for stronger customer lifecycle tracking.
• 📦 Combine stock and sales analysis with demand forecasting for better supply planning.

---

🔗 References

• AdventureWorks Dataset Dictionary: https://drive.google.com/file/d/1bwwsS3cRJYOg1cvNppc1K_8dQLELN16T/view