Documentation

Green Compliance Intelligence — Data Endeavour Ltd

Overview

Green Compliance Intelligence delivers enriched environmental compliance data covering 3.1M regulated facilities across the US, UK, and EU. Raw regulatory data is sourced from public registries, normalised to a consistent schema, and enriched with deterministic risk scores, AI-assisted entity resolution, and industry classification.

The dataset is updated weekly and delivered as five curated read-only views via Google Analytics Hub. Subscribers access the data as a linked dataset directly in their own GCP project — no ETL or data transfer required.

Access & setup

The dataset is delivered via Google Analytics Hub, which is part of Google Cloud. If you already use GCP, skip to step 3. If not, setup takes about five minutes and costs nothing — you only pay for BigQuery queries you run, which for typical usage is pennies per month.

Step 1 — Create a Google Cloud account

Go to console.cloud.google.com and sign in with any Google account (Gmail or Google Workspace). If this is your first time, Google will prompt you to activate Cloud — you'll need to enter a credit card, but there is a free tier and you will not be charged for simply subscribing to this dataset.

Step 2 — Create a project

In the top bar, click the project selector (it may say "Select a project") and choose New Project. Give it a name (e.g. compliance-data) and click Create. This is the container your linked dataset will live in.

Step 3 — Subscribe via Google Cloud Marketplace

Open the listing at Green Compliance Intelligence on Google Cloud Marketplace, choose a plan, and click Subscribe, selecting the project you created in Step 2. Entitlement is automatic — no approval wait. The five views will appear immediately in your BigQuery console under a linked dataset in your project.

Step 4 — Open BigQuery and run your first query

Go to console.cloud.google.com/bigquery. In the left-hand panel, expand your project and find the linked dataset. Click any view to see its schema. Click Query to open an editor — a sample SELECT is pre-populated. Click Run to see results.

Exporting data

Two options are available depending on how you want to work with the data. Neither requires writing any code or leaving your browser.

Option A — Google Sheets (recommended for most users)

Connected Sheets lets you pull BigQuery data directly into a Google Sheet and refresh it on demand — no SQL required beyond the initial query. This is the easiest path if you work in spreadsheets and want to filter, pivot, or chart the data alongside your own files.

1. Open Google Sheets and create a new spreadsheet.
2. In the menu bar, click Data → Data Connectors → Connect to BigQuery.
3. Select your GCP project, then find the linked dataset and choose a view (e.g. v_global_compliance_risk).
4. Click Connect. Sheets will load a preview — click Apply to import the data.
5. To filter before importing (recommended for large views), click Edit query and add a WHERE clause — for example WHERE country_code = 'GBR' to limit to UK facilities.
6. Once imported, click Refresh at any time to pull the latest weekly data.

Option B — CSV download from the BigQuery console

If you want a flat file to open in Excel or load into another tool, you can download query results directly from the BigQuery console as a CSV.

1. Go to console.cloud.google.com/bigquery and open a new query tab.
2. Write or paste a query (use the sample queries below as a starting point). Include a WHERE clause to scope the results — for example by country, risk category, or company name.
3. Click Run. Results appear in the panel below the editor.
4. Click Save results → CSV (local file). The file downloads immediately.

For result sets larger than around 16,000 rows, use Save results → Google Sheets instead, or export to Google Drive via Save results → Google Drive (CSV).

Sample queries

The examples below can be pasted directly into the BigQuery console. Replace your-gcp-project and your_linked_dataset with your project ID and the name you gave the linked dataset at subscription.

Screen Critical facilities in a country

View: v_global_compliance_risk

SELECT
  regulatory_id,
  facility_name,
  parent_company_name,
  city,
  predicted_industry,
  predicted_pollutant,
  severity_score,
  risk_category
FROM `your-gcp-project.your_linked_dataset.v_global_compliance_risk`
WHERE country_code = 'GBR'          -- ISO 3166-1 alpha-3: USA, GBR, DEU, FRA, ESP, ITA …
  AND operational_priority = 'Critical'
ORDER BY severity_score DESC
LIMIT 100;

Look up a company and retrieve its full compliance footprint

Views: v_dim_global_companies joined to v_global_compliance_risk

SELECT
  co.entity_name,
  co.business_status,
  co.is_lei_verified,
  cr.facility_name,
  cr.city,
  cr.country_code          AS facility_country,
  cr.risk_category,
  cr.severity_score,
  cr.operational_priority
FROM `your-gcp-project.your_linked_dataset.v_dim_global_companies` co
LEFT JOIN `your-gcp-project.your_linked_dataset.v_global_compliance_risk` cr
  ON UPPER(co.entity_name) = UPPER(cr.parent_company_name)
