Booking outdoor screens on a map
A Drupal platform where an advertiser draws a campaign on a map of Dutch digital screens, sees reach and price as the selection changes, and books the screens through the network’s own player software.
In 2011 I was freelancing for Itsavirus, an agency, and one of the things I built there was MyAdbooker: a booking system for digital out-of-home advertising, built for CS Digital Media, which ran screens in shops, stations, buses, hospitals and schools across the Netherlands. The pitch on the home page was “your Adwords for outdoor advertising”. The idea was that a media buyer should not have to phone anyone: pick an audience, draw where you want to be seen, pick the dates, and the screens are booked.
What follows is what a copy of the code shows. It has no git history, it is a partial copy, and the newest things in it are from the summer of 2014, so it is the site as it was after several people had worked on it, not the site I left.
The inventory
Everything starts with a spreadsheet per network. The owners of the screens kept lists, and a migration module reads them, one CSV per network, into one Drupal node per location. The August 2014 sheet has 2,719 of them across more than thirty networks: Primera, ABN Amro, Shell, the RET buses in Rotterdam, Albert Heijn, Pathé, schools, hospitals, golf courses. Each location carries its address and coordinates, how many screens it has and of which size (there is a field per diagonal, 15 inch to 55 inch and “big”), gross reach per week, media reach per two weeks, the split by gender, four age brackets and social class, opening hours, and a Broadsign id. Broadsign is the digital signage network the screens ran on, and that id is what a booking eventually becomes.
The mapper
The advertiser’s tool is one page with an OpenLayers map and three steps in the sidebar: audience, where, when. Audience is filters over those percentages. Where is either a shape drawn on the map, a city or province typed into a box and geocoded through Google with the Netherlands as bias, or a route: two addresses go to the Google Directions API through a small proxy, the polyline comes back, and every location within a set distance of the line is selected. When is two dates and a spot length, five to sixty seconds.
Every change runs one Views query, and the map, the counts and the price all need its result. Rather than query again for the totals, a patched OpenLayers bridge, openlayers_quick_query, converts the geometry for the map and, in the same loop, adds up the locations, screens and reach and leaves them in the session; the module comment says “for efficiency purposes”. The footer reads them and draws four pie charts, gender, age, kind of place and social class, and a price.
┌──────────────────────┐ CSV per network ┌──────────────────────────────┐
│ screen owners' │ ──────────────────► │ inventory (Drupal nodes) │
│ spreadsheets │ migrate module │ address · geo · screens by │
└──────────────────────┘ │ size · reach · audience % │
│ opening hours · Broadsign id │
┌──────────────────────┐ └──────────────┬───────────────┘
│ the mapper │ one Views query, │
│ (OpenLayers) │ filters + shapes │
│ audience · where · │ ───────────────────────────────────┘
│ when │ result: nid list, totals in session
└──────────┬───────────┘ │
│ ┌──────────────┐ ┌───────────▼──────────────┐
│ shapes, routes │ Google │ │ footer: pie charts, │
├─────────────────►│ geocoder · │ │ reach, price estimate │
│ │ directions │ └──────────────────────────┘
│ book └──────────────┘
▼
┌──────────────────────┐ JSON ┌────────────────┐ SOAP ┌────────────────┐
│ Drupal Commerce │ ──────► │ reservation │ ─────► │ CS Digital │
│ packet · workflow │ │ server │ │ Media │
│ invoice PDF │ │ 8 per hour max │ │ (Broadsign │
└──────────────────────┘ └────────────────┘ │ display units) │
└────────────────┘
The pieces and how they talk. The SOAP service was CS Digital Media’s; the rest is Drupal.
What a screen costs
The price formula is small and lives in one function. A spot length factor is looked up from a table, 20 seconds being 1.00 and a minute 2.15; it is multiplied by the duration in weeks, by a base price the administrator sets per network in a table that also holds the network’s loop length, and by the number of screens selected on that network. That is the price per network, and the flight is the sum. A rebuild that let each network apply its own modifiers is still in the file, commented out, above a note that says it broke the formula. The version that shipped is the plain one.
price of one flight, per network, then summed spot factor × weeks × network base price × screens ─────────── ───── ────────────────── ─────── 10 s 0.63 end − an admin table: SUM over the 15 s 0.83 start, € per network and selected locations 20 s 1.00 in days its loop length of that network 30 s 1.33 ÷ 7 (default 20 min) 45 s 1.75 60 s 2.15 nearest row wins: a 12 s spot is priced as a 10 s one
The formula, with the spot length table as it is in the code.
Booking
A campaign is a Drupal Commerce order. Buying a packet copies a template campaign into a real one, approved by default, and a workflow takes it through editing, approved, running and finished. Booking itself is two calls. The first is JSON to a reservation server with the location ids, the dates and the hours, taken from the day parts the buyer ticked, and it answers with a reservation id; a local table mirrors it and refuses a ninth booking in the same hour on the same screen. The second is SOAP to CS Digital Media, carrying the reservation, the film (its URL, an MD5 and its length in seconds) and the list of Broadsign display units to play it on. A test mode checkbox on the settings page promises that “bookings will always succeed”.
In the copy I have, the list of display units handed to SOAP is not the selection. It is one hard-coded id, left in from a test against a single screen, and the hours are forced to one in the morning until eleven at night. A booking made through this code would have played on one screen and told the buyer it had succeeded. I do not know if that ever reached production; it is the kind of stub that survives because test mode makes everything look fine.
Heat maps, footfall, and the loop
Three of the seven features on the home page are thinner in the code. Heat maps are real: a script reads inhabitants per postcode into nodes and heatmap.js draws them as a layer, with a second layer for income per inhabitant. Real-time footfall is a module that connects to a telecom company’s database, a table of counts per location per hour, and draws people reached against time of day with a date slider and a compare mode; in this copy the function that fetches the numbers is commented out and the demo date is fixed to 9 September 2013. And real-time planning, the module called rtp, is a console that opens over the map with three upload slots and a two-minute loop of eight fifteen-second chunks you can drag around before pressing “send to player”. The loop it shows is hard-coded. The agency describes the product today as a real-time bidding platform; there is no bidding in the code I have, only that console and the reservation counter.
3 images — swipe or scroll sideways
The theme
The theme is written in LESS with Roboto and Source Sans Pro, and it declares a region called “mapping tools” beside the usual ones, because the mapper’s controls needed a home the content column could not give them. It overrides the login, register and profile pages, restyles jQuery UI to match, and carries the footer template the charts sit in. Around the modules already mentioned are the small ones an agency site accumulates: an SMS login, a message inbox, crawlable AJAX navigation, XLS exports on cron, PDF quotes on CS Digital Media’s letterhead.