Methodology

-- Extract lookup table for domain to country mapping
CREATE OR REPLACE TABLE gdelt-dataviz-project.gdelt_agg.domain_lookup AS
SELECT domain, topcountry.countrycode AS MediaCountryCode
FROM (
    SELECT 
        domain, 
        -- We take the strict Top 1 result from the array
        ARRAY_AGG(STRUCT(countrycode, cnt) ORDER BY cnt DESC LIMIT 1)[OFFSET(0)]
            AS topcountry 
    FROM (
        SELECT 
            -- OFFICIAL GDELT REGEX: Extracts the clean country code
            REGEXP_EXTRACT(location, r'^[1-5]#.*?#(.*?)#') AS countrycode, 
            -- Clean the domain
            IFNULL(NET.REG_DOMAIN(DocumentIdentifier), 'unknown') AS domain, 
            -- Exact Count
            COUNT(1) AS cnt 
        FROM `gdelt-bq.gdeltv2.gkg_partitioned`, 
            -- OFFICIAL GDELT UNNEST: Explodes the list of locations
            UNNEST(SPLIT(V2Locations, ';')) AS location 
        WHERE 
            LOWER(DocumentIdentifier) LIKE '%http%'                 
        GROUP BY countrycode, domain 
        HAVING cnt > 5 AND countrycode IS NOT NULL
    ) 
    GROUP BY domain
)

