A TF-IDF Chronology Of The Global Geographic Graph Of The Top Terms Associated With Italy Jan-Apr 2020

How might we apply a TF-IDF analysis to the contextual field of the Global Geographic Graph of English language online news coverage to compile a daily chronology of the top phrases associated with Italy from Jan 1 to April 30, 2020, tracing the rise of Covid-19 in the country? Using a single query we get the table below, tracing everything from Kobe Bryant's death to the first glimmers of Covid-19 news relating to the country to the complete pandemic saturation of global English language news mentions of the country in March.

You can download the final spreadsheet and see the final results below:

TECHNICAL DETAILS

Creating the table above took just 10 seconds in BigQuery using the following query:

WITH nested AS (
WITH data AS (
select date, 'WEB' station, word, count(1) count from (
SELECT DATE(DateTime) date, ML.NGRAMS(SPLIT(ContextualText, ' '), [2, 2], ' ') words FROM `gdelt-bq.gdeltv2.ggg` WHERE DATE(DateTime) >= "2020-01-01" and DATE(DateTime) <= "2020-04-30" and Location like '%Italy%' 
), unnest(words) word group by date, word having length(word) > 2 and count>500 order by count desc
)
, word_day_type AS (
# how many times a word is mentioned in each "document"
SELECT word, SUM(count) counts, date, station
FROM data
GROUP BY 1, 3, 4
)
, day_type AS (
# total # of words in each "document"
SELECT SUM(count) counts, date, station
FROM data
GROUP BY 2, 3
)
, tf AS (
# TF for a word in a "document"
SELECT word, date, station, a.counts/b.counts tf
FROM word_day_type a
JOIN day_type b
USING(date, station)
)
, word_in_docs AS (
# how many "documents" have a word
SELECT word, COUNT(DISTINCT FORMAT('%s %s', FORMAT_TIMESTAMP( "%m/%d/%E4Y", date), station)) indocs
FROM word_day_type
GROUP BY 1
)
, total_docs AS (
# total # of docs
SELECT COUNT(DISTINCT FORMAT('%s %s', FORMAT_TIMESTAMP( "%m/%d/%E4Y", date), station)) total_docs
FROM data
)
, idf AS (
# IDF for a word
SELECT word, LOG(total_docs.total_docs/indocs) idf
FROM word_in_docs
CROSS JOIN total_docs
)
SELECT date, station,
ARRAY_AGG(STRUCT(ARRAY_TO_STRING(words, ', ') AS wordlist)) top_words
FROM (
SELECT date, station, ARRAY_AGG(word ORDER BY tfidf DESC LIMIT 10) words
FROM (
SELECT word, date, station, tf.tf * idf.idf tfidf
FROM tf
JOIN idf
USING(word)
)
GROUP BY date, station
)
GROUP BY date, station
ORDER BY date DESC
) select FORMAT_TIMESTAMP( "%m/%d/%E4Y", date) day, station, wordlist from nested, UNNEST(top_words) order by date asc, station asc

We hope this inspires you on your own creative uses of the Global Geographic Graph!