WHERE UPPER(co.entity_name) LIKE '%WASTE MANAGEMENT%'
ORDER BY cr.severity_score DESC NULLS LAST;

Rank the top EU pollutants for a given year

View: v_agg_emissions_summary

SELECT
  pollutant_name,
  ROUND(SUM(total_emissions) / 1e9, 2)   AS total_million_tonnes,
  COUNT(DISTINCT country_name)            AS countries_reporting,
  SUM(total_records)                      AS contributing_facilities
FROM `your-gcp-project.your_linked_dataset.v_agg_emissions_summary`
WHERE reporting_year = 2023
GROUP BY pollutant_name
ORDER BY total_million_tonnes DESC
LIMIT 10;

Find high-risk facilities within a geographic bounding box

View: v_geospatial_hotspots — example bounding box: Greater London

SELECT
  facility_name,
  parent_company_name,
  clean_city,
  latitude,
  longitude,
  risk_category,
  severity_score
FROM `your-gcp-project.your_linked_dataset.v_geospatial_hotspots`
WHERE latitude  BETWEEN 51.3 AND 51.7
  AND longitude BETWEEN -0.5 AND 0.3
  AND severity_score >= 5
ORDER BY severity_score DESC;

Due diligence screen — compare corporate parents by risk score

View: v_corporate_risk_summary

SELECT
  parent_company_name,
  total_facilities_managed,
  ROUND(avg_corporate_risk_score, 2)  AS avg_risk_score,
  highest_individual_facility_risk,
  critical_alert_count,
  watchlist_count,
  portfolio_pollutant_mix
FROM `your-gcp-project.your_linked_dataset.v_corporate_risk_summary`
WHERE parent_company_name IN (
  'Waste Management',
  'Arch Resources, Inc.',
  'Republic Services',
  'Clean Harbors'
)
ORDER BY avg_corporate_risk_score DESC;

v_global_compliance_risk

Source: US EPA ECHO, UK Environment Agency (permits + enforcement), EEA E-PRTR
Grain: One row per regulated facility
Coverage: USA (99%), UK, EU (DE, ES, FR, IT, PL, CZ, HU, DK, and others)

Risk category distribution

Known caveats

parent_company_name and matching_confidence are NULL for ~4% of rows — facilities where no corporate parent could be resolved. EEA E-PRTR facilities (the large majority of this gap) and a small number of fully-compliant, zero-violation facilities are excluded from entity resolution entirely by design — resolution is prioritised for risk-flagged facilities, not run against the full dataset. USA accounts for the majority of records due to EPA ECHO scale. city quality is lower for EU records from EEA E-PRTR.

v_dim_global_companies

Source: GLEIF (Global LEI Foundation), UK Companies House
Grain: One row per legal entity
Coverage: Global — 3.3M entities across all jurisdictions

Known caveats

99.99% of records are from GLEIF. UK Companies House records are supplementary. country_code is null for a small number of GLEIF records where the source jurisdiction field is not populated.

v_agg_emissions_summary

Source: EEA E-PRTR (European Pollutant Release and Transfer Register)
Grain: One row per country + pollutant + reporting year
Coverage: EU member states, years 2007–2024

Known caveats

Full EU E-PRTR dataset (~372K facility records) aggregated to 13.6K rows. total_emissions units are kilograms as reported — not normalised across substances. 2024 data is partial (facilities report with a ~12-month lag).

v_geospatial_hotspots

Source: US EPA ECHO, UK Environment Agency, EEA E-PRTR (coordinates via GeoNames postcode lookup)
Grain: One row per regulated facility with resolved coordinates
Coverage: 3.12M facilities — ~98% of all facilities in v_global_compliance_risk (~2% cannot be geocoded and are absent from this view entirely; rows present have 100% coordinate coverage)

Known caveats

Coordinates are postcode centroid-level, not facility-level. Precision is ±500m to ±5km depending on postcode area density. GeoNames coverage spans 21 countries. parent_company_name is NULL for ~4% of rows — see entity resolution note.

v_corporate_risk_summary

Grain: One row per resolved corporate parent entity
Coverage: 944K entities — one row per resolved corporate parent

This view provides a rolled-up risk profile per corporate parent, aggregating facility-level compliance data across all subsidiaries and operating sites where a parent entity has been resolved.

Risk score methodology — overview

The dataset produces four types of enrichment. The majority is deterministic and fully auditable. Gemini LLM is used only as a fallback in entity resolution.

Risk category and severity score

Columns: risk_category, severity_score, operational_priority

Produced by deterministic SQL rules applied to compliance status data from source registries (primarily US EPA ECHO). Fully transparent and auditable. No AI involved.

Risk category mapping

Severity score (1–10)

