Roadblock Avails Estimator

A planning tool for connected-TV roadblock ads. A roadblock is a premium smart-TV homescreen takeover, sold as a full-day block across the whole platform. Before a salesperson can pitch one, they need two answers: how much ad space is free on these dates, and whether a competing advertiser has already taken them. This tool answers both in one place, in about five minutes.

Live demo → · Source on GitHub →

Everything you see in the demo is fake, on purpose. The tool runs on live client data internally, so a sanitisation pipeline feeds the public version, swapping every brand, client, salesperson and number for a synthetic stand-in while preserving the shape of the real data. The same overlaps and clashes still occur, so the engine behaves in the demo as it does in production. “Kestrel” and “OrbitTV” stand in for the real vendor and smart-TV platform. Operational security required this, and building it correctly was part of the work. See Making it safe to share.

At a glance

• 30 minutes → 5 minutes. A request that took the campaign management team 15–30 minutes of manual work, plus email round-trip lag, is now a self-serve lookup sales run themselves.
• 1–2 hours of cross-team back-and-forth removed per week, at a typical 4–5 requests a week.
• ~15 people across two teams use it today, still in daily service as the first-pass inventory check.
• Caught a live clash on a tens-of-thousands-of-dollars campaign. A returning high-value client’s requested dates had already been booked by another advertiser. The tool surfaced it same-day instead of after an email round trip, and the client moved the dates before it became a lost deal.
• 272 tests covering the inventory maths and the conflict engine’s edge cases.
• No backend, no database, no credentials. A static build that deploys anywhere.

My role

I built this solo, in about three weeks of planning, building and testing alongside my day job: the inventory model, the conflict engine, the bilingual Japanese/English interface, and the sanitisation pipeline that made it safe to publish. Two teams put it into real use and it is still running.

The problem

Before the tool existed, every inventory check ran through the campaign management (CM) team. Sales would write up a request and wait for the CM team to pick it up. The CM team then worked through the same manual steps each time: read the request, check the requested dates, sort which fell on a Saturday, Sunday or weekday, because rates differ, work through the requested creative mix (often more than one ad format), pull the Japan numbers out from all the other countries, then reformat the results into a clean table to attach to an email back to sales.

At low campaign volumes this was manageable. As volume grew it got slow and tedious: 15 to 30 minutes per request depending on the number of ad formats, on top of the lag between sales sending the request and the CM team getting to it. And since the platform had to confirm the numbers directly later on, this was a lot of time spent on an internal step that would be repeated anyway.

The second half of the problem was competing advertisers. That data lived in a separate spreadsheet that not everyone could access, so sales would ask the CM team, who would check a list shared by the platform, then report back. That list updates daily and can change without notice, so someone else could take a date the sales team had built a whole campaign around, with no warning.

What it cost when it went wrong

Tens of thousands of dollars in campaign budget could ride on it if another advertiser took a client’s preferred dates during the back-and-forth over availability or contract terms. Every email round trip also cost both teams time and added another point where something could slip.

The approach, and the decisions that mattered

The tool collapses that whole loop into one place, and it solves two problems at once.

The first is the recurring lookup work. Instead of the CM team pulling and formatting numbers by hand for every request, a salesperson picks the ad product, the dates and the creative mix, and the estimator returns available impressions and budget figures in the same shape the CM team used to build by hand. The repeated 15-to-30-minute task becomes a self-serve lookup, and the internal round trip between the two teams goes away.

The second is conflict detection, and it is the part I am most proud of. The engine reasons about what conflicts instead of drawing a calendar of overlapping bars. It merges the two sources of booking data, deals already won (signed) and deals still in the pipeline (in negotiation), removes duplicates, and treats them differently: a won deal blocks the date outright, a pipeline deal only warns. It matches on ad product and time window as well as dates, so two bookings can overlap in time without ever competing for the same inventory. It also pulls in the booking data that used to live in that separate, half-accessible spreadsheet, so no source is left for anyone to forget.

Two kinds of value come out of that split. The estimator removes recurring manual work, and the engine removes the risk of a double-booked or missed clash.

The engine, in one screenshot

Three days here are locked (🔒) because another advertiser already holds them. The days either side stay open. Once you select a slot, the overlapping time windows on that same day grey out, so you cannot double-count a 24-hour block and the 6pm–midnight block inside it. The total updates as you go.

The underlying bookings come from a shared calendar view, the second source the engine merges:

The three cases that matter

The engine has to get three cases right, and you can reproduce all three in the live demo:

• It blocks a real clash. Two won deals want the same slot and time window on overlapping dates. That is a double-book, so the engine blocks the date outright. A naive check comparing date ranges catches this one too, but only if it handles the first and last day of a range correctly, which is a classic place to be off by one.
• It stays quiet on a false clash. Two bookings overlap in time but target different time windows, so they never compete for the same inventory. A tool that only draws overlapping bars cries wolf here. This one matches on product and time window, so it clears the date.
• It does not flag a deal against itself. A campaign that was in the pipeline and is now won appears in both feeds. Without de-duplication, the engine would report it as conflicting with a copy of itself. Merging and de-duping first removes that false positive.

