At Risk Management – How Every Ad Ops Department Should Start Their Day

Digital media is notoriously difficult to manage – not only are there technical and administrative challenges with getting a campaign live, but as the inventory is so perishable, campaign delivery can be tricky.

Underdelivery Despite Capacity

It’s no secret most publishers on the internet have plenty of inventory on a site wide basis, but often struggle with meeting campaign goals as they add targeting restrictions, frequency caps, competitive separation requirements, or have to deliver to a specific geographic area.  Not only that, but in many cases publishers and advertisers have a need to actively track delivery trends to ensure campaigns deliver as expected, and meet their goal within the scheduled timeframe.  Ad servers generally fill this role when capacity isn’t a problem, but when there isn’t enough supply to satisfy the demand, it falls on Ad Operations to prioritize delivery, and optimize problem areas.

But when there are hundreds or thousands of campaigns running at a given time, all with different goals, targeting requirements, and flight dates, how can anyone make sense of the data?  Many publishers have an inadequate process, relying on billing of financial reports to surface problems, or pawn the delivery management onto the sales team, which rarely has the expertise to effectively address the problem.

A far better solution is to build a simple report, a dashboard really, and use it as front line tools to catch problems early, before they spiral on for days.  This report is typically known as an At Risk Report and in many cases can not only save revenue, but time, frustration, and significantly cut down on the finger pointing that Ad Ops groups often face.

Building an Effective At Risk Management Report

The At Risk Management Report is the most critical day-to-day report for most sophisticated publisher-facing delivery management groups.  The primary job of this report is to create a ‘delivery dashboard’ of sorts to catch all campaigns that are not delivering to their expected quota, allowing Ad Ops to flag issues early, and optimize as necessary.  Some ad servers, such as DFP, offer this type of report, pre-built, with many of the necessary pieces of data, but if yours does not, it is usually simple work to get create the report yourself. In either case, I recommend simply getting the raw data and moving it into Excel, where you can apply your own custom filters and calculations using Pivot Tables.

The first step in creating an At Risk report is to pull the right data.  At minimum you’ll need advertiser / order / flight or campaign / start date / end date / targeting requirements / priority / ad size / flight or campaign status, as well as delivery information to date and the flight or campaign goal. In addition to those pieces of information though, consider adding other fields like sales rep, trafficker, site, or paid rate (be it CPM, CPC, CPA, or something else) to help route issues to the right contacts later.

The raw data should look something like this:

At Risk Report - Basic Management

The On-Schedule Indicator

Once you have a data set created and in hand, the one critical field you’ll want to add to your data in Excel is an On Schedule Indicator, or OSI.  The OSI is a concept that DFP initially developed to help index campaigns to a standard performance metric.  In this case, the metric is what % of goal the campaign is expected to deliver.

The OSI is especially helpful because it corrects for different flight dates, goal levels, and the other pieces of noise that make it difficult to simply look at the raw data and determine which campaigns are delivering fine and which need help.  The OSI calculation is displayed as a %, which you can use as a filter in a pivot table so you can just look at the flights that are expected to deliver less than 100% of goal.  Typically the OSI is calculated off impressions, but you could easily change the formula references to run off clicks if you sell on a CPC basis.

You can use the following formula in Excel to create your own OSI – this may seem more complicated than necessary, but the formula is written in a way so that if the current date exceeds the end date of your campaign, the formula uses the campaign’s end date to calculate loss instead of the current date:

=IF(Current Date > End Date, ((Delivery To Date / (End Date – Start Date)) * (End Date – Start Date)) / (Goal), ((Delivery To Date / (Current Date – Start Date)) * (End Date – Start Date)) / (Goal))

You can now see that the OSI easily exposes problematic campaigns:

At Risk Report - OSI Sorting

Organizing the Data

I recommend you create an initial pivot table to filter and organize the data.  Start by applying a report filter to reduce the results to campaigns that have OSIs under 100% and then use the row filters to break out that data by target, then advertiser and finally by flight or campaign.  Then, prioritize the results by sorting by end date, or urgency, and / or revenue at stake, but you can prioritize issues in whatever way makes most sense for your business. At the end of the day, you want a master view that looks something like this:

At Risk Report - Advertiser Level Breakout


In my experience, you should try to pull and review your At Risk report every single day, which will give you a deep knowledge of what is happening on your site, where inventory is scarce, and which campaigns need special attention.  If you find that you have very few struggling campaigns, it may be enough to pull the report on a weekly basis, but it typically takes only a few minutes to refresh the data, update your pivot table dashboard.

More Error-Catching Customizations

As a side benefit, you can use the same data set from the At Risk report to catch trafficking errors, or flag other potential issues that don’t pop-up as pacing concerns.  For example, you could create a report to look at any campaigns that deliver a 728×90 ad in a 300×250 ad slot.  Or, you could use the data to create a calculated field that alerts you if any flight exceeds an abnormal delivery threshold, perhaps signaling an incorrect goal due to a trafficking error.

There are likely a handful of common human-errors you can track and catch with some thoughtful filters in your At Risk report.  Consider the common issues facing your Ad Ops team, which errors you are most prone and see if you can create a filter or calculated field using the At Risk data to help you reliably identify those issues.  An At Risk report won’t solve all your problems, but has the potential to play a cornerstone role in improving your organization.



  1. Yes, you can spend a lot of time creating your dashboards in excel, but why has no adserver just built this into the platform? Reporting and monitoring tools in ad servers are absolutely rubbish. Why?

  2. It’s a good question; and it reminds me of something Tom Shields from Yieldex always says, that ad servers were never designed to be an insights platform, they were designed to distribute messages at scale, and be a third party source for basic metrics. So as nice as it would be to get an insights platform on the back of the data ad servers generate, it’s not really in their DNA. Some ad servers do have some decent reporting engines on them, and I think you can find things like the At-Risk report canned in most of the major players. If you’re really serious about digging into your data though, you’ll have to get comfortable with Excel at the very least, and probably look to invest in a product like Yieldex, or invest in some talent that knows how to work with Hadoop.

  3. Thanks for the great calculation (and easy to get explanation) but is it possible to show the calculation with the actual column headings used in the raw data example? This would really help me understand the calculation better along with allowing me to recreate it with my own data. Thanks again!

  4. Hi Leanne,

    Sorry for the late response on my part, but here’s the answer to your question:

    =IF(Current Date > Flight End, ((Delivered / (Flight End – Flight Start)) * (Flight End – Flight Start)) / (Goal), ((Delivered / (Current Date – Flight Start)) * (Flight End – Flight Start)) / (Goal))

    The only column you won’t see in the raw data is ‘current date’, and that’s just what it sounds like – the current day’s date, from which pacing is then calculated. If your delivered number is 50% of your goal, your OSI will depend on what % through the flight you are, and that’s why the current day is required. For this particular example, the ‘current date’ was set to 8/31.

    Hope that helps –

Leave a Reply

Your email address will not be published. Required fields are marked *