Bike Store Network Optimization & Strategic Analysis

Overview

This repository contains an end-to-end relational database analysis for a retail bike store network operating across three physical locations in California, New York, and Texas.

Data Source: Bike Store Relational Database (Kaggle), a sample dataset designed for SQL analysis and strategic retail modeling.

The central theme of this project is Network Optimization. To deliver actionable business value, the analysis was intentionally structured into three distinct focus areas designed to address core physical store operations:

• Redistributing staff and inventory between stores (resource optimization)
• Suggesting the optimal location for a new store (network expansion)
• Suggesting the optimal location for closing a store (network contraction)

Within each focus area, specific business questions were formulated to be both commercially relevant and answerable using the relational dataset. For the complete breakdown of all business questions, see Project_Scope_and_Questions.md.

This project intentionally concentrates on the theme of high-level network optimization, therefore other analytical avenues were excluded to maintain a dedicated strategic focus. Additional topics, such as customer retention or sales seasonality, could serve as prime topics for future projects.

---

Key SQL Techniques Used

• Shipping Latency Percentiles: PERCENTILE_CONT(0.75) to calculate 75th percentile shipping lead times per city and store, combined with AVG() and MAX().

• Multi-Level Aggregation: GROUPING SETS to aggregate inventory stock and sales metrics simultaneously at both the category-store level and overall store level.

• Cross Joins for Inventory Matrix: CROSS JOIN to generate all possible store-product combinations, enabling complete inventory stock reporting (including zero-quantity stock items).

• Window Functions & Ranking: RANK() and OVER(PARTITION BY ...) to rank candidate cities by order volume and total revenue within each state.

• Period-over-Period Trends: LAG() to calculate quarter-over-quarter (QoQ) percentage growth rates across revenue and order volume per store.

• Date & Time Operations: DATE_DIFF() for calculating shipping duration in days and FORMAT_DATE('%Y-Q%Q', order_date) for quarterly aggregation.

• Conditional Ratios & Counts: AVG(CASE WHEN ... THEN 1 ELSE 0 END) to calculate the percentage of positive growth quarters, and SUM(CASE WHEN ...) to classify staff roles.

• Null Handling: COALESCE() to replace missing stock values with zeros.

---

Executive Summary & Strategic Recommendations

Note: Detailed numerical metrics, result tables, and deep-dive business implications for each focus area are documented in their dedicated reports in the respective folders.

Focus Area 1: Store-Level Resource Optimization

• Questions Asked:

  • How do store revenue and order volume compare to staff counts (managers vs. non-managers) to identify over-staffed or under-staffed locations?
  • Which stores hold high stock levels in bicycle categories they rarely sell, indicating inventory that should be reallocated?

• Key Recommendations:

  • Staffing: Reallocate 1 non-manager employee from Rowlett Bikes (TX) to Baldwin Bikes (NY).
  • Inventory: Shift excess inventory stock from Rowlett Bikes (TX) and Santa Cruz Bikes (CA) to Baldwin Bikes (NY), prioritizing Cyclocross Bicycles, Children Bicycles, Comfort Bicycles, and Electric Bikes.

Focus Area 2: Candidate Cities for Expansion

• Preliminary Finding & Scope Pivot: A preliminary SQL check revealed that 100% of customer orders are fulfilled in-state (zero out-of-state customer orders exist). As a result, expansion scope pivoted from a new state expansion to an in-state regional expansion (a new city within one of the current 3 states).

• Questions Asked:

  • After excluding "home cities" (cities where current stores are located), which cities rank in the Top 5 for both total order volume and total revenue within their state? These will be our candidate cities for expansion.
  • What share of state orders and revenue does each candidate city represent (Market Share)?
  • What are the shipping time profiles (average, 75th percentile, and max shipping time) for each candidate city? High shipping times are an indicator that a city will benefit significantly from a new local store.

