Spaces:

CommunityOne
/

open-navigator

Running on CPU Upgrade

python scripts/enrich_nonprofits_bigquery.py \
    --input data/gold/nonprofits_tuscaloosa_form990.parquet \
    --export-sql scripts/bigquery_tuscaloosa_missions.sql

Step 2: Run Query in BigQuery

Go to https://console.cloud.google.com/bigquery
Click "COMPOSE NEW QUERY"
Paste SQL from scripts/bigquery_tuscaloosa_missions.sql
Click "RUN"
Wait for results (~200-400 rows expected)

Step 3: Export Results

Click "SAVE RESULTS" → "CSV (local file)"
Save as: data/cache/bigquery_results.csv

Step 4: Merge into Gold Data

python scripts/enrich_nonprofits_bigquery.py \
    --input data/gold/nonprofits_tuscaloosa_form990.parquet \
    --from-csv data/cache/bigquery_results.csv \
    --update-in-place

Option 2: Direct Query (Requires gcloud Auth)

Setup (one-time):

# Install gcloud CLI
curl https://sdk.cloud.google.com | bash
exec -l $SHELL

# Authenticate
gcloud auth application-default login

Run:

python scripts/enrich_nonprofits_bigquery.py \
    --input data/gold/nonprofits_tuscaloosa_form990.parquet \
    --output data/gold/nonprofits_tuscaloosa_bigquery.parquet \
    --project YOUR_PROJECT_ID

Data Schema

New Fields Added

Field	Type	Description	Coverage
`bigquery_mission`	string	Activity or mission description from Form 990	~30-40%
`bigquery_website`	string	Website URL from Form 990	~30-40%
`bigquery_tax_year`	string	Tax year of the filing	~30-40%
`bigquery_form_type`	string	Form type: "990" or "990-EZ"	~30-40%
`bigquery_updated_date`	string	Date when BigQuery data was added (YYYY-MM-DD)	100%

Data Sources Queried

The script queries across multiple IRS 990 tables:

bigquery-public-data.irs_990.irs_990_2023 (Full Form 990)
bigquery-public-data.irs_990.irs_990_2022 (Full Form 990)
bigquery-public-data.irs_990.irs_990_2021 (Full Form 990)
bigquery-public-data.irs_990.irs_990_ez_2023 (990-EZ for smaller orgs)
bigquery-public-data.irs_990.irs_990_ez_2022 (990-EZ for smaller orgs)
bigquery-public-data.irs_990.irs_990_ez_2021 (990-EZ for smaller orgs)

Deduplication: Prefers most recent year, then Full 990 over 990-EZ.

Combined Data Coverage

After enrichment with both GivingTuesday and BigQuery:

For Tuscaloosa (921 nonprofits)

Missions:

EO-BMF: 0 (0%)
GivingTuesday: ~299 (32.5%)
BigQuery: ~200-400 (30-40%)
Combined: ~400-500 (40-50%) ✅

Websites:

EO-BMF: 0 (0%)
GivingTuesday: 0 (0%)
BigQuery: ~200-400 (30-40%)
Combined: ~200-400 (30-40%) ✅

Financials:

GivingTuesday: 307 orgs with revenue/expenses/assets (33.3%)
BigQuery: Same data, different source

Best Practices

When to Use BigQuery vs GivingTuesday

Data Need	Best Source
Mission statements	Both (GivingTuesday + BigQuery for coverage)
Website URLs	BigQuery (GivingTuesday doesn't extract this)
Detailed financials	GivingTuesday Data Lake (XML parsing)
Grants paid	GivingTuesday Data Lake
Executive compensation	BigQuery (irs_990_schedule_j_YYYY)
Related organizations	BigQuery (irs_990_schedule_r_YYYY)

Update Frequency

Re-run BigQuery enrichment:

Annually after IRS releases new Form 990 data (typically June/July)
When expanding to new jurisdictions
After major nonprofit landscape changes

Data Cleaning

Mission statements from BigQuery may contain XML artifacts:

import re

# Remove XML tags
mission = re.sub(r'<[^>]+>', ' ', mission)

# Clean whitespace
mission = re.sub(r'\s+', ' ', mission).strip()

Cost

FREE when using:

Public BigQuery datasets via web UI
Within Google Cloud's 1TB free tier per month

Typical query cost: $0 (Tuscaloosa query ~10 MB)

Troubleshooting

"No results returned"

EINs may not have filed 990 in queried years
Check if organizations are too small (< $50K revenue exempts from 990)
Try expanding --years to include more historical data

"CSV column names don't match"

BigQuery exports use lowercase column names. The script handles this automatically.

"Existing BigQuery columns found"

The script automatically drops and replaces existing BigQuery columns when using --update-in-place.

Examples

Full Alabama health nonprofits:

# 1. Export SQL
python scripts/enrich_nonprofits_bigquery.py \
    --input data/gold/nonprofits_organizations.parquet \
    --export-sql scripts/bigquery_alabama_health.sql \
    --states AL --ntee E

# 2. Run in BigQuery web UI, export CSV

# 3. Merge
python scripts/enrich_nonprofits_bigquery.py \
    --input data/gold/nonprofits_organizations.parquet \
    --from-csv data/cache/bigquery_alabama_health.csv \
    --update-in-place

Sample 100 orgs for testing:

python scripts/enrich_nonprofits_bigquery.py \
    --input data/gold/nonprofits_tuscaloosa_form990.parquet \
    --export-sql scripts/bigquery_sample.sql \
    --sample 100

BigQuery Nonprofit Enrichment

Overview

Workflow

Option 1: Web UI (No Authentication Required) ✅ RECOMMENDED