Business Questions

In this project, I am working with the Super Bowl Commercial table using SQLiteStudio to answer these questions:

1a. Which brand has had the most Super Bowl commercials?

1b.Do they have a distinct style?

2. How have different characteristics for commercials trended across time?

3. Can you identify any patterns for the most successful commercials on YouTube?

Insights

1a. Bud Light has had the most commercials, being 56.

1b. They have a distinct style of being funny, with 95% (53 out of 56) of the commercials being listed as "TRUE" in the funny column.

2. Superbowl commercials became longer over time. For example, after 2010, the average lengths of super bowl commercials were above 40 seconds.

3. I based commercial success criteria on having the highest amount of YouTube likes. The most successful commercials on YouTube have a high YouTube view amount (over 7 million), high amount of tv viewers (over 90 million), and don't use sex.

Data Cleaning

Some rows in the following columns were blank:

• youtube_link

• youtube_views

• youtube_likes

I wrote this query to return only the rows with complete data:

SELECT *

FROM BIT_DB.SuperBowlCommercials

WHERE youtube_link IS NOT NULL

AND youtube_link <> ''

AND youtube_views IS NOT NULL

AND youtube_views <> ''

AND youtube_likes IS NOT NULL

AND youtube_likes <> '';

Analysis

1a. I used the COUNT aggregation function to count the number of commercials per brand :

SELECT brand, COUNT(*) as commercial_amount

FROM BIT_DB.SuperBowlCommercials

WHERE youtube_link IS NOT NULL

AND youtube_link <> ''

AND youtube_views IS NOT NULL

AND youtube_views <> ''

AND youtube_likes IS NOT NULL

AND youtube_likes <> ''

GROUP BY brand;

1b. I used the AND operator to return only the "Bud Light" commercials that were considered "funny". It returned 53 funny commercials. There is a total of 56 "Bud Light" commercials, so outside of the query editor I calculated 53/56 = 94.643% or 95% :

SELECT *

FROM BIT_DB.SuperBowlCommercials

WHERE youtube_link IS NOT NULL

AND youtube_link <> ''

AND youtube_views IS NOT NULL

AND youtube_views <> ''

AND youtube_likes IS NOT NULL

AND youtube_likes <> ''

AND brand = "Bud Light"

AND funny = "TRUE";

2. I used the AVG aggregation to find the average length of a commercial per year. Also used the ROUND function to clean up the decimals :

SELECT year, ROUND(AVG(length)) AS avg_length

FROM BIT_DB.SuperBowlCommercials

WHERE youtube_link IS NOT NULL

AND youtube_link <> ''

AND youtube_views IS NOT NULL

AND youtube_views <> ''

AND youtube_likes IS NOT NULL

AND youtube_likes <> ''

AND year BETWEEN "2000" AND "2021"

GROUP BY year;

3. Since I based commercial success criteria on having the highest amount of YouTube likes, I used the ORDER BY statement to list the rows by highest to lowest number of youtube_likes. I limited to show the top 5 only :

SELECT *

FROM BIT_DB. SuperBowlCommercials

WHERE youtube_link IS NOT NULL

AND youtube_link <> ''

AND youtube_views IS NOT NULL

AND youtube_views <> ''

AND youtube_likes IS NOT NULL

AND youtube_likes <> ''

ORDER BY youtube_likes desc

LIMIT 5;