Back to posts
Part 3 of 5 · Location Intelligence in Fabric
May 19, 2026•12 min read

Enriching Trade Areas with Census Demographics in Microsoft Fabric

The trade area polygon tells you who can reach a store. Census demographics tell you who they are. The full workflow: picking ACS variables that matter for retail, pulling them into a Fabric lakehouse, and apportioning block groups into drive-time polygons with honest error bars.

A drive-time polygon with no attributes is a shape on a map. The moment it starts earning its keep is when you can say this store draws from 48,000 people, median household income $71k, skewing 25 to 34, and defend every one of those numbers. That defense runs through the Census Bureau.

In trade area analysis I built drive-time isochrones and stored them as polygons in the warehouse, with demographics listed as the first enrichment layer. In the free datasets guide I covered the Census APIs at overview level: what TIGER and ACS are, the licence terms, the access patterns. This post is the missing middle. It walks the full workflow, end to end, in Microsoft Fabric: which ACS variables to request, which geography level to request them at, how to land them in a lakehouse table with PySpark, and the part almost everyone gets wrong, how to apportion block group values into a trade area polygon that ignores every Census boundary ever drawn.

The output is one table: a demographic profile per store, joined back to the trade area table, feeding directly into the scoring model from site selection. Everything below is free data and standard Fabric tooling.

Picking the Variables That Matter

The American Community Survey publishes tens of thousands of variables. For retail trade areas you need perhaps a dozen, and the ACS 5-year estimates are the right product: they are the only release published down to block group level, and the pooled 5-year sample keeps the margins of error tolerable at small geographies. The variables I request on almost every engagement:

Population and households

B01003_001E total population and B11001_001E total households. The denominator for everything else, and the raw size of the addressable market.

Income

B19013_001E median household income, plus its margin of error B19013_001M. Pull the MOE every time. It costs nothing and it is your honesty budget later.

Age bands

The B01001 table splits population by sex and age. A 25 to 34 band is the sum of four cells: males 25 to 29 and 30 to 34 (_011E, _012E) plus the female equivalents (_035E, _036E). Build the bands your merchandising team actually segments on.

The daytime population caveat

ACS counts people where they sleep, not where they spend the day. A downtown lunch concept enriched with residential ACS will look like it has no market. For workplace population, use the Census LEHD LODES workplace files as a separate layer; do not ask ACS a question it cannot answer.

Two practical constraints worth knowing before you write any code: the API caps each request at 50 variables, and suppressed estimates come back as large negative sentinel values like -666666666 rather than nulls. Both are easy to handle in the ingestion notebook, and both will silently corrupt your averages if you don't.

Block Groups or Tracts

ACS 5-year data is published at both tract level (roughly 4,000 people each) and block group level (roughly 600 to 3,000 people each). The tradeoff is precision against noise, and the right answer depends on the size of your trade areas.

Block groups ✓ for drive-time trade areas

Smaller polygons mean less of each unit straddles your trade area boundary, so the apportionment step below introduces less error. For a 10 or 15 minute urban isochrone, this is the level to use.

The cost: individual block group estimates are noisy. A block group median income can carry a margin of error of 30 percent or more. You are betting that aggregating dozens of them inside a trade area washes the noise out, and that bet usually pays.

Tracts for large or rural areas

Tighter margins of error per unit, and for a 30 minute rural trade area covering hundreds of units, the boundary-straddling error matters less because straddling units are a small share of the total.

Also the practical choice for variables not published at block group level, which includes some detailed income and language tables.

My default: block groups for the core retail workflow, tracts as a fallback when a variable is missing or the geography is sparse. The ingestion pattern is identical either way, so supporting both is cheap.

Pulling ACS into the Lakehouse

The Census API is one of the most stable public APIs in existence, and one of the plainest: a GET request that returns a JSON array of arrays, first row headers. The one structural rule that trips people up is that small geographies must be qualified by their parents. You cannot ask for every block group in the country in one call; the for=block group:* predicate requires an in=state:.. county:.. qualifier, so the notebook loops over the counties your stores operate in.

# PySpark in a Fabric notebook: ACS 5-year block group estimates, one call per county
import requests, os
from pyspark.sql import Row
CENSUS_KEY = os.environ["CENSUS_API_KEY"]
YEAR = 2023
VARS = ["B01003_001E", "B11001_001E", "B19013_001E", "B19013_001M",
"B01001_011E", "B01001_012E", "B01001_035E", "B01001_036E"]
COUNTIES = [("06", "037"), ("06", "059"), ("04", "013")] # LA, Orange, Maricopa
def clean(v): # suppressed estimates arrive as -666666666, not null
v = int(v) if v is not None else None
return None if v is not None and v < 0 else v
rows = []
for state, county in COUNTIES:
resp = requests.get(f"https://api.census.gov/data/{YEAR}/acs/acs5",
params={"get": ",".join(["NAME"] + VARS),
"for": "block group:*",
"in": f"state:{state} county:{county}",
"key": CENSUS_KEY}, timeout=60).json()
header, data = resp[0], resp[1:]
for rec in data:
d = dict(zip(header, rec))
rows.append(Row(
GEOID = d["state"] + d["county"] + d["tract"] + d["block group"],
Population = clean(d["B01003_001E"]),
Households = clean(d["B11001_001E"]),
MedianHHInc = clean(d["B19013_001E"]),
MedianHHIncMOE = clean(d["B19013_001M"]),
Age25to34 = sum(clean(d[v]) or 0 for v in
["B01001_011E", "B01001_012E", "B01001_035E", "B01001_036E"])))
spark.createDataFrame(rows).write.mode("overwrite") \
.saveAsTable("enrichment.acs5_blockgroups_2023")

