Project case study

SQL Data Warehouse Analytics

SQL Server analytics on a star-schema data warehouse covering sales trends, product performance and customer segmentation, with reusable reporting views.

Problem

Simulates a real analytics environment where transactional sales data in a dimensional warehouse has to answer practical questions: how is the business growing over time, which products and categories drive revenue, and which customers matter most.

Approach

Queried a star schema in SQL Server (gold.fact_sales, gold.dim_customers, gold.dim_products). Analyses covered: changes over time (yearly sales, customers and quantity sold); cumulative analysis (running total sales and moving average price); product performance (year-over-year change, comparison to each product's average, and High / Mid / Low performer groups); part-to-whole (revenue and customer share by category); and segmentation (products by cost range; customers as VIP = 12+ months active and over 5,000 spent, Regular = 12+ months and 5,000 or less, New = under 12 months). Packaged the logic into two reusable reporting views, one for customers and one for products, ready to feed dashboards.

  • Bikes drive 96.5% of 29.36M total sales
  • Accessories reach the most customers (15,114) but bring in only 2.4% of sales
  • 18,484 customers segmented: 1,655 VIP, 2,198 Regular, 14,631 New

Tools

SQL ServerT-SQLStar SchemaData Warehousing

Key Insights

  • Bikes account for 96.46% of total sales (28.3M of 29.36M). Accessories contribute 2.39% and Clothing 1.16%.
  • Accessories reach the most customers (15,114, vs 9,132 for Bikes and 6,852 for Clothing), but a bike customer is worth about 3,100 in sales versus about 46 for an accessories customer.
  • Active customers grew from 2,216 in 2011 to 17,427 in 2013, and units sold from 2,216 to 52,807, while the moving average price fell from about 3,100 to about 1,670, likely reflecting a shift toward lower-priced products.
  • The customer base is mostly new: 14,631 of 18,484 customers (79%), with 2,198 Regular and 1,655 VIP customers.
  • The portfolio skews affordable: 211 of 295 products (72%) cost under 500, and only 39 cost over 1,000.
  • The top-selling products are all bikes (Mountain-200 and Road-150 models) at roughly 1.0-1.4M each, while several catalog items, such as component frames, have no recorded sales.

Recommendations

  • Bundle accessories and clothing with bike purchases (for example bike + helmet + accessories) to raise order value.
  • Use accessories as entry products to attract new customers, then guide them toward bikes.
  • Reduce dependence on one category: with 96.5% of sales from bikes, expand and promote the accessories and clothing lines.
  • Convert new customers with onboarding offers and follow-up campaigns, and protect VIP customers with loyalty rewards.
  • Promote mid-range and premium products, and review Low Performer and zero-sales products for pricing, promotion or discontinuation.

Data notes

  • 2010 and 2014 are partial years in the dataset (2014 contains only the first weeks of January), so the 2014 drop is not a real decline.
  • Category customer shares count a customer once per category they bought from, so they add up to more than the total number of customers.

Links