Database Snapshot
ACS/ZCTA rows33,772
ACS/ZCTA columns52
Rows joined to TV markets33,756
TV markets represented211
Bridge stations82
DMA ZIP rows41,306
Test sales/call records560
Raw ACS extracts16
Source vintage2024
The ACS data is ZCTA-level, which approximates USPS ZIP codes. The repository stores ZCTA-only extracts from official Census bulk files because full all-geography raw tables can exceed GitHub file-size limits.
Temporary Home Shopping Test Upload
This workbook is temporary test data for building report logic and the website. It intentionally includes more columns than expected customer sales uploads and should be removed after production customer data is available.
| Field | Value |
|---|---|
| Dataset status | temporary_test_data |
| Purpose | Sample upload data for report design, database testing, and website construction. Delete after customer production data is available. |
| Last import | 2026-06-13T16:37:12Z |
| Main sheet | Home Shopping Test Data |
| Record rows | 560 |
| Columns | 41 |
| Workbook SHA-256 | fcd701e124ace2e71af0adac1baff42ff914e4c551b73e5deb111a756349af91 |
| Repository workbook | data/test/home-shopping/home_shopping_shop_test_file.xlsx |
| Repository CSV | data/test/home-shopping/home_shopping_shop_test_records.csv |
| Note | This test file contains sales, no-sale calls, and product returns. It includes more columns than expected customer sales uploads; ingestion should allow extra columns but report logic should document the required minimum production fields. |
Home Shopping Test Upload Columns
| # | Column Name |
|---|---|
| 1 | Record Type |
| 2 | Order Type |
| 3 | Customer |
| 4 | Telemarketing Co. |
| 5 | Product Code |
| 6 | Product Type |
| 7 | ISCI |
| 8 | Campaign Name |
| 9 | Affiliate Name |
| 10 | Affiliate Market |
| 11 | Time Zone |
| 12 | Dialed (800#) |
| 13 | Promo Code |
| 14 | YYYY/MM/DD |
| 15 | 24:MM |
| 16 | ANI-Consumer Phone |
| 17 | Ship City |
| 18 | Ship State |
| 19 | Ship Zip Code |
| 20 | Ship Country |
| 21 | Call Length(seconds) |
| 22 | Column1 |
| 23 | R1-Number of Calls |
| 24 | R2-Number of Quantity Orders |
| 25 | Order Number |
| 26 | Total Amount |
| 27 | Commission % |
| 28 | Affiliate Fee % |
| 29 | ConsumerEXP Fee % |
| 30 | Total Fee |
| 31 | Bridge Call Sign |
| 32 | Bridge Channel |
| 33 | Bridge Station City |
| 34 | Bridge Station State |
| 35 | Bridge Type |
| 36 | DMA Market |
| 37 | DMA State |
| 38 | DMA County |
| 39 | DMA TV Households |
| 40 | FIPS |
| 41 | Source Notes |
Repository Contents
| Path | Size / Type |
|---|---|
| bridge_test_data.json | 21.9 MB |
| data/processed/acs_zcta_demographics_2024_tv_markets.csv | 17.8 MB |
| data/metadata/acs_zcta_demographics_2024_metadata.json | 8.3 KB |
| data/test/home-shopping/home_shopping_shop_test_file.xlsx | 126.4 KB |
| data/test/home-shopping/home_shopping_shop_test_records.csv | 198.1 KB |
| data/test/home-shopping/home_shopping_shop_test_file_metadata.json | 2.7 KB |
| data/raw/acs/2024/zcta_extracts/ | folder |
| outputs/bridge-news-station-zip-test/Bridge_News_DMA_ZIP_Test_File.xlsx | 3.8 MB |
| outputs/home-shopping-shop-test/Home Shopping - Shop Test File.xlsx | 148.9 KB |
| docs/data-dictionary.md | 13.3 KB |
| docs/acs-zcta-column-options.md | 7.3 KB |
| docs/reporting-requirements.md | 5.4 KB |
| scripts/download_census_bulk_zcta_demographics.py | 10.6 KB |
| scripts/validate_acs_zcta_data.py | 9.1 KB |
Raw ACS Source Extracts
| Raw Extract File | ACS Table | Size | ZCTA Rows | Official Source URL |
|---|---|---|---|---|
| acsdt5y2024-b01001-zcta.dat | b01001 | 10.7 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b01001.dat |
| acsdt5y2024-b01002-zcta.dat | b01002 | 1.4 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b01002.dat |
| acsdt5y2024-b01003-zcta.dat | b01003 | 789.4 KB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b01003.dat |
| acsdt5y2024-b02001-zcta.dat | b02001 | 2.7 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b02001.dat |
| acsdt5y2024-b03003-zcta.dat | b03003 | 1.3 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b03003.dat |
| acsdt5y2024-b11001-zcta.dat | b11001 | 2.6 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b11001.dat |
| acsdt5y2024-b17001-zcta.dat | b17001 | 12.4 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b17001.dat |
| acsdt5y2024-b19013-zcta.dat | b19013 | 915.7 KB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b19013.dat |
| acsdt5y2024-b19301-zcta.dat | b19301 | 858.4 KB | 33303 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b19301.dat |
| acsdt5y2024-b23025-zcta.dat | b23025 | 2.2 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b23025.dat |
| acsdt5y2024-b25001-zcta.dat | b25001 | 767.2 KB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b25001.dat |
| acsdt5y2024-b25002-zcta.dat | b25002 | 1.2 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b25002.dat |
| acsdt5y2024-b25003-zcta.dat | b25003 | 1.2 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b25003.dat |
| acsdt5y2024-b25064-zcta.dat | b25064 | 872.6 KB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b25064.dat |
| acsdt5y2024-b25077-zcta.dat | b25077 | 952.5 KB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b25077.dat |
| acsdt5y2024-b28002-zcta.dat | b28002 | 3.3 MB | 33772 | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData/acsdt5y2024-b28002.dat |
All Processed ACS/ZCTA Columns
| # | Column Name | Demographic / Field Title | Category |
|---|---|---|---|
| 1 | zcta | ZCTA / ZIP approximation | Geography |
| 2 | zcta_name | Census ZCTA name | Geography |
| 3 | tv_market | TV Market / DMA | TV Market Join |
| 4 | tv_market_state | TV Market state | TV Market Join |
| 5 | county | County | TV Market Join |
| 6 | tv_households | TV households | TV Market Join |
| 7 | fips | County/state FIPS | TV Market Join |
| 8 | bridge_station_count | Bridge station count | TV Market Join |
| 9 | bridge_stations | Bridge station call signs | TV Market Join |
| 10 | total_population | Total population | Demographic |
| 11 | male_population | Male population | Demographic |
| 12 | female_population | Female population | Demographic |
| 13 | median_age | Median age | Demographic |
| 14 | households | Households | Demographic |
| 15 | median_household_income | Median household income | Demographic |
| 16 | per_capita_income | Per-capita income | Demographic |
| 17 | poverty_population | Population below poverty level | Demographic |
| 18 | civilian_labor_force | Civilian labor force | Demographic |
| 19 | unemployed_population | Unemployed population | Demographic |
| 20 | white_alone | White alone | Race / Ethnicity |
| 21 | black_alone | Black or African American alone | Race / Ethnicity |
| 22 | american_indian_alaska_native_alone | American Indian and Alaska Native alone | Race / Ethnicity |
| 23 | asian_alone | Asian alone | Race / Ethnicity |
| 24 | native_hawaiian_pacific_islander_alone | Native Hawaiian and Other Pacific Islander alone | Race / Ethnicity |
| 25 | some_other_race_alone | Some Other Race alone | Race / Ethnicity |
| 26 | two_or_more_races | Two or More Races | Race / Ethnicity |
| 27 | hispanic_or_latino | Hispanic or Latino | Race / Ethnicity |
| 28 | housing_units | Housing units | Housing |
| 29 | occupied_housing_units | Occupied housing units | Housing |
| 30 | vacant_housing_units | Vacant housing units | Housing |
| 31 | owner_occupied_units | Owner-occupied units | Housing |
| 32 | renter_occupied_units | Renter-occupied units | Housing |
| 33 | median_home_value | Median home value | Housing |
| 34 | median_gross_rent | Median gross rent | Housing |
| 35 | internet_subscription_households | Households with internet subscription | Demographic |
| 36 | no_internet_access_households | Households with no internet access | Demographic |
| 37 | poverty_rate | Poverty rate | Derived Rate |
| 38 | unemployment_rate | Unemployment rate | Derived Rate |
| 39 | owner_occupied_rate | Owner-occupied rate | Derived Rate |
| 40 | renter_occupied_rate | Renter-occupied rate | Derived Rate |
| 41 | vacancy_rate | Vacancy rate | Derived Rate |
| 42 | internet_subscription_rate | Internet subscription rate | Derived Rate |
| 43 | no_internet_access_rate | No internet access rate | Derived Rate |
| 44 | hispanic_or_latino_rate | Hispanic or Latino rate | Derived Rate |
| 45 | source_name | Source name | Source / Audit |
| 46 | source_url | Source URL | Source / Audit |
| 47 | source_dataset | Source dataset | Source / Audit |
| 48 | source_vintage | Source vintage | Source / Audit |
| 49 | source_geography | Source geography | Source / Audit |
| 50 | downloaded_at | Downloaded at | Source / Audit |
| 51 | updated_at | Updated at | Source / Audit |
| 52 | notes | Notes | Source / Audit |
Reporting Controls
Planned customer-facing reports should support multi-select filters, time-based analysis, TVHH/OTAHH demographic weighting, broadcast owner filters, and flexible sorting.
Filters
| Control | Mode | Purpose |
|---|---|---|
| TV Market / DMA | Multi-select | Include one or more markets in a report. |
| Bridge Station | Multi-select | Include one or more stations/call signs. |
| Station Type | Multi-select | Filter by LPTV, CLS A, FPTV, or future normalized station type. |
| Broadcast Owner | Multi-select | Filter by owner/operator once owner data is loaded. |
| Campaign / Customer / Product | Multi-select | Filter sales and calls by commercial context. |
| Date / Day of Week / Daypart | Range + multi-select | Analyze time-of-day and day-of-week performance. |
| Demographic Segment | Multi-select | Filter or group by selected demographics and derived rates. |
Demographic Weighting Basis
| Weighting Basis | Use |
|---|---|
| TV household weighted | Default for broadcast reach and market opportunity reports. |
| OTA household weighted | Use for over-the-air opportunity and broadcast-only analysis. |
| ACS household weighted | Use for ZIP/ZCTA demographic summaries. |
| Population weighted | Use for person-level demographic summaries. |
| Sales weighted | Use for sales-performance demographic profiles. |
| Call weighted | Use for call/no-sale demographic profiles. |
Sorting
| Sortable Field Group | Direction |
|---|---|
| Any demographic column | asc / desc |
| Derived rates | asc / desc |
| TV households | asc / desc |
| OTA households | asc / desc |
| Revenue, orders, calls, conversion rate | asc / desc |
| Station count, ZIP/ZCTA count | asc / desc |
Sample ZCTA Rows
| ZCTA | TV Market | County | Bridge Stations | Population | Households | Median Income | Median Home Value | Internet Subscription Rate |
|---|---|---|---|---|---|---|---|---|
| 10001 | New York, NY | NEW YORK | WHTV | 30511 | 15737 | 129852 | 561100 | 0.948974 |
| 60601 | Chicago, IL | COOK | WCHU | 15235 | 9631 | 121458 | 526200 | 0.941543 |
| 77002 | Houston, TX | HARRIS | KVVV | 21015 | 7644 | 86394 | 305000 | 0.908687 |
| 90001 | Los Angeles, CA | LOS ANGELES | KFLA, KILA | 56189 | 13671 | 60767 | 560300 | 0.854729 |
| 99501 | Anchorage, AK | ANCHORAGE | KLDY, KDMD | 16175 | 7610 | 70891 | 379100 | 0.927332 |
Source And Update Metadata
| Field | Value |
|---|---|
| source_name | U.S. Census Bureau |
| source_base_url | https://www2.census.gov/programs-surveys/acs/summary_file/2024/table-based-SF/data/5YRData |
| source_dataset | ACS 2024 5-Year Table-Based Summary File |
| source_vintage | 2024 |
| source_geography | ZCTA |
| downloaded_at | 2026-06-13T15:06:08.437957+00:00 |
| updated_at | 2026-06-13T15:07:06.216211+00:00 |
| processed_output | /Users/michaelkokernak/Documents/Codex/2026-06-13/files-mentioned-by-the-user-bridge/data/processed/acs_zcta_demographics_2024_tv_markets.csv |
| row_count | 33772 |
| tv_market_filter | |
| tv_market_count | 211 |
| tables | {"b01003": {"B01003_E001": "total_population"}, "b01001": {"B01001_E002": "male_population", "B01001_E026": "female_population"}, "b01002": {"B01002_E001": "median_age"}, "b11001": {"B11001_E001": "households"}, "b19013": {"B19013_E001": "median_household_income"}, "b19301": {"B19301_E001": "per_capita_income"}, "b17001": {"B17001_E002": "poverty_population"}, "b23025": {"B23025_E003": "civilian_labor_force", "B23025_E005": "unemployed_population"}, "b02001": {"B02001_E002": "white_alone", "B02001_E003": "black_alone", "B02001_E004": "american_indian_alaska_native_alone", "B02001_E005": "asian_alone", "B02001_E006": "native_hawaiian_pacific_islander_alone", "B02001_E007": "some_other_race_alone", "B02001_E008": "two_or_more_races"}, "b03003": {"B03003_E003": "hispanic_or_latino"}, "b25001": {"B25001_E001": "housing_units"}, "b25002": {"B25002_E002": "occupied_housing_units", "B25002_E003": "vacant_housing_units"}, "b25003": {"B25003_E002": "owner_occupied_units", "B25003_E003": "renter_occupied_units"}, "b25077": {"B25077_E001": "median_home_value"}, "b25064": {"B25064_E001": "median_gross_rent"}, "b28002": {"B28002_E002": "internet_subscription_households", "B28002_E013": "no_internet_access_households"}} |
| notes | ["Raw files in this repository are ZCTA-row extracts from official Census ACS table-based Summary File .dat files.", "Full raw source tables include all geographies and some exceed GitHub per-file size limits.", "ACS data is published by ZCTA, not every USPS ZIP.", "TV market fields are joined from bridge_test_data.json DMA ZIP rows."] |
Validation
Run the repository validation suite after any refresh:
python scripts/validate_acs_zcta_data.py