Grocery penetration block by block in Denver

This example shows how to build a block-by-block penetration map for a brand set, the market-void side of a void analysis: for every Census Block Group in Denver, how many visits each grocery brand captures from the people living there, which brand leads, and how saturated the block group is against the metro norm. We run it for Sprouts Farmers Market against King Soopers and Safeway.

For this we use our US Places metrics and the neighborhood (block group) foot traffic, fetched from Python and joined in DuckDB, so every step of the analysis is a SQL statement you can read and change:

import os
import requests
import pandas as pd
import duckdb

API = "https://api.pine59.com/v3/datasets"
HEADERS = {"Authorization": f"Bearer {os.environ['PINE59_API_KEY']}"}

def run_query(dataset, body):
    r = requests.post(f"{API}/{dataset}:runQuery", json=body, headers=HEADERS)
    r.raise_for_status()
    return [rec["fields"] for rec in r.json().get("records", [])]

METRO   = "Denver-Aurora-Lakewood, CO Metro Area"
BRANDS  = ["Sprouts Farmers Market", "King Soopers", "Safeway"]
QUARTER = "2025-04-01"   # trade areas are quarterly; Q2 2025

1. Fetch trade areas and store visits

Two ingredients, both per store. The trade area lists the home block groups a store's visitors come from with the fraction each contributes (trade_area, one row per store and origin block group, quarterly). The store's visit volume over the same quarter comes from foot_traffic_month. Both datasets embed the place fields, so a brand plus metro filter is all it takes:

trade_areas = pd.DataFrame([
    row
    for brand in BRANDS
    for row in run_query("visitation_usa_places.trade_area", {
        "fields": [
            {"name": "location_id"},
            {"name": "brands"},
            {"name": "trade_area_location_id"},
            {"name": "people_fraction"},
        ],
        "filters": [
            {"fieldName": "brands",          "operator": "==", "value": brand},
            {"fieldName": "us_cbsa",         "operator": "==", "value": METRO},
            {"fieldName": "trade_area_type", "operator": "==", "value": "HOME"},
            {"fieldName": "observation_start_date", "operator": "==", "value": QUARTER},
        ],
        "pageSize": 10000,
    })
])

store_visits = pd.DataFrame(run_query("visitation_usa_places.foot_traffic_month", {
    "fields": [
        {"name": "location_id"},
        {"name": "brands"},
        {"name": "visits_sum", "aggregation": "SUM"},
    ],
    "filters": [
        {"fieldName": "brands",  "operator": "in", "values": BRANDS},
        {"fieldName": "us_cbsa", "operator": "==", "value": METRO},
        {"fieldName": "observation_start_date", "operator": ">=", "value": "2025-04-01"},
        {"fieldName": "observation_start_date", "operator": "<",  "value": "2025-07-01"},
    ],
    "groupBy": [{"fieldName": "location_id"}, {"fieldName": "brands"}],
    "pageSize": 10000,
}))

print(len(store_visits), "stores,", len(trade_areas), "store-to-block-group rows")

Q2 2025: 146 stores (83 King Soopers, 42 Safeway, 21 Sprouts) and 12,769 store-to-block-group rows. Store counts from a traffic window differ by a store or two from the places directory, which lists every record whether or not it had traffic that quarter. One brand per trade-area request keeps each response on a single page; the fraction column is people_fraction, the aggregated visit column comes back as visits_sum_SUM.

2. Captured visits, rank, and leader per block group

The join is one multiplication per row: a store's visits times the fraction of its visitors living in a block group is the visits that block group sends to that store. Summed per brand and ranked, that is the competitive position of every block group in the metro:

per_cbg = duckdb.sql("""
    WITH captured AS (
        SELECT ta.trade_area_location_id AS cbg, ta.brands,
               ta.people_fraction::DOUBLE * sv.visits_sum_SUM::DOUBLE AS visits_from_cbg
        FROM trade_areas ta JOIN store_visits sv USING (location_id)
    ),
    per_brand AS (
        SELECT cbg, brands, SUM(visits_from_cbg) AS visits
        FROM captured GROUP BY cbg, brands
    ),
    ranked AS (
        SELECT cbg, brands, visits,
               RANK() OVER (PARTITION BY cbg ORDER BY visits DESC) AS rank,
               SUM(visits) OVER (PARTITION BY cbg)                 AS set_visits
        FROM per_brand
    )
    SELECT cbg, set_visits,
           MAX(CASE WHEN brands = 'Sprouts Farmers Market' THEN visits END) AS sprouts_visits,
           MAX(CASE WHEN brands = 'Sprouts Farmers Market' THEN rank END)   AS sprouts_rank,
           ARG_MAX(brands, visits)                                          AS top_brand
    FROM ranked
    GROUP BY cbg, set_visits
""").df()

