Project Date: August 2026
Data Source: County Health Rankings & Roadmaps (CHR&R)
Tools: Google BigQuery (SQL) | Tableau Public (Web Authoring)
County Health Rankings & Roadmaps provides county-level measures related to health outcomes, health behaviors, social and economic factors, the physical environment, and clinical care across the United States.
For this project, I used Google BigQuery to clean and restructure the source data and Tableau Public to build an interactive dashboard for exploring public health measures across more than 3,000 U.S. counties.
The dashboard allows users to:
map geographic differences in selected health measures;
compare counties with high and low values;
review changes in a selected county over time; and
compare two health measures in a county-level scatter plot.
The project required cleaning a long-format public health dataset, building consistent five-digit county FIPS codes for geographic analysis, creating parameter-controlled Tableau calculations, and accounting for measures reported in different units.
The objective was to transform a large public health dataset into an interactive dashboard that supports several types of exploration within one interface.
The finished dashboard allows users to:
analyze county-level health measures geographically;
identify counties at the high and low ends of a selected measure;
review historical patterns for individual counties;
compare two health measures at the same time; and
explore relationships among behavioral, socioeconomic, environmental, clinical, and health outcome measures.
Rather than answering one predefined business or research question, the dashboard was designed as an exploratory public health tool that can support multiple questions depending on the measures, county, state, and time period selected.
The CHR&R source data combines geographic information with health measures stored in a long, or "tall," format. In this structure, individual rows contain a measure name and corresponding raw value rather than storing every health measure in a separate column.
The source also contains several geographic levels, including national, state, and county records.
Before using the data in Tableau, I cleaned it in Google BigQuery through three query iterations. The final version was designed to retain usable county-level observations and create a consistent geographic identifier for mapping.
The final table was created with the following query:
CREATE OR REPLACE TABLE `county-health-rankings-504715.county_health_rankings_and_roadmaps.county_health_rankings_clean` AS
SELECT DISTINCT
`State` AS state,
`County` AS county,
CAST(`State code` AS INT64) AS state_code,
CAST(`County code` AS INT64) AS county_code,
-- Concatenates 2-digit state code + 3-digit county code to guarantee a complete 5-digit FIPS for geographic mapping
CONCAT(
LPAD(CAST(`State code` AS STRING), 2, '0'),
LPAD(CAST(`County code` AS STRING), 3, '0')
) AS fipscode_formatted,
`Year span` AS year_span,
`Measure name` AS measure_name,
CAST(`Measure id` AS INT64) AS measure_id,
`Numerator` AS numerator,
`Denominator` AS denominator,
`Raw value` AS raw_value,
CAST(`Confidence Interval Lower Bound` AS FLOAT64) AS ci_lower_bound,
CAST(`Confidence Interval Upper Bound` AS FLOAT64) AS ci_upper_bound
FROM `county-health-rankings-504715.county_health_rankings_and_roadmaps.county_health`
WHERE `State` != 'US'
AND `County code` != 0
AND `State` IS NOT NULL
AND `Measure name` IS NOT NULL
AND `Raw value` IS NOT NULL
AND `Year span` != '.';
The final query constructs a five-digit FIPS code by combining:
the two-digit state code; and
the three-digit county code.
CONCAT(
LPAD(CAST(`State code` AS STRING), 2, '0'),
LPAD(CAST(`County code` AS STRING), 3, '0')
)
This approach preserves leading zeros. For example, a code such as 01001 remains five digits rather than being interpreted as 1001.
Maintaining the full five-digit identifier was important for consistent county-level geographic mapping in Tableau.
The query excludes:
State != 'US'
to remove national records and:
County code != 0
to remove state-level aggregate rows.
This keeps the analytical table focused on county-level observations and prevents national or state summaries from being mixed with county records.
Records were excluded when:
State was missing;
Measure name was missing;
Raw value was missing; or
Year span contained . rather than a usable year value.
SELECT DISTINCT was used to remove exact duplicate records from the resulting table.
After cleaning, the table was exported as a CSV and imported into Tableau Public for dashboard development.
The cleaned dataset remains in long format. A single Raw Value field can therefore represent measures with very different meanings and units.
Examples include:
percentages;
rates per 100,000;
preventable hospital stays per 1,000 enrollees; and
daily fine particulate matter in μg/m³.
To support interactive measure selection while preserving appropriate display formats, I created Tableau parameters and calculated fields that route values according to the metric selected by the user.
Three string-list parameters control the dashboard.
Role: Discrete Measure
Type: Parameter — String List
Default Value: Adult obesity
Status: Valid
This parameter controls the primary health measure shown on the geographic map, ranked county views, and timeline.
Role: Discrete Measure
Type: Parameter — String List
Default Value: Adult obesity
Status: Valid
This parameter independently controls the X-axis measure used in the scatter plot.
Role: Discrete Measure
Type: Parameter — String List
Default Value: Physical inactivity
Status: Valid
This parameter independently controls the Y-axis measure used in the scatter plot.
The project documentation includes the following 12 measures:
Adult obesity
Children in poverty
Daily fine particulate matter
Diabetic screening
Mammography screening
Physical inactivity
Unemployment
Uninsured
Premature Death
Violent crime rate
Sexually transmitted infections
Preventable hospital stays
Unless otherwise noted, the calculated fields below were recorded in Tableau as:
Role: Continuous Measure
Type: Calculated Field
Default Aggregation: Sum
Status: Valid
Returns the raw value associated with the measure selected through the main health measure parameter.
IF [Measure Name] = [Select Health Measure] THEN [Raw Value] END
Returns values for the metric selected through the X-axis parameter.
IF [Measure Name] = [Select X-Axis Health Metric] THEN [Raw Value] END
Returns values for the metric selected through the Y-axis parameter.
IF [Measure Name] = [Select Y-Axis Health Metric] THEN [Raw Value] END
Extracts the first four characters of the Year Span field and converts them to an integer.
INT(LEFT([Year Span], 4))
This creates a year field that can be used consistently on the timeline even though the source data reports time using text-based year spans.
The timeline uses this field as a discrete year controller. Only available observations for the selected measure are displayed, with the line connecting the available data points.
Because the selected measures do not all use the same unit, additional calculated fields separate them into formatting groups.
This allows Tableau to display percentages, rates, and other values appropriately while still using parameter-driven measure selection.
Used for measures expressed as percentages:
Adult obesity
Children in poverty
Physical inactivity
Unemployment
Uninsured
Diabetic screening
Mammography screening
IF [Measure Name] = [Select Health Measure] AND
([Measure Name] = 'Adult obesity' OR
[Measure Name] = 'Children in poverty' OR
[Measure Name] = 'Physical inactivity' OR
[Measure Name] = 'Unemployment' OR
[Measure Name] = 'Uninsured' OR
[Measure Name] = 'Diabetic screening' OR
[Measure Name] = 'Mammography screening')
THEN [Raw Value]
END
Used for measures documented in this project as rates per 100,000:
Premature Death
Violent crime rate
Sexually transmitted infections
IF [Measure Name] = [Select Health Measure] AND
([Measure Name] = 'Premature Death' OR
[Measure Name] = 'Violent crime rate' OR
[Measure Name] = 'Sexually transmitted infections')
THEN [Raw Value]
END
Used for measures that require other units:
Preventable hospital stays — per 1,000 enrollees
Daily fine particulate matter — μg/m³
IF [Measure Name] = [Select Health Measure] AND
([Measure Name] = 'Preventable hospital stays' OR
[Measure Name] = 'Daily fine particulate matter')
THEN [Raw Value]
END
The same formatting logic is duplicated for the X and Y axes so that each axis can independently display a different type of measure.
For example, the X-axis can display a percentage while the Y-axis displays a rate per 100,000.
IF [Measure Name] = [Select X-Axis Health Metric] AND
([Measure Name] = 'Preventable hospital stays' OR
[Measure Name] = 'Daily fine particulate matter')
THEN [Raw Value]
END
IF [Measure Name] = [Select X-Axis Health Metric] AND
([Measure Name] = 'Adult obesity' OR
[Measure Name] = 'Children in poverty' OR
[Measure Name] = 'Physical inactivity' OR
[Measure Name] = 'Unemployment' OR
[Measure Name] = 'Uninsured' OR
[Measure Name] = 'Diabetic screening' OR
[Measure Name] = 'Mammography screening')
THEN [Raw Value]
END
IF [Measure Name] = [Select X-Axis Health Metric] AND
([Measure Name] = 'Premature Death' OR
[Measure Name] = 'Violent crime rate' OR
[Measure Name] = 'Sexually transmitted infections')
THEN [Raw Value]
END
IF [Measure Name] = [Select Y-Axis Health Metric] AND
([Measure Name] = 'Preventable hospital stays' OR
[Measure Name] = 'Daily fine particulate matter')
THEN [Raw Value]
END
IF [Measure Name] = [Select Y-Axis Health Metric] AND
([Measure Name] = 'Adult obesity' OR
[Measure Name] = 'Children in poverty' OR
[Measure Name] = 'Physical inactivity' OR
[Measure Name] = 'Unemployment' OR
[Measure Name] = 'Uninsured' OR
[Measure Name] = 'Diabetic screening' OR
[Measure Name] = 'Mammography screening')
THEN [Raw Value]
END
IF [Measure Name] = [Select Y-Axis Health Metric] AND
([Measure Name] = 'Premature Death' OR
[Measure Name] = 'Violent crime rate' OR
[Measure Name] = 'Sexually transmitted infections')
THEN [Raw Value]
END
The Tableau dashboard uses a structured grid/container layout to keep the major analytical views visible and allow users to move between geographic, ranked, historical, and correlation-based perspectives.
It contains four primary visualizations.
Provides a nationwide county-level view of the selected health measure and helps identify geographic clusters or regional differences.
The map uses the formatted five-digit FIPS code to support county-level geographic mapping.
Color intensity represents the value of the health measure currently selected through the Select Health Measure parameter.
This allows the same map to display different measures without requiring a separate worksheet for each metric.
Shows counties at the high and low ends of the selected health measure.
Interpretation depends on the direction of the measure.
For measures such as:
Adult obesity;
Children in poverty;
Unemployment; or
Uninsured,
a higher value generally represents a less favorable condition.
For screening measures such as:
Mammography screening; and
Diabetic screening,
a higher percentage represents greater screening and therefore should not automatically be interpreted as a worse result.
The ranked view uses a Tableau context filter:
NOT ISNULL([Map Color Master])
The filter removes records without a usable mapped value before the ranking calculation is applied.
This prevents null records from being included when Tableau determines the highest and lowest counties for the selected measure.
Displays how a selected county's reported health measure changes across the available historical observations.
This view can be used to examine whether a measure is improving, worsening, or remaining relatively stable over the periods represented in the source data.
The source reports time using the Year Span text field.
To create a usable timeline field, the dashboard extracts the first year:
Timeline Year Anchor = INT(LEFT([Year Span], 4))
The timeline uses this extracted year as a discrete field.
This allows observations from different year-span formats to be placed on a common year-based axis while retaining the available measurement points from the source data.
Allows users to compare two health measures across U.S. counties.
Examples include examining how:
Unemployment relates to Children in poverty; or
other behavioral, socioeconomic, environmental, clinical, and health outcome measures vary together.
The scatter plot is intended for exploratory comparison. An observed relationship should not be interpreted as evidence that one measure causes the other.
County and state fields are included on the Tableau Detail card, creating a county-level distribution of more than 3,000 observations when data are available for both selected measures.
Two independent parameters control the axes:
Select X-Axis Health Metric
Select Y-Axis Health Metric
The corresponding calculated fields retrieve the selected values from the same long-format dataset.
Separate percentage, rate-per-100,000, and other-unit calculations allow each axis to be formatted according to the type of measure selected.
The dashboard uses a global Tableau filter action so the visualizations work together rather than functioning as independent charts.
Selecting a county on the geographic map.
County Health Map.
Historical Time Series
Health Measure Correlation Scatter Plot
Selecting a county therefore updates the related views so users can move from the national geographic overview to information about a specific county.
This project was designed as an exploratory dashboard rather than an analysis built around one fixed research or business question.
Because users can change the:
county;
state;
primary health measure;
X-axis measure;
Y-axis measure; and
available historical period,
the dashboard can support many different comparisons.
Examples of questions it can help explore include:
Which counties report particularly high or low values for a selected health measure?
Are geographic clusters visible for a particular health condition or risk factor?
How has a selected measure changed over the available historical observations for one county?
How closely do two selected county-level measures appear to vary together?
Do socioeconomic or environmental measures show patterns alongside selected health outcomes?
The dashboard is intended to help identify patterns and areas for further investigation. It does not establish causal relationships between health measures.
The final cleaning query was reached through three iterations.
Keeping the earlier versions documents how the cleaning approach changed during development.
The first version excluded national totals, state-level aggregate records, missing state values, and records without a measure name.
It also retained Data Release Year.
-- Step 1: Create a clean table excluding US overall and State-level rollups
CREATE OR REPLACE TABLE `county_health_rankings_and_roadmaps.county_health_rankings_clean` AS
SELECT
State,
County,
CAST(`State code` AS INT64) AS state_code,
CAST(`County code` AS INT64) AS county_code,
LPAD(CAST(CAST(fipscode AS INT64) AS STRING), 5, '0') AS fipscode_formatted,
`Year span` AS year_span,
CAST(`Data Release Year` AS INT64) AS data_release_year,
`Measure name` AS measure_name,
CAST(`Measure id` AS INT64) AS measure_id,
Numerator,
Denominator,
`Raw value` AS raw_value,
CAST(`Confidence Interval Lower Bound` AS FLOAT64) AS ci_lower_bound,
CAST(`Confidence Interval Upper Bound` AS FLOAT64) AS ci_upper_bound
FROM `county-health-rankings-504715.county_health_rankings_and_roadmaps.county_health`
WHERE State != 'US'
AND `County code` != 0
AND State IS NOT NULL
AND `Measure name` IS NOT NULL;
The second version introduced:
SELECT DISTINCT;
standardized lowercase aliases;
removal of records with missing raw values; and
removal of malformed Year span values represented by ..
CREATE OR REPLACE TABLE `county-health-rankings-504715.county_health_rankings_and_roadmaps.county_health_rankings_clean` AS
SELECT DISTINCT
`State` AS state,
`County` AS county,
CAST(`State code` AS INT64) AS state_code,
CAST(`County code` AS INT64) AS county_code,
LPAD(CAST(CAST(`fipscode` AS INT64) AS STRING), 5, '0') AS fipscode_formatted,
`Year span` AS year_span,
`Measure name` AS measure_name,
CAST(`Measure id` AS INT64) AS measure_id,
`Numerator` AS numerator,
`Denominator` AS denominator,
`Raw value` AS raw_value,
CAST(`Confidence Interval Lower Bound` AS FLOAT64) AS ci_lower_bound,
CAST(`Confidence Interval Upper Bound` AS FLOAT64) AS ci_upper_bound
FROM `county-health-rankings-504715.county_health_rankings_and_roadmaps.county_health`
WHERE `State` != 'US'
AND `County code` != 0
AND `State` IS NOT NULL
AND `Measure name` IS NOT NULL
AND `Raw value` IS NOT NULL
AND `Year span` != '.';
The final revision changed the FIPS construction method.
Instead of padding the existing FIPS value after casting, it explicitly concatenates a two-digit state code and three-digit county code.
CREATE OR REPLACE TABLE `county-health-rankings-504715.county_health_rankings_and_roadmaps.county_health_rankings_clean` AS
SELECT DISTINCT
`State` AS state,
`County` AS county,
CAST(`State code` AS INT64) AS state_code,
CAST(`County code` AS INT64) AS county_code,
-- Concatenates 2-digit state code + 3-digit county code to guarantee 100% complete 5-digit FIPS
CONCAT(
LPAD(CAST(`State code` AS STRING), 2, '0'),
LPAD(CAST(`County code` AS STRING), 3, '0')
) AS fipscode_formatted,
`Year span` AS year_span,
`Measure name` AS measure_name,
CAST(`Measure id` AS INT64) AS measure_id,
`Numerator` AS numerator,
`Denominator` AS denominator,
`Raw value` AS raw_value,
CAST(`Confidence Interval Lower Bound` AS FLOAT64) AS ci_lower_bound,
CAST(`Confidence Interval Upper Bound` AS FLOAT64) AS ci_upper_bound
FROM `county-health-rankings-504715.county_health_rankings_and_roadmaps.county_health`
WHERE `State` != 'US'
AND `County code` != 0
AND `State` IS NOT NULL
AND `Measure name` IS NOT NULL
AND `Raw value` IS NOT NULL
AND `Year span` != '.';
This became the final cleaned table used for the Tableau workflow.