The Importance of Simple Query Optimization

After my customary, inexplicable year off from posting, I decided that the best way to get back on the horse is with a quick note about how really minor tweaks to SQL queries can make a huge difference in performance. This issue came up while I was doing some preliminary tinkering with a potential recommendation engine-style project, and I thought that it was a really good example, especially for people who are new to SQL or are starting to do more complex queries against relational databases, but haven't had to worry about execution time too much yet, either because the database is small, or it isn't code that will run in production, or what have you.

Below you will see screenshots of two nearly identical queries, which serve to basically just identify the most common video series viewed by users who have watched one other series. You could simplify the concept by saying, 'Users who watched X also watched Y' (and we only want the top five results).

Version 1:

Version 2:

Now, if you look in the lower right corner of each of these, you will see how long the query took to execute, and notice immediately that the Version 1 query took 5x as long as the Version 2 query.

(Note: 105 seconds is brutal, but 21 seconds is still too slow for most applications, but we are focused on relative difference for now. This was querying a PostgreSQL database, but running the same query against an Amazon Redshift db took less than 5 seconds!)

The results are the same for both, so what's the difference here? If you look at the middle sub-query, aliased here as '_other_views,' you will notice that in Version 2, there is a GROUP BY clause that is missing from the first version. Why does it make it so much faster? Think about what this query is trying to accomplish by segment.

First segment - identify all users who watched series with an ID of '1688' since [date]

Second segment - of those users, identify each OTHER series that every one of them has watched since [date]

Third segment - get a count of how many users have watched each other series, order them from most viewers to fewest, and take the top five

That second segment is the tough one. Looking at a longer time period, with potentially thousands of users watching thousands of series dozens of times each, we are still performing a search over millions of rows in the database. In the first query version, however, for each instance of an individual watching a series we are creating a row in the temporary table _other_views, but the reality is that we only care about the first instance of viewing each series (this is also a place we could consider using an 'EXISTS' statement).

Instead of :
Person 1 :: Series 1 [:: View 1]
Person 1 :: Series 1 [:: View 2]
Person 1 :: Series 1 [:: View 3]
Person 1 :: Series 2 [:: View 1]
Person 1 :: Series 2 [:: View 2]
etc.,

We just want:
Person 1 :: Series 1 [:: View 1]
Person 1 :: Series 2 [:: View 1]
etc.

And that's what the GROUP BY in that sub-query achieves. It makes sure that we don't create duplicate rows of each combination of User/Series in our temp table, that the final SELECT statement then has to churn through in order to get the count of distinct viewers for each series.

Another way to demonstrate the difference is to only run the first two sub-queries, in order to see how big of a result set your final SELECT is forced to sift through:

Version 1 (without GROUP BY) - 3,628,502 records returned

Version 2 (with GROUP BY) - 11,903 records returned

So just that one little change in our middle sub-query results in an immense dimension reduction of the result set, with over 300x fewer records to count and order by in the final step. Both ways are technically 'correct' in that they will execute fully and return the same output, but it's a great example about how a little attention to what's actually going on under the hood can pay huge dividends in performance.

Own Your Data (Or at least house a copy)

There is a common trope among data analysts that 80% of your time is spent collecting, cleaning, and organizing your data, and just 20% is spent on analyzing or modeling. While the exact numbers may vary, if you work with data much you have probably heard something like that, and found it to be more or less true, if exaggerated. As society has moved to collecting exponentially more information, we have of course seen proliferation in the types, formats, and structures of the information, and thus the technologies that we use to store it. Moreover, very often you want to build a model that incorporates data that someone else is holding, according to their own methods that may or may not mesh well with your own.

For something as seemingly simple as a marketing channel attribution model, you might be looking at starting with a flat file containing upwards of 50-100 variables to start, pulled from 10+ sources. I recently went through this process to update two years' worth of weekly data, and it no joke took days and days of data prep just to get a CSV that could be imported into R or SAS for the actual analysis and modeling steps. Facebook, Twitter, Adwords, Bing, LinkedIn, YouTube, Google Analytics, a whole host of display providers... the list goes on and on. All of them using different date formats, calling similar/identical variables by different names, limited data exports to 30 days or 90 days at a time, etc..

