RM Analytics

01

The Problem

Radio Milwaukee runs four brands. Add the website, the social accounts, the email list, donations, ratings and the finance spreadsheets, and in any given week the numbers live in about eight different places, each with its own login and its own idea of what counts as a listener. So when a director asked a normal question, the honest answer was give me a day. How is HYFIN's audience trending compared to last year. Did the spring campaign move anything. Which newsletters actually get opened. Every one of those meant pulling a spreadsheet out of one tool, pulling another out of a second tool, and lining them up by hand. The numbers existed. Getting an answer out of them didn't, and the questions worth asking were exactly the ones that needed two sources at once.

02

The AI Design Decision

One decision did more work than all the others. Every source lands exactly the way it arrives, untouched.

The six services this pulls from all disagree about what to call things, how to write dates, and what counts as one listen. Forcing them into one tidy shape on the way in means that every time a vendor changes something, something breaks, and you never trust the numbers again. So each one gets its own area that mirrors it, and they only get joined together in a separate layer you can rebuild without touching the originals.

Two more calls I'd defend. I chose how finely to store each number on purpose, instead of storing everything at the smallest possible size just in case. Audience reach doesn't add up: you can't add Monday's to Tuesday's and get a real answer, so it's kept at exactly the sizes we report at.

And the ratings report is loaded by a person uploading a file, on purpose. There's no automatic connection to maintain and no password to rotate, because for a confidential report that arrives once a month, a human pressing a button is the right amount of technology.

What I didn't build first is the AI. The chat is the last ten percent that makes the other ninety usable, and starting with the shiny part would have given me a demo sitting on nothing.

03

The Architecture

1. 01Reports arrive by email, automatically, or a person uploads one
2. 02each lands in its own area, stored exactly the way it arrived
3. 03a separate layer joins them together when a question needs two sources at once
4. 04every important number is defined once, in one place
5. 05the dashboard and the AI analyst both read that same definition, so they can't disagree

04

The Responsible AI Constraint

The assistant can't make up numbers, because it doesn't know any. Every figure in an answer comes back from a real look up against the database, and it has to say where it came from and what dates it covers.

Every important number is defined once, in one place, and both the dashboard and the chat read that same definition. So the two can never quietly disagree.

For questions nobody planned for, the assistant writes its own database lookup, and that's fenced four separate ways rather than one. It runs as an account that can only read, never change. A checker allows one look up at a time and refuses anything that could alter or delete. It can only reach approved areas. And the cap on how much comes back is wrapped around the whole request, so a smaller limit hidden inside part of it can't get around it.

Donor information isn't protected by any of that, because it was never loaded in the first place. No names, no email addresses, no phone numbers.

And the assistant is told that we don't track that yet is a perfectly good answer, because a confident wrong number is worse than no number.

05

The Result

Live at data.radiomilwaukee.org behind a login, for Radio Milwaukee staff and board only, with separate tabs for programming, fundraising, sponsorship, and finance and leadership. Streaming, donations, ratings, email and competitor social accounts are loaded. Website data is partly in. Paid social and the full finance picture are still being connected, and the assistant says so rather than guessing. The streaming service emails six scheduled reports, and a tag in the subject line tells the system which is which. Loading the same report twice does nothing bad, so a late or resent report updates what changed and duplicates nothing. Backfilling the hourly history alone was around sixty thousand rows. The ratings report arrives as a grid built to be read on a page, and gets turned into one row per fact so it can be searched like everything else. The business case is written down: about a thousand dollars a year in tools at nonprofit rates, against forty to a hundred and fifty thousand to have an agency build it, or a hundred and twenty thousand a year for a full time data engineer.

06

If you remember one thing

Most of the work wasn't the AI. It was the plumbing: catching reports, reading each vendor's format, deciding what a number means, keeping the donor data out. The chat is the last ten percent that makes the other ninety usable.

07

From the analyst's instructions

Excerpt. The full version is 184 lines and is server side, so a user cannot change it.