CUSP DATA MODEL
Child Universal Success Platform (CUSP) ingests, consolidates, integrates, and processes data on children, providers, and funding programs from disparate data sources to produce a single holistic view of the entire early childhood landscape in the state.
Child Population
A geo-referenced child-level dataset of all children in a state, including child age, household income, household employment status, and address.
Eligible Children
A geo-referenced child-level dataset of all children eligible for key funding programs available in the state (e.g., state subsidy, Head Start/Early Head Start, public PreK, etc.).
Served Children
A geo-referenced child-level dataset of all children who are served by early childhood providers and child care funding programs.
Provider Base
A geo-referenced provider-level table of all early childhood providers and programs in the state, deduplicated by location and funding program, and organized by key characteristics such as provider type, capacity, quality rating (if available), etc.
Geographic Location
All children and providers are geo-coded to addresses within the state. This allows for children and providers to be mapped in various ways, e.g., by census tract, ZCTA, town, county, legislative district, school district, etc.
GET DATA
Need data directly from CUSP? Here is a list of frequently requested data queries. Copy the query to your clipboard using ‘Copy’ button and paste it in your query editor. Run the query and get results in the form of output tables.
Need the query modified? Modify it directly in the query editor and run it.
Don’t see what you need on the list? Go directly to the database and write your own query or, ask 3Si and we will get you the data you need.
POPULATION
For each house legislative district, give me the population count of children 0 through 13.
select legislative_district_lower, count(*) as child_cts from output.children as A
left join output.boundaries as B on B.address_uid = A.child_address_uid
where load_dt = (select max(load_dt) from output.children)
and boundary_yr = (select max(boundary_yr) from output.boundaries)
group by legislative_district_lower
order by cast(legislative_district_lower as int)
For each senate legislative district, give me the population count of children 0 through 13.
select legislative_district_upper, count(*) as child_cts from output.children as A
left join output.boundaries as B on B.address_uid = A.child_address_uid
where load_dt = (select max(load_dt) from output.children)
and boundary_yr = (select max(boundary_yr) from output.boundaries)
group by legislative_district_upper
order by cast(legislative_district_upper as int)
providers
List all active licensed providers in the state, by county.
select county, provider_name from output.providers as A
left join output.boundaries as B on B.address_uid = A.provider_address_uid
where load_dt = (select max(load_dt) from output.children)
and boundary_yr = (select max(boundary_yr) from output.boundaries)
and provider_licensing_id is not null
and county is not null
and substring(provider_licensing_id, 1,2) not in ('UN','EX')
order by county
List all active child care learning centers in the state, by county.
select county, provider_name from output.providers as A
left join output.boundaries as B on B.address_uid = A.provider_address_uid
where load_dt = (select max(load_dt) from output.children)
and boundary_yr = (select max(boundary_yr) from output.boundaries)
and provider_licensing_id is not null
and county is not null
and provider_facility_type in ('Child Care Learning Center', 'Center')
order by county
List all active family child care learning homes in the state, by county.
select county, provider_name from output.providers as A
left join output.boundaries as B on B.address_uid = A.provider_address_uid
where load_dt = (select max(load_dt) from output.children)
and boundary_yr = (select max(boundary_yr) from output.boundaries)
and provider_licensing_id is not null
and county is not null
and provider_facility_type in ('Child Care Learning Home’)
order by county
List all providers in the state with a quality rating of 3, by county.
select county,provider_name from custom_table.base_table as A
left join output.providers as C on A.provider_uid = C.provider_uid and A.load_dt=C.load_dt
left join output.boundaries as B on B.address_uid = C.provider_address_uid
where A.load_dt = (select max(load_dt) from output.children)
and boundary_yr = (select max(boundary_yr) from output.boundaries)
and county is not null
and QR_Rating = 3
order by county
SERVICE
What is the count and percentage of children served by GA Pre-K that are eligible for (but not served by) CAPS?
with prek_children as (select load_dt, count(*) as prek_ct from output.children
where program_model_name='prek' group by load_dt)
, caps_prek as (select load_dt, count(*) as caps_eligible_prek from output.children
where program_model_name='prek' and granular_income_smi <= 50 and unemployment_code=0 group by load_dt)
select A.load_dt, caps_eligible_prek, prek_ct, caps_eligible_prek*1.0/prek_ct as percent_of from prek_children as A
left join caps_prek as B on A.load_dt = B.load_dt
where A.load_dt = (select max(load_dt) from output.children)
GAPS
Give me a list of the top 10 counties that have the highest service gaps in the state.
with child_tb as (select *
, case when (granular_income_fpl <= 100 and age_group != 'School Age') then 1
when age_group = 'Preschool_4yr' then 1
when (granular_income_smi <= 50 and unemployment_code =0) then 1
else 0 end as subsidy_eligible
, case when program_model_name not in ( 'unserved', 'pp') then 1 else 0 end as subsidy_served
from output.children as A
left join (select address_uid, boundary_yr, county from output.boundaries) as B on B.address_uid = A.child_address_uid
where load_dt = (select max(load_dt) from output.children)
and boundary_yr = (select max(boundary_yr) from output.boundaries))
, math_tb as ( select county, sum(subsidy_served) as served_pop, sum(subsidy_eligible) as elig_pop
, (sum(subsidy_served)*1.0/sum(subsidy_eligible)) as percent_served_of_eligible
from child_tb
group by county)
select top 10 county from math_tb
where county is not null
order by percent_served_of_eligible asc