Schema and reusable cleanup
I inspected the CrimeData schema, then created cleaned_incidents to standardize field names and expose the start and end values as usable date-time fields without repeating cleanup logic.
Personal project · Public data + SQL · October — November 2025
Campus Incident Explorer
An independent dashboard that converts Boise State’s public crime log into searchable records, trend charts, filters, and an approximate campus heatmap, with a companion BigQuery analysis.
- Role
- Design + engineering
- Timeline
- October — November 2025
- Stack
- Next.js · TypeScript · Cheerio · Chart.js · MapLibre GL · Google BigQuery · GoogleSQL · GitHub Actions
- Metrics
- 197-record BigQuery analysis · 12-hour refresh · Search + CSV export
Overview
Campus Incident Explorer pairs a responsive Next.js dashboard built from scraped public records with a reproducible Google BigQuery and GoogleSQL analysis of a 197-record snapshot.
The source log is authoritative but difficult to scan and compare. The project preserves the published fields while adding search, sorting, filtering, pagination, aggregation, and export.
What I built
Built the Cheerio scraper, data-cleaning path, derived categorization, and Next.js dashboard.
Implemented search, sorting, filtering, pagination, charts, CSV export, a MapLibre campus heatmap, mobile layouts, and reduced-motion behavior.
Imported a 197-record snapshot into Google BigQuery and wrote reusable GoogleSQL views and queries for cleanup, monthly aggregation, duration calculations, and category joins.
Key choices
Kept the official public log as the source of truth and commits new JSON only when the scraped dataset changes.
Kept the public dashboard on a static dataset to reduce deployment complexity, while using BigQuery as a separate, reproducible analysis workflow rather than implying that it powers the live site.
Labels the project as unofficial and documents that categories are derived, coordinates are approximate, and updates can lag the source.
Outcomes
Refreshes the public dataset every 12 hours with GitHub Actions and avoids empty commits when nothing changed.
Makes source records easier to inspect through a searchable table, derived views, charts, and filtered CSV downloads.
Demonstrates web scraping, scheduled automation, visualization, responsive design, and SQL analysis with schemas, reusable views, aggregations, joins, date handling, and calculated fields.
BigQuery analysis
A focused BigQuery workflow for a 197-record dataset.
As a companion to the dashboard, I imported a 197-record snapshot of the public incident log into Google BigQuery for a small, reproducible analysis exercise. The GoogleSQL work covers aggregations, joins, date handling, calculated fields, and exploratory analysis; it is not presented as production-scale data warehousing.
- Rows imported
- 197
- Source fields
- 7
- Reusable views
- 2
Monthly summary view
I created monthly_incident_summary as a reusable view and used DATE_TRUNC, COUNT, grouping, ordering, and other date handling to analyze reported incidents by month.
Joins and calculated fields
Additional GoogleSQL queries calculated incident duration in minutes and joined incidents to a keyword lookup table for broader category analysis, using distinct case counts to limit duplicate matches.
The imported CrimeData table contains seven nullable date, timestamp, and string fields from the public source data.
A completed GoogleSQL query groups the 197 imported records by reported month with DATE_TRUNC and COUNT.