Every year, millions of Americans sit down to watch the highly-anticipated Super Bowl. Accompanied by an action-packed NFL football game, funny and emotional commercials, and a halftime show that generally involves celebrities and Hollywood icons, the Super Bowl brings people together from all walks of life for one night of relaxation and entertainment.
Our focus for this project is to direct our attention to the advertisements, specifically the thirty second ads that are played throughout the game during commericals and other airtime breaks. These ads are widely regarded as some of the most expensive TV network slots on the planet, and the prices of these advertisements show just that.
The aim of this project is to observe the relationship between the cost of these thiry second ads over the course of the entire history of the Super Bowl. We want to know, is it worth it to buy these ads? Is that value of these advertisements worth the cost? Let's begin diving into it!
We are observing whether it’s worth it for companies to buy an ad for the NFL’s Super Bowl. To assess this accurately, we look at statistics related to cost per thousand impressions (CPM), viewership data, and ad cost for all Super Bowl events held between 1966 and 2019. By looking at these specific sets of data, it will help us deduce important info about which years were best for companies to buy an ad, which years were not as worth it, and if there are any outliers.
Before we begin getting into the data, let us first propose an initial hypothesis. The hypothesis is that despite inflation surrounding the U.S. dollar, we believe that if the costs of advertisements throughout the Super Bowl's history were to account for inflation of today's U.S. dollar value, there would be no change in the CPM (cost per thousand views). We are using CPM as our measurement of advertisement effectiveness, since a lower-cost CPM ad is more effective for companies than higher-cost CPM ads, since those companies would save money and be able to produce the same marketing and promotional benefits.
We import the following libraries to get, use, and manipulate the data from an internet source. With these libraries, we store, graph, plot, and showcase the data to find useful info and visually represent things for user simplicity.
The usual python data science libraries used are Requests, BeautifulSoup, Pandas, Numpy, Datetime, Math, String, and Matplotlib.
We also utilize Stats Models which is used later on in the project with hypothesis testing.
Additionally, we install a library called Easy Money and utilize the EasyPeasy() import, which will help us later on in the project when we're going to want to reproduce the ad costs to account for inflation.
# Uncomment the line below each time the Kernel restarts
#! pip install easymoney
from easymoney.money import EasyPeasy
import requests as reqs
from bs4 import BeautifulSoup
import pandas as pd
import numpy as np
import datetime
import math
import string
import matplotlib.pyplot as plt
import matplotlib
from matplotlib.ticker import ScalarFormatter
import statsmodels.formula.api as sm
Here, we first fetch our data from the superbowl-ads.com URL listed below. This will provide us with all the necessary data we will need to draw conclusions for our hypothesis.
After requesting and getting the URL page's HTML code, we utilize Beautiful Soup and get the text.
URL = "https://superbowl-ads.com/cost-of-super-bowl-advertising-breakdown-by-year/"
page = reqs.get(URL)
soup = BeautifulSoup(page.text)
Now, we have the page's HTML in our "soup" variable, and need to begin searching for the data set on the website page. This can be done easily by using Beautiful Soup's find() method, which gives us the exact table that we need.
Afterwards, the page is scraped by searching for all the rows within the table that we previously scraped, and each row is appended to a temporary two-dimensional array called result.
Finally, we remove the top row from the table because it is unnecessary, and then save the new top row as columns_title, before removing that row as well.
table = soup.find('table' ,attrs={'border':'0'})
table_rows = table.find_all('tr')
result = []
for tr in table_rows:
td = tr.find_all('td')
row = [tr.text for tr in td]
if row:
result.append(row)
result.remove(result[0])
column_titles = result[0]
result.remove(result[0])
All that's left is to put the result into a Pandas Data Frame with the column_titles variable as the Data Frame's column titles.
df = pd.DataFrame(result, columns=column_titles)
df.head()
| Super Bowl | Season | Game Date | Network | Rating | Share | Avg. Cost Per 30-Seconds | Avg. Number of Viewers | |
|---|---|---|---|---|---|---|---|---|
| 0 | LIV | 2019 | Feb 2 2020 | FOX | 41.6 | 69 | $5,600,000 | 99,900,000 |
| 1 | LIII | 2018 | Feb 3 2019 | CBS | 41.1 | 67 | $5,200,000 | 98,477,000 |
| 2 | LII | 2017 | Feb 4 2018 | NBC | 43.1 | 68 | $5,235,000 | 103,400,000 |
| 3 | LI | 2016 | Feb 5 2017 | FOX | 45.3 | 70 | $5,400,000 | 111,300,000 |
| 4 | L (50) | 2015 | Feb 7 2016 | CBS | 46.6 | 72 | $4,800,000 | 111,900,000 |
The table on the site we pulled our data from contains a lot of information, some of which we do not need. We started by taking in all the data in the table, but now we'll be condensing it down to what we want.
First, the Data Frame is re-indexed such that it begins from 1 and not from 0. This will help us later on with organization.
Next, we drop all the columns that we do not need. This includes the columns 'Super Bowl', 'Game Date', 'Network', 'Rating', and 'Share'. Once those are dropped, the data remaining is all we'll need for further evaluation.
A little bit of organization is needed before we explore the data. The columns, 'Avg. Cost Per 30-Seconds' and 'Avg. Number of Viewers' need to be numeric, not strings like the way they came. The commas and dollar signs are removed from these two columns and the results are converted into integers. These values are then put back into the data frame.
Finally, for simplicity and clairty, the columns are renamed to 'Year', 'Cost', and 'Views'.
df.index = np.arange(1,len(df)+1)
df.drop('Super Bowl', inplace=True, axis=1)
df.drop('Game Date', inplace=True, axis=1)
df.drop('Network', inplace=True, axis=1)
df.drop('Rating', inplace=True, axis=1)
df.drop('Share', inplace=True, axis=1)
for index, row in df.iterrows():
row["Avg. Cost Per 30-Seconds"] = row["Avg. Cost Per 30-Seconds"].replace(",", "")
row["Avg. Cost Per 30-Seconds"] = row["Avg. Cost Per 30-Seconds"].replace("$", "")
row["Avg. Cost Per 30-Seconds"] = int(row["Avg. Cost Per 30-Seconds"])
row["Avg. Number of Viewers"] = row["Avg. Number of Viewers"].replace(",", "")
row["Avg. Number of Viewers"] = int(row["Avg. Number of Viewers"])
df.columns = ['Year', 'Cost', 'Views']
df.head()
| Year | Cost | Views | |
|---|---|---|---|
| 1 | 2019 | 5600000 | 99900000 |
| 2 | 2018 | 5200000 | 98477000 |
| 3 | 2017 | 5235000 | 103400000 |
| 4 | 2016 | 5400000 | 111300000 |
| 5 | 2015 | 4800000 | 111900000 |
The last thing needed for this data to be in the correct form for usage and evaluation is reversing its order, such that the first row is from the year 1966 and the last row is 2019.
# reverse the Data Frame to begin with the oldest date
df = df.iloc[::-1]
# re-organize the indexes starting from 1
df.index = np.arange(1,len(df)+1)
df.head()
| Year | Cost | Views | |
|---|---|---|---|
| 1 | 1966 | 37500 | 24430000 |
| 2 | 1966 | 42500 | 26750000 |
| 3 | 1967 | 54500 | 39120000 |
| 4 | 1968 | 55000 | 41660000 |
| 5 | 1969 | 78200 | 44270000 |
The data is now properly organized and ready to be analyzed.
As a summary, here is what the data represents:
In this section, we will graph several different relationships. Namely, having to do with the average cost of ads, average number of views, CPM, and inflated cost values.
# only views bar graph
df = pd.DataFrame(df)
name = df['Year']
price = df['Views']
# Figure Size
fig = plt.figure(figsize =(50, 35))
plt.ticklabel_format(style='plain', axis='y')
# Horizontal Bar Plot
plt.bar(name, price, color='orange')
plt.xticks(rotation=90)
plt.xlabel("Years", fontsize=30)
plt.ylabel("# of Viewers", fontsize=30)
plt.title("# of Viewers in Relation to Year", fontsize=30)
plt.grid("On")
# Show Plot
plt.show()
We first look at viewership compared to the years of the Super Bowl and create a bar graph. We can see a generally increasing trend, where viewership has increased over time. But there are surely some drops. In 2018, for example, there was a decline in Super Bowl ratings due to players kneeling for the national anthem and fans not tuning in because they disagreed with those actions during the entire NFL season.
Source: (https://www.washingtontimes.com/news/2018/feb/6/nfl-ratings-down-due-anthem-protests-survey/)
# only costs bar graph
df = pd.DataFrame(df)
name = df['Year']
price = df['Cost']
# Figure Size
fig = plt.figure(figsize =(50, 35))
plt.ticklabel_format(style='plain', axis='y')
# Horizontal Bar Plot
plt.bar(name, price, color='blue')
plt.xticks(rotation=90)
plt.xlabel("Year", fontsize=30)
plt.ylabel("Cost", fontsize=30)
plt.title("Cost of Advertising in Relation to Year", fontsize=30)
# Show Plot
plt.show()
For this bar graph, the cost for ads increased as time passed on. And there were no huge spikes or drops.
_ = df.plot(kind='bar', secondary_y='Views', figsize=(50,35))
ax1, ax2 = plt.gcf().get_axes()
ax1.legend(loc='upper left', title='Avg. Cost for Advertisement')
ax2.legend(loc='upper right', title='Avg. Number of Viewers')
ax1.set_title('Avg. Cost of Advertising in Relation to Avg. Viewership', fontsize=45)
ax1.set_ylabel('Avg. Cost of 30 Second Advertisement', fontsize=30)
ax1.set_xlabel('Year of Season', fontsize=30)
ax2.set_ylabel('Avg. Number of Viewers', fontsize=30)
plt.xticks(df.index,df["Year"].values)
ax1.ticklabel_format(style='plain', axis='y')
ax2.ticklabel_format(style='plain', axis='y')
plt.rcParams.update({'font.size': 22})
plt.show()
We now look at the cost of ads with the viewership and see the rate at which average cost increases is much higher and faster than that for the viewership numbers. The companies are spending more money but getting essentially the same return because the viewers are not increasing at such a high rate. This is not that great for companies looking to get a better return.
Below, we utilize the EasyPeasy() function to help us inflate the cost values from each year of the Super Bowl.
ep = EasyPeasy()
inflatedDF = df
inflatedCol = []
for index, row in df.iterrows():
intCost = int(row["Cost"])
intYear = int(row["Year"])
euro_value = ep.normalize(amount=intCost, region="US", from_year=intYear, to_year="latest", pretty_print=False)
adjusted_us_value = ep.currency_converter(amount=euro_value, from_currency="EUR", to_currency="USD", pretty_print=False)
inflatedCol.append(adjusted_us_value)
inflatedDF["Cost"] = inflatedCol
inflatedDF.head()
| Year | Cost | Views | |
|---|---|---|---|
| 1 | 1966 | 299011.63 | 24430000 |
| 2 | 1966 | 338879.83 | 26750000 |
| 3 | 1967 | 422839.13 | 39120000 |
| 4 | 1968 | 409236.63 | 41660000 |
| 5 | 1969 | 551722.84 | 44270000 |
We implement inflation here to get the real-value numbers and stats because we want to see everything on an even playing field in terms of cost. And here, cost doesn’t increase as fast compared to the last graph because inflation is accounted for.
_ = inflatedDF.plot(kind='bar', secondary_y='Views', figsize=(50,35))
ax1, ax2 = plt.gcf().get_axes()
ax1.legend(loc='upper left', title='Avg. Cost for Advertisement')
ax2.legend(loc='upper right', title='Avg. Number of Viewers')
ax1.set_title('Avg. Cost of Advertising AFTER INFLATION in Relation to Avg. Viewership', fontsize=45)
ax1.set_ylabel('Avg. Cost of 30 Second Advertisement', fontsize=30)
ax1.set_xlabel('Year of Season', fontsize=30)
ax2.set_ylabel('Avg. Number of Viewers', fontsize=30)
plt.xticks(inflatedDF.index,inflatedDF["Year"].values)
ax1.ticklabel_format(style='plain', axis='y')
ax2.ticklabel_format(style='plain', axis='y')
plt.rcParams.update({'font.size': 22})
plt.show()
Now, we will examine the CPM (Cost Per 1000 views).
# examining CPM (cost per thousand viewers)
# CPM Formula: cpm = cost of ad / total number of viewres * 1000
cpmCol = []
for index, row in inflatedDF.iterrows():
cost = float(row["Cost"])
views = float(row["Views"])
cpm = cost / views * 1000
cpmCol.append(cpm)
Add a new column to the Data Frame with the CPM listed for each year.
inflatedDF["CPM"] = cpmCol
inflatedDF.head()
| Year | Cost | Views | CPM | |
|---|---|---|---|---|
| 1 | 1966 | 299011.63 | 24430000 | 12.239526 |
| 2 | 1966 | 338879.83 | 26750000 | 12.668405 |
| 3 | 1967 | 422839.13 | 39120000 | 10.808771 |
| 4 | 1968 | 409236.63 | 41660000 | 9.823251 |
| 5 | 1969 | 551722.84 | 44270000 | 12.462680 |
# plt.figure(30, 15)
# plt.scatter(inflatedDF["Year"], inflatedDF["CPM"])
# plt.show()
# only costs bar graph
inflatedDF = pd.DataFrame(inflatedDF)
# make years ints
intYears = []
for index, row in inflatedDF.iterrows():
intYears.append(int(row["Year"]))
year = intYears
cpm = inflatedDF['CPM']
# Figure Size
fig = plt.figure(figsize =(50, 35))
plt.ticklabel_format(style='plain', axis='y')
# Horizontal Bar Plot
plt.scatter(year, cpm, s=1000,c='lightblue')
plt.xticks(rotation=90)
plt.ylabel("CPM (Cost of Ad Per Thousand Views)")
plt.title("CPM per Year")
z = np.polyfit(year, cpm, 5)
p = np.poly1d(z)
plt.plot(year,p(year),"r--")
# Show Plot
plt.show()
For this CPM per year graph, we show the increase in cost per thousand viewers and we see that no matter what, when the viewership is still the same, the CPM increases for 1000 viewer subsets. This shows that once again, it isn’t the best return for companies since they are not getting a bang for their buck (spending more than they should have to, to get a return). Each year, they can expect to spend more money on essentially the same number of viewers.
For the hypothesis testing, we have a null hypothesis on top of an alternative hypothesis. The significance level is important in this scenario since we have to see whether the p-value is inside the significance level. This will allow us to reject or accept the null hypothesis. This test is being done since we want to see if the model we make is a good representation/fit of the data.
Let's look at an example now. In our example we will be looking to see if there is a linear correlation between views, ad cost, and CPM. Since the variable that we are trying to predict is numerical, we use a polyfit line.
Before we run the test we must set up our hypotheses and the significance level:
𝛼 (significance level) = 0.05
𝐻𝑜= There is no linearity between CPM and year
𝐻𝑎= There is linearity between the CPM and year
sm.ols(formula="CPM ~ Year", data=inflatedDF).fit().summary()
| Dep. Variable: | CPM | R-squared: | 1.000 |
|---|---|---|---|
| Model: | OLS | Adj. R-squared: | 1.000 |
| Method: | Least Squares | F-statistic: | 2178. |
| Date: | Wed, 21 Jul 2021 | Prob (F-statistic): | 0.0170 |
| Time: | 20:32:45 | Log-Likelihood: | 97.784 |
| No. Observations: | 55 | AIC: | -87.57 |
| Df Residuals: | 1 | BIC: | 20.83 |
| Df Model: | 53 | ||
| Covariance Type: | nonrobust |
| coef | std err | t | P>|t| | [0.025 | 0.975] | |
|---|---|---|---|---|---|---|
| Intercept | 12.4540 | 0.214 | 58.077 | 0.011 | 9.729 | 15.179 |
| Year[T.1967] | -1.6452 | 0.371 | -4.429 | 0.141 | -6.365 | 3.074 |
| Year[T.1968] | -2.6307 | 0.371 | -7.083 | 0.089 | -7.350 | 2.089 |
| Year[T.1969] | 0.0087 | 0.371 | 0.023 | 0.985 | -4.711 | 4.728 |
| Year[T.1970] | -1.9567 | 0.371 | -5.268 | 0.119 | -6.676 | 2.763 |
| Year[T.1971] | -2.7377 | 0.371 | -7.371 | 0.086 | -7.457 | 1.982 |
| Year[T.1972] | -2.2276 | 0.371 | -5.998 | 0.105 | -6.947 | 2.492 |
| Year[T.1973] | -0.7845 | 0.371 | -2.112 | 0.281 | -5.504 | 3.935 |
| Year[T.1974] | -2.4339 | 0.371 | -6.553 | 0.096 | -7.153 | 2.285 |
| Year[T.1975] | -3.2874 | 0.371 | -8.851 | 0.072 | -8.007 | 1.432 |
| Year[T.1976] | -3.2923 | 0.371 | -8.864 | 0.072 | -8.012 | 1.427 |
| Year[T.1977] | -3.6744 | 0.371 | -9.893 | 0.064 | -8.394 | 1.045 |
| Year[T.1978] | -2.6335 | 0.371 | -7.090 | 0.089 | -7.353 | 2.086 |
| Year[T.1979] | -2.0699 | 0.371 | -5.573 | 0.113 | -6.789 | 2.649 |
| Year[T.1980] | 0.1930 | 0.371 | 0.520 | 0.695 | -4.526 | 4.912 |
| Year[T.1981] | -1.6246 | 0.371 | -4.374 | 0.143 | -6.344 | 3.095 |
| Year[T.1982] | 0.6657 | 0.371 | 1.792 | 0.324 | -4.054 | 5.385 |
| Year[T.1983] | -0.1276 | 0.371 | -0.344 | 0.789 | -4.847 | 4.592 |
| Year[T.1984] | 2.8385 | 0.371 | 7.642 | 0.083 | -1.881 | 7.558 |
| Year[T.1985] | 1.8415 | 0.371 | 4.958 | 0.127 | -2.878 | 6.561 |
| Year[T.1986] | 3.7949 | 0.371 | 10.217 | 0.062 | -0.924 | 8.514 |
| Year[T.1987] | 5.8784 | 0.371 | 15.827 | 0.040 | 1.159 | 10.598 |
| Year[T.1988] | 5.6519 | 0.371 | 15.217 | 0.042 | 0.933 | 10.371 |
| Year[T.1989] | 7.3459 | 0.371 | 19.778 | 0.032 | 2.627 | 12.065 |
| Year[T.1990] | 7.4763 | 0.371 | 20.129 | 0.032 | 2.757 | 12.196 |
| Year[T.1991] | 7.8412 | 0.371 | 21.111 | 0.030 | 3.122 | 12.561 |
| Year[T.1992] | 4.7766 | 0.371 | 12.860 | 0.049 | 0.057 | 9.496 |
| Year[T.1993] | 5.4620 | 0.371 | 14.706 | 0.043 | 0.743 | 10.181 |
| Year[T.1994] | 11.6168 | 0.371 | 31.277 | 0.020 | 6.897 | 16.336 |
| Year[T.1995] | 7.1335 | 0.371 | 19.206 | 0.033 | 2.414 | 11.853 |
| Year[T.1996] | 10.0801 | 0.371 | 27.139 | 0.023 | 5.361 | 14.799 |
| Year[T.1997] | 10.6763 | 0.371 | 28.745 | 0.022 | 5.957 | 15.396 |
| Year[T.1998] | 17.8894 | 0.371 | 48.165 | 0.013 | 13.170 | 22.609 |
| Year[T.1999] | 24.4286 | 0.371 | 65.771 | 0.010 | 19.709 | 29.148 |
| Year[T.2000] | 26.7531 | 0.371 | 72.029 | 0.009 | 22.034 | 31.472 |
| Year[T.2001] | 24.5923 | 0.371 | 66.212 | 0.010 | 19.873 | 29.312 |
| Year[T.2002] | 23.2585 | 0.371 | 62.621 | 0.010 | 18.539 | 27.978 |
| Year[T.2003] | 23.6167 | 0.371 | 63.585 | 0.010 | 18.897 | 28.336 |
| Year[T.2004] | 25.7527 | 0.371 | 69.336 | 0.009 | 21.033 | 30.472 |
| Year[T.2005] | 24.0564 | 0.371 | 64.769 | 0.010 | 19.337 | 28.776 |
| Year[T.2006] | 20.4103 | 0.371 | 54.952 | 0.012 | 15.691 | 25.130 |
| Year[T.2007] | 22.1304 | 0.371 | 59.583 | 0.011 | 17.411 | 26.850 |
| Year[T.2008] | 24.0711 | 0.371 | 64.808 | 0.010 | 19.352 | 28.790 |
| Year[T.2009] | 21.0149 | 0.371 | 56.580 | 0.011 | 16.296 | 25.734 |
| Year[T.2010] | 20.6908 | 0.371 | 55.707 | 0.011 | 15.972 | 25.410 |
| Year[T.2011] | 23.7129 | 0.371 | 63.844 | 0.010 | 18.994 | 28.432 |
| Year[T.2012] | 26.9533 | 0.371 | 72.568 | 0.009 | 22.234 | 31.673 |
| Year[T.2013] | 27.4019 | 0.371 | 73.776 | 0.009 | 22.683 | 32.121 |
| Year[T.2014] | 28.1456 | 0.371 | 75.779 | 0.008 | 23.426 | 32.865 |
| Year[T.2015] | 34.3858 | 0.371 | 92.579 | 0.007 | 29.666 | 39.105 |
| Year[T.2016] | 39.8648 | 0.371 | 107.331 | 0.006 | 35.145 | 44.584 |
| Year[T.2017] | 41.0026 | 0.371 | 110.394 | 0.006 | 36.283 | 45.722 |
| Year[T.2018] | 41.9704 | 0.371 | 113.000 | 0.006 | 37.251 | 46.690 |
| Year[T.2019] | 44.2936 | 0.371 | 119.255 | 0.005 | 39.574 | 49.013 |
| Omnibus: | 34.524 | Durbin-Watson: | 2.500 |
|---|---|---|---|
| Prob(Omnibus): | 0.000 | Jarque-Bera (JB): | 1375.573 |
| Skew: | 0.000 | Prob(JB): | 1.99e-299 |
| Kurtosis: | 27.500 | Cond. No. | 39.6 |
From this result, we can see that for each year, the p-value definitely varies. For example, in 1969, the p-value was at 0.985, which is way above 0.05. But for 2019, it’s at 0.005.
To be more specific, from 1967 to 1985, the p-value tended to be greater than 0.05. This means for this time period we do not reject the null hypothesis. However, from 1985 to 2019, the p-value was more-so below 0.05. This means that we do reject the null hypothesis that there is no linearity between CPM and year.
However, we can see the various curvatures in the graph and this would support the fact that there is no clear linearity, but we are leaning more towards rejecting the null since this takes place in the most recent time frame. Note that if we made a line of best-fit, it would better show linearity over time. However, if you look at our CPM graph from earlier, the curviture is quite noticeable with a given degree of 5. With a degree of 1, however, this line becomes quite linear.
Overall, we are able to detect linearity between the Cost of an Ad per 1000 viewers and years, especially in the last 35 years.
What this tells us is that the CPM is linearly increasing, which means that from year to year, the cost of advertising will increase but not he viewership necessarily. From one year to the next, the rate at which the cost of advertisements increases was far greater than the rate at which viewership was increasing.
This means that despite the rising numbers in viewers, the costs for ads has gone up. More specifically, the CPM has gone up, which is the better and more important piece of information. From a marketing teams' perspective that is considering launching their advertisement during the Super Bowl, they can expect to pay more money each year to show their ad to the same number of people.
In conclusion, it is difficult to say whether advertisements are worth promoting during a Super Bowl commercial, namely because of the price that airtime costs. However, given the amount of eyeballs and viewers tuned in, it's also a great time for marketing growth. Data from Google trends can be seen that shows which types of companies gain more value each year by advertising commericals, but based off the data collected here and the projections that were analyzed, it is generally not worth it to advertise anymore during a Super Bowl commercial.