Operational priority

Limitations

Compliance status reflects what has been reported to the regulator, not independently verified. UK EA and EEA facilities have less granular violation count data than EPA ECHO — scores for these facilities may be less precise. A score of 4 (Status Unknown) does not indicate a compliant facility; it indicates insufficient data to assess compliance.

Industry and pollutant classification

Columns: predicted_industry, predicted_pollutant

Produced by deterministic rules applied in priority order:

1. EEA E-PRTR sector code (1=Energy, 2=Metals, 3=Minerals, 4=Chemical, 5=Waste, 6=Paper, 7=Agriculture, 8=Food)
2. UK EA activity type keyword matching
3. Facility name keyword matching (fallback for all sources without sector codes)

Industry categories: Energy & Power, Metal Production, Mineral Industry, Chemical Industry, Waste Management, Paper & Pulp, Agriculture, Food & Beverage, Oil & Gas, Other Industrial.

Limitations

Classification is best-effort inference with no manual review. Facility name keyword matching (the fallback) is less reliable than structured sector codes. ~0.002% of facilities have no facility name and cannot be classified.

Corporate parent linkage — entity resolution

Column: parent_company_name (in v_global_compliance_risk and v_geospatial_hotspots)

This is the only enrichment step that uses a large language model. Matching is attempted in three tiers:

Tier 1 — Exact SQL match

Facility name compared directly against the GLEIF Golden Copy LEI registry after trimming and uppercasing, matched on name and country code. Highest-confidence match.

Tier 2 — Cleaned SQL match

Legal suffixes (LLC, INC, LTD, PLC, CO, CORP) and punctuation stripped from both sides before comparison, matched on country code.

Tier 3 — Gemini 3.5 Flash (LLM fallback)

For facilities that fail Tiers 1 and 2, Gemini 3.5 Flash is prompted to identify the corporate parent company name only — it is never asked for the LEI code itself. Once a parent name is identified, the LEI is populated by looking that name up against the same GLEIF Golden Copy registry Tiers 1 and 2 use (exact match, then cleaned match), with no country restriction so a parent registered in a different country from the facility still resolves. If no real GLEIF record matches, the LEI is left blank rather than populated from the model's own text. EEA E-PRTR facilities are excluded from entity resolution entirely as they do not carry company-level identifiers. Entity resolution is also deliberately scoped to risk-flagged facilities — those with a non-compliant status or a recorded violation — so a small number of fully-compliant, zero-violation facilities are excluded from resolution too, prioritising Gemini throughput where corporate parent linkage matters most.

Limitations

The corporate parent name identified by Gemini is not guaranteed to be correct and should be verified for high-stakes use cases — the model can occasionally misattribute an obscure facility to a confidently-named but incorrect parent. The LEI code, where present, is always a genuine GLEIF registry match — it is never generated or guessed by the model. Name matching across jurisdictions is imperfect for facilities operating under trading names or subsidiary names. Private companies without LEIs may not be resolvable.

Known data issue — current dataset

A country code format mismatch between the internal pipeline and the GLEIF reference data caused Tiers 1 and 2 to silently produce no matches during the initial enrichment run, so every record was initially routed to Tier 3 (Gemini LLM). This was fixed in June 2026, and a retroactive correction on 4 June 2026 reclassified 68,472 records (2.2%) to Tier 1 or Tier 2; the remaining 97.8% of that initial run are confirmed Tier 3 matches. All facilities added since are processed through all three tiers in sequence. The entity_resolution_method column in v_global_compliance_risk reflects the actual method used for each record.

LEI verification fix (2026-09-01)

Prior to this date, Tier 3 asked Gemini to return the GLEIF LEI code directly from its own recall, alongside the parent company name. Validation during the gemini-2.5-flash → gemini-3.5-flash migration found this LEI recall was unreliable across both models — cross-checked against real GLEIF records, every non-null LEI ever produced this way was fabricated, regardless of which model produced it. The fix described in the Tier 3 section above was applied going forward, and a one-time correction was run against all historical Tier 3 records on 2026-09-01. lei_code coverage increased from ~14% to ~25% as a result, since the uniform lookup recovered genuine matches for some facilities the model had previously left blank.

Geocoordinates

Columns: latitude, longitude (in v_geospatial_hotspots)

Derived from a GeoNames postcode lookup table (1.8M entries covering UK, US, and EU postcodes). Facilities are matched on postal code and country code. Where multiple entries exist for the same postcode, the highest-accuracy entry is used. Approximately 2% of facilities cannot be geocoded and are absent from v_geospatial_hotspots.

Data sources

Update cadence

Data licences & attribution

All underlying data sources permit commercial redistribution. Required attributions when publishing derived work: