Earlier this week we looked at advertising versus uncaptioned airtime on television news using our massive new Television News Advertising Inventory Files (AIF) Captioning Time dataset. One of the findings of that initial analysis was that there were a set of classes of captioning errors that skewed our results that we've built safeguards against now and reprocessed the entire dataset to remove the problematic broadcasts. Now using the following query we get far more reasonable results:

SELECT DAY, SUM(UNCAPSEC) UNCAPSEC, SUM(ADSEC) ADSEC, SUM(TOTSEC) TOTSEC, SUM(UNCAPSEC)/SUM(TOTSEC)*100 PERC_UNCAPTIME, SUM(ADSEC)/SUM(TOTSEC)*100 PERC_ADTIME FROM (
SELECT DATE(date) DAY, count(1) UNCAPSEC, 0 ADSEC, 0 TOTSEC FROM `[TEMPTABLE]` where station='CNN' and (type='UNCAPTIONED') AND DATE(date) >= "2009-07-02" group by DAY
UNION ALL
SELECT DATE(date) DAY, 0 UNCAPSEC, count(1) ADSEC, 0 TOTSEC FROM `[TEMPTABLE]` where station='CNN' and (type='ADVERTISEMENT') AND DATE(date) >= "2009-07-02" group by DAY
UNION ALL
SELECT DATE(date) DAY, 0 ADSEC, 0 ADSEC, count(1) TOTSEC FROM `[TEMPTABLE]` where station='CNN' AND DATE(date) >= "2009-07-02" group by DAY
) group by DAY having TOTSEC>=64800 order by DAY ASC

CNN

MSNBC

The artifacting in 2011 is due to a high density of problematic broadcasts during that period.

Fox News

Comparisons

Using the query below we can directly compare the channels. NOTE that here we limit ourselves to just April 2012 through present to avoid the problematic broadcasts in the 2009-2011 period.

SELECT DATE(date) DAY,
countif(station='CNN' AND type='UNCAPTIONED') cnn_uncap, countif(station='CNN' AND type='ADVERTISEMENT') cnn_ad, countif(station='CNN') cnn_tot,
countif(station='MSNBC' AND type='UNCAPTIONED') msnbc_uncap, countif(station='MSNBC' AND type='ADVERTISEMENT') msnbc_ad, countif(station='MSNBC') msnbc_tot,
countif(station='FOXNEWS' AND type='UNCAPTIONED') fox_uncap, countif(station='FOXNEWS' AND type='ADVERTISEMENT') fox_ad, countif(station='FOXNEWS') fox_tot
FROM `gdelt-bq.bqtemp.aif3` where (DATE(Date) >= "2012-04-01" and DATE(date) <= "2020-12-31") group by DAY 
having cnn_tot > 64800 AND msnbc_tot > 64800 and fox_tot > 64800
order by DAY asc

Captioned Advertising Airtime

The percentage of captioned airtime across the three channels April 2012 – present that was labeled as advertising.

Uncaptioned Airtime

The percentage of airtime across the three channels April 2012 – present that was uncaptioned.

Uncaptioned + Captioned Advertising Airtime

The percentage of airtime across the three channels April 2012 – present that was either captioned airtime labeled as advertising or uncaptioned.

We'll be using these graphs as a starting point to explore a wealth of questions around television news advertising, captioning trends and our ability to exclude advertising to refine our results for public health analyses like Covid-19!