This demo was originally created by G. Hussain Chinoy and reworked to fit a housing use case.

Local governments are responsible for providing assistance to those in need but often lack the resources to do so at scale. As a result, many people who are eligible for assistance programs do not receive them quickly enough. One way to address this problem is to use data analytics to identify those who are most likely to need assistance and then target them with outreach efforts.

Using housing as an example, we will demonstrate step-by-step how to use BigQuery to understand our applicant population, then use Generative AI in Vertex AI to cater assistance programs to the right people.

Segment our population into cohorts

First, we will use ML and public datasets in BigQuery to segment different cohorts of underserved populations. The following SQL query creates a k-means model that will group county populations in the United States into 3 general clusters. You can copy and paste the query directly into the BigQuery UI.

CREATE OR REPLACE MODEL `<dataset-name>.housing_pool`
OPTIONS
 ( MODEL_TYPE='KMEANS',
   NUM_CLUSTERS=3 ) AS
  
WITH
 acs_2017 AS (
 SELECT
   geo_id,
   rent_over_50_percent + rent_40_to_50_percent + rent_35_to_40_percent AS rent_over_35_percent,
   housing_units_renter_occupied AS rental_units,
   commuters_by_public_transportation/total_pop AS public_transit,
   vacant_housing_units/total_pop AS vacant_units,
   families_with_young_children/total_pop AS young_families,
 FROM
   `bigquery-public-data.census_bureau_acs.county_2017_5yr`
   WHERE housing_units_renter_occupied <> 0),


LIHTC_Units AS (
 SELECT
   SUM(n_units) AS total_LIHTC_units,
   SUBSTR (fips2010,0,5) AS shortFIPS
 FROM
   `bigquery-public-data.sdoh_hud_housing.2017_lihtc_database_hud`
 WHERE n_units <> 0
 GROUP BY shortFIPS ),
 join_tbl AS (
 SELECT
   ML.MIN_MAX_SCALER(ROUND((rent_over_35_percent/rental_units)/(total_LIHTC_units/rental_units),3)) OVER() AS rent_burden,
   ML.MIN_MAX_SCALER(ROUND(public_transit/(total_LIHTC_units/rental_units),3)) OVER() AS public_transit,
   ML.MIN_MAX_SCALER(ROUND(vacant_units/(total_LIHTC_units/rental_units),3)) OVER() AS vacant_units,
   ML.MIN_MAX_SCALER(ROUND(young_families/(total_LIHTC_units/rental_units),3)) OVER() AS young_families
 FROM acs_2017
 JOIN LIHTC_Units ON acs_2017.geo_id = LIHTC_Units.shortFIPS )


SELECT * FROM join_tbl

Here are some details about the query:

• It joins data from the Census Bureau’s American Community Survey (ACS) and from the Department of Housing and Urban Development (HUD) Low-Income Housing Tax Credit (LIHTC) program
• We look at the population who pay over 35% of their income towards rent
• Other inputs include the population that commutes using public transportation, vacant housing units, and families with young children
• The inputs are normalized based on the total population
• We further factor total_LIHTC_units into our features in order to heavily weigh populations that have less LIHTC coverage
• Finally, we use MIN_MAX_SCALER as a helpful ML preprocessing function to scale our units between 0 and 1

After a minute of running, the query to create the model should be done. Click Go To Model, and then click the Evaluation tab to see numeric breakdowns of our clusters.

You can now use this model to see which cluster your county belongs to (here is a helpful reference from the Federal Communications Commission).

Understand our results and get recommendations

When looking at the blend of socioeconomics for each cohort, it may be difficult to find a concise way to describe each of them. This only gets harder as we increase the number of features. Generative AI can assist in synthesizing the main themes of our data and presenting it back in a way that can “prompt” follow up action.

Save the image above of the evaluation breakdown. We’ll use it in our prompt to the Gemini Multimodal model in Vertex AI: inside your project, click “Insert Media” to insert the image, then paste the following prompt:

