Data cleaning — the slim dataset

We rewrite "Amazon" to "Amazon Store". Every other platform name passes through unchanged.

Why: The EU's exports use both names interchangeably for what is (in DSA terms) the same designated VLOP. Keeping the two as separate values in dashboards is confusing; merging them under the official designated name gives one clean Amazon row everywhere.

code: PLATFORM_RENAMES dict at the top of build_slim.py

A new column that summarises which kind of action the platform took on the SoR, with one of: Visibility, Account, Provision, Monetary, Multiple (more than one of the above), or None (no action recorded). Previously named action_categories.

Why: The EU's schema lists four separate decision columns (decision_visibility, decision_account, decision_provision, decision_monetary), each of which can independently be populated or empty. There is no single "what kind of action was taken" field in the raw data. We derive one because that is the first question most users ask. The four underlying columns are also kept individually in the slim (see below) for users who want the specific severity of each.

code: derived in the base CTE of the slim INSERT in build_slim.py

Collapsed and relabelled. The raw column is a JSON array (sometimes empty, sometimes with one value, occasionally with several). We collapse it to a single readable value: the inner enum when the array has exactly one element (relabelled, e.g. "Content disabled" instead of "DECISION_VISIBILITY_CONTENT_DISABLED"), "Multiple" when the array has more than one element (very rare), or NULL when the array is null or empty (no visibility action).

Why: Metabase cannot directly group by a JSON array, and the all-caps enum labels are hard to read in chart legends. Collapsing to single labelled values makes the field both pivotable and presentable.

code: DECISION_VISIBILITY_LABELS dict + dv_clean_sql CASE in build_slim.py

The severity of a monetary restriction, when one was applied: "Suspension" or "Termination". NULL when no monetary action was taken (the vast majority of SoRs). Newly carried into the slim.

code: DECISION_MONETARY_LABELS in build_slim.py

The severity of a service-provision restriction, when one was applied: "Partial suspension", "Total suspension", "Partial termination", or "Total termination". NULL when no provision action was taken. Newly carried into the slim.

code: DECISION_PROVISION_LABELS in build_slim.py

The severity of an account restriction, when one was applied: "Suspended" or "Terminated". NULL when no account action was taken. Newly carried into the slim.

code: DECISION_ACCOUNT_LABELS in build_slim.py

The raw enum is renamed for readability: "DECISION_GROUND_ILLEGAL_CONTENT" becomes "Illegal content", "DECISION_GROUND_INCOMPATIBLE_CONTENT" becomes "Incompatible with T&C". Values otherwise unchanged.

Why: Same legibility reason as above. Stored as a single source-of-truth dict in the build script so that the website explainer and the data are guaranteed to use the same labels.

code: DECISION_GROUND_LABELS in build_slim.py

Relabelled for readability: "AUTOMATED_DECISION_FULLY" becomes "Fully automated", etc.

code: AUTOMATED_DECISION_LABELS in build_slim.py

The legal ground the platform cited, kept as the raw free-text string (e.g. "Defamation", "Court order", "IP_Protection_Complaint_General"). Filled only for the ~0.2% of SoRs on the ILLEGAL_CONTENT branch; NULL otherwise. About 12,000 distinct values across the full history, mostly stock phrases. Newly carried into the slim.

Why raw: the values are templated enough to chart, but we keep the exact text rather than auto-grouping "similar" grounds — collapsing them would bake a subjective editorial judgement into a research dataset.

The platform's free-text explanation for an illegal-content decision, kept verbatim. Filled on the same ~0.2% of SoRs as the legal ground; NULL otherwise. A "why did this specific decision happen" lookup field rather than a chart axis. Newly carried into the slim.

The terms-of-service ground the platform cited, kept as raw free text (e.g. "Community Guidelines", "Generic image", "Violation of section S1.1 ..."). Filled on the ~99.8% of SoRs on the INCOMPATIBLE_CONTENT (T&C) branch; NULL otherwise. ~66,000 distinct values across the full history — useful for "look up the reason" and top-N charts, but a very long list as a filter dropdown. Newly carried into the slim.

Note: this is the one field that meaningfully grows the slim (filled on nearly every row), but its values are templated enough that it roughly doubles the row count rather than exploding it.

The 16 EU statement-category enums are renamed to plain-language labels: "STATEMENT_CATEGORY_SCAMS_AND_FRAUD" becomes "Scams and fraud", "STATEMENT_CATEGORY_PROTECTION_OF_MINORS" becomes "Protection of minors", and so on.

