Project Analysis: 36 Years of UK Retail Sector Growth and Resilience (1989-2025)
- Jan 25
- 4 min read
Updated: Jun 28
A comprehensive economic analysis project demonstrating ETL, data modeling, and strategic insight extraction from 10MB+ datasets.
📊 Project Overview
This project analyzes 36 years of UK retail sector data to identify economic inflection points, compare real growth vs. inflationary effects, and evaluate business resilience across economic cycles.
Key Finding: Large businesses maintained 4.41% average growth pre-2020, while small businesses showed 2.08% but greater agility during crises. Digital channels (non-store retail) emerged as the dominant growth driver post-2020.
🎯 Business Questions Answered
Volume vs. Value: How do we separate real growth from inflation-driven price hikes?
Corporate Resilience: Do large companies outperform SMEs during crises?
Seasonal Patterns: What are the true underlying trends after removing seasonality?
Digital Impact: How did non-store retail reshape market dynamics post-2020?
Recovery Velocity: What's the market's ability to rebound? (April 2021: 78.91% spike)
📈 Key Insights
Market Cycles
Period | Large Business Growth | Small Business Growth | Key Event |
Pre-2020 | 4.41% | 2.08% | Stable baseline |
2020 (COVID) | -3.00% | +1.94% | Lockdown paradox |
2021 | 14.90% | 12.88% | Historic recovery peak (78.91% in April) |
2023-2025 (Projected) | 4.65% | 6.99% | Small biz lead acceleration |
Sector Performance
Top Performers: Non-store Retail (6.55 avg), Textile/Clothing (4.82 avg)Laggards: Household Goods Stores (0.99 avg for small biz)
The Lockdown Paradox
April 2020: Large businesses crashed to -23.60% | Small businesses to -27%
But October 2020: Small businesses rebounded to +15.26% vs. large at +5%
Insight: SMEs pivot faster to digital; large firms stabilize slower but stronger
🛠️ Technical Stack
Layer | Tools | Purpose |
Data Source | ONS (www.ons.gov.uk) | UK Government retail statistics |
Data Ingestion | Excel | Raw data collection & validation |
ETL & Analysis | PostgreSQL (SQL) | Data cleaning, transformation, aggregation |
Visualization | Power BI | Interactive dashboards & reporting |
Project Format | Markdown + Dashboards | Documentation & insights |
📊 Dataset Specifications
Source: UK Office for National Statistics (ONS)
Raw Size: ~10 MB (450K+ records)
Transformed Datasets: 2 optimized datasets, ~450 rows × 36 columns each
Time Range: January 1989 – December 2025
Granularity: Monthly data by business size & retail sector
Key Metrics: Sales Volume (units), Sales Value (£), Growth %, YoY change
🔄 Methodology
Phase 1: Data Collection & Preparation
Downloaded monthly retail data from ONS portal
Cross-validated across multiple ONS datasets to ensure consistency
Identified missing months and applied standard statistical imputation
Phase 2: SQL Cleaning & Transformation
-- Example: Calculate real growth (volume) vs nominal growth (value)
SELECT
year,
month,
business_type,
sector,
(volume_t / LAG(volume_t) OVER (PARTITION BY business_type ORDER BY year, month) - 1) * 100 AS real_growth_pct,
(value_t / LAG(value_t) OVER (PARTITION BY business_type ORDER BY year, month) - 1) * 100 AS nominal_growth_pct
FROM retail_metrics
WHERE year >= 1989;
Phase 3: Statistical Analysis
Seasonal Adjustment: Applied X-13 style decomposition to remove seasonal fluctuations
Cycle Detection: Identified boom/bust peaks and troughs
Outlier Handling: Flagged COVID-related anomalies separately for contextual analysis
Trend Extrapolation: Used exponential smoothing for 2023-2025 projections
Phase 4: Power BI Visualization
Dashboard 1: Overall view with timeline, sector rankings, business size comparison
Dashboard 2: Detailed 2020 COVID analysis (monthly breakdown)
Dashboard 3: 2021 Recovery year (peak growth insights)
Interactive slicers for year, month, business type, sector filtering
📌 Key Deliverables
Dashboard 1: 36-Year Overview
Timeline selector (year/month)
Overall growth trends (large vs. small business)
Top 5 performing sectors (ranked by average value)
Bottom 5 underperforming sectors
Real-time growth rate comparisons
Dashboard 2: 2020 COVID Deep Dive
Monthly growth by business type
Sector-specific impact analysis
Peak contraction point (April 2020: -23.60% for large biz)
Recovery velocity tracking (October rebound)
Dashboard 3: 2021 Recovery Peak
Historic growth spike visualization (April 2021: 78.91%)
Sector resilience rankings
Month-over-month momentum tracking
Performance averages by business size
💡 Strategic Insights for Decision Makers
For Investors
Small businesses offer higher growth potential (6.99% 2023-2025) but higher volatility
Large corporations provide stability (4.65% steady growth) and crisis resilience
Digital channels are non-negotiable: Non-store retail is the only consistently outperforming segment
For Business Strategy
Pivot velocity matters: Companies that shift online recover 3-5x faster
Seasonality is predictable: Plan inventory and staffing around consistent seasonal peaks
Sector selection is critical: Textile/Clothing/Footwear outperforms Household Goods by 5x in growth
For Economic Policy
SMEs provide economic flexibility during crises (faster pivots, lower losses)
Large corporations provide employment stability (slower layoffs, stronger wage floors)
Digital infrastructure investment should be priority 1 for growth acceleration
📁 Project Structure
uk-retail-analysis/
├── README.md # This file
├── data/
│ ├── raw/ # Raw ONS CSV downloads
│ │ ├── retail_sales_by_size.csv
│ │ └── retail_sales_by_sector.csv
│ └── processed/ # Post-SQL transformation outputs
│ ├── retail_metrics_clean.csv
│ └── retail_growth_analysis.csv
├── scripts/
│ ├── 01_data_cleaning.sql # Data validation & standardization
│ ├── 02_calculations.sql # Growth metrics & aggregations
│ └── 03_seasonal_adjustment.sql # Trend decomposition
├── dashboards/
│ └── retail_analysis.pbix # Power BI report file
├── docs/
│ ├── methodology.md # Technical deep-dive
│ ├── glossary.md # Define all metrics
│ └── findings_summary.pdf # Executive summary
└── exports/
└── visualizations/ # PNG exports for presentations
🎓 What This Project Demonstrates
Technical Skills
✅ Data Engineering: ETL pipeline design (10MB → refined datasets)
✅ SQL Mastery: Complex aggregations, window functions, time-series analysis
✅ Statistical Analysis: Seasonality decomposition, trend detection, forecasting
✅ BI/Visualization: Multi-page dashboards with interactivity & storytelling
✅ Data Quality: Validation, imputation, outlier handling
Business Skills
✅ Economic Analysis: Understanding market cycles, inflection points, sector dynamics
✅ Strategic Thinking: Identifying actionable insights from complex datasets
✅ Cross-functional Communication: Translating data into business decisions
✅ Problem Definition: Asking the right questions before diving into data
Domain Knowledge
✅ Retail Economics: SME vs. Corporate dynamics, digital disruption impact
✅ Macro Trends: COVID impact, recovery patterns, sector rotation
✅ Financial Metrics: Volume vs. Value distinction, real growth measurement
📜 License
This analysis is based on UK Office for National Statistics (ONS) open data, licensed under the Open Government Licence v3.0.
Author: Mostafa El Sayed | Data Analyst | Cairo, EgyptLast Updated: November 2024Data Freshness: Updated monthly from ONS portal
🎯 For Recruiters & Technical Leads
If you're evaluating this portfolio:
This project is NOT a tutorial or toy example. It's a production-grade analysis addressing real business questions about a £370B+ market. It demonstrates:
End-to-end data thinking — from source data to strategic insight
Technical depth — SQL optimization, statistical rigor, BI complexity
Business acumen — Understanding what matters to decision-makers
Communication — Making complex analysis accessible
good effort