• Key Recommendations:

  • Primary Expansion Target: Mount Vernon, NY.
  • Additional Expansion Targets Outside NY: San Angelo and Houston in Texas; Canyon Country, Palos Verdes Peninsula, and South El Monte in California.

Focus Area 3: Candidate Store for Closure

• Questions Asked:

  • Which store exhibits low sales demand alongside high shipping times? This may indicate that it's located in a small or remote market.
  • Which store exhibits the weakest quarterly growth trajectory (average QoQ% growth and % of positive growth quarters)?
  • Which store has the largest staff? Therefore the network would benefit the most from cutting it's payroll.
  • Which store has the smallest inventory stock? Therefore minimal effort/cost is required to redeploy that inventory upon potential closure.

• Key Recommendations:

  • Primary Closure Candidate: Rowlett Bikes, TX.
  • Secondary Closure Candidate: Santa Cruz Bikes, CA (not far off from the primary candidate in terms of closure priority).

---

Database Architecture

The database consists of a normalized relational schema of 9 tables, modeling core retail operations and connecting store locations, customer demographics, sales, products, inventory stocks, among other retail-related entities.

SQL Environment & Execution Note: All queries in this project were written for Google BigQuery using a dataset named data (e.g., data.stores, data.orders). To execute the provided .sql files directly, upload the Kaggle CSV tables into a BigQuery dataset named data, or update the data.[table_name] prefix in the SQL files to match your chosen dataset name.

Entity-Relationship Diagram (ERD)

Schema Components

• stores: Contact and location information for each bike store.
• staffs: Employee profiles, store and manager assignments.
• customers: Customer contact details and locations.
• orders: Order records, including customer, store, dates and other order details.
• order_items: The specific products, quantities, and prices included in each order.
• products: Bicycle catalog details, including model years and list prices.
• stocks: Inventory quantities for each product at each store location.
• categories & brands: Category and brand names used to classify products.

---

Repository Structure & Navigation

bike-store-network-optimization/
├── README.md
├── Project_Scope_and_Questions.md
├── ERD.png
├── 01_Resource_Optimization/
│   ├── 01_Resource_Optimization_Report.md
│   ├── 01_staffing_efficiency.sql
│   ├── 01_staffing_efficiency_results.csv
│   ├── 02_inventory_allocation.sql
│   └── 02_inventory_allocation_results.csv
├── 02_Regional_Expansion/
│   ├── 02_Regional_Expansion_Report.md
│   ├── 01_customer_state_by_store.sql
│   ├── 01_customer_state_by_store_results.csv
│   ├── 02_expansion_candidate_cities.sql
│   └── 02_expansion_candidate_cities_results.csv
└── 03_Store_Closure/
    ├── 03_Store_Closure_Report.md
    ├── 01_store_demand_and_shipping_times.sql
    ├── 01_store_demand_and_shipping_times_results.csv
    ├── 02_quarterly_growth_trajectory.sql
    └── 02_quarterly_growth_trajectory_results.csv

Direct Navigation Links

• Master Project Scope & Questions: Project_Scope_and_Questions.md
• Focus Area 1 (Resource Optimization):
  • Report: 01_Resource_Optimization_Report.md
  • SQL Queries: 01_staffing_efficiency.sql, 02_inventory_allocation.sql
  • Result Datasets: 01_staffing_efficiency_results.csv, 02_inventory_allocation_results.csv
• Focus Area 2 (Regional Expansion):
  • Report: 02_Regional_Expansion_Report.md
  • SQL Queries: 01_customer_state_by_store.sql, 02_expansion_candidate_cities.sql
  • Result Datasets: 01_customer_state_by_store_results.csv, 02_expansion_candidate_cities_results.csv
• Focus Area 3 (Store Closure Evaluation):
  • Report: 03_Store_Closure_Report.md
  • SQL Queries: 01_store_demand_and_shipping_times.sql, 02_quarterly_growth_trajectory.sql
  • Result Datasets: 01_store_demand_and_shipping_times_results.csv, 02_quarterly_growth_trajectory_results.csv