{Mission}
You help government officials market housing 
assistance programs. You customize assistance 
programs to the needs of specific populations.

{Instructions}
Given the following population clusters and their 
socioeconomic makeup shown in the picture, explain 
if the cluster is suitable for housing assistance. 
Then for each of the clusters suitable for 
assistance, come up with a brand persona and 
campaign title based on their distinct attributes, 
and explain what would be the appropriate next 
step for each of those clusters.

The parameters used were:

   Temperature: 0.1,
   Token limit: 1389,
   Top-K: 33,
   Top-P: 0.77

After pressing “Submit,” the result may be similar to this:

Cluster 1: Young Families with Low Rent Burden
  - Suitable for assistance: No
  - The rent burden is very low, indicating that housing 
    costs are not a significant challenge for this cluster. 
  - The public transit usage is also low, suggesting that 
    they may have access to other means of transportation. 
  - The vacancy rate is relatively high, which means there 
    may be sufficient housing options available without 
    the need for assistance.

Cluster 2: Young Families with Moderate Rent Burden
  - Suitable for assistance: Yes
  - The rent burden is higher than Cluster 1, indicating 
    that housing costs may be a challenge for some 
    families in this cluster.
  - The public transit usage is slightly higher, 
    suggesting that they may rely on public transportation 
    for commuting.
  - The vacancy rate is higher than Cluster 1, but still 
    relatively low, indicating that there may be some 
    competition for affordable housing.

Brand persona: Struggling Young Family 
Title label: "Helping Young Families Thrive: Affordable 
    Housing Solutions" 
Next step: Provide information about affordable housing 
    options, rental assistance programs, and financial 
    counseling services.

Cluster 3: Young Families with Moderate Rent Burden and 
    High Public Transit Usage
  - Suitable for assistance: Yes
  - The rent burden is similar to Cluster 2, but the 
    public transit usage is significantly higher, 
    indicating that they heavily rely on public 
    transportation for commuting.
  - The vacancy rate is lower than Cluster 2, suggesting 
    that there may be a shortage of affordable housing 
    options in this area.

Brand persona: Commuting Young Family 
Title label: "Affordable Housing for Commuters: Making 
    Transit-Oriented Living a Reality" 
Next step: Provide information about affordable housing 
    options near public transit hubs, transportation 
    subsidies, and car-sharing programs.

This response helps us to de-prioritize cohorts that align with cluster 1 while shoring up the specific needs of clusters 2 and 3. Feel free to dial the temperature and other parameters as you see fit and provide more agency-specific guidelines on scoring applicant need.

Next steps

This introductory level use case provides a blueprint for building your own models using these queries and prompts as an initial template. Where you can go from here:

• The ACS dataset can get more granular, up to the block group and census tract level, but providing your agency’s historical applicant data would have the greatest impact on a model’s utility
• BigQuery and Vertex AI provide many more prediction services, such as creating a recommendation engine that could assist applicants with the best resources or analyzing sentiment on tickets or survey responses to track emerging areas of concern

See you in the neighborhood: using Generative AI to support housing assistance programs was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

I’ve been having too much fun mashing together whatever public datasets I can get my hands on, with BigQuery being my one-stop shop for super quick analytics, machine learning, and GIS visualization.

Putting on my citizen data scientist hat, the hypothesis I wanted to test was: “Does higher rent lead to more housing code violations?”

Here, I used rent as a proxy for cost of living, where a higher cost of living may overburden owners and prevent them from keeping their properties up to code. I focused my initial study area to Montgomery County, MD in the DC suburbs. Here were the steps I took:

1. Prep and load the data (boring but Dataprep makes it fun)

Rental info here and code violation data here. Just export and download the CSVs.

Now set up your GCP environment: go to the console and create a project with a billing account if you haven’t already (we should be well under the $300 in free trial credits). You may be enabling APIs along the way.

We’re actually going to clean our data in Dataprep first. Find Dataprep on the console left-side menu and click through the agreements.

Let’s start by wrangling the DHCA Rental Survey data. Import the CSV from your local machine or Cloud Storage, then create a new recipe.