Deal stage matters on top of that. A won booking blocks a date; a pipeline booking on the same date warns instead, so the salesperson can see the risk and move their dates before anything is committed.

The predicate itself

All three cases fall out of one function. Given a date and a time window, it returns whether that slot is free, warned or blocked, and why:

type SlotState = "free" | "warned" | "blocked";
type Commitment = "won" | "pipeline";

// [start, end) half-open hours, 0–24. Touching endpoints don't conflict:
// a 6–9PM booking leaves 9PM–12AM free.
const overlaps = (a: [number, number], b: [number, number]) =>
  a[0] < b[1] && a[1] > b[0];

function slotState(
  campaigns: Campaign[],
  date: string,
  slot: [number, number]
): { state: SlotState; reasons: Reason[] } {
  const reasons: Reason[] = [];

  for (const c of campaigns) {
    if (c.startDate > date || date > c.endDate) continue;

    // Per-day flight times when we have them; otherwise assume the whole day,
    // so an unparseable booking is never silently treated as free.
    const flights = c.flightTimes.filter((f) => f.date === date);
    const intervals = flights.length
      ? flights
      : [{ hours: [0, 24] as [number, number], parsed: false }];

    for (const ft of intervals) {
      if (!overlaps(ft.hours, slot)) continue;
      // Either source can commit the slot; the ops sheet often leads the CRM.
      reasons.push({
        ...c,
        commitment:
          c.commitment === "won" || ft.commitment === "won" ? "won" : "pipeline",
        hours: ft.hours,
        parsed: ft.parsed,
      });
    }
  }

  const state: SlotState = reasons.some((r) => r.commitment === "won")
    ? "blocked"
    : reasons.length > 0
      ? "warned"
      : "free";

  return { state, reasons };
}

Three decisions in there carried most of the thinking:

• Half-open intervals. a[0] < b[1] && a[1] > b[0] is what makes adjacent slots non-conflicting. Closed intervals would block 9PM–midnight every time 6–9PM was sold, losing a whole saleable window to an off-by-one.
• Fail closed on unparseable input. A flight time the parser can’t read becomes [0, 24] rather than free. The worst case is then a planner double-checking a date that was available, instead of a client being sold something already gone.
• Two-source commitment OR. The operations sheet often books a slot before anyone marks the opportunity won in the CRM, so either source flipping to won blocks it. That one line encodes how the two teams work day to day, which the systems on their own do not reflect.

The function returns reasons alongside the state instead of a bare boolean. That is what lets the interface tell a salesperson which campaign is holding a date and at what stage, instead of showing a red cell with no recourse.

The build

Next.js, TypeScript and React, tested with Jest. The app is static, with no backend, no database and no credentials anywhere, which the constraints below made necessary. The interface runs in both Japanese and English.

272 tests cover the inventory maths and the engine’s edge cases: inclusive date boundaries, weekday and weekend rate splits, multi-format creative mixes, de-duplication, and the product-and-time-window conflict cases above.

Making it safe to share

The internal tool runs on live client data, so publishing anything meant solving the security problem first. It ships with a pipeline that swaps every brand, client, salesperson and number for a synthetic stand-in while keeping the structure of the data intact. The same overlaps and clashes still occur, so the engine behaves in public as it does internally. A leak check fails the build if a real value slips through, and I rebuilt the git history from a clean slate so nothing sensitive sits in old commits.

For the kind of work I want to move into, this part matters as much as the engine. Taking real client data, making it safe to expose in public, and proving it is safe, is a large part of the job.

The outcome

The sales team is now self-serve for everything short of special cases. They can read the availability picture themselves before they request a formal check from the platform. The old loop of 15 to 30 minutes spread across two teams is now a 5-minute check sales run on their own. At 4 to 5 requests a week that removes 1 to 2 hours of back-and-forth, frees the CM team from fielding those requests, and around 15 people across the two teams still use it.

The bigger payoff is the risk it removes. A lost date could cost tens of thousands in campaign budget, and one case made that concrete. A high-value client came back after a two-year absence, so the sales team was handling them carefully. On the same day as the estimate request, the tool showed that another advertiser had already booked one day of the client’s requested run. The client had time to shift their dates early, before it turned into a lost deal.

What I would do differently

The tool had to live on the open web because of internal resource limits, so I had to be careful about what data it could touch. Building it again, I would spend more time on secure display, or pick a stack with authentication built in (Google Apps Script, for one) so it could sit behind a login.

That constraint limits the current version. Client names, budgets and similar sensitive fields cannot appear in the same tool, so part of the picture still lives elsewhere. The next version has to close that gap and get the whole picture into one authenticated view without widening the data exposure.