Obviously, worth the effort for a big one-time study, but what about actually building a model for production? What about wanting to update your data set periodically and make sure the coefficients haven't changed too much? When dealing with in-house data (customer behavior, revenue forecasting, lead scoring, etc.) we often get spoiled by our databases, because we can just bang out a SQL query to return whatever information we want, in whatever shape we want. Plus, most tools like Tableau or R will plug right into a database, so you don't even have to transfer files manually.

At day's end, it quickly became apparent to me that having elements of our data, from CRM to Social to Advertising, live in an environment that I can't query or code against is just not compatible with solving the kinds of problems we want to solve. So of course, the next call I made was to our superstar data pipeline architect, an all-around genius who was building services for the Dev team running off our many AWS instances. I ask him to start thinking about how we should implement a data warehouse and connections to all of these sources if I can hook up the APIs, and he of course says he has already not only thought of it, but started building it. Turns out, he had a Redshift database up and was running Hadoop MapReduce jobs to populate it from our internal MongoDB!

So with that box checked, I started listing out the API calls we would want and all of the fields we should pull in, figure out the hook ups to the third party access points. Of course, as we have an agency partner for a lot of our paid media, that became the biggest remaining road block to my data heaven. I schedule a call to the rep in charge of their display & programmatic trade desk unit, just so we can chat about the best way to hook up and siphon off all of our daily ad traffic data from their proprietary system. After some back and forth, we finally arrive at a mainly satisfying strategy (with a few gaps due to how they calculate costs and potentially exposing other clients' data to us), but here is the kicker:

As we are trying to figure this out, he says that we are the first client to even ask about this.

I was so worried about being late to the game, that it didn't even occur to me that we would have to blaze this trail for them.

The takeaway? In an age of virtually limitless cheap cloud storage, and DevOps tools to automate API calls and database jobs, there is no reason that data analysts shouldn't have consistent access to a large, refreshing data lake (pun fully intended). The old model created a problem where we spend too much time gathering and pre-processing data, but the same technological advances that threaten to compound the problem can also solve it. JSON, SQL, Unstructured, and every other kind of data can live together, extracted, blended and loaded by HDFS into a temporary cloud instance, as needed.

The old 80/20 time model existed, and exists, because doing the right thing is harder, and takes more up-front work, but I'm pretty excited to take this journey and see how much time it saves over the long run.

(Famous last words before a 6 month project that ultimately fails to deliver on expectations; hope springs eternal)

What do you think? Have you tried to pull outside data into your own warehouse structure? Already solved this issue, or run into problems along the way? Share your experience in the comments!

The Trouble With Stats (or the people who pretend to use them)

My father also used to enjoy telling me "figures don't lie, but liars figure." He was essentially trying to explain why people often don't trust those who use numbers to explain things that don't naturally appeal to our (highly flawed) instincts. There is a Simpsons quote along similar lines, but for those of you who are fans, you already know it, and for anyone else, it would be a waste of time.

There is another, larger problem, however, that also damages the reputation of statistical analysis and the things it can tell us about our world. It's when people know/do just enough to be dangerous, and without basic rigour produce findings that are unsupportable. When they spread those findings, people who don't have a strong understanding of the analytical process tend to take them at face value, and then end up being let down later. Rather than blaming the particular culprit who practiced the bad stats, they just blame stats in general. These bad actors aren't always guilty of malice, but it doesn't absolve them of crimes against science, and there is a perfect example on ESPN.com today.

Obligatory picture of Nomar, 'cause baseball!

Check out the article here (it's an Insider article, so if you aren't already behind the pay wall, you won't be able to read it). To summarize, Mr. Keating is trumpeting the value of a simple composite stat, runs per hit (R/H from now on), as some sort of gem in terms offensive value, in both real and fantasy terms. He begins, ironically enough, by pointing out that "many sabermetricians [statisticians who study baseball] barely glance at stats like runs and RBIs, which depend heavily on a player's offensive context" (which is true), and then immediately goes on to make a vague declaration about other skills he must have (not true).

Keating proceeds to provide a handful of historical examples of other players with a high R/H, in fun anecdotal fashion. Fully three of the seven paragraphs in the article don't talk about the particular player that it focuses on, Brian Dozier of the Twins, and even the ones that mention him hardly focus on him. The examples provided vary widely across run environments, from the height of the power-infused steroid era, to pre-WWII baseball, with no accounting for what the differences in offensive numbers looked like at those times.

Finally he cherry-picks a few contemporary data points that support his assertion, none of which are particularly relevant to the point that he is making (and in some cases, contradictory).

At no point does he cover anything like the methodology he used to arrive at his conclusions, or even imply that there was any methodology, which might be more disturbing. Here is one thing I know about people who appropriately use statistics: they love to share the gory details.

Now, I will admit, I had a little prior experience with this particular stat, because I had looked into it a few years ago, and ultimately rejected it as useful. However, context always matters, so it was worth another look. One of the first things that Keating does in the article is subtly undermine sabermatricians and complex statistics, which is curious for a guy who's bio at the bottom of the page says that he covers statistical subjects as a senior reporter. Basically, what he says is that R/H is a stat "...that is so simple you can calculate it in your head and it'll tip you off to hidden fantasy values" (I challenge both parts of that statement).

He mentions BABIP later on without any explanation, so he is assuming a certain amount of sophistication on the part of his readers, which is understandable since most fantasy enthusiasts have more exposure to statistical analysis than an average baseball fan. Certainly, anyone interested in "new" composite stats would also be familiar with OPS, and quite possibly wOBA and wRC as well, so the assumption has to be that R/H offers something that these other stats don't.

This is where the whole thing falls apart under scrutiny. Keating says that R/H is a good proxy for identifying other skills like speed, walk rate, and power, but all of those other stats I just mentioned do the same thing, but the difference is, they actually work. Depending on your analytical preference (or which site you get your baseball fix from), you might choose to believe in OPS, wOBA, or wRC (or base runs, etc.), but the reality is, they are all pretty good, and so they correlate very highly to one another (r values of above .95 between all of them). R/H, on the other hand, correlates with none of them, not even close.

Of course, those are all stats meant to capture real-world offensive value in a context neutral way, and maybe R/H only works for fantasy value. So the obvious thing to do would be look at R/H against ESPN fantasy player values. Unfortunately, once again, R/H has almost no correlation to the player rater values (whereas the fantasy rater correlates really well with those other stats).

"Wait," you might (and should) say, "the fantasy player rater is going to be heavily weighted towards counting stats, and thus might not be appropriate when comparing against a weight stat like R/H." Sadly, you, like Mr. Keating, would be wrong again, as Dozier was 7th in the majors in plate appearances (PA) among qualified batters, so he actually has an unfair advantage in this case.

The truth is, of players who had enough plate appearances to qualify for the batting title last year, 4 out of the top 5 by R/H were not in the top 30 fantasy players, and only 4 of the top 15 made the cut. Most of whom were not even guys with an balanced speed/power/discipline profile, like Dozier (which Keating says the stat should identify), but power hitters like Donaldson, Rizzo, and Bautista. If you look at wRC+, every single one of the top-15 players was in the top 40 by fantasy value, and has an r value twice as high as R/H (.71 vs .31).

A common way to look at how much noise might be in a particular stat is to look at year-over-year correlation, and I will say that for players with at least 350 PA in both 2013 and 2014 there was a slight correlation. At just below an r value of .5, however, it wasn't particularly strong, highlighting just how much luck goes into this formula by including runs, and frankly, this number would probably change by looking at more years (I admit to not going deeper here, but I will point out the failing if someone wants to look into it).

Bottom line: runs per hit is not a great stat over all, and it is not a great stat in the context of fantasy baseball value. I won't venture to say that Keating did this analysis and decided to hide the results from his readers, nor will I say that he was too lazy to do any analysis, but I do know that it took me all of thirty seconds to start finding problems with the assertion. As someone who plays fantasy baseball, I have no interest in giving my competition an advantage, but I also can't figure out how this kind of misleading "analysis" does any good for the field.

The issue isn't just that the number doesn't say what Keating claims it does, because it will, by the nature of its component parts and some of the reasons he gives, have some relationship with the skills he is trying to identify. The issues are that it does so very inefficiently due to flaws in the components used, that we already have much better stats for highlighting the same information. The last, biggest issue is that despite being at least somewhat aware of these flaws, Keating is touting this approach nonetheless, under the guise of statistical analysis, without providing any evidence in support.

Dozier is a good player, and has fantasy value, but anyone using R/H to evaluate players is going to be disappointed in their fantasy season. It is neither predictive nor descriptive of a player's skill, and shouldn't be used that way. The problem is that by placing this article behind the pay wall of an authority like ESPN, the false premise is given a credence that it doesn't warrant, and undermines statistical analysis as a whole.

Putting the 'Fun' in 'Logistic FUNctions!'

So, this weekend I was playing around with R to graph some data sets and get the variables that go into the formula for doing logistic modeling.  While trying to figure out some f(t) or t-values given the inputs, I was annoyed at calculating the results by hand, even if I was able to get the variables from R.  It's pretty normal math for this kind of work, and it's good to know how to do it manually step-by-step, but eventually the fun wore off, and I just wanted to get it done.

Anyhow, I built a "Logistic Function Solver" aka calculator in Excel, and figured I would share it on the off-chance anyone else needs such a thing and doesn't feel like taking the time to build one.  It's already been useful at work.

Here it is

Anyhow, you should be able to access the file on Google Drive with that link, and then make a copy for yourself.  Let me know in the comments if this doesn't work for you.

Basically, cells with the light yellow background and green text are ones in which you should enter your values, and then your desired output will be in the green background with red text.  (Leave the other cells alone, they have formulas)

Enjoy!

Marketing & Data Priorities for a New Business

Note: Apologies, some hiccup with Blogger caused an old, unfinished version of this to be posted.  I think I captured the gist of it, and filled in the missing words.

In my career, I have worked with some very large, mature companies while on the agency side, and in-house with a small-to-medium sized business, though closer to small at the time when I joined.  I bring this up because a colleague of mine recently decided to leave our thriving enterprise to join a start-up, as one of the 'first five.'  I think that everyone considers what it would be like to work for a company from the very beginning, no matter his or her profession, and so it got me thinking about what I would do in that position.

I would guess that for many people, myself included, the attraction of working for a start-up as an early employee isn't about the beer cart, the foosball Fridays, or even the stock options, but about the ability to come in and practice our particular craft on a tabula rasa.  Deep down, almost everyone who is good at what they do thinks that they could be a little bit better if not burdened by the ghosts of business past.  Every company evolves over time, but like a city, new growth is inevitably on top of the old, no matter the lengths that you go to clear the site.  I've never met a developer who hasn't been frustrated by old code, an SEO who understands why a website was built a certain way, or a DBA who would design a database the way he found it.

Obviously, I think that it probably goes without saying that most of these legacy problems are not the result of incompetence, but rather a combination of a lack of foresight, and the normal "things that happen" over time.  We can assume that most of the people who build the legacies that the rest of us inherit have good intentions, but lack the luxury of building a foundation that will stand the test of time.  The key, for our hypothetical selves at start-ups as it was for our real-life predecessors, is to wed the long-term concerns with the immediate business needs of the fledgling company.

So as a thought exercise, here are some considerations and strategies that I would prioritize if I found myself moving to a brand new company:

Advertising:
Think about your target audience, and how they gather information.  Chances are, budget will be an issue, so the most important things will be efficiency and extremely narrow targeting.  There is always a natural progression of one platform at a time, but that's a mistake.  The evidence all points to a multi-platform strategy from the beginning.  I hate to fall back on a cliche like "synergy," but it is easier than actually explaining the math.  The point is, launch your content, social, paid social, and paid search all at once, even if you only do a limited amount of each one.  Coordinate them, because they amplify one another, and you will maximize the effect of your spend.

• Know your audience
• What need does your product fill, who has that need?
• If you had that need, how would you go about satisfying it? Try it.

• Make sure that you have a keyword strategy that looks beyond CPC to CPA/CPE or whatever your target user action is
• Note that this means you will have to track everything FROM THE START

• Know your budget, and what will fit into it
• Set some money aside for testing, maybe 10% at first
• Start with very tight keyword groups, be proactive with matchtypes and negative keywords, and watch any GDN or YouTube spend carefully
• Careful targeting is better for conversions and budget, so start with only your own country

Content:
Produce as much of it as possible, make it valuable, don't make it sell your own product.  Mix your mediums, make sure that you think about the life cycle of each piece, and how you can distribute it.

• Ensure that you have a place for content to live permanently, preferably on your site.  You want a link that can live forever
• Use your content as a source of advertising material, from keywords to messaging.  If you find yourself saying something a lot in your content, it's probably important in your industry

SEO:
This is one is simple on the face of it, but also one of the easiest matters for a new company to overlook and one of the hardest things to plan for as it grows.  Generally speaking, your best bet is to everything right, but here are a few ideas that you can start with:

• Make sure that every page that has clear focus
• that you explain that focus on that page with at least a few hundred words worth of text
• and that you repeat that focus very concisely in the metadata  
• Make sure that your URL structure is logical 
• clear hierarchy in the sitemap based on importance of the page
• hyperlink deeper pages to appropriate higher-level nav pages using relevant anchor text

Data Collection:
Set yourself up with a Google Analytics account immediately.  It doesn't matter if you plan to use another web tracking platform down the road, you want to make sure that you are measuring out of the gate.  This is free, and it will join to your AdWords account to give you significant targeting benefits.

• Don't just stick to the out-of-the-box defaults with GA, spend the extra time to make it fit your needs
• Activate ecommerce tracking if need be, and add the little bit of code (seriously, like one line) needed to gather demographic data
• Add events and goals to customize your map of the customer journey
• are there key pages you want to track? forms? videos?
• think as you build your site, about every key interaction you will have with visitors from discovery through whatever signifies success, and be ready to track each step

Don't feel limited to Google Analytics for your data collection and housing, either.  It's great for one part of the interactions you will have, but there is more to your business than your website, and you will want information from other sources as well.  MS Access will do if you have loads of CSV files or similar data that will overwhelm Excel, but eventually you may need to move to a SQL database, or other RDB.  Maybe you want to skip that, and go straight to NoSQL, given the direction that the business work is moving in.  With AWS and Google Cloud, storing data in a non-relational manner and then accessing it freely with MapReduce jobs is becoming easier and cheaper every day.

The point is, track everything.  Collect all of the information that you can from the get go, because you will always think of things down the line and wish that you had tracked them all along.  Starting with day one, you will be asking questions about your customers and your business that will have profound effects on your marketing strategy and execution.  Give yourself every chance to make informed decisions whenever possible.

There may be more to follow this, but I wanted to get it out before the new year.  Good luck in 2015, especially if you work at a new business!

Don't limit yourself

Top 5 Skills for the Modern Marketer/Data Analyst

[Skip to the bottom if you just want the top 5 list]

Over the years that I have been in digital marketing and analysis, I have been constantly shocked by the gaps and deficiencies that I have found in not only my own, but the entire industry's skill set.  When I first began as a lowly search specialist, I came in with nothing more than a decent understanding of how organic search engines worked, a basic familiarity with Excel, and a passionate, though amateurish, interest in statistical theory.  Within three months, my relevant knowledge base had expanded exponentially, but still I felt that I lacked useful skills, and frankly, that most people in the industry did as well.  I have been trying to rectify that situation ever since.

Right off the bat, I was amazed at how little rigorous statistical analysis was being applied to SEM and other digital media buying and planning channels, given the volume of available data that was being (or could be) collected.  This was manifest not only in the proportion of data analysts to account team members (which was very low at the time), but also in the absence of fundamental conceptual understanding of statistics held by the marketers themselves.  It was naive of me to think that every account team would have a dedicated analyst (though I had assumed as much before my first day), but even at the time I thought that some rudimentary education on the theory and practice of utilizing data sets should be a prerequisite for a digital marketer.

Even more simply, I realized quickly that my Excel proficiency was not where it needed to be, or at least not at a point that my work couldn't be substantially improved by getting better with spreadsheet applications.  What I thought I had known about Excel (still one of my all-time favorite human inventions) was a drop in the bucket compared to what I felt like I ended up needing, but as I developed those skills I was once again shocked by their conspicuous absence from the average marketer's tool kit.  The number of people in our office who really knew Excel, and could maximize the efficiency of its capabilities, was limited to single digits, even though it is the bread and butter of any search marketer.  From there, overly large data sets led me to need to use MS Access, a program which even fewer people were qualified to use, causing all kinds of missed opportunities and bottlenecks.  While most people in every office that I have ever worked in tend to just seek out those who have that knowledge when they need it, very few companies require, or even encourage, widespread acquisition of information and skills that are borderline critical to the work their employees do.

When tagging and tracking issues came up (and they always do), I found myself frustrated by the gate-keepers and communication disconnects that exist between marketers and IT/website maintenance teams, so I realized that I would have to understand (at least at a rudimentary level) HTML, and then JavaScript.  I had to learn more principles of SEO at times, which also required understanding of those basic web development languages.  I had to understand other marketing channels to really see interactions, I had to understand offline sales processes to gain insights into lead generation marketing, which meant that I had to first learn about CRM pipelines, and then CRM platforms like Salesforce and Hubspot.  As the lines between social, paid social, content, and SEO blurred, I had to approach each subject in turn; in order to understand any one of them I had to understand all of them.  To understand what my data meant I needed to know all of the data that was collected, so I had to learn about databases.  In order to make use of the databases, I had to learn SQL.  I'm so far from where I started, and yet still so much further still from where I need to be.  I will never have enough knowledge and understanding to do my job as well as I think I should.

But at every step in my career I have been surprised to see just how many people in the industry lack not only the skills that I have been seeking, but even the awareness of the roles that they should play, within the agency world and without.  For so many years everything was siloed in terms of labor division that marketers (and really, everyone in business) came to believe that the world outside of their specific responsibility was segmented this way as well.  There is this common theme in the industry today that those walls are finally breaking down, that channels are at long last interacting and that the ecosystem has finally become diverse and highly dependent, but this is a false concept.  The ecosystem has always been complex, and the fact that we are finally starting to recognize it doesn't excuse us from responsibility for the gaps in the past, nor the continuing specification of skills moving forward.

A search marketer can't get away with simply knowing the AdWords and Bing platforms anymore, or at least shouldn't be able to in your workplace.  Would you want someone in charge of a campaign that doesn't understand how the tracking codes work in a jquery library?  Do you want someone presenting to clients or superiors not only raw information, but conclusions and insights, who doesn't understand sampling concepts, or how to differentiate between correlation and cause?  How can a marketer assess the value of a user action without understanding the offline sales process, or the difference in the consumer journey for B to C versus B to B?

For so long digital marketers were like Oz, we claimed to be wizards and got away with it because no one looked behind the curtain.  People finally looked behind curtain and found that in fact, it was all done with machines, and they were actually fine with that, because we said we were running the machines expertly.  The problem is that now digital marketers are often demonstrated to simply be the people standing next to the machine, with no more understanding of how it works than those who were on the other side of the curtain.  In order to stay relevant, we all need to not only be able to read the outputs, but understand and interact with the inputs as well.  The world is changing fast, and education, in any form, is the only path to relevance.

So to sum this up into a top-five list (because that's what the internet wants), here we go:

Top 5 Skills for Every Data-Driven Marketer

1.) Microsoft Excel (custom sorting, formulas, pivot tables)

2.) Basic Statistical Theory (samples size & significance, correlation vs causation, variance & standard deviation)

3.) CRM Process/Offsite Interaction (digital is not a separate realm, it is part of the broader business we engage in)

