This post is part of our Summer Intern Blog Series! Each of our most recent class of interns wrote a blog post on their biggest accomplishments and lessons of the summer. This installment comes from Christine, a rising senior at NYU Stern, concentrating in Statistics and Data Science. In her free time, you can find her learning to play guitar, dreaming about her future dogs, or sipping on bubble tea.
I’m a sucker for a good mystery. Growing up, I read every single Nancy Drew book. In high school, my after-school activity of choice was watching re-runs of CSI: Miami and Criminal Minds, always trying to catch the murderer before the detectives could.
At AppNexus, the Client Insights Analytics (CIA) team is a subset of the larger Data Science organization, and the acronym is very fitting for the investigative data analysis the team does. As CIA’s summer intern, I got to live out my detective dreams through my summer project, developing a buy-side tool that alerts our internal clients of instances of inefficient spending as a result of a hindrance to second price auction logic.
An example of inefficient buying is a buyer purchasing inventory with set soft floors. At a high level, a soft floor is a yield management tool that allows sellers to place a threshold on the selling price of their ad placements –bids that come in above the threshold compete in the standard second price auction while bids below the threshold compete in a first price auction. Buyers aren’t typically fans of soft floors because they make it difficult to know what kind of auction they’re competing in and can make inventory unexpectedly expensive. The tool I worked on addresses the need for buyers to be alerted when their clear prices appear abnormal based on their bidding strategy. They can then use these insights to take further action at their discretion.
The Learning Curve
I spent the first few weeks of my internship learning about the different databases AppNexus uses, how they are set up, what limitations they have, and how to query them. The databases I used for my project involved querying in SQL and Vertica. I came in with minimal SQL experience, only knowing basic queries from an introductory class I had taken last year, and no Vertica experience whatsoever; however, Vertica’s syntax is very similar to SQL, so I was able to pick it up quickly. The part that was even newer to me was incorporating SQL and Vertica queries within my Python scripts, saving them as pandas data frames, and running different pandas functions to manipulate the data. Once I learned these skills, I started to explore the data.
During this phase, I spent a lot of time running queries to find indicators of abnormal prices. I talked through some ideas and strategies with my manager and director, both of whom gave me an idea of what to search. I focused on discrepancies in bid reduction over time which involved seeing what buyers bid on different ads and whether that bid was often reduced to variable prices (as should be the case in a healthy marketplace). My search involved looking for 5 different cases:
Flat Campaign, No Bid Reduction: bid and clear price (CPM) are equal:
Flat Campaign, Consistent Bid Reduction: bid is frequently reduced to a consistent clear price:
Dynamic Campaign, No Bid Reduction: bid and clear price are equal:
Dynamic Campaign, Consistent Bid Reduction: bid reduced to a consistent clear price:
Dynamic Campaign, Periods of Competition: bid not reduced if below a certain price, otherwise competition occurs at periods of spiking bid values:
Crafting an Algorithm
Once I had a handle on the data and spotted what the trends looked like, I had to teach my computer how to find the trends on its own. In my first iteration, I wrote the main part of my algorithm. This involved running Vertica queries, determining value thresholds that would flag a tag’s behavior as indicative of a soft floor, and finally returning a data frame containing all flagged tags. From there, I added additional functions to provide any extra information not pulled from the Vertica database, such as seller name, campaign name, etc. for greater ease of use.
Building a Web App
Throughout the 10 weeks of my internship, I combed through data, interviewed various AppNexians for context, and truly felt like a data detective. My summer with CIA taught me so many new skills, provided insight into what I want my career path to be, and showed me I can solve anything.