Sponsorship tracker sync

The ask

The sales team sells sponsorships of recurring segments (weather, traffic, sports) in local markets. All of that inventory lives in one large spreadsheet. Each sponsorship has a set of rows: open slots are yellow, sold ones are white, and a dashboard on top reports booked revenue.

Bookings are created in the sales system. Getting them into the tracker meant someone copying each new booking over by hand. The news advertising team asked for that to happen on its own every morning.

What was actually wrong

The copying was the visible cost. The harder part was everything a person handles from memory while copying:

• a booking that shows up again unchanged;
• a booking revised after it was entered;
• a booking that isn’t approved yet;
• a booking that can’t be placed at all, because the name is wrong or the sponsorship is sold out.

An automation that only copied would either skip those quietly or guess. Neither is acceptable when the dashboard reports revenue. So the real job was to place what’s certain, mark what changed, and put everything else in front of a person.

How I found out

I went through the daily report column by column: what each field means, how sponsorship names actually get typed, and what the report adds below the bookings. Then I worked through the tracker’s own conventions with the team: what the fills mean, which columns are pre-filled inventory data that must never be overwritten, and what the dashboard counts as a booking (white rows only).

The last piece was the morning review: what the person checking the tracker needs to see, and what they’d skip past.

Options and the call

The tracker is an Excel workbook in a shared folder, and the team works in it every day. So it stayed the source of truth, and everything was built around it:

• Power Automate for the schedule, the email, and moving files;
• Office Scripts (TypeScript that runs inside Excel) for the matching and writing.

There was no new app, login, or place to look.

The other call worth showing came during rollout. The review email started listing two items that weren’t bookings: the report’s own summary rows. One fix was to teach the matching step every shape a summary row can take. The other was a single rule earlier in the process: stop reading at the first row whose booking ID is “Total”. I went with the single rule.

What I built

Three scheduled pieces:

1. 4:00 AM: the sales system emails the day’s new-bookings report as a spreadsheet.
2. 5:00 AM: a small flow saves the attachment to a shared folder.
3. 5:30 AM: the main flow does the rest:
   • prepares the report: finds the header row and cuts everything from the first Total row down;
   • archives every row to a history workbook with a run ID, timestamp and source file, so any morning can be traced;
   • matches each booking to its sponsorship and writes it to the tracker;
   • logs anything that wasn’t written;
   • sends one email if anything needs a person.

The matching rules:

• Names. Names are compared without case, spaces or dashes, so 1187 - Northgate - - WEATHER finds 1187-Northgate-WEATHER. The tracker keeps its own spelling.
• New bookings take the first open slot for their sponsorship. If the sponsorship is sold out, the booking goes to review. Nothing gets overwritten.
• Not approved yet: the booking is written in red. When the approval comes through, the same row clears and no new slot is used.
• Repeats. A booking already on the tracker is identified by its booking ID plus its sponsorship.
  • If nothing changed, it’s skipped.
  • If something changed, the row is updated in place and turns orange, and the email shows the difference, for example $2,125 → $2,375 (+$250).
• Blank incoming values never erase what’s on the tracker.
• Review. A missing name, missing dates, or a run that has already ended goes to review. When a name is close to a real one, the email suggests the closest tracker name.

I also rebuilt the dashboard on top of the tracker. It shows:

• linear and streaming revenue as separate figures;
• revenue by account exec;
• a date-range filter;
• filters that take several regions or account execs at once.

Every rule has a check in a local test suite that runs the actual scripts against a mock of Excel. As of the latest release it has 92 checks, including a full not-approved → approved → repeat cycle. Changes go to copies of the workbook before the real one.

The demo above re-creates one morning with synthetic names and numbers.

What stayed familiar, what went away, what people had to learn

Stayed familiar: the tracker. The workbook, tabs and columns are the same, and yellow and white mean what they always meant. The dashboard still reads from it.

Went away: copying bookings by hand, and checking each one against what’s already there.

New:

• two colors: red for not approved yet, orange for changed this morning;
• one morning email. It only arrives when something needs attention, and each item says why.

Result

It’s been live since the end of August. During the first two weeks (Aug 31–Sep 11), the team owned the daily review and I fixed what came up:

• the report’s summary rows;
• names typed with stray spaces and dashes;
• showing how much a revised booking changed.

It took about 40 hours to build. The figures above are estimates, not timings. Before launch, manual entry took each of the three people about 288 minutes a workday, roughly 70–80% of their time: about 3,744 hours a year across the team. It now takes about 10% of their time, which the panel shows as 48 minutes of an 8-hour day.

What I’d change

• Measure before launch. A week of timing the manual process would have turned these estimates into a measured before-and-after.
• Write down the report’s format first. The summary rows reached the review email before I had written down that they existed. Defining what a valid row looks like, and what isn’t one, should come before the first script.
• Ask for a revision timestamp. The report doesn’t say when a booking last changed, so an older export processed late could overwrite newer values. For now the rule is simple: never replay an old export. A last-modified column would make replays safe.