Project Homepage  |  Key Findings and Recommendations  |  Documentation/Process  |  References, Data Sources, and Notes

Documentation / Process

Data Sources and Credibility

CDC PLACES: Local Data for Better Health

Purpose in this project: County-level estimated prevalence of leisure-time physical inactivity, coronary heart disease, stroke, arthritis, and mobility disability.

Granularity: U.S. county level.

Methodology: PLACES produces model-based small-area estimates using BRFSS survey data together with Census and American Community Survey information. The project used crude prevalence estimates.

Credibility Assessment

• Reliable: High. Maintained and documented by the CDC.

• Original: Medium. PLACES values are modeled estimates derived from underlying BRFSS, Census, and ACS data rather than direct measurements of every county population.

• Comprehensive: High. Provides standardized measures across more than 3,000 U.S. county-level geographies.

• Current: Moderate. The dataset provided an appropriate contemporary snapshot for the original project, but this case study is cross-sectional and is not connected to a continuously updating data source.

• Cited: High. CDC provides extensive methodological documentation and measure definitions.

CDC specifically describes PLACES as multilevel-regression/post-stratification small-area estimates and recommends careful interpretation of county comparisons, including consideration of confidence intervals and population characteristics. 

CDC WONDER: Underlying Cause of Death

Purpose in this project: County-level death counts and population denominators for ICD-10 Chapter IX, Diseases of the Circulatory System (I00-I99).

Granularity: U.S. county level.

Credibility Assessment

• Reliable: High. Mortality data originate from U.S. death certificates and are distributed through CDC WONDER.

• Original: High/Moderate. The underlying vital-statistics records are primary administrative data, although the project used an aggregated public CDC extract.

• Comprehensive: High. Broad national county coverage.

• Current: Moderate. Appropriate for the project's historical snapshot but not a live mortality surveillance dataset.

• Cited: High. CDC documents suppression, reliability, mortality definitions, and query methodology.

CDC WONDER suppresses counts from 1-9 for confidentiality and historically flags mortality rates based on fewer than 20 deaths as statistically unreliable. 

SQL Processing

Query 01a — Initial PLACES Extraction

The initial query standardized FIPS codes and pivoted the long-format CDC PLACES data into county-level columns.

Selected measures included:

• LPA — leisure-time physical inactivity

• STROKE — stroke

• CHD — coronary heart disease

• FALLS — attempted falls measure

The FALLS field returned no usable values, prompting a revision rather than filling the missing field with an assumed value.

Query 01b / 02a — Revised PLACES Extraction

The revised query removed the unusable falls field and added mobility disability and arthritis.

SELECT

    LPAD(CAST(LocationID AS STRING), 5, '0') AS fips_code,

    StateDesc AS state_name,

    LocationName AS county_name,

    MAX(CASE WHEN MeasureId = 'LPA'

        THEN Data_Value END) AS pct_physical_inactivity,

    MAX(CASE WHEN MeasureId = 'STROKE'

        THEN Data_Value END) AS pct_stroke,

    MAX(CASE WHEN MeasureId = 'CHD'

        THEN Data_Value END) AS pct_coronary_heart_disease,

    MAX(CASE WHEN MeasureId = 'MOBILITY'

        THEN Data_Value END) AS pct_mobility_disability,

    MAX(CASE WHEN MeasureId = 'ARTHRITIS'

        THEN Data_Value END) AS pct_arthritis

FROM

    `public-health-and-activity.public_health_analysis.places_county_raw`

WHERE

    Data_Value_Type = 'Crude prevalence'

GROUP BY

    LocationID,

    StateDesc,

    LocationName

ORDER BY

    fips_code;

Purpose

• Restored leading zeros to FIPS identifiers.

• Reduced a long-format source containing multiple measure rows per county into one county-level analytical row.

• Limited the dataset to five measures used in the final analysis.

• Created a manageable table for spreadsheet-based validation and downstream visualization.

The original query log used pct_older_adult_falls as the output alias for the MOBILITY measure; that field name was later recognized as incorrect and changed. 

Query 02 — CDC WONDER Mortality Extraction

SELECT

    LPAD(CAST(`County Code` AS STRING), 5, '0') AS fips_code,

    County AS county_name,

    Deaths AS raw_cardio_deaths,

    Population AS total_population

FROM

    `public-health-and-activity.public_health_analysis.cardio_mortality_raw`

WHERE

    Notes IS NULL

ORDER BY

    fips_code;

Purpose

• Standardized mortality-data FIPS codes to five characters.

• Retained county name, raw mortality count, and population denominator.

• Removed footer/note rows from the original CDC export.

• Prepared mortality data for joining to the PLACES table.

The source-provided crude rate was not retained in the SQL output because it included nonnumeric suppression/reliability labels. A new crude rate was calculated later in Google Sheets.