Why: The raw codes are stable identifiers useful for technical filters; the labels are what we want in chart legends and dashboard filters.

code: CATEGORY_LABELS dict in build_slim.py (16 entries)

Same treatment as decision_visibility: array collapse + label. Single-element arrays become the inner enum (e.g. "Product", "Video", "Image"); multi-element arrays (very rare, under 0.5% of rows) become "Multiple"; empty/null values become NULL.

code: CONTENT_TYPE_LABELS + ct_clean_sql in build_slim.py

ISO-2 language codes are kept verbatim (EN, DE, ES, IT, FR, PL, NL, JA, … every code that appears in the raw data). Empty/null values become NULL. No grouping into "Other" — every language present in the raw is also present in the slim.

Why: Bucketing minority languages into "Other" would erase researcher-relevant signal for communities whose moderation is rarely measured.

code: NULLIF(content_language, '') in the cleaned CTE

The day the SoR was indexed by the European Commission (the raw created_at column, renamed for legibility). Day precision — the most natural time axis for daily volume charts.

The raw territorial_scope is a JSON array of 0–30 EU/EEA country codes. We expand each SoR into one row per country it targets. A SoR with territorial_scope ["DE","FR","IT"] contributes three rows in the slim (one per country).

Why: Metabase map plots need data in long form — one (country, value) tuple per row. Storing it as an array would make every map question awkward. The exploded shape is the universally compatible form. The sor_count and country_mentions columns are designed to be summed safely against this exploded shape.

code: UNNEST(from_json(territorial_scope, '["VARCHAR"]')) in the exploded CTE

A label for how widely the action applies geographically. Values: all_eu_or_eea (the action covers 27 or more countries — the dominant pattern, especially for product platforms), single_country (exactly one country), or multi_country (2–26 countries, rare).

Why: Filtering "actions targeting a specific country" vs "EU-wide enforcement" is one of the most common research questions. Counting array length by hand at query time is slow; pre-bucketing makes that filter a one-click operation.

code: the scope_bucket CASE in the exploded CTE

Renamed for readability: "SOURCE_VOLUNTARY" → "Voluntary", "SOURCE_ARTICLE_16" → "Article 16 notice", "SOURCE_TRUSTED_FLAGGER" → "Trusted flagger", "SOURCE_TYPE_OTHER_NOTIFICATION" → "Other notification".

code: SOURCE_TYPE_LABELS in build_slim.py

Boolean. Already clean in the raw data — copied unchanged.

"ACCOUNT_TYPE_BUSINESS" → "Business", "ACCOUNT_TYPE_PRIVATE" → "Private". Empty/NULL stays NULL — the SoR concerns content rather than an account. (Filled on ~18% of rows; of those, ~97% are Business.)

code: ACCOUNT_TYPE_LABELS in build_slim.py

Boolean. TRUE if the platform is an EU-designated Very Large Online Platform (VLOP). Looked up from docs/platform_status.xlsx in the repo and baked into every slim row.

Boolean. TRUE for Bing and Google Search (the two designated Very Large Online Search Engines as of May 2026). Independent of is_vlop.

The platform's headquarters country. NULL if unknown for that platform.

The EU member state whose Digital Services Coordinator oversees the platform. Populated for all VLOPs/VLOSEs and for ~70% of other Online Platforms (those with a clearly documented EU establishment). NULL for the remainder.

Why: Lets users filter "platforms supervised by Ireland", "platforms under German oversight", etc. without external lookups.

source: docs/platform_status.xlsx (manually maintained, refreshed against the EU's designation page)

The "real" SoR count. Equal to 1 for exactly one row per SoR (the row corresponding to the alphabetically-first country in the SoR's territorial scope) and 0 for the others. Use this whenever the answer should be "how many SoRs?" — it gives the right number regardless of how the data is sliced, including when country is not in the GROUP BY.

Why the deduper: Because we expand each SoR into multiple rows (one per country), a naive count would multiply the total by the number of countries each SoR targets — about 14x on average, since most SoRs target all of EU+EEA. The sor_count column is designed so summing it always gives the true SoR count.

The "mentions" count: equal to 1 on every row. Sum it after grouping by country to get accurate per-country totals (the number of SoRs that target each country). Don't sum it ungrouped — the result will be inflated by territorial coverage.

Use: country maps, country-comparison bars, "actions affecting France" charts. Anything where the country dimension is part of the breakdown.