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 Report
The At-Risk 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:
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:
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:
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.
[This article was originally published on Run of Network in Oct of 2011]