Using the sample GKG 2.0 BigQuery network query, the following visualization shows the network of the top 1,500 connections among names mentioned in global coverage of the Greek debt crisis in the first half of July.  The actual query examined all coverage mentioning Greece from July 1-15, 2015, and constructed a histogram of all of the names that appear together in coverage, selecting the top 1,500 strongest connections.  Looking at the network below, the diagram yields a pretty robust approximation of the reality of the debt crisis, with all of the major players front and center and connected to each other in the ways you would expect.  So, while this visualization does not yield any groundbreaking new findings that a well-read reader would not have already gathered from following the myriad coverage of the debt crisis, it does reinforce that GDELT captures a strong approximation of the actual leadership dynamics underlying global politics.

 

 

Technical Details

For the technically-minded, the diagram above was made following the Google BigQuery + GKG 2.0 Sample Queries network examples (towards the bottom of that page), using the following query:

SELECT a.name, b.name, COUNT(*) as count
FROM (FLATTEN(
SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) name
FROM [gdelt-bq:gdeltv2.gkg]
WHERE DATE>20150700000000 and DATE < 20150715000000 and V2Locations like '%Greece%'
,name)) a
JOIN EACH (
SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) name
FROM [gdelt-bq:gdeltv2.gkg]
WHERE DATE>20150700000000 and DATE < 20150715000000 and V2Locations like '%Greece%'
) b
ON a.GKGRECORDID=b.GKGRECORDID
WHERE a.name<b.name
GROUP EACH BY 1,2
ORDER BY 3 DESC
LIMIT 1500

Several other modifications of the above were tested, such as adding a language filter to each of the two where clauses above – testing " TranslationInfo LIKE '%srclc:ell%' " for Greek, " TranslationInfo LIKE '%srclc:deu%' " for German, and " TranslationInfo LIKE '%srclc:fra%' " for French.

A topical histogram was also tested, to return the top World Bank topics discussed in Greek-related coverage during those days:

SELECT theme, COUNT(*) as count
FROM (
select REGEXP_REPLACE(SPLIT(V2Themes,';'), r',.*', ") theme
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150700000000 and DATE < 20150715000000 and V2Locations like '%Greece%' and TranslationInfo LIKE '%srclc:ell%'
)
group by theme
having theme like 'WB_%'
ORDER BY 2 DESC
LIMIT 15

The results are unsurprising, but reassuring that the approach generates reasonable results:

Category	Number Mentions
WB_1104_MACROECONOMIC_VULNERABILITY_AND_DEBT	48156
WB_450_DEBT	47717
WB_696_PUBLIC_SECTOR_MANAGEMENT	40189
WB_2432_FRAGILITY_CONFLICT_AND_VIOLENCE	38134
WB_840_JUSTICE	32349
WB_2470_PEACE_OPERATIONS_AND_CONFLICT_MANAGEMENT	28405
WB_2471_PEACEKEEPING	27019
WB_2473_DIPLOMACY_AND_NEGOTIATIONS	26922
WB_936_ALTERNATIVE_DISPUTE_RESOLUTION	26865
WB_843_DISPUTE_RESOLUTION	26865
WB_939_NEGOTIATION	26734
WB_471_ECONOMIC_GROWTH	13874
WB_135_TRANSPORT	13592
WB_698_TRADE	12943
WB_621_HEALTH_NUTRITION_AND_POPULATION	12862

Finally, a modified version of the network code was tested that returns an approximation of the average tone of each link (in reality it is really an average of the two averages of their independent occurrences):

SELECT a.name, b.name, COUNT(*) as count, (avg(a.tone)+avg(b.tone))/2 as linktone
FROM (FLATTEN(
SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) name, FLOAT(REGEXP_EXTRACT(V2Tone, '^(.*?),')) tone
FROM [gdelt-bq:gdeltv2.gkg]
WHERE DATE>20150700000000 and DATE < 20150715000000 and V2Locations like '%Greece%'
,name)) a
JOIN EACH (
SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) name, FLOAT(REGEXP_EXTRACT(V2Tone, '^(.*?),')) tone
FROM [gdelt-bq:gdeltv2.gkg]
WHERE DATE>20150700000000 and DATE < 20150715000000 and V2Locations like '%Greece%'
) b
ON a.GKGRECORDID=b.GKGRECORDID
WHERE a.name<b.name
GROUP EACH BY 1,2
ORDER BY 3 DESC
LIMIT 1500

Though, in the case of Greece this does not yield sufficient stratification to produce a distinct visual. You could also add additional filters, such as narrowing to just a subset of media outlets by adding a filter like " SourceCommonName in (select domain from [mytable])) " to the two where clauses above (replacing "mytable" with the name of your BigQuery table).  For example, you might filter against the Alexa One Million list to get a more Westernized perspective on a topic.

Instead of a network query, if you just want to fetch a list of URLs of coverage of Greece, you could add "IFNULL(REGEXP_EXTRACT(TranslationInfo, 'srclc:(.*?);'), 'eng') lang" to your select query to return the three-letter language code of the article (automatically recasts nulls to "eng" for English).

If you want to add topical filters to your query, traditionally you might use a "LIKE '%%' " query, but if you want to ensure that there are at least two matches in the document of any of your selected themes, this is not possible with a traditional LIKE (you can easily set up a LIKE to require that a given theme appear twice, but not that any of a set of themes appear twice).  Instead, you can use the REGEXP_MATCH operator "REGEXP_MATCH(V2Themes, '(WB_1104_MACROECONOMIC_VULNERABILITY_AND_DEBT|WB_696_PUBLIC_SECTOR_MANAGEMENT|WB_2473_DIPLOMACY_AND_NEGOTIATIONS|WB_936_ALTERNATIVE_DISPUTE_RESOLUTION).*(WB_1104_MACROECONOMIC_VULNERABILITY_AND_DEBT|WB_696_PUBLIC_SECTOR_MANAGEMENT|WB_2473_DIPLOMACY_AND_NEGOTIATIONS|WB_936_ALTERNATIVE_DISPUTE_RESOLUTION)')".

Happy visualizing!