The 12 digit GEOID assembled from state, county, tract, and block group codes is the join key to everything else Census publishes. Schedule this notebook annually: each ACS 5-year vintage lands in roughly December of the following year, so the 2023 vintage is current through late 2025. Name the vintage in the table name, and keep prior vintages around, because year over year comparisons across vintages are a genuinely useful trend signal.

The attributes need geometry to be useful, and that comes from the TIGER/Line block group shapefiles loaded by the pattern in the free datasets guide: one geopandas read per state, geometry stored as WKT, partitioned by state FIPS, joined to the ACS table on GEOID. One warning from experience: match the TIGER year to the ACS vintage. Block group boundaries were redrawn for the 2020 Census, and joining 2023 ACS attributes to 2019 TIGER geometry drops or mismatches thousands of block groups without a single error message.

Areal Interpolation: The Step Everyone Skips

Here is the problem. Your trade area is a drive-time isochrone shaped by the road network. Block groups are shaped by Census cartographers. The two sets of boundaries have never heard of each other, so every trade area will fully contain some block groups and slice through others. The lazy join, counting any block group whose polygon intersects the trade area, overcounts badly: a 15 minute isochrone that clips 5 percent of a large suburban block group inherits 100 percent of its population.

The standard fix is areal interpolation: intersect each block group with the trade area, compute the share of the block group's area that falls inside, and apportion its counts by that share. Clip 40 percent of a block group's area, claim 40 percent of its people. Compute the areas in an equal-area projection, not in raw latitude and longitude, or the weights themselves will be wrong.

# PySpark notebook: apportion block group values into trade area polygons by area overlap
import geopandas as gpd
from shapely import wkt
def to_gdf(table, wkt_col):
df = spark.table(table).toPandas()
return gpd.GeoDataFrame(df, geometry=df[wkt_col].apply(wkt.loads),
crs=4326).to_crs(5070) # EPSG:5070 = equal-area for CONUS
bg = to_gdf("enrichment.acs5_blockgroups_2023", "geometry_wkt")
ta = to_gdf("spatial.trade_areas", "trade_area_wkt") # isochrones from the earlier post
bg["bg_area"] = bg.geometry.area
ix = gpd.overlay(bg, ta[["LocationID", "geometry"]], how="intersection")
ix["w"] = ix.geometry.area / ix["bg_area"] # share of each block group inside
# counts apportion by weight; the median gets a household-weighted approximation
ix["pop_part"] = ix["Population"] * ix["w"]
ix["hh_part"] = ix["Households"] * ix["w"]
ix["age_part"] = ix["Age25to34"] * ix["w"]
ix["inc_wt"] = ix["MedianHHInc"] * ix["hh_part"]
prof = ix.groupby("LocationID").agg(
TradeAreaPop = ("pop_part", "sum"),
TradeAreaHH = ("hh_part", "sum"),
Age25to34 = ("age_part", "sum"),
IncWt = ("inc_wt", "sum")).reset_index()
prof["MedianHHIncEst"] = prof["IncWt"] / prof["TradeAreaHH"]
spark.createDataFrame(prof.drop(columns=["IncWt"])) \
.write.mode("overwrite").saveAsTable("spatial.trade_area_demographics")

Now the honest part. Area weighting assumes population is spread uniformly across each block group, and it never is. People cluster in the subdivisions, not the golf course. Three error sources stack up, and you should be able to name all three when someone asks how good the numbers are:

Uniformity error

Worst for large block groups that are partly parkland, water, or industrial. In dense urban areas, where block groups are small and homogeneous, apportioned totals typically land within a few percent of ground truth. In exurban fringe, expect 10 to 20 percent on individual clipped units.

Sampling error

ACS is a survey, and block group estimates carry real margins of error. This is why you pulled B19013_001M. Aggregating dozens of block groups shrinks the relative error substantially, but a trade area built from six block groups deserves a wide interval, and your profile table should say so.

The median problem

Medians do not aggregate. The household-weighted average of block group medians in the code above is an approximation, and everyone in the industry uses it. Label the column as an estimate and never present it as a true trade area median.

If the uniformity assumption is costing you, the upgrade path is dasymetric refinement: clip block groups to residential land cover, or weight by Census block level population counts instead of raw area, before apportioning. That is real additional work, and for most retail scoring models the plain area weighting is accurate enough that the scoring weights, not the demographics, dominate the uncertainty.

Per-Store Profiles, and Feeding the Scoring Model

The final move is the easy one: join the apportioned profile back to the trade area table built in trade area analysis, so every store carries its demographics as flat columns. No geometry survives to query time; Power BI reads plain numbers.

-- Per-store demographic profile joined to the trade area table, ready for scoring
SELECT l.LocationID,
l.StoreName,
CAST(d.TradeAreaPop AS INT) AS TradeAreaPopulation,
CAST(d.MedianHHIncEst AS INT) AS MedianHHIncomeEst,
d.Age25to34 / NULLIF(d.TradeAreaPop, 0) AS Pct25to34,
d.TradeAreaPop / NULLIF(l.CompetitorCount + 1, 0) AS PopPerCompetitor
FROM spatial.Locations l
JOIN spatial.trade_area_demographics d
ON d.LocationID = l.LocationID

Those columns slot straight into the weighted scoring model from site selection: population and income are two of the four factors in that model, and now they come from apportioned block groups instead of a crude intersects join. The same apportionment code scores candidate sites too, because a candidate's projected isochrone is just another polygon in the trade area table. That symmetry is the payoff of doing this once, properly, in the lakehouse: existing stores, candidate sites, and cannibalization overlaps all read from one demographic pipeline with one vintage, one methodology, and error bars you can actually explain.

Tags

GISData EnrichmentCensusMicrosoft FabricSpatial Analytics