4.) Minimal HTML, JavaScript knowledge (metadata tags, H1s, how API calls work, tagging intricacies & common problems)

5.) SQL/RDB Querying (pick one, MYSQL, PostgreSQL, even NOSQL, it doesn't matter; maybe learn R or Hadoop if you want to get fancy)

Don't Fight the Data, Marketing People

Why do people find it so hard to change their behavior, despite evidence that they have been operating under assumptions that turned out to be false?  Maybe I have simply been lucky enough to spend much of my life in a world where data flows in a nearly unlimited stream, to be recorded and calculated by computers.  Answers to questions, and more numbers and information than we will ever have time to analyze are never more than a few clicks away at this point.  Yet still, once an idea has become entrenched, it is surprisingly hard to pry it loose.

An example which immediately shows my bias is the difficulty in getting companies to recognize the value, or even the necessity of devoting a portion of their marketing dollars to paid search, or even general digital advertising.  Plenty of studies have been done demonstrating the beneficial effects of having both online and offline media, or how search captures the interest generated by television, etc..  Nielsen itself, the king of measuring TV advertising reach and effectiveness, has produced these studies, even specifically calling for TV dollars to be re-allocated to online media for a more efficient marketing mix.  Yet still, in the face of overwhelming statistical evidence, marketing professionals who are used to traditional media don’t acknowledge what’s right in front of them.

The best parallel that I can draw comes from baseball, which in many ways reminds me of search marketing.  In both cases, compared to either other sports or other media channels, baseball/search is much more easily measured and quantifiable.  Between engine data and onsite tracking, the consumer journey through search can be quantified as a series of discrete actions, just like a game of baseball.  Looking at football or TV commercials, there are just too many variables to accurately attribute the effect of any one moving part.  With baseball however, like search, every play is recorded, and has been for a hundred years.

Where am I going with this?  Bunting.  I have made this speech a million times, but I will make it again for the one person who reads this who hasn’t heard it before.  If you watch enough baseball, especially in the NL, chances are you have seen a fair few sacrifice bunts in your time.  It has been a respected tool in the managers toolbox for a long time, and is celebrated by baseball purists everywhere.  The problem is that it is a bad decision, and it turns out that it always has been.

With so much available data, it turns out that you can really learn a lot about a subject.  By using linear weights based on every single play over a given period of time, you can create a very accurate run expectancy matrix (it turns out that you want a sample size of at least 3 years to get a model that follows the real results closely).  What this matrix does is tell you what the expected number of runs a team will score on average in any given game state (i.e. ‘a man on second base with two outs’).

Brass tacks: With a man on second and no one out (the most common, and conventionally ‘best,’ time for a sacrifice bunt) the run expectancy is around 1.166, which means that a team in that situation will on average score 1.166 runs in the inning.  With a runner on third and one out, which would be the result of a successful sacrifice bunt, the run expectancy is 0.976.  So if the sac bunt is executed perfectly and all goes according to the manager’s plan, he has created a situation in which his team’s run expectancy has dropped by 16.3%.  Keep in mind, that’s about the most defensible situation in which to bunt.  When starting with one out, or a man on first, the decline becomes much sharper.

Now, I am not saying that no one should ever under any circumstances order a bunt.  There are a variety of factors that come into play and they all need to be considered.   My point is simply that most of the time when you see a sacrifice bunt it is the product of a mindset that simply refuses to acknowledge the value of the data.  When no one had done the math it was an interesting argument, and now it just seems quaint and a little backwards, like chewing tobacco or fights where no one gets punched.

This is what I think of when companies believe that they are doing fine because they run some TV commercials and newspaper inserts.  A study found that about 90% of the CPG (consumer packaged goods) target audience watches TV, and about 81% go online.  Yet about 80% of CPG companies’ marketing dollars go to TV ads, compared to about 3% spent on OLA (online advertising).  

Why do people and industries insist on being behind the curve?  Not wanting to fall for a fad is one thing, but this here internet isn’t exactly new, or a flash in the pan.  In 1915, you could make a case for building horse carriages, in case those darn automobiles didn’t catch on.  In 1940, not so much.  What about the guys who swore that the talking pictures would never replace radio dramas?  Or dinosaurs that figured mammals were never going to be a big deal?

Not knowing something because the information wasn’t available to you is one thing, but willfully ignoring the data that is right in front of you is another.  Consumers are doing everything online, so be there when they are. 

And quit bunting so much.