Here’s the recipe I used for the DHCA Rental Survey, essentially extracting the zip code, converting it into a string, and deleting missing rows:

extractpositions col: {Facility Address} type: last count: 6
rename type: manual mapping: [{right_Facility Address},'zipcode']
textformat col: zipcode type: trimwhitespace
textformat col: {Facility Name} type: trimwhitespace
textformat col: {Facility Name} type: trimquotes
filter type: custom rowType: single row: ISMISMATCHED(zipcode, ['Zipcode']) action: Delete
drop col: {Facility Address} action: Drop
drop col: {Average Rent  2015} action: Drop
drop col: {Average Rent  2016} action: Drop
drop col: {Average Rent  2017} action: Drop
drop col: {Average Rent  2018} action: Drop
drop col: {Bedroom Types} action: Drop
textformat col: {Facility Name} type: trimwhitespace
settype col: zipcode type: String

Click “Run Job” and specify the following settings to keep it simple: “no compression, single file, with headers, with quotes, with delimiter: ,”.

You can actually look at the Dataflow (Apache Beam) job if you’re curious about the mapreduce steps.

Follow the same steps for the Code Violations CSV. Wrangle recipe below. MoCo will acknowledge its violations data is a little messy, so I deleted a lot of columns where there were missing values.

drop col: Latitude action: Drop
drop col: Longitude action: Drop
drop col: {:@computed_region_a9cs_3ed7} action: Drop
drop col: {:@computed_region_r648_kzwt} action: Drop
settype col: {Zip Code} type: String
drop col: {Date Closed} action: Drop
drop col: {Zip Codes} action: Drop
drop col: {Council Districts} action: Drop
drop col: Councils action: Drop
drop col: Communities action: Drop
drop col: Municipalities action: Drop
drop col: {Unit Number} action: Drop
drop col: {Service Request Number} action: Drop
drop col: {Service Request Created Date} action: Drop
drop col: {Service Request Closed Time} action: Drop
drop col: {Service Request Status} action: Drop
drop col: Corrected action: Drop
drop col: {Inspection Date} action: Drop
drop col: {Location Description} action: Drop
drop col: {Code Reference} action: Drop
drop col: Disposition action: Drop
drop col: Location action: Drop
drop col: {Date Assigned}: Drop

Now for the main attraction: from the console, go to BigQuery to start loading our tables.

Create a dataset where you’ll store tables. I’m calling mine “moco_rent_violations.”

Then create a new table.

I’ll name this table “code_violations” and find the source CSV from the Dataprep job output in Google Cloud Storage stored in bucket:

gs://dataprep-staging-*/<USER>/jobrun/

Instead of auto detecting the schema, I’ll actually be prescriptive and “Edit as text,” making all my fields type STRING:

Then click “Create table”! You can see successful (or unsuccessful) job loads in Job history.

We’ll similarly load our DHCA Rental Survey table and call it “rental”. Schema below:

Yay! All our tables are loaded.

2. Instead of nested joins, create lots of views

I’m going to now join these tables on zip code and selecting number of violations and the associated rent difference in that zip code. Enter the following in the BigQuery Query Editor:

SELECT 
zipcode, 
count(DISTINCT Violation_ID) as violations,
AVG(Average_Rent_Percent_Difference_From_Previous_Year) as rent_diff 
FROM 
`<PROJECT-ID>.moco_rent_violations.code_violations`
JOIN
`<PROJECT-ID>.moco_rent_violations.rental`
ON
Zip_Code = zipcode
GROUP BY
zipcode

Now to get ready for the next step of visualization, I want to join GIS polygons in this query (with the ZCTA dataset in GCP marketplace). If you haven’t gathered from my Dataprep run-through, I really don’t like long code, so if I can save myself typing a couple nested joins then that’s a win.

I can do this by saving these aggregated query results as a “View” and essentially interact with it as a table, but should the underlying data from the original tables change, the View would surface these updates in its aggregation.

Now I can join this violations_rental view with zip code polygons:

