Macroeconomic BI Case Study

U.S. Unemployment & Labor Dynamics Analytics

Macroeconomic business intelligence architecture analyzing multi-tiered labor market metrics across 50 states and 380+ metropolitan statistical areas (MSAs) using Bureau of Labor Statistics (BLS) series.

U.S. Bureau of Labor Statistics (BLS) Google Looker Studio Dimensional Star Schema Custom Spread Calculations (bps) State & Metro Geographic Drill-Down

The Macroeconomic Challenge

National headline unemployment rates often mask severe geographic disparities, localized industrial shocks, and uneven post-pandemic recovery speeds across state and metro economies.

The BI Analytical Solution

Built an end-to-end Looker Studio reporting platform integrating BLS time series, structured dimensional schemas, custom spread calculations (bps), and interactive cross-filtering by geographic tier.

Strategic Business Impact

Enables workforce planners, corporate economists, and policy advisors to benchmark labor market tightness, assess regional talent availability, and forecast labor cost pressures in real time.

Live Interactive BI Report

Interactive Labor Dynamics Dashboard Open in Full Screen

Interactive Navigation Guide: Use the state and metro dropdown filters at the top of the canvas to drill into specific local economies. Hover over time series lines to view exact month-over-month variances, and click on any state on the map to cross-filter the entire scorecard.

Looker Studio Engine Connected Live BLS Seasonally Adjusted Series

Strategic Macroeconomic Findings & Recommendations

Synthesizing the multi-year data trends across states and metropolitan statistical areas surfaces four distinct structural takeaways:

1. Sunbelt vs. Rust Belt Divergence

Sunbelt metro hubs (Texas, Florida, North Carolina) demonstrated superior labor absorption speeds post-2021, maintaining unemployment spreads 60–90 bps lower than legacy industrial corridors in the Midwest.

2. Intra-State Metro vs. Rural Disparity

High-GDP states such as California and New York exhibit extreme internal variance: tier-1 metropolitan tech centers maintain tight labor conditions, while agricultural and inland counties experience double the unemployment rate.

3. Structural Sector Resilience

States with diversified healthcare, advanced defense, and public sector employment baselines showed the lowest standard deviation in unemployment throughout economic cyclical adjustments.

4. Workforce Location Strategy

For enterprise talent acquisition, mid-sized Tier-2 MSAs (500K–1.5M population) offer the optimal balance of labor supply liquidity without the extreme wage inflation observed in Tier-1 mega-cities.

Macroeconomic Problem & Analytical Objectives

Evaluating labor market health requires analyzing data far beyond national aggregate numbers. Aggregated headline rates fail to communicate critical regional realities: while coastal technology hubs might experience rapid labor tightening, manufacturing corridors or energy-dependent regions frequently encounter structural lag or delayed recovery.

This business intelligence project unifies multi-tiered data from the U.S. Bureau of Labor Statistics (BLS) covering the 2020–2025 period across all 50 states, Puerto Rico, and 380+ Metropolitan Statistical Areas (MSAs). The analytical goal is to empower corporate talent strategists, regional site selectors, and economic researchers with frictionless drill-down intelligence.

50 + 1
States & Territories
Comprehensive US Coverage
380+
Metro Statistical Areas (MSAs)
Granular Urban Clusters
60+ Mos
Historical Time Horizon
Jan 2020 – Mid 2025
100%
Seasonally Adjusted Series
Normalized Trend Benchmarking

Dataset Source & Data Foundation

Access BLS Data Portal
Official Data Source

U.S. Bureau of Labor Statistics (BLS)

Time Horizon / Period

January 2020 – May 2025 (Monthly Series)

Coverage & Scope

National, 50 States, Puerto Rico & 380+ MSAs (Seasonally Adjusted)

Stakeholder Decision Personas

BI Dimensional Architecture (Star Schema)

To deliver sub-second query performance and seamless cross-filtering across geographic tiers in Looker Studio, the raw BLS time-series flat files were transformed into a normalized Star Schema.

Fact_Unemployment_Monthly

  • DateKey (FK) INT [YYYYMM]
  • GeoKey (FK) VARCHAR(10)
  • UnemploymentRate DECIMAL(4,2)
  • LaborForce BIGINT
  • EmployedPopulation BIGINT
  • UnemployedPopulation BIGINT

Dim_Geography

  • GeoKey (PK) VARCHAR(10)
  • AreaName VARCHAR(100)
  • AreaType ENUM(State, MSA)
  • FIPS_Code CHAR(5)
  • CensusRegion VARCHAR(20)
  • StateAbbreviation CHAR(2)

Dim_Calendar

  • DateKey (PK) INT [YYYYMM]
  • DateValue DATE
  • Year INT
  • MonthNumber / MonthName VARCHAR(15)
  • Quarter CHAR(2)

Calculated Field Logic & Spread Metrics

Calculated fields were engineered directly into the semantic layer to allow instant regional benchmarking against the national baseline: 

-- Spread vs. National Average (Basis Points)
Spread_vs_National_bps = (UnemploymentRate - National_Average_Rate) * 100

-- Month-over-Month (MoM) Velocity
MoM_Change_Pct = (UnemploymentRate - LAG(UnemploymentRate, 1) OVER (PARTITION BY GeoKey ORDER BY DateKey))

-- Labor Market Stress Index (Normalized 0-100)
Labor_Stress_Index = ((UnemploymentRate - MIN(UnemploymentRate)) / (MAX(UnemploymentRate) - MIN(UnemploymentRate))) * 100