Perhaps due to the astronomic rise of Internet usage across an increasing number of platforms, both the buy side and the sell side find it necessary to track users more specifically than ever before — by the usual demographic information but now also by device accessed (down to the model), location (down to the street corner) and social networks.
Most articles begin by pointing out the shortcomings of the old advertising world – papers, cigars and that fuzzy thing called intuition – in favor of the promised land of analytics, real-time bidding, and data mining.
For the uninitiated, these terms appear to paint the ad tech scene in a favorable light – one that is fortunately constantly evolving, and unfortunately up to its knees in fancy jargon. Clearing through the rhetorical haze requires a mind that knows what’s required to measure, manage, slice and compile the numbers that clicks, impressions, revenue and ad inventory generate every second.
What to Monitor
Any good analytics nerd will begin by first figuring out what needs to be tracked. As a hired hand on the publisher side side, at the very least, I monitor:
- Impressions
- Revenue
- CPM
- CTR
- Clicks
- Sales
All day, every day for nearly every campaign. A simple data pull from your ad server will give you the number of impressions and clicks. Data obtained from your various ad platforms should give you an accurate measure of revenue made daily, weekly, or monthly for any of your campaigns – remnant or direct. Sales information can be pulled from Omniture or whichever platform you use to track user behavior. Because CPM and CTR are derived from the aforementioned variables, they are calculated separately.
Which of these factors you monitor depends on the nature of your business. Working for a publisher? The most important variables hands down are impressions, revenue, and CPM per site section and run of site.
Analyzing for an e-commerce platform? Be prepared to go knee deep in revenue, number of sales, conversion rate, and CTR at each point in the sales funnel. On the buy-side? Along with the standard web analytics variables, agencies and brands are most concerned with ROI ([revenue made]/[money spent]) for each campaign and ([revenue made]/[money spent])/[avg unique users in a segment] for ROI per user segment.
In general, the best way to determine which aspects to monitor and analyze is to take your queue from industry trends found in blogs, Quora, conferences as well as what executives at your company want to know.
Data Maintenance
Once you have figured out what to manage, you must now figure out how to manage it. The only way I have found to effectively measure variables for more than 3,000 campaigns over time is through utilizing Structured Query Language (SQL). Notice how the previous sentence bears no mention of Excel. Contrary to the old adage that Excel is the ad tech god’s gift to quant geeks the world over, it is not.
SQL is designed to store and analyze millions of rows of data in an accessible and efficient archive. That is its only purpose in life. Excel is designed to calculate and present data in an attractive matter.
Both are useful. However, the latter should not be used to do the job of the former which regrettably happens far too often in this industry. This confusion results in spreadsheets stuffed with neverending tabs and .xlsx files that are unable to hold so much data without crashing the application.
If you want to remain relevant (and sane) in ad analytics, it is imperative that you familiarize yourself with SQL. The degree to which you learn SQL will be based on your job description. CEOs may not need to know the difference between an inner join and an outer join (although, in my dream world, they would).
Analytics and yield management folks should certainly know the difference in addition to how to create and organize relational databases, how to download CSV data files from DART, DSPs, and SSPs and upload CSVs into a SQL database, the syntax for different SQL queries, and which queries to run on which tables.
Some helpful resources to start:
Excel vs SQL (or Access) http://accesstips.blogspot.com/2011/04/im-excel-user-why-should-i-use-access.html
Database Design
http://accesstips.blogspot.com/2011/05/getting-started-with-database-design.html
http://www.tomjewett.com/dbdesign/dbdesign.php?page=tables.php
SQL Tutorial
http://sqlzoo.net/1.htm
SQL Query Syntax
http://www.1keydata.com/sql/sql.html
For when you’re stumped on SQL
http://stackoverflow.com/questions/tagged/sql
Why the Fuss?
Once the data for all campaigns have been analyzed, now what? Why go through all the trouble to learn SQL, painstakingly create tables and meticulously analyze reams of data? Analytics professionals are charged with maintaining data in order to distill new and important insights from it.
Moreover, those who have a stake in the data should always have a backup for the data. To rely on platform databases to store your data is a risk you would rather not take. Due to the changing nature of digital advertising, companies end partnerships regularly. Once the partnership ends, what happens to the historical data? For that reason alone, having a reliable backup in a proprietary database is wise.
You do not need to be a serious data geek or a mathematical genius to become fluent in SQL. All it takes is an analytical mind, a good amount of curiosity, passion for the possibilities of digital advertising, and a bit of help from a kind nerd on your company’s programming team.