SELECT 
violations_rental.zipcode,
violations,
rent_diff,
zip_area.zcta_geom AS zipcode_polygon,
ST_GeogPoint(int_point_lon,int_point_lat) AS lat_lon
FROM 
`<PROJECT-ID>.moco_rent_violations.violations_rental` AS violations_rental
JOIN
`bigquery-public-data.geo_us_boundaries.us_zip_codes` AS zip_area
ON
  zip_area.zip_code = violations_rental.zipcode
WHERE
  county LIKE '%Montgomery%'
  AND state_name = 'Maryland'
ORDER BY
rent_diff DESC

Save the results as a View “violations_rental_zip.”

3. Visualize the situation today

Follow the instructions from the ZCTA dataset to get your project whitelisted to start using BigQuery Geo Viz.

Once whitelisted, paste the last query above in “1. Select data” then press “Run.” After processing, click “See results” to move to “2. Define columns,” and select “lat_lon” as the Geometry column.

Click “Add styles” to move to “3. Style.”

As an example, for fillColor, we’ll color as a Data-driven Function “linear” on Field “rent_diff.” You can then toggle the levels of coloring based on rent difference percentage.

For circleRadius, we’ll have a Data-driven Function “linear” on Field “violations” and set the circle radius levels.

If successful, we should have a circle plot of rent increases based on color and size based on number of violations.

4. Create, evaluate, and predict with ML

On first look, there doesn’t seem to be an overt correlation. But we can quantify correlation or lack thereof with linear regression. So let’s build an ML model using SQL! It’s easy.

DISCLAIMER: this is for demonstration purposes, as the number of samples (28) is too aggregated and ideally we would have data more local than zip code. For the model, I found zip codes 20905 (Colesville) and 20871 (Clarksburg) to be outliers based on rent percentages so we’ll exclude them. This is the “query” to create our ML linear regression model:

CREATE OR REPLACE MODEL `moco_rent_violations.rent_violations_ML`
OPTIONS(model_type='linear_reg') AS
SELECT 
violations as label, 
rent_diff 
FROM `<PROJECT-ID>.moco_rent_violations.violations_rental_zip`
WHERE
zipcode != '20905' 
AND zipcode != '20871'

In less than a minute, we’ll have a SQL table that is actually an ML model.

How good is it? Let’s evaluate using this query:

WITH eval_table AS (
SELECT violations as label, 
rent_diff 
FROM `<PROJECT-ID>.moco_rent_violations.violations_rental`)
SELECT
*
FROM
  ML.EVALUATE(MODEL moco_rent_violations.rent_violations_ML,
    TABLE eval_table)

Seems all over. We could probably delete violations outliers as well to decrease mean_absolute_error, but we’d really have too few data. We’ll continue though for demonstration.

Let’s do a hypothetical prediction with this model! We’ll SELECT zip codes with their rent_diff numbers and use that to predict the hypothetical number of code violations for a given rent increase.

WITH pred_table AS (
SELECT 
ST_GeogFromText(zipcode_polygon) as zip_polygon,
rent_diff as rent_diff 
FROM `<PROJECT-ID>.moco_rent_violations.violations_rental_zip`
Where
zipcode != '20905'
and zipcode != '20871')
SELECT
*
FROM
  ML.PREDICT(MODEL moco_rent_violations.rent_violations_ML,
    TABLE pred_table)
Order by rent_diff asc

Alrighty then. But let’s go one step further and visualize this. As in, visualize this ML model. Paste this query into BigQuery Geo Viz, except this time, make the Geometry column “zip_polygon.”

Click on one of the zip codes. In the example below, the model is saying for a 1.8% increase in rent, we can expect around 6,500 code violations.

Conclusion: there are probably a lot of other factors at play, but this is a super easy, fast, and economical first step to visually gain an awareness. This entire process has run me up 25 cents in expenses including failures and missteps. None of this requires a PhD or computer science background, and given more data, we can create a full Tensorflow model using AutoML tables, which is literally point-and-click. Until next time.