Forecast gap, from 28–30% to 10% or less
Took over the volume forecast for a 24/7 operation across phone, chat and tickets, though forecasting wasn't part of my job, and rebuilt it around real contact history so staffing ran on numbers the team could trust.
Gap between forecast and actual volume cut from 28–30% to 10% or less in a normal month
The knot
The account ran 24/7 on three channels (chat, tickets and phone) at 5,000 to 10,000 contacts a week, staffed by 100+ contractors in 75+ countries. Forecasting wasn’t part of my job, but the existing model was missing badly: actual volume was landing 28–30% away from it. On that many staffed hours, a miss that size means either a queue full of waiting customers or a floor full of idle agents, and often both in the same week. So I took it over.
The problem was what the model was built on. It derived contact volume from the number of subscribers, which didn’t move the way incoming contacts did. And it was built monthly, treating every calendar date as its own case, when in practice a Monday behaves a lot like other Mondays.
There was a second complication that most workforce planning guides don’t cover. Contractors on this account weren’t guaranteed hours. Coverage came from the hours people volunteered, so I also had to predict how many hours would be offered, and when.
What I did
Start from clean history
- Anchored the model to real daily contact volume per channel, going back to 2021. I tested subscriber counts as a driver and kept them only for renewal waves, the one place they predicted volume.
- Scored every day against the same weekday’s median, per channel, and left out the days far outside the normal range, so a one-off spike couldn’t drag the baseline.
- Used only data from after a contact form was switched off, so old contact behavior didn’t leak into the baseline.
- Found that callbacks were logged when they were answered, not when the customer joined the queue. That pushed voice demand into later hours than it really arrived. I switched the source to queue time and rebuilt the voice pattern.
Build the forecast in layers
- Baseline: the median volume for each weekday and channel. Moving from calendar dates to weekdays made a clear difference on its own.
- Seasonality: 12 monthly multipliers per channel, built from three years of history with AI-assisted analysis.
- Events: I measured how far each past promotion, series launch and renewal wave pushed volume above the baseline, day by day, and turned that into an uplift curve for each kind of event. A promotion peaks early and fades, with a short tail after it ends. A launch peaks on its first Friday.
- Hours: each day is split into hours using that channel’s arrival pattern, stored as a share of the week, so the same pattern works whether the week brings 5,000 contacts or 10,000.
- Upkeep: arrival patterns refreshed monthly, medians every quarter and then monthly toward the end, and every event curve checked against what actually happened.
Turn volume into hours
- A workload model turns hourly volume into staffed hours per channel, from handle time, occupancy and, for chat, concurrency.
- Approved hours were usually lower than what the forecast called for, so the sheet also spreads a smaller budget across channels, days and hours in proportion to need, with side-by-side scenarios at +100 and +200 hours.
- A supply forecast of the hours people would realistically offer, by time of day and region, sits next to the demand side, and intraday reallocation runs on the same numbers as the weekly plan.
The whole thing ran in Google Sheets: six hidden tabs that build the parameters, and twelve working tabs that turn them into a weekly plan.
What changed
After several rounds of changes, the gap came down to 10% or less in a normal month, and several days and weeks came in under 5%. Months with promotions stayed harder, because the client didn’t always tell us about them in time.
From then on, the model decided where every approved hour went. It planned 2,500+ staffed hours a week, and up to 10,000 at peak.
One week that made the case for it: marketing ran a big push and volume spiked. The model had predicted it. Chat, our busiest channel, came in within 5% of forecast, so we handled the volume without a backlog and nobody waited hours for a reply. CSAT stayed where it was.
What I’d tell someone doing the same
Before tuning a model, check what it’s anchored to, and check the data itself. Ours was wrong before anyone touched a formula: it was predicting from the wrong driver, and one timestamp was moving voice demand by hours.
If I built it again, I’d staff voice with Erlang C instead of a fixed occupancy. Voice only ran well with 10–15% more hours than the workload model gave, which is the slack queueing theory says small queues need. I’d also score outliers with the median absolute deviation, because standard deviation gets inflated by the same spikes it’s meant to catch.
Try the idea
A small interactive version, with made-up data.
Toy version with made-up numbers. Both lines come from the same simple smoothing model. The only difference is whether an unusual week is allowed to become next week's baseline. Here a spike is swapped for the recent median; the real model left out days far outside their weekday's normal range. The real rebuild cut the gap between forecast and actual volume from 28–30% to 10% or less in a normal month.