CREATE OR REPLACE TABLE gdelt-dataviz-project.gdelt_agg.weekly_media_conflict_reports AS
SELECT
  -- 1. TIME: Weekly Granularity
  DATE_TRUNC(
    DATE(PARSE_TIMESTAMP('%Y%m%d%H%M%S', CAST(m.MentionTimeDate AS STRING))), 
    WEEK(MONDAY)
  ) AS mention_week,

  -- 2. LOCATION: WHERE the conflict is happening
  e.ActionGeo_CountryCode AS conflict_country,

  -- 3. ACTORS: WHO is involved in the conflict
  IFNULL(e.Actor1CountryCode, 'UNK') AS actor1_country,
  IFNULL(e.Actor2CountryCode, 'UNK') AS actor2_country,

  -- 4. MEDIA SOURCE: WHERE the media is reporting from
  -- Uses the GDELT Lookup we created above
  IFNULL(dlc_lookup.MediaCountryCode, 'UNK') AS media_country,

  -- 5. METRICS (Aggregated)
  COUNT(*) AS mentions_count,
  COUNT(DISTINCT m.GLOBALEVENTID) AS distinct_events,
  COUNT(DISTINCT m.MentionSourceName) AS distinct_media_sources,

  -- 6. METRICS: CLASS PROFILE (Volume vs Diversity)
  -- Volume (How many articles?)
  COUNT(CASE WHEN e.QuadClass = 3 THEN 1 END)
  AS verbal_conflict_mentions,
  COUNT(CASE WHEN e.QuadClass = 4 THEN 1 END)
  AS material_conflict_mentions,
  -- Diversity (How many unique events?)
  COUNT(DISTINCT CASE WHEN e.QuadClass = 3 THEN m.GLOBALEVENTID END)
  AS verbal_conflict_unique_events,
  COUNT(DISTINCT CASE WHEN e.QuadClass = 4 THEN m.GLOBALEVENTID END)
  AS material_conflict_unique_events,

  -- 7. METRICS: ACTOR PROFILE
  -- STATE (Gov, Mil, Police, ecc)
  COUNT(CASE WHEN 
      e.Actor1Type1Code IN ('GOV', 'MIL', 'COP', 'JUD', 'LEG', 'SPY') OR 
      e.Actor2Type1Code IN ('GOV', 'MIL', 'COP', 'JUD', 'LEG', 'SPY') 
  THEN 1 END) AS state_mentions,
  
  COUNT(DISTINCT CASE WHEN 
      e.Actor1Type1Code IN ('GOV', 'MIL', 'COP', 'JUD', 'LEG', 'SPY') OR 
      e.Actor2Type1Code IN ('GOV', 'MIL', 'COP', 'JUD', 'LEG', 'SPY') 
  THEN m.GLOBALEVENTID END) AS state_unique_events,

  -- INSURGENTS (Rebels, Separatists, etc)
  COUNT(CASE WHEN 
      e.Actor1Type1Code IN ('REB', 'INS', 'SEP', 'OPP', 'CRM') OR 
      e.Actor2Type1Code IN ('REB', 'INS', 'SEP', 'OPP', 'CRM') 
  THEN 1 END) AS insurgents_mentions,

  COUNT(DISTINCT CASE WHEN 
      e.Actor1Type1Code IN ('REB', 'INS', 'SEP', 'OPP', 'CRM') OR 
      e.Actor2Type1Code IN ('REB', 'INS', 'SEP', 'OPP', 'CRM') 
  THEN m.GLOBALEVENTID END) AS insurgents_unique_events,

  -- CIVILIANS (The Victims usually)
  COUNT(CASE WHEN 
      e.Actor1Type1Code IN ('CVL', 'REF', 'ELI', 'BUS', 'EDU', 'LAB', 'MED', 'REL', 'AGR') OR 
      e.Actor2Type1Code IN ('CVL', 'REF', 'ELI', 'BUS', 'EDU', 'LAB', 'MED', 'REL', 'AGR') 
  THEN 1 END) AS civilians_mentions,

  COUNT(DISTINCT CASE WHEN 
      e.Actor1Type1Code IN ('CVL', 'REF', 'ELI', 'BUS', 'EDU', 'LAB', 'MED', 'REL', 'AGR') OR 
      e.Actor2Type1Code IN ('CVL', 'REF', 'ELI', 'BUS', 'EDU', 'LAB', 'MED', 'REL', 'AGR') 
  THEN m.GLOBALEVENTID END) AS civilians_unique_events,

  -- INTERNATIONALS (NGO, IGO)
  COUNT(CASE WHEN 
      e.Actor1Type1Code IN ('IGO', 'NGO', 'MNC') OR 
      e.Actor2Type1Code IN ('IGO', 'NGO', 'MNC') 
  THEN 1 END) AS international_mentions,

  COUNT(DISTINCT CASE WHEN 
      e.Actor1Type1Code IN ('IGO', 'NGO', 'MNC') OR 
      e.Actor2Type1Code IN ('IGO', 'NGO', 'MNC') 
  THEN m.GLOBALEVENTID END) AS international_unique_events,

  -- Sentiment Analysis
  AVG(m.MentionDocTone) AS avg_tone,
  STDDEV(m.MentionDocTone) AS stddev_tone,
  APPROX_QUANTILES(m.MentionDocTone, 2)[OFFSET(1)] AS median_tone,

  -- Impact (Weighted by mentions)
  AVG(e.GoldsteinScale) AS avg_impact,
  STDDEV(e.GoldsteinScale) AS stddev_impact,
  APPROX_QUANTILES(e.GoldsteinScale, 2)[OFFSET(1)] AS median_impact,

  -- Most mentioned article
  ARRAY_AGG(
    STRUCT(m.MentionIdentifier, e.NumMentions) 
    ORDER BY e.NumMentions DESC LIMIT 1
  )[OFFSET(0)].MentionIdentifier AS top_event_article_url,

  -- Most impactful article
  ARRAY_AGG(
    STRUCT(m.MentionIdentifier, e.GoldsteinScale) 
    ORDER BY e.GoldsteinScale ASC LIMIT 1
  )[OFFSET(0)].MentionIdentifier AS most_impactful_event_article_url  


FROM `gdelt-bq.gdeltv2.eventmentions_partitioned` m
JOIN `gdelt-bq.gdeltv2.events_partitioned` e
  ON m.GLOBALEVENTID = e.GLOBALEVENTID