A more detailed breakdown of SQL queries can be found in this document: Public Health & Activity: Query Log.

Google Sheets Cleaning and Validation

The BigQuery outputs were imported into a working Google Sheets file for joining, validation, and analytical-field creation.

Geographic Standardization

Google Sheets again removed some leading zeros from numeric FIPS values after import. A formatted FIPS field was created using:

=TEXT(B2,"00000")

The five-digit FIPS code was then used as the common key for joining the PLACES and mortality datasets.

Dataset Join

The health and mortality tables were joined using XLOOKUP on FIPS code.

This produced one analytical table containing geography, selected health prevalence estimates, mortality counts, population, and subsequently created analytical classifications.

Mortality Rate

A crude mortality rate per 100,000 residents was calculated as:

=IFERROR((Raw_Cardiovascular_Deaths / Total_Population) * 100000,"")

Rows containing suppressed or otherwise nonnumeric death counts returned blank rather than generating calculation errors.

Validation Checks

The cleaning process included:

• Duplicate review: no duplicates identified.

• State/county validation: one national aggregate record labeled "United States" was identified and removed.

• Percentage boundaries: no analyzed percentage values fell below 0% or above 100%.

• Logical consistency checks across selected health measures.

• Review of missing and suppressed mortality values.

• Descriptive statistics including mean, median, standard deviation, minimum, and maximum.

• Manual review of extreme values before Tableau import.

Small-Population Flag

Counties below 5,000 residents were placed into an analysis-specific population tier labeled High Volatility.

A total of 145 counties met this threshold.

The purpose of this field was not to classify urban versus rural counties. It was created specifically to help viewers isolate counties in which small denominators may make rates per 100,000 especially sensitive to small changes in event counts.

Health-Metric Outlier Classification

A county was labeled an Extreme Outlier when at least one of the five selected health measures was more than three standard deviations from the national mean.

Conceptually:

ABS(County Value - National Mean) > 3 × Standard Deviation

Counties with missing or nonnumeric values in the required measures were classified as incomplete.

Results:

• 2,909 Standard Counties

• 48 Extreme Outlier Counties

• 188 Excluded / Incomplete

Specific Outlier Classification

A second field preserved the specific measures responsible for each county's outlier status.

Results:

• Physical inactivity: 10

• Stroke: 30

• Coronary heart disease: 11

• Mobility disability: 15

• Arthritis: 6

This produced 72 metric-level outlier flags across 48 counties.

Final Tableau Export

After cleaning was completed, the working analytical sheet was copied and pasted as values only into a final sheet. This removed active spreadsheet formulas and reduced the likelihood of import or recalculation problems in Tableau.

An additional spreadsheet detailing a log of all cleaning strategies can be found in this document: Health Metrics and Cardiovascular Mortality: Cleaning Log

Tableau Field, Calculation, and Analysis Summary

One important distinction is worth preserving in your revised documentation: many of the fields used in Tableau were actually calculated upstream in Google Sheets rather than created as Tableau calculated fields.

Analytical Fields Imported into Tableau

Calculated Cardio Death Rate Per 100K
Crude cardiovascular/circulatory mortality rate calculated from raw deaths and county population.

Population Volume Tier
Separates counties below 5,000 residents from the remaining counties for small-population sensitivity analysis.

Health-Metric Outlier Status
Classifies counties as Standard, Extreme Outlier, or Incomplete.

Specific Health-Metric Outlier Type
Identifies whether the extreme-value flag came from physical inactivity, stroke, coronary heart disease, mobility disability, arthritis, or multiple measures.

Cardiovascular Outlier Status
Separates counties with extreme cardiovascular mortality rates from standard and incomplete counties.

Tableau Interactivity

The workbook uses interactive controls and filters to allow the viewer to investigate:

• State

• Population-volume tier

• Cardiovascular-mortality outlier status

• Individual health-metric outlier status

• Selected health measure

The health-measure map allows the viewer to move among coronary heart disease, stroke, arthritis, and mobility disability without requiring a separate dashboard for every measure.

Tableau Statistical Calculations

Tableau's built-in linear trend-line functionality was used for scatterplot analysis.

Models were stratified by:

Cardiovascular Outlier Status

Cardiovascular Outlier Status × (Selected Health Metric + Intercept)

or:

Population Volume Tier

Population Volume Tier × (Total Population + Intercept)

The resulting trend-line exports included:

• Regression equation

• Slope and intercept

• R²

• p-value

• Standard error

• ANOVA/model output

No multivariable regression was performed in Tableau.

Important documentation note: I would title this section "Tableau Field, Calculation, and Statistical Modeling Summary" rather than claiming that every field above was a Tableau calculated field. Your available source documents do not preserve a complete inventory of actual Tableau-native calculated-field syntax, so inventing that syntax would make the documentation less accurate rather than more complete.