I started by running requirements gathering with 10+ staff — stakeholder interviews and process mapping — to map how data, enrollment, and marketing actually flowed through the organization, and where it broke. That surfaced two connected problems: operational data was scattered across disconnected spreadsheets, and marketing had no reliable line of sight into what was actually driving enrollment. The build addresses both as one system.

Operational, marketing, and CRM data land in BigQuery as a single source of truth. Validated enrollments don't just sit there — they flow back out to the ad platforms as offline conversions, so acquisition spend is optimized against real enrollments, not just form fills.

The operational core is a 5-base relational architecture in Airtable, with ERD documentation, metadata definitions, and validation rules that keep data clean at the source. Attendance is the junction table, linking students to both programs and events.

• Students — core records: personal info, school details, medical/dietary notes, guardian contacts
• Attendance — junction table linking students to programs and events with check-in/out times
• Programs — recurring activities with capacity, scheduling, and resource tracking
• Staff — full-time, volunteers, and student leaders with certifications, availability, and assignments
• Events — community events with budget, vendor, sponsor, and marketing sub-tables

I rebuilt tracking on GA4 and Google Tag Manager with a clean event and conversion taxonomy, then closed the loop with offline conversion tracking so ad platforms optimize against outcomes that are actually real.