Direct-sales and programmatic digital-ad revenue forecasting
Revenue forecasting can be a challenge for online businesses. They need tools to forecast both their direct sales and programmatic revenue, track changes that occur over time, and forecast the impact of unexpected events in direct sales revenue on programmatic earnings, and vice versa.
A leading financial services website sells inventory via a direct sales model and programmatically in open auctions. To better forecast its revenue, it needed to understand how revenue tracked, in the past, present, and future.
Like all publishers, the company’s revenue doesn’t accumulate in a linear fashion; it accumulates episodically. In the beginning of the first quarter, its sales teams are selling into the first quarter, but by March, they’re selling into Q2 or even Q3. This made it difficult to accurately forecast quarterly revenue. Additionally, the company wanted to know the implications on its programmatic revenue if conditions didn’t occur as expected on the direct side of the house.
The company had several challenges to overcome to achieve its revenue forecasting goals:
- Data disparity – Client and revenue data was generated and housed in multiple systems, including Salesforce.com and Operative on the direct sales side, and PubMatic, Rubicon Project and AdEx on the programmatic side. All the data needed to be retrieved and normalized into a single program.
- Lack of granular programmatic data – Programmatic data was only available on a weekly level, and the client had little information on the actual pricing received.
- Difficulty getting real-time data – The data analysis team created spreadsheets to gain some insights, a process that wasn’t scalable and required significant manual input. That meant it was difficult to get insights on demand or updated in real time.
- No future or historical views – The spreadsheets didn’t allow a level of drilling, either in a future or an historical view, which meant the team couldn’t gain any significant insights.
We went to work designing booking curves for the client, so they could forecast both their direct sales and programmatic revenue, track changes that occur over time, and forecast the impact of unexpected events in direct sales revenue on programmatic earnings, and vice versa.
For the booking curves to be useful, we needed to design models that tracked revenue year-over-year, as well as quarter-over-quarter. Additionally, booking curves can vary from client to client, even for the same order.
We began by pulling historical data from Salesforce.com and Operative into Python, which is well suited for normalizing disparate datasets and for crunching data. Next we pulled data from PubMatic, Rubicon Project, and AdEx using APIs.
Although the client had an abundance of data for its directly sold revenue, its programmatic data was much less granular. Creating a booking curve for the programmatic side of the house required a minimum of week-level views of prior volumes, as well as an analysis of the actual pricing involved and the price settings. We had to make smart assumptions and build a collaborative model that assessed:
- What is that strategy?
- How short or long-term is that strategy?
- What is it driven by?
To help the data analysis team share the insights throughout the company, we fed the data model into Snowflake which was accessed via Tableau, and created custom dashboards to display the booking curves.
How did it turn out?
- Booking curves for accurate forecasting – The company now has accurate booking curves to forecast its direct and programmatic revenue.
- What-if scenarios – Built-in scenarios allow the company to predict implications of direct sales activities on programmatic revenue and vice versa.
- Early warning – The solution provides an early warning signal that earnings may not be what they anticipated. This signal allows them to take corrective actions, and advise the appropriate C-level executives.