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:
B01003_001E total population and B11001_001E total households. The denominator for everything else, and the raw size of the addressable market.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.