vanhausBook a free discovery call
All work

NJ business news publisher

Advertiser reports that credit the right ad

Pick an advertiser, a date range, and the newsletters. Get opens and ad clicks for every send, credited to whoever actually held that ad slot that day.

The problem

Where the time was going.

The publisher sells ad slots in its email newsletters and owes each advertiser a report: how many readers opened the newsletters their ad ran in, and how many clicked the ad. Mailchimp has the raw numbers, but turning them into a per-advertiser, per-day report was a manual grind on a desktop script that only ran on one machine.

The numbers were also hard to get right. Mailchimp logs clicks against links, not advertisers, and the ad links point at ad slots that rotate. One slot held five different advertisers between June and October, so following the link today credits whoever is in the slot now, not who was there on the send date.

Reports often span months and hundreds of campaigns, far more work than a single web request can finish.

What we built

The fix.

A web app for invited staff. Pick an advertiser, a date range, and any mix of newsletters, choose the columns, and run it. One run produces a CSV per newsletter, in the same layout the team already used, with monthly subtotals and a grand total.

Clicks are credited using the ad server's own placement history. A click only counts when that advertiser held that ad slot on the send date, and older plain links to the advertiser's site still count too. Every report includes an attribution log showing which advertiser held each slot, and when.

Long reports run as resumable jobs. They survive a page reload, a closed tab, or a server timeout and pick up from the last saved campaign. A progress bar shows campaigns processed and time left, and any past report can be re-run with the same settings.

Results

What changed.

95%less time on reporting
204rows checked against a known advertiser report, matched row for row

Reports any staffer can run from a browser, with every click credited to the advertiser who actually held the slot.

How it works

Stack and approach, in plain language.

  • Next.js
  • Supabase (auth and Postgres)
  • Mailchimp Marketing API
  • Broadstreet ad server API
  • Vercel
  1. The app pulls the campaign list from Mailchimp once, tags each campaign with every newsletter type it matches using whole-word matching, and reads opens, recipients, and per-link clicks for each send.
  2. Ad placement history is synced from the ad server every few hours. For each ad link, the app checks which advertiser held that slot on the send date and only counts the clicks that belong to them.
  3. Work runs in 30-second steps that save their place in the database, so a months-long report never times out. Send dates are handled in Eastern time, so a 9 PM blast lands on the right day.
  4. API keys stay on the server and never reach the browser. CTR is total clicks over total opens, the same formula on every row and total.

Next step

Got a similar problem?

Thirty minutes, no cost, no pitch deck. Tell us what is eating your time and we will tell you what we would build.

Book a free discovery call

More work

See everything