Roadblock Availability Estimator

A planning tool for connected-TV roadblock ads. A roadblock is a premium smart-TV home-screen placement, usually sold as a full-day booking across the platform. Before discussing one with a client, a salesperson needs to know how much inventory is available and whether another advertiser already holds any of the requested dates. This tool brings both answers into one five-minute check.

Live demo → · Source on GitHub →

Everything in the public demo is synthetic. The internal tool works with client data, 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 data. The same kinds of overlaps and clashes still occur, so the public demo can show how the tool behaves without exposing real information. “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.
• Helped catch a live clash on a high-value campaign. Another advertiser had already booked one of the requested dates. The tool surfaced the conflict the same day, giving the client time to adjust the plan.
• 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 planned, built, and tested this independently over about three weeks, alongside my day job. That included the inventory calculations, conflict checks, Japanese and English interface, and the sanitisation process used for the public demo. Two teams adopted the internal version, which remains in use.

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 part of the problem was competing advertisers. That information lived in a separate spreadsheet with limited access, so sales had to ask the CM team to check it and report back. The list changed daily, which meant a date could be taken while a campaign was still being planned.

Why it mattered

These bookings can represent substantial campaign budgets. Missing a clash could mean reworking a plan late or losing the client’s preferred dates. Each email round trip also took time from both teams and created another opportunity for a detail to be missed.

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, the part I am proudest of. The tool checks whether bookings compete for the same inventory instead of treating every date overlap as a conflict. It merges 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.

The estimator reduces repeated manual work, while the conflict check lowers the risk of a missed clash or double booking.

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.

How the check works

The three cases are handled by one function. Given a date and time window, it returns whether the slot is free, needs a warning, or is blocked, along with the reason. The code is included here for readers who want the technical detail:

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 do, this part matters as much as the calculation. Making a useful demo without exposing client information was an important part of the project, not an afterthought.

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 earlier visibility of risk. In one case, a returning client requested a run that included a day another advertiser had already booked. The tool showed the conflict on the same day as the estimate request, giving the client time to move the dates before the plan progressed further.

What I would do differently

The tool had to run on the open web because of internal resource limits, so I had to be careful about what data it could use. If I built it again, I would start with authentication and a secure internal hosting option, such as Google Apps Script, rather than adapting the design around a public deployment.

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.