Public affairs & narrative monitoring
Automating civic intelligence.
Built a custom Apps Script and Gemini pipeline to collect Reddit posts and news alerts, classify sentiment and risk, and support a nonprofit’s field operations.
The challenge
A local conversation could matter to field operations.
A nonprofit operating community programs across multiple cities needed to follow public sentiment, civic narratives, and potential operational risks across different cities. The relevant conversations were scattered across news coverage, Google Alerts, and local Reddit threads. Checking each source by hand made it easy to lose track of an early signal.
How I built it
One place to collect and assess incoming mentions.
I built a Google Apps Script pipeline to bring RSS feeds, targeted Reddit communities, and Google Alerts into a master Google Sheet. The Gemini API analyzed incoming mentions and classified sentiment, organizational category, and risk level.
- Collect posts and news alerts in a shared data source.
- Classify sentiment as positive, neutral, or negative, and categorize mentions across PR, Operations, Community, and Safety.
- Feed a live Looker Studio risk dashboard and send HTML email alerts to leadership when mentions were classified as high risk.
The dashboard
Follow the local picture.
A Looker Studio dashboard brought the classified mentions together so the team could explore the narratives around field operations.
The alert
Bring a flagged mention to leadership.
Automated HTML emails surfaced mentions classified as high risk, with the underlying source available for context.