Spreadsheets instead of code

We eliminated development and maintenance cost of music video programming software by replacing it with a powerful spreadsheet

Background

imageTunes Co (not the real name) provides music video programming via a dozen cable television channels. Tunes runs a complex operation that involves content production, music programming, advertisement (ad) insertion and audience measurement. The operation is time critical because Tunes has to provide the video of an entire week’s programming including ads by 5 pm every Saturday.

Tunes employees used an application that had an RDBMS back-end and forms front-end to conduct its operation. The operation is described below.

Programmers artfully create a week’s program. They use their knowledge of music and culture to make compelling programs consisting of music videos. A program is a play-list of music videos.

An ad “trafficker” has to insert ads into the programming. He has to decide what advertisement to place between music videos. He uses a list, called orders, consisting of advertisers, the number of impressions the advertisers purchased and the price they paid. He also uses the number of impressions fulfilled in previous weeks.

The trafficker has to ensure that ads are viewed at least as much as the advertisers paid for. He uses viewership analytics provided by Neilson and Rentrak to estimate future viewership from previous airings of the music videos. For programs not aired previously, he estimates the number of views. The programming director forecasts the popularity of new content relative to that of programs with a history.

The trafficker gives a of music videos with advertisements in-between the music videos to the Producer. The producer uses a video-editing program to splice music videos and ads in the order given by the trafficker. He sends the week-long video to the cable companies electronically.

The Problem

Advertisement orders were over-fulfilled, under-fulfilled or fulfilled incorrectly. There was frenzied manual activity every Thursday and Friday as everyone in Tunes tried to manually fix an incorrect ad-inserted playlist.

The root cause was that rigidity and quality of the RDBMS application worsened as the business rules for trafficking evolved. For example,
1. Spread ads over a week even if you can fulfill the demand in a day
2. Play a Pepsi ad after every song on the New-Age channel
3. Play coke on every channel that airs at least 1 hour of new-age every day but only after the new-age songs
4. Don’t play a Ford advertisement within 30 minutes of a Toyota advertisement unless necessary to fulfill an order on time

The art of programming evolved too. Once upon a time, there was one channel for every genre. For example, there was a Pop, R&B and Blues Channel. Today channels air a mix of genres as each channel is perfected to appeal to specific psycho-demographics.

I was hired to build an application from ground up to replace the broken RDBMS application

The Innovation – The Programming Spreadsheet

I started by learning the business of Tunes. Soon I realized that the Tunes operation was an art. A mind-boggling amount of knowledge and skill went into making Tunes successful.

To learn, I used a spreadsheet with real data to understand how Tunes worked by example. It allowed me to visualize and change the data easily.

I collaborated with the Programmers and Ad-traffickers using my spreadsheet to gather requirements. One of them said about my spreadsheet “That’s how our application should look”, and another said “I wish I could make the changes in our application as easily as you are changing your spreadsheet”. That is when I realized all we needed was a good spreadsheet.

I polished my spreadsheet and recorded some macros to ease the job of creating views and pivot tables. This spreadsheet became the Programming Spreadsheet
The Programming Spreadsheet became the core system in Tunes and is in use even 7 years later

The Spreadsheet

The spreadsheet has multiple tabs or sheets. The inventory sheet is a list of all original and acquired content that Tunes possessed. Producers and Content Acquisition Department add new content to the inventory. Programmers would add last-aired and channel-aired data to this list. The Analytics Department would add viewership data to this list.

Programmers add or update the attributes of the videos in the inventory such as genre, psycho-demographics and date it last aired.

The orders sheet is a list of sales orders. Salesmen enter new orders into this sheet
Programmers inform traffickers when they are done creating the schedule for a week.
Traffickers first update the orders sheet with the previous week’s fulfillment data.

Then they would fill in new and partially filled orders to create the weekly playlist of ads and content.

Producers would export the weekly playlist sheet as a comma separated list. A bash script would make calls to ffmpeg to make the final video.

Tradeoffs

The RDBMS software promised optimal fulfillment. Upon analysis, it was obvious that Tunes had such a high inventory of ad slots, that a simple allocation of ads slots to unfulfilled orders, while skipping slots that violated any rule was adequate. Each column of the trafficking sheet had conditional formatting that would check for and highlight conflicts.

The RDBMS software promised automatic programming that would make playlists that would get the highest viewership. In reality, programming music video is an art not easily replicated by algorithms. Algorithms would lack timely contextual input from the real world. For example, a proposed tour of Adelle in Philadelphia would cause people to view Adelle’s music videos in the week before her tour. Expert content programmers were far more effective at this than computer programmers

The software promised a database of historical playlists with detailed information about their performance that would be readily accessible. However, exporting the inventory sheet every week to a Microsoft Access database provided the same historical perspective.

The Access database was a treasure trove of information. One script showed a strong relation between popularity of upbeat music in the first and last week of a month, but greater popularity of moody music during the second and third months
Measuring Success

Note: I don’t recall the numerical values of the metrics below, since it has been 7 years since I last worked on this project

1. Order fulfillment, which is the percent of orders filled on-time was up
2. Advertisement conflict, which is the percent of time that a fulfillment
violated a generic rule or a rule specified by an advertiser was down
3. Increase in brand association was up. Brand association is
measured as the percent of users who correctly identified the brands associated with a song, genre or artist. This number should increase after a successful advertisement campaign on Tunes
4. Median time between advertisements increased even as total advertisements increased.
5. Variation in time between advertisements decreased. When using the RDMS software, traffickers would “jam” a popular channel with advertisements when they run out of time to find a way to spread the advertisements. The spreadsheet eliminated this practice.

In addition, the RDMS software was completely retried. The frenzied activity on Thursdays and Fridays dramatically stopped. The conversation in Tunes changed from automating operations to making products that are relevant in the iPod dominated world of music. When I left Tunes, the CEO remarked, “You achieved in three months what your predecessors couldn’t achieve in three years”. I recently met a VP still at Tunes, and she said they still use the Programming Spreadsheet 7 years later

Leave a Reply

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