-- JOIN LOOKUP
LEFT JOIN `gdelt-dataviz-project.gdelt_agg.lookup_table` dlc_lookup
  ON NET.REG_DOMAIN(m.MentionIdentifier) = dlc_lookup.Domain

WHERE
  m._PARTITIONTIME >= TIMESTAMP('2015-01-01')
  AND e._PARTITIONTIME >= TIMESTAMP('2015-01-01')
  
-- We only consider material and verbal conflict events
  AND e.QuadClass in (3,4)

  -- Geographic Filter (Exclude events without country or generalist)
  AND e.ActionGeo_CountryCode IS NOT NULL

GROUP BY 1, 2, 3, 4, 5
HAVING mentions_count > 5

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import json                            
path_mentions = "../data/GDELT/conflict_data_final.csv"
df_mentions = pd.read_csv(path_mentions)
df_mentions.to_parquet("../data/GDELT/conflict_data_final.parquet", index=False)
key_cols = ['mention_week', 'conflict_country', 'actor1_country', 'actor2_country', 'media_country']
df_keys = df_mentions[key_cols].drop_duplicates().reset_index(drop=True)
df_keys['conflict_id'] = df_keys.index
df_mentions = df_mentions.merge(df_keys, on=key_cols, how='left')

# Now create normalized tables:
save_path = "../data/GDELT/"

# 1. Dimension table (the keys + ID)
df_dimensions = df_mentions[['conflict_id'] + key_cols].drop_duplicates()
df_dimensions.to_parquet(save_path + "gdelt_keys.parquet", index=False)

# 2. Volume metrics (counts and mentions)
df_volume = df_mentions[['conflict_id', 'mentions_count', 'distinct_events', 'distinct_media_sources']]
df_volume.to_parquet(save_path + "gdelt_volume_metrics.parquet", index=False)

# 3. Conflict type metrics (verbal vs material)
df_conflict_types = df_mentions[['conflict_id', 
                         'verbal_conflict_mentions', 'material_conflict_mentions',
                         'verbal_conflict_unique_events', 'material_conflict_unique_events']]
df_conflict_types.to_parquet(save_path + "gdelt_conflict_types.parquet", index=False)

# 4. Actor type metrics (state, insurgents, civilians, international)
df_actors = df_mentions[['conflict_id',
                 'state_mentions', 'state_unique_events',
                 'insurgents_mentions', 'insurgents_unique_events',
                 'civilians_mentions', 'civilians_unique_events',
                 'international_mentions', 'international_unique_events']]
df_actors.to_parquet(save_path + "gdelt_actor_metrics.parquet", index=False)

# 5. Sentiment/tone metrics
df_sentiment = df_mentions[['conflict_id', 
                    'avg_tone', 'stddev_tone', 'median_tone']]
df_sentiment.to_parquet(save_path + "gdelt_sentiment.parquet", index=False)

# 6. Impact metrics
df_impact = df_mentions[['conflict_id',
                 'avg_impact', 'stddev_impact', 'median_impact']]
df_impact.to_parquet(save_path + "gdelt_impact.parquet", index=False)

# 7. Article references (URLs)
df_articles = df_mentions[['conflict_id', 
                   'top_event_article_url', 'most_impactful_event_article_url']]
df_articles.to_parquet(save_path + "gdelt_articles.parquet", index=False)

print("Files created:")
print(f"- gdelt_keys.parquet ({df_dimensions.shape})")
print(f"- gdelt_volume_metrics.parquet ({df_volume.shape})")
print(f"- gdelt_conflict_types.parquet ({df_conflict_types.shape})")
print(f"- gdelt_actor_metrics.parquet ({df_actors.shape})")
print(f"- gdelt_sentiment.parquet ({df_impact.shape})")
print(f"- gdelt_impact.parquet ({df_impact.shape})")
print(f"- gdelt_articles.parquet ({df_articles.shape})")