print(len(per_cbg), "block groups with capture")
print(per_cbg.top_brand.value_counts(normalize=True).round(3))

Result, Q2 2025: 2,173 origin block groups with measurable capture.

How to read it: King Soopers leads in 72% of them, Safeway in 24%, Sprouts in 4%; Sprouts captures 7% of the three-brand set's visits overall. The table behind these shares is the competitive map: every block group where the challenger already leads, and every one where it is a distant third.

Trade-area fractions are reported down to a floor, so captured visits are conservative. Rankings and comparisons between block groups are robust; treat absolute levels as lower bounds.

3. Saturation against the metro norm

How heavily does the set already draw on a block group, relative to the rest of the metro? Divide the set's captured visits by the block group's resident activity, from the block-group-grain foot traffic (neighborhood_visitation.foot_traffic_month, resident_visits_sum, pulled one month per request), and index against the metro median. One definition matters here: resident_visits_sum counts the visits the block group's own residents make inside the block group, not their grocery demand. The ratio is therefore an activity-normalized index, a way to compare block groups of different size and activity on the same scale, not a share of demand captured:

cbg_visits = pd.concat([
    pd.DataFrame(run_query("neighborhood_visitation.foot_traffic_month", {
        "fields": [{"name": "location_id"}, {"name": "resident_visits_sum"}],
        "filters": [
            {"fieldName": "us_cbsa", "operator": "==", "value": METRO},
            {"fieldName": "observation_start_date", "operator": "==", "value": month},
        ],
        "pageSize": 10000,
    }))
    for month in ["2025-04-01", "2025-05-01", "2025-06-01"]
])

saturation = duckdb.sql("""
    WITH resident AS (
        SELECT location_id AS cbg, SUM(resident_visits_sum::DOUBLE) AS resident_visits
        FROM cbg_visits GROUP BY location_id
    ),
    joined AS (
        SELECT p.*, r.resident_visits, p.set_visits / r.resident_visits AS capture_ratio
        FROM per_cbg p JOIN resident r USING (cbg)
        WHERE r.resident_visits > 0
    )
    SELECT *, capture_ratio / MEDIAN(capture_ratio) OVER () AS saturation_idx
    FROM joined
""").df()

print(round(saturation.capture_ratio.median(), 3), "median capture ratio")
print((saturation.saturation_idx < 0.5).sum(), "block groups under half the median")

How to read it: in the median Denver block group, the set's captured visits come to about 12% of the block group's resident activity (Q2 2025); that is the metro norm the index is set to 1 against. An index well below 1 says people there visit this brand set less than the norm for their level of activity: 395 of the 1,623 block groups with resident activity sit under half the median. Read it as an indicator, not a verdict. A low index can mean unmet demand, a strong independent scene, different shopping habits, or leakage to brands outside the set. It says where to look; the void analysis reference says how.

4. The headroom shortlist

Filtering to the expansion-relevant corner surfaces the headroom map: a large resident base, saturation below 0.7 times the median, and minimal Sprouts presence (absent or ranked last). Every threshold here is a parameter:

shortlist = duckdb.sql("""
    SELECT cbg, set_visits, sprouts_rank, saturation_idx, resident_visits
    FROM saturation
    WHERE resident_visits >= (SELECT MEDIAN(resident_visits) FROM saturation)
      AND saturation_idx < 0.7
      AND (sprouts_rank IS NULL OR sprouts_rank = 3)
    ORDER BY resident_visits DESC
""").df()

print(len(shortlist), "block groups")
print(shortlist.head(8).round(2).to_string(index=False))

219 block groups pass. The top rows by resident activity, Q2 2025:

¹ Area names are readability labels inferred from block-group centroids (neighborhood_visitation.census_block_groups carries the polygon). The data speaks GEOIDs.

How to read it: the northeast growth corridor (Green Valley Ranch, Montbello, Brighton, Thornton) and Aurora's east side combine substantial resident bases with below-norm capture and little or no Sprouts presence; Highlands Ranch west is the one southern entry. Joined with the block-group polygons, the same table is a choropleth (captured visits, rank, or saturation per block group), and the standing dataset a territory or marketing team refreshes each quarter.

The resident-base floor, the 0.7 saturation cut, and the brand set itself are choices, not truths: a marketing team mapping media spend and an expansion team hunting sites will set them differently from the same base table.

Scaling this. The recipe assumes every response fits one page: one brand per trade-area request, one month per neighborhood request, at most 10,000 rows each. That holds for 146 stores in one metro. It stops holding for a nationwide brand or a whole state, where trade areas alone run to millions of rows and paging a grouped query is not reliable. For anything beyond a metro, run the same request shape through the export API: it executes server-side and returns files